Hướng dẫn which version of mysql is best? - phiên bản nào của mysql là tốt nhất?

MySQL 8.0 đã mang đến những thay đổi và sửa đổi rất lớn được thúc đẩy bởi nhóm Oracle MySQL. Các tập tin vật lý đã được thay đổi. Ví dụ, *.frm, *.trg, *.trn và *.Par không còn tồn tại. Hàng tấn các tính năng mới đã được thêm vào như CTE (biểu thức bảng phổ biến), các chức năng cửa sổ, chỉ mục vô hình, regexp (hoặc biểu thức chính quy) sau đó đã được thay đổi và hiện cung cấp hỗ trợ unicode đầy đủ và an toàn cho multibyte. Từ điển dữ liệu cũng đã thay đổi. Bây giờ, nó được kết hợp với một từ điển dữ liệu giao dịch lưu trữ thông tin về các đối tượng cơ sở dữ liệu. Không giống như các phiên bản trước, dữ liệu từ điển được lưu trữ trong các tệp siêu dữ liệu và các bảng không chuyển đổi. Bảo mật đã được cải thiện với việc bổ sung mới của bộ nhớ đệm_sha2_password hiện là xác thực mặc định thay thế mysql_native_password và cung cấp tính linh hoạt hơn nhưng được thắt chặt bảo mật phải sử dụng kết nối an toàn hoặc kết nối không được mã hóa hỗ trợ trao đổi mật khẩu bằng cách sử dụng cặp khóa RSA.caching_sha2_password which is now the default authentication replacing mysql_native_password and offers more flexibility but tightened security which must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair.

Với tất cả các tính năng thú vị, các cải tiến, cải tiến mà MySQL 8.0 cung cấp, nhóm của chúng tôi đã quan tâm để xác định phiên bản hiện tại MySQL 8.0 thực hiện đặc biệt là hỗ trợ của chúng tôi cho các phiên bản MySQL 8.0.x trong ClusterControl đang diễn ra (vì vậy hãy theo dõi về điều này). Bài đăng trên blog này đã giành chiến thắng khi thảo luận về các tính năng của MySQL 8.0, nhưng dự định sẽ đánh giá hiệu suất của nó so với MySQL 5.7 và xem nó đã được cải thiện như thế nào sau đó.

Thiết lập và môi trường máy chủ

Đối với điểm chuẩn này, tôi dự định sử dụng thiết lập tối thiểu để sản xuất bằng môi trường AWS EC2 sau:

Loại ví dụ: T2.xlarge Storage Storage: GP2 (Lưu trữ SSD với tối thiểu 100 và tối đa là 16000 IOPS) VCPUS: 4 Bộ nhớ: 16GIB MySQL 5.7 Phiên bản: Máy chủ cộng đồng MySQL (GPL) 5.7.24 MySQL 8.0 Phiên bản: MySQL Community Server - GPL 8.0,0,14
Storage: gp2 (SSD storage with minimum of 100 and maximum of 16000 IOPS)
vCPUS: 4
Memory: 16GiB
MySQL 5.7 version: MySQL Community Server (GPL) 5.7.24
MySQL 8.0 version: MySQL Community Server – GPL 8.0.14

Có một vài biến đáng chú ý mà tôi đã đặt cho điểm chuẩn này, đó là:

• innodb_max_dirty_pages_pct = 90 ## Đây là giá trị mặc định trong MySQL 8.0. Xem ở đây để biết chi tiết.
• innodb_max_dirty_pages_pct_lwm = 10 ## Đây là giá trị mặc định trong MySQL 8.0
• innodb_flush_neighbors=0
• innodb_buffer_pool_instances=8
• innodb_buffer_pool_size=8GiB

Phần còn lại của các biến được đặt ở đây cho cả hai phiên bản (MySQL 5.7 và MySQL 8.0) được ClusterControl điều chỉnh cho mẫu My.cnf.

Ngoài ra, người dùng tôi đã sử dụng ở đây không phù hợp với xác thực mới của MySQL 8.0, sử dụng bộ nhớ đệm_sha2_password. Thay vào đó, cả hai phiên bản máy chủ đều sử dụng mysql_native_password plus innodb_dedicdy_server biến bị tắt (mặc định), đây là một tính năng mới của MySQL 8.0.caching_sha2_password. Instead, both server versions uses mysql_native_password plus innodb_dedicated_server variable is OFF (default), which is a new feature of MySQL 8.0.

