Chúng tôi đang thực hiện dự án di chuyển cơ sở dữ liệu MySQL từ máy chủ Linux tại chỗ sang máy ảo Windows trên Azure (IaaS). (Có một lý do cụ thể vì lý do đó chúng tôi đã sử dụng tùy chọn IaaS thay vì cung cấp Azure MySQL PaaS).
Sau khi di chuyển, chúng tôi thấy rằng các truy vấn trên cơ sở dữ liệu MySQL chậm hơn đáng kể (khoảng 10 lần) trên máy chủ mới. VM được cấu hình với 64 CPU và 256 GB RAM (VM tại chỗ có 48 CPU và 256 GB RAM).
Tất cả các bảng trong cơ sở dữ liệu đang sử dụng công cụ InnoDB. Chúng tôi đã đọc khá nhiều về độ chậm của truy vấn với các bảng InnoDB và hầu hết trong số đó dường như đang trỏ đến innodb_buffer_pool_size - mà tôi đã định cấu hình thành 185 GB (khoảng 70% tổng số RAM). Chúng tôi cũng đã thử thực hiện một số thay đổi khác trong my.ini cấu hình như thế nào
key_buffer_size = 20MB
innodb_io_abilities = 2000
truy vấn_cache_size = 0
query_cache_type = 0
tăng thread_cache_size
innodb_read_io_threads
innodb_write_io_threads
V.v. Nhưng dường như không có gì hữu ích với hiệu suất truy vấn.
Chúng tôi đã so sánh các chỉ mục trên cả hai máy chủ và chúng giống nhau. Và ở mức cao, có vẻ như các chỉ mục không bị hỏng trên Azure VM. Ngoài ra, chúng tôi đang cố gắng đo hiệu suất bằng cách chạy MySQL workbench bên trong Azure VM, vì vậy băng thông mạng không phải là vấn đề.
- Có ai có thể đề xuất bất kỳ tùy chọn nào khác mà chúng tôi có thể cố gắng cải thiện hiệu suất không?
Một vài điểm nữa.
- Những gì chúng tôi nhận thấy là mặc dù một số truy vấn mất hơn 30 phút để chạy, (dường như chúng đang chạy trong máy chủ tại chỗ chỉ trong 5 phút), mức sử dụng CPU trên VM vẫn rất thấp (dưới 10%).Có một số cài đặt như innodb_buffer_pool_size` để phân bổ một số lượng CPU cho Máy chủ MySQL không?
Như tôi đã đề cập trước đây, máy ảo tại chỗ dựa trên Linux và máy ảo Azure đang chạy trên Windows - đó có phải là vấn đề không? Tôi không thể tìm thấy bất kỳ bằng chứng rõ ràng nào cho thấy MySQL trên Windows sẽ gây ra sự suy giảm hiệu suất nghiêm trọng như vậy.
Cấu hình my.ini đầy đủ của tôi ở bên dưới:
# Các giá trị điều chỉnh mặc định khác
# Tệp cấu hình phiên bản máy chủ MySQL
# --------------------------------------------- ---------------------
# Được tạo bởi Trình hướng dẫn cấu hình phiên bản máy chủ MySQL
#
#
# Hướng dẫn cài đặt
# --------------------------------------------- ---------------------
#
# Trên Linux, bạn có thể sao chép tệp này vào /etc/my.cnf để đặt các tùy chọn chung,
# mysql-data-dir/my.cnf để đặt các tùy chọn dành riêng cho máy chủ
# (@localstatedir@ cho cài đặt này) hoặc để
# ~/.my.cnf để đặt các tùy chọn dành riêng cho người dùng.
#
# Trên Windows, bạn nên giữ tệp này trong thư mục cài đặt
# máy chủ của bạn (ví dụ: C:\Program Files\MySQL\MySQL Server X.Y). Đến
# đảm bảo máy chủ đọc tệp cấu hình, sử dụng tùy chọn khởi động
# "--defaults-tệp".
#
# Để chạy máy chủ từ dòng lệnh, hãy thực hiện điều này trong một
# vỏ dòng lệnh, ví dụ:
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# Để cài đặt máy chủ dưới dạng dịch vụ Windows theo cách thủ công, hãy thực hiện thao tác này trong
# vỏ dòng lệnh, ví dụ:
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# Và sau đó thực hiện điều này trong trình bao dòng lệnh để khởi động máy chủ, ví dụ:
# khởi động mạng MySQLXY
#
#
# Hướng dẫn sửa file này
# --------------------------------------------- ---------------------
#
# Trong tệp này, bạn có thể sử dụng tất cả các tùy chọn dài mà chương trình hỗ trợ.
# Nếu bạn muốn biết các tùy chọn mà chương trình hỗ trợ, hãy khởi động chương trình
# với tùy chọn "--help".
#
# Thông tin chi tiết hơn về các tùy chọn riêng lẻ cũng có thể được
# tìm thấy trong sách hướng dẫn.
#
# Để được tư vấn về cách thay đổi cài đặt, vui lòng xem
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# PHẦN KHÁCH HÀNG
# --------------------------------------------- ---------------------
#
# Các tùy chọn sau sẽ được đọc bởi các ứng dụng máy khách MySQL.
# Lưu ý rằng chỉ các ứng dụng khách do MySQL vận chuyển mới được đảm bảo
# để đọc phần này. Nếu bạn muốn chương trình máy khách MySQL của riêng bạn
# tôn vinh những giá trị này, bạn cần chỉ định nó như một tùy chọn trong quá trình
# Khởi tạo thư viện máy khách MySQL.
#
[khách hàng]
#ống=
# ổ cắm=MYSQL
cổng = 3306
[mysql]
không có tiếng bíp
# bộ ký tự mặc định=
# PHẦN MÁY CHỦ
# --------------------------------------------- ---------------------
#
# Các tùy chọn sau sẽ được đọc bởi Máy chủ MySQL. Đảm bảo rằng
# bạn đã cài đặt máy chủ đúng cách (xem bên trên) nên nó đọc cái này
# tập tin.
#
# server_type=1
[mysqld]
plugin-load-add=validate_password.dll
xác thực-mật khẩu=FORCE_PLUS_PERMANENT
# Ba tùy chọn tiếp theo loại trừ lẫn nhau đối với SERVER_PORT bên dưới.
# bỏ qua mạng
# enable-name-pipe
# chia sẻ bộ nhớ
# shared-memory-base-name=MYSQL
# Đường ống mà Máy chủ MySQL sẽ sử dụng
# ổ cắm=MYSQL
# Cổng TCP/IP mà Máy chủ MySQL sẽ lắng nghe
cổng = 3306
# Đường dẫn đến thư mục cài đặt. Tất cả các đường dẫn thường được giải quyết liên quan đến điều này.
# basedir="C:/Program Files/MySQL/MySQL Server 5.7/"
# Đường dẫn đến thư mục gốc của cơ sở dữ liệu
datadir=F:\MySQL\Dữ liệu
# Bộ ký tự mặc định sẽ được sử dụng khi một lược đồ hoặc bảng mới được
# đã tạo và không có bộ ký tự nào được xác định
# ký tự-bộ-máy chủ=
# Công cụ lưu trữ mặc định sẽ được sử dụng khi tạo bảng mới khi
default-storage-engine=INNODB
# Đặt chế độ SQL thành nghiêm ngặt
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Ghi nhật ký chung và chậm.
log-output=TỆP
nhật ký chung = 0
general_log_file="ZEUWPJIRA01.log"
truy vấn chậm-log=1
slow_query_log_file="ZEUWPJIRA01-slow.log"
long_query_time=10
# Lỗi đăng nhập.
log-error="ZEUWPJIRA01.err"
# ***** Bản sao nhóm liên quan *****
# Chỉ định tên cơ sở để sử dụng cho tệp nhật ký nhị phân. Với ghi nhật ký nhị phân
# đã bật, máy chủ ghi lại tất cả các câu lệnh thay đổi dữ liệu thành nhị phân
# nhật ký, được sử dụng để sao lưu và sao chép.
# log-bin
# ***** Bản sao nhóm liên quan *****
# Chỉ định ID máy chủ. Đối với các máy chủ được sử dụng trong cấu trúc liên kết sao chép,
# bạn phải chỉ định một ID máy chủ duy nhất cho mỗi máy chủ sao chép, trong
# phạm vi từ 1 đến 2^32 â 1. âUniqueâ có nghĩa là mỗi ID phải khác nhau
# từ mọi ID khác đang được sử dụng bởi bất kỳ nguồn sao chép hoặc bản sao nào khác.
máy chủ-id=26
# ***** Bản sao nhóm liên quan *****
# Tên máy chủ hoặc địa chỉ IP của bản sao sẽ được báo cáo cho nguồn
# trong quá trình đăng ký bản sao. Giá trị này xuất hiện trong đầu ra của SHOW SLAVE HOSTS
# trên máy chủ nguồn. Không đặt giá trị này nếu bạn không muốn bản sao
# tự đăng ký với nguồn.
# report_host=0.0
# ***** Bản sao nhóm liên quan *****
# Xác định thuật toán được sử dụng để băm ghi trích xuất trong một giao dịch. nếu bạn
# đang sử dụng Sao chép nhóm, biến này phải được đặt thành XHASH64 vì quá trình
# trích xuất ghi từ một giao dịch là cần thiết để phát hiện xung đột trên tất cả
# thành viên nhóm.
# giao dịch_write_set_extract=0.0
Lower_case_table_names=1
# Bảo mật tệp riêng tư.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
# Số phiên đồng thời tối đa mà máy chủ MySQL sẽ
# cho phép. Một trong những kết nối này sẽ được dành riêng cho người dùng có
# SIÊU đặc quyền cho phép quản trị viên đăng nhập ngay cả khi
# đã đạt đến giới hạn kết nối.
#max_connections=151
#[29062021]do Sridharan thay đổi để tăng số phiên đồng thời tối đa
max_connections=1000
#[29062021]do Sridharan thay đổi để tăng lỗi kết nối tối đa
max_connect_errors=500
# Số lượng bảng mở cho tất cả các chủ đề. Tăng giá trị này
# tăng số lượng bộ mô tả tệp mà mysqld yêu cầu.
# Do đó, bạn phải đảm bảo đặt số lượng tệp đang mở
# cho phép ít nhất 4096 trong biến "open-files-limit" trong
# phần [mysqld_safe]
#table_open_cache=2000
table_open_cache =2048
table_definition_cache = 2048
myisam_sort_buffer_size = 8M
# bỏ qua khóa bên ngoài
# Kích thước tối đa cho các bảng tạm thời bên trong (trong bộ nhớ). Nếu một cái bàn
# phát triển lớn hơn giá trị này, nó sẽ tự động được chuyển đổi vào đĩa
# dựa trên bảng Giới hạn này dành cho một bảng duy nhất. Có thể có nhiều
# của họ.
tmp_table_size=4G
# Có bao nhiêu chủ đề chúng ta nên giữ trong bộ đệm để sử dụng lại. Khi một khách hàng
# ngắt kết nối, các luồng của khách hàng sẽ được đưa vào bộ đệm nếu không có
# nhiều hơn các chuỗi thread_cache_size trước đó. Điều này làm giảm đáng kể
# số lượng tạo chủ đề cần thiết nếu bạn có nhiều chủ đề mới
# kết nối. (Thông thường điều này không mang lại hiệu suất đáng chú ý
# cải thiện nếu bạn triển khai chuỗi tốt.)
# thread_cache_size=10
thread_cache_size=64
truy vấn_cache_size = 0
query_cache_type = 0
#*** MyISAM Tùy chọn cụ thể
# Kích thước tối đa của tệp tạm thời mà MySQL được phép sử dụng trong khi
# tạo lại chỉ mục (trong quá trình REPAIR, ALTER TABLE hoặc LOAD DATA INFILE.
# Nếu kích thước tệp lớn hơn mức này, chỉ mục sẽ được tạo
# thông qua bộ đệm chính (chậm hơn).
myisam_max_sort_file_size=100G
# Kích thước của bộ đệm được phân bổ khi sắp xếp các chỉ mục MyISAM
# trong REPAIR TABLE hoặc khi tạo chỉ mục với CREATE INDEX
# hoặc THAY ĐỔI BẢNG.
myisam_sort_buffer_size=6G
# Kích thước của Bộ đệm khóa, được sử dụng để lưu trữ các khối chỉ mục cho các bảng MyISAM.
# Không đặt nó lớn hơn 30% bộ nhớ khả dụng của bạn, vì một số bộ nhớ
# cũng được hệ điều hành yêu cầu lưu trữ các hàng.Ngay cả khi bạn không sử dụng
# Các bảng MyISAM, bạn vẫn nên đặt thành 8-64M vì nó cũng sẽ như vậy
# được sử dụng cho các bảng đĩa tạm thời bên trong.
key_buffer_size=20M
# Kích thước của bộ đệm được sử dụng để thực hiện quét toàn bộ bảng MyISAM.
# Được phân bổ cho mỗi luồng, nếu cần quét toàn bộ.
read_buffer_size=64K
read_rnd_buffer_size=256K
tmp_table_size=64M
max_heap_table_size=64M
#*** Tùy chọn cụ thể của INNODB ***
# innodb_data_home_dir=
# Sử dụng tùy chọn này nếu bạn có máy chủ MySQL hỗ trợ InnoDB
# nhưng bạn không định sử dụng nó. Điều này sẽ tiết kiệm bộ nhớ và không gian đĩa
# và tăng tốc một số thứ.
# bỏ qua-innodb
# Nếu được đặt thành 1, InnoDB sẽ xóa (fsync) nhật ký giao dịch vào
# đĩa tại mỗi lần xác nhận, cung cấp hành vi ACID đầy đủ. Nếu bạn là
# sẵn sàng thỏa hiệp sự an toàn này và bạn đang chạy nhỏ
# giao dịch, bạn có thể đặt giá trị này thành 0 hoặc 2 để giảm I/O đĩa xuống
# nhật ký. Giá trị 0 có nghĩa là nhật ký chỉ được ghi vào tệp nhật ký và
# tệp nhật ký được xóa vào đĩa khoảng một lần mỗi giây. Giá trị 2
# có nghĩa là nhật ký được ghi vào tệp nhật ký ở mỗi lần xác nhận, nhưng nhật ký
# tệp chỉ được xóa vào đĩa khoảng một lần mỗi giây.
innodb_flush_log_at_trx_commit=2
# Kích thước của bộ đệm mà InnoDB sử dụng để đệm dữ liệu nhật ký. Càng sớm càng
# đã đầy, InnoDB sẽ phải xóa nó vào đĩa. Khi nó đỏ bừng
# dù sao một lần mỗi giây, sẽ không có ý nghĩa gì nếu nó rất lớn
# (ngay cả với các giao dịch dài).
innodb_log_buffer_size=200M
# InnoDB, không giống như MyISAM, sử dụng vùng đệm để lưu trữ cả chỉ mục và
# hàng dữ liệu. Bạn đặt cái này càng lớn thì càng cần ít đĩa I/O hơn
# truy cập dữ liệu trong bảng. Trên một máy chủ cơ sở dữ liệu chuyên dụng, bạn có thể thiết lập
# tham số lên tới 80% kích thước bộ nhớ vật lý của máy. Đừng đặt nó
Tuy nhiên, # quá lớn vì sự cạnh tranh của bộ nhớ vật lý có thể
# gây phân trang trong hệ điều hành. Lưu ý rằng trên các hệ thống 32 bit, bạn
# có thể bị giới hạn ở 2-3,5G bộ nhớ cấp người dùng cho mỗi quy trình, vì vậy đừng
# đặt nó quá cao.
#[29062021]do Sridharan thay đổi để tăng kích thước nhóm bộ đệm innodb từ 16G lên 24G
#[06072021]do Sridharan thay đổi để tăng kích thước nhóm bộ đệm innodb từ 24G lên 48G
innodb_buffer_pool_size=185G
# Kích thước của mỗi tệp nhật ký trong một nhóm nhật ký. Bạn nên đặt kích thước kết hợp
# tệp nhật ký vào khoảng 25% -100% kích thước nhóm bộ đệm của bạn để tránh
# hoạt động xóa nhóm bộ đệm không cần thiết khi ghi đè lên tệp nhật ký. Tuy nhiên,
# lưu ý rằng kích thước tệp nhật ký lớn hơn sẽ tăng thời gian cần thiết cho
# Quá trình phục hồi.
innodb_log_file_size=4G
# Số luồng được phép bên trong nhân InnoDB. giá trị tối ưu
# phụ thuộc nhiều vào ứng dụng, phần cứng cũng như HĐH
# thuộc tính bộ lập lịch. Giá trị quá cao có thể dẫn đến hỏng luồng.
#innodb_thread_concurrency=17
innodb_thread_concurrency=32
# Kích thước gia tăng (tính bằng MB) để mở rộng kích thước của tệp vùng bảng hệ thống InnoDB tự động mở rộng khi tệp đầy.
innodb_autoextend_increment=64
# Số vùng mà nhóm bộ đệm InnoDB được chia thành.
# Đối với các hệ thống có vùng đệm trong phạm vi nhiều gigabyte, việc chia vùng đệm thành các phiên bản riêng biệt có thể cải thiện tính đồng thời,
# bằng cách giảm tranh chấp khi các chủ đề khác nhau đọc và ghi vào các trang được lưu trong bộ nhớ cache.
#innodb_buffer_pool_instances=8
#[29062021 Sridharan] đã tăng số phiên bản vùng đệm từ 8 lên 12
innodb_buffer_pool_instances=12
#[29062021 Sridharan] đã thêm cấu hình io_abilities, read_io_threads và write_io_threads để tăng tốc truy vấn
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_abilities = 2000
innodb_io_capital_max = 4000
# Xác định số luồng có thể vào InnoDB đồng thời.
innodb_concurrency_tickets=5000
# Chỉ định khoảng thời gian tính bằng mili giây (ms) mà một khối được chèn vào danh sách con cũ phải ở đó sau lần truy cập đầu tiên trước đó
# nó có thể được chuyển sang danh sách con mới.
innodb_old_blocks_time=1000
# Nó chỉ định số lượng tệp .ibd tối đa mà MySQL có thể tiếp tục mở cùng một lúc. Giá trị tối thiểu là 10.
innodb_open_files=300
# Khi bật biến này, InnoDB sẽ cập nhật số liệu thống kê trong các câu lệnh siêu dữ liệu.
innodb_stats_on_metadata=0
# Khi bật innodb_file_per_table (mặc định trong 5.6.6 trở lên), InnoDB lưu trữ dữ liệu và chỉ mục cho từng bảng mới tạo
# trong một tệp .ibd riêng biệt, thay vì trong vùng bảng hệ thống.
innodb_file_per_table=1
# Sử dụng danh sách các giá trị sau: 0 cho crc32, 1 chostrict_crc32, 2 cho innodb, 3 chostrict_innodb, 4 cho none, 5 chostrict_none.
innodb_checksum_algorithm=0
# Số lượng yêu cầu kết nối chưa xử lý mà MySQL có thể có.
# Tùy chọn này hữu ích khi chuỗi MySQL chính nhận được nhiều yêu cầu kết nối trong một thời gian rất ngắn.
# Sau đó, sẽ mất một khoảng thời gian (mặc dù rất ít) để luồng chính kiểm tra kết nối và bắt đầu một luồng mới.
# Giá trị back_log cho biết có bao nhiêu yêu cầu có thể được xếp chồng lên nhau trong thời gian ngắn này trước MySQL trong giây lát
# ngừng trả lời các yêu cầu mới.
# Bạn chỉ cần tăng mức này nếu bạn mong đợi một số lượng lớn kết nối trong một khoảng thời gian ngắn.
back_log=80
# Nếu giá trị này được đặt thành giá trị khác không, tất cả các bảng sẽ được đóng sau mỗi giây flush_time để giải phóng tài nguyên và
# đồng bộ hóa dữ liệu chưa được xóa vào đĩa.
# Tùy chọn này chỉ được sử dụng tốt nhất trên các hệ thống có tài nguyên tối thiểu.
flush_time=0
# Kích thước tối thiểu của bộ đệm được sử dụng để quét chỉ mục đơn giản, quét chỉ mục phạm vi và liên kết không sử dụng
# chỉ mục và do đó thực hiện quét toàn bộ bảng.
tham gia_buffer_size=256K
# Kích thước tối đa của một gói hoặc bất kỳ chuỗi trung gian hoặc chuỗi nào được tạo hoặc bất kỳ tham số nào được gửi bởi
# mysql_stmt_send_long_data() Hàm API C.
max_allowed_packet=1024M
# Nếu nhiều hơn mức này, nhiều yêu cầu kết nối liên tiếp từ máy chủ bị gián đoạn mà không có kết nối thành công,
# máy chủ chặn máy chủ đó thực hiện các kết nối tiếp theo.
max_connect_errors=100
# Thay đổi số lượng bộ mô tả tệp có sẵn cho mysqld.
# Bạn nên thử tăng giá trị của tùy chọn này nếu mysqld báo lỗi "Quá nhiều tệp đang mở".
open_files_limit=4161
# Nếu bạn thấy nhiều sort_merge_passes mỗi giây trong đầu ra SHOW GLOBAL STATUS, bạn có thể xem xét tăng
# giá trị sort_buffer_size để tăng tốc các thao tác ORDER BY hoặc GROUP BY không thể cải thiện bằng tối ưu hóa truy vấn
# hoặc lập chỉ mục được cải thiện.
sort_buffer_size=256K
# Số lượng định nghĩa bảng (từ tệp .frm) có thể được lưu trữ trong bộ đệm ẩn định nghĩa.
# Nếu bạn sử dụng nhiều bảng, bạn có thể tạo bộ nhớ đệm định nghĩa bảng lớn để tăng tốc độ mở bảng.
# Bộ đệm định nghĩa bảng chiếm ít không gian hơn và không sử dụng bộ mô tả tệp, không giống như bộ đệm bảng thông thường.
# Giá trị tối thiểu và mặc định đều là 400.
table_definition_cache=1400
# Chỉ định kích thước tối đa của sự kiện nhật ký nhị phân dựa trên hàng, tính bằng byte.
# Hàng được nhóm thành các sự kiện nhỏ hơn kích thước này nếu có thể. Giá trị phải là bội số của 256.
binlog_row_event_max_size=8K
# Nếu giá trị của biến này lớn hơn 0, một bản sao sẽ đồng bộ hóa tệp master.info của nó với đĩa.
# (dùng fdatasync()) sau mỗi sự kiện sync_master_info.
sync_master_info=10000
# Nếu giá trị của biến này lớn hơn 0, máy chủ MySQL sẽ đồng bộ hóa nhật ký chuyển tiếp của nó với đĩa.
# (dùng fdatasync()) sau mỗi lần sync_relay_log ghi vào nhật ký chuyển tiếp.
sync_relay_log=10000
# Nếu giá trị của biến này lớn hơn 0, một bản sao sẽ đồng bộ hóa tệp relay-log.info của nó với đĩa.
# (sử dụng fdatasync()) sau mỗi giao dịch sync_relay_log_info.
sync_relay_log_info=10000
# Tải plugin mysql khi bắt đầu."plugin_x ; plugin_y".
# plugin_load
# Cổng TCP/IP mà Giao thức MySQL Server X sẽ lắng nghe.
# loose_mysqlx_port=33060
# relay-log=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-relay-bin.log
relay-log=F:\MySQL\Data\mysql-relay-bin.log
# log_bin=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-bin.log
log_bin=F:\MySQL\Data\mysql-bin.log
binlog_do_db=jira_datacenter
binlog_format=HỖN HỢP