Để làm cho cuộc sống dễ dàng hơn, tôi thiết lập nút phiên bản cộng đồng MySQL 5.7 với ClusterControl từ một máy chủ riêng sau đó đã xóa nút trong một cụm và tắt máy chủ ClusterControl để làm cho nút MySQL 5.7 không hoạt động (không có lưu lượng truy cập). Về mặt kỹ thuật, cả hai nút MySQL 5.7 và MySQL 8.0 đều không hoạt động và không có kết nối hoạt động nào đi qua các nút, do đó, nó về cơ bản là một bài kiểm tra điểm chuẩn thuần túy.

Các lệnh và tập lệnh được sử dụng

Đối với nhiệm vụ này, Sysbench được sử dụng để thử nghiệm và mô phỏng tải cho hai môi trường. Dưới đây là các lệnh hoặc tập lệnh sau đây đang được sử dụng trong bài kiểm tra này:

sb-prepare.sh

#!/bin/bash

host=$1
#host192.168.10.110
port=3306
user='sysbench'
password='[email protected]'
table_size=500000
rate=20
ps_mode='disable'
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --threads=1 --max-requests=0 --time=3600 --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --tables=10 --report-interval=1 --skip-trx=on --table-size=$table_size --rate=$rate --db-ps-mode=$ps_mode prepare

sb-run.sh

#!/usr/bin/env bash

host=$1
port=3306
user="sysbench"
password="[email protected]"
table_size=100000
tables=10
rate=20
ps_mode='disable'
threads=1
events=0
time=5
trx=100
path=$PWD

counter=1

echo "thread,cpu" > ${host}-cpu.csv

for i in 16 32 64 128 256 512 1024 2048; 
do 

    threads=$i

    mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
    tmpfile=$path/${host}-tmp${threads}
    touch $tmpfile
    /bin/bash cpu-checker.sh $tmpfile $host $threads &

    /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --events=$events --threads=$threads --time=$time --mysql-host=$host --mysql-user=$user --mysql-password=$password --mysql-port=$port --report-interval=1 --skip-trx=on --tables=$tables --table-size=$table_size --rate=$rate --delete_inserts=$trx --order_ranges=$trx --range_selects=on --range-size=$trx --simple_ranges=$trx --db-ps-mode=$ps_mode --mysql-ignore-errors=all run | tee -a $host-sysbench.log

    echo "${i},"`cat ${tmpfile} | sort -nr | head -1` >> ${host}-cpu.csv
    unlink ${tmpfile}

    mysql -h $host -e "SHOW GLOBAL STATUS" >> $host-global-status.log
done

python $path/innodb-ops-parser.py $host

mysql -h $host -e "SHOW GLOBAL VARIABLES" >> $host-global-vars.log

Vì vậy, tập lệnh chỉ đơn giản là chuẩn bị cho lược đồ SBTest và điền các bảng và hồ sơ. Sau đó, nó thực hiện kiểm tra tải/ghi bằng cách sử dụng/lớn /share/sysbench/oltp_read_write.lua. Kịch bản bỏ các biến trạng thái toàn cầu và các biến MySQL, thu thập sử dụng CPU và phân tích các hoạt động hàng Innodb được xử lý bởi Script Innodb-Ops-Parser.py. Các tập lệnh sau đó tạo các tệp *.csv dựa trên các nhật ký bị bỏ được thu thập trong điểm chuẩn, sau đó tôi đã sử dụng bảng tính Excel ở đây để tạo biểu đồ từ các tệp *.csv. Vui lòng kiểm tra mã ở đây trong kho Github này.

Bây giờ, hãy để tiến hành kết quả biểu đồ!

Hoạt động hàng Innodb

Về cơ bản ở đây, tôi chỉ trích xuất các hoạt động hàng Innodb thực hiện các lựa chọn (đọc), xóa, chèn và cập nhật. Khi số lượng chủ đề tăng lên, MySQL 8.0 vượt trội đáng kể hơn MySQL 5.7! Cả hai phiên bản không có bất kỳ thay đổi cấu hình cụ thể nào, nhưng chỉ các biến đáng chú ý tôi đã đặt. Vì vậy, cả hai phiên bản đều sử dụng khá nhiều giá trị mặc định.

Điều thú vị là, liên quan đến các yêu cầu của nhóm máy chủ MySQL về hiệu suất của các lần đọc và ghi trong phiên bản mới, các biểu đồ chỉ ra một cải tiến hiệu suất đáng kể, đặc biệt là trong một máy chủ tải cao. Hãy tưởng tượng sự khác biệt giữa MySQL 5.7 so với MySQL 8.0 cho tất cả các hoạt động hàng innodb của nó, có một sự khác biệt cao đặc biệt là khi số lượng chủ đề tăng lên. MySQL 8.0 tiết lộ rằng nó có thể thực hiện hiệu quả bất kể khối lượng công việc của nó.

Giao dịch xử lý

Như được hiển thị trong biểu đồ trên, hiệu suất MySQL 8.0 hiển thị một lần nữa một sự khác biệt lớn trong thời gian cần thiết để xử lý các giao dịch. Càng thấp, nó càng hoạt động tốt hơn, điều đó có nghĩa là nó nhanh hơn để xử lý các giao dịch. Các giao dịch được xử lý (biểu đồ thứ hai) cũng tiết lộ rằng cả hai số giao dịch không khác nhau. Có nghĩa là, cả hai phiên bản thực hiện gần như cùng một số lượng giao dịch nhưng khác nhau về mức độ nhanh chóng có thể kết thúc. Mặc dù tôi có thể nói, MySQL 5.7 vẫn có thể xử lý rất nhiều ở mức tải thấp hơn, nhưng tải trọng thực tế, đặc biệt là trong sản xuất có thể được dự kiến ​​sẽ cao hơn - đặc biệt là giai đoạn bận rộn nhất.

Biểu đồ ở trên vẫn hiển thị các giao dịch mà nó có thể xử lý nhưng tách các lần đọc với các chữ viết. Tuy nhiên, có những ngoại lệ thực sự trong các biểu đồ mà tôi đã bao gồm khi chúng là những mẩu tin nhỏ của kết quả sẽ làm lệch biểu đồ.

MySQL 8.0 cho thấy một cải tiến tuyệt vời đặc biệt là để đọc. Nó hiển thị hiệu quả của nó trong ghi đặc biệt cho các máy chủ có khối lượng công việc cao. Một số hỗ trợ bổ sung tuyệt vời ảnh hưởng đến hiệu suất MySQL cho các lần đọc trong phiên bản 8.0 là khả năng tạo một chỉ mục theo thứ tự giảm dần (hoặc quét chỉ mục chuyển tiếp). Các phiên bản trước chỉ có quét chỉ mục tăng dần hoặc lùi và MySQL phải thực hiện FileSort nếu cần một thứ tự giảm dần (nếu cần tập tin, bạn có thể xem xét kiểm tra giá trị của max_length_for_sort_data). Các chỉ mục giảm dần cũng giúp trình tối ưu hóa có thể sử dụng các chỉ mục nhiều cột khi thứ tự quét hiệu quả nhất trộn lẫn thứ tự tăng dần cho một số cột và thứ tự giảm dần cho người khác. Xem ở đây để biết thêm chi tiết.

Tài nguyên CPU

Trong quá trình điểm chuẩn này, tôi quyết định lấy một số tài nguyên phần cứng, đáng chú ý nhất là việc sử dụng CPU.

Hãy để tôi giải thích trước làm thế nào tôi lấy tài nguyên CPU ở đây trong quá trình điểm chuẩn. Sysbench không bao gồm số liệu thống kê tập thể cho các tài nguyên phần cứng được sử dụng hoặc sử dụng trong quá trình khi bạn đang điểm chuẩn cơ sở dữ liệu. Do đó, những gì tôi đã làm là tạo một lá cờ bằng cách tạo một tệp, kết nối với máy chủ đích thông qua SSH, sau đó thu hoạch dữ liệu từ Linux Command Top Top và phân tích nó trong khi ngủ một giây trước khi thu thập lại. Sau đó, thực hiện tăng sử dụng CPU nổi bật nhất cho quy trình MySQLD và sau đó xóa tệp cờ. Bạn có thể xem lại mã ở đó tôi có trong GitHub.mysqld process and then remove the flag file. You can review the code there I have in github.

Vì vậy, hãy để thảo luận lại về kết quả biểu đồ, dường như tiết lộ rằng MySQL 8.0 tiêu thụ rất nhiều CPU. Nhiều hơn MySQL 5.7. Tuy nhiên, nó có thể phải đối phó với các biến mới được thêm vào trong MySQL 8.0. Ví dụ: các biến này có thể ảnh hưởng đến máy chủ MySQL 8.0 của bạn:

• innodb_log_spin_cpu_abs_lwm = 80
• innodb_log_spin_cpu_pct_hwm = 50
• innodb_log_wait_for_flush_spin_hwm = 400
• innodb_parallel_read_threads = 4

Các biến có giá trị của nó được để lại bởi các giá trị mặc định của nó cho điểm chuẩn này. Ba biến đầu tiên xử lý CPU để đăng nhập lại, trong MySQL 8.0 là một cải tiến do thiết kế lại cách Innodb viết vào nhật ký làm lại. Biến innodb_log_spin_cpu_pct_hwm có ái lực CPU, điều đó có nghĩa là nó sẽ bỏ qua các lõi CPU khác nếu MySQLD chỉ được ghim đến 4 lõi. Đối với các luồng đọc song song, trong MySQL 8.0, nó thêm một biến mới mà bạn có thể điều chỉnh số lượng chủ đề được sử dụng.innodb_log_spin_cpu_pct_hwm has CPU affinity, which means it would ignore other CPU cores if mysqld is pinned only to 4 cores, for instance. For parallel read threads, in MySQL 8.0, it adds a new variable for which you can tune how many threads to used.

Tuy nhiên, tôi đã không đào sâu vào chủ đề. Có thể có những cách mà hiệu suất có thể được cải thiện bằng cách tận dụng các tính năng mà MySQL 8.0 cung cấp.

Sự kết luận

Có rất nhiều cải tiến có trong MySQL 8.0. Kết quả điểm chuẩn cho thấy đã có một sự cải thiện ấn tượng, không chỉ trong việc quản lý khối lượng công việc đọc mà còn về khối lượng công việc đọc/ghi cao so với MySQL 5.7.

Đi qua các tính năng mới mà MySQL 8.0, có vẻ như nó đã tận dụng lợi thế của các công nghệ cập nhật nhất không chỉ trên phần mềm (như cải tiến tuyệt vời cho memcached, quản lý từ xa cho công việc DevOps tốt hơn, v.v.) mà còn Cũng trong phần cứng. Ví dụ, việc thay thế Latin1 bằng UTF8MB4 làm mã hóa ký tự mặc định. Điều này có nghĩa là nó sẽ yêu cầu nhiều không gian đĩa hơn vì UTF8 cần 2 byte trên các ký tự không phải là Hoa Kỳ. Mặc dù điểm chuẩn này không tận dụng phương thức xác thực mới với bộ nhớ đệm_sha2_password, nhưng nó đã giành được hiệu suất ảnh hưởng đến hiệu suất cho dù nó sử dụng mã hóa. Khi nó được xác thực, nó được lưu trữ trong bộ đệm, có nghĩa là xác thực chỉ được thực hiện một lần. Vì vậy, nếu bạn đang sử dụng một người dùng cho khách hàng của mình, thì nó sẽ là một vấn đề và an toàn hơn các phiên bản trước.caching_sha2_password, it won’t affect performance whether it uses encryption. Once it’s authenticated, it is stored in cache which means authentication is only done once. So if you are using one user for your client, it won’t be a problem and is more secure than the previous versions.

Vì MySQL tận dụng phần cứng và phần mềm cập nhật nhất, nên nó thay đổi các biến mặc định của nó. Bạn có thể đọc ở đây để biết thêm chi tiết.

Nhìn chung, MySQL 8.0 đã thống trị MySQL 5.7 một cách hiệu quả.

Đăng ký để có được nội dung tốt nhất và mới nhất của chúng tôi

Phiên bản MySQL nào tốt nhất để sử dụng?

Cái nào nhanh hơn 5,6 hoặc 5,7 MySQL?

MySQL 8.0 có ổn định không?

Tại sao tôi nên nâng cấp lên MySQL 8?