Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between the performance of MySQL version 5.7vs 8.0. it is introduced in great detail and has a certain reference value. Interested friends must read it!

Background

Test the performance of mysql5.7 and mysql8.0 at different concurrency in read-write, read-only, and write-only modes (tps,qps)

The prerequisite test is to restart the mysql service before testing mysql5.7.22 and mysql8.0.15sysbench, and clear the cache of os (avoid hitting cache during multiple tests) every time the test is conducted, the test data is newly generated and then the test of mysql5.7 and mysql8.0 is carried out to ensure that the configuration parameters of mysql5.7 and mysql8.0 are the same in each test.

Machine

Cat / etc/redhat-release | xargs echo 'version' & & dmidecode-s system-product-name | xargs echo 'Virtualization' & & cat / proc/cpuinfo | grep "processor" | wc-l | xargs echo 'cpu core' version CentOS Linux release 7.5.1804 (Core) whether to virtualize KVM cpu core 4 copy code

Myql5.7.22

5.7.22-loginnodb_buffer_pool_size 128Minnodb_log_buffer_size 64Minnodb_log_file_size 48Mbinlog_format ROWlog_bin ONtransaction_isolation REPEATABLE-READ copy code

Mysql8.0.15

8.0.15innodb_buffer_pool_size 128Minnodb_log_buffer_size 64Minnodb_log_file_size 48Mbinlog_format ROWlog_bin ONtransaction_isolation REPEATABLE-READ copy code

Sysbench

Sysbench-Vsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) copy code test

Performance of mysql5.7 and mysql8.0 under different persistence strategies (binlog, redo log persistence) in read-write mode, read-only mode, and write-only mode (oltp_read_write,oltp_read_only,oltp_write_only)

The sysbench test time is 60s and the number of tables tested is 20

The tests were carried out in double 1 mode (security) and 02 mode (high performance), respectively.

SHOW GLOBAL VARIABLES WHERE Variable_name IN ('sync_binlog','innodb_flush_log_at_trx_commit') +-- +-+ | Variable_name | Value | +-+-+ | innodb_flush_log_at_trx_commit | 1 | | sync_binlog | 1 | +- -- +

The performance of mysql5.7 and mysql8.0 in read-write mode

In double-1 configuration, the performance of mysql5.7.22 is similar to that of mysql8.0.15 tps and qps in read-write mode. The performance of mysql8.0.15 decreases when 120 threads are concurrent:

The performance of mysql5.7 and mysql8.0 in read-only mode

In double-1 configuration, in read-only mode, the tps and qps of mysql5.7.22 are about 1 / 3 better than mysql8.0.15. After the number of concurrent threads increases, tps and qps do not increase, but show a downward trend.

Performance of mysql5.7 and mysql8.0 in write-only mode

In double-1 configuration, in write-only mode, with the increase of the number of concurrency, the performance of mysql5.7.22 is better than that of mysql8.0.15.

In 0 2 mode

SHOW GLOBAL VARIABLES WHERE Variable_name IN ('sync_binlog','innodb_flush_log_at_trx_commit') +-- +-+ | Variable_name | Value | +-+-+ | innodb_flush_log_at_trx_commit | 2 | | sync_binlog | | 0 | +-- +-+ copy code |

The performance of mysql5.7 and mysql8.0 in read-write mode

The performance of mysql5.7.22 is better than that of mysql8.0.15; when the concurrency is low in read-write mode, and the performance of mysql8.0.15 is better than that of mysql5.7.22; when the concurrency of 80 threads is above 80 threads.

The performance of mysql5.7 and mysql8.0 in read-only mode

In 0.2 configuration, the performance of mysql5.7.22 is better than that of mysql8.0.15 in read-only mode, and with the increase of concurrency, the performance does not increase, but has a downward trend.

Performance of mysql5.7 and mysql8.0 in write-only mode

In 0 2 configuration, the tps jitter of mysql5.7.22 is relatively large in write-only mode. The qps of mysql5.7.22 is better than mysql8.0.15. Conclusion on the whole, mysql5.7.22 outperforms mysql8.0.15 in read-write mode, read-only mode and write-only mode. As the number of parallelism increases, the performance will not increase, and there will be a decline. The test results are carried out in a very low configuration, which does not mean absolute attention.

Sysbench needs to be set-- db-ps-mode=disable disables precompiled statements, otherwise concurrent test threads will report the following error

FATAL: mysql_stmt_prepare () failedFATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)" FATAL: mysql_stmt_prepare () failedFATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)" FATAL: thread_init' function failed: / usr/local/share/sysbench/oltp_common.lua:288: SQL API error FATAL: mysql_stmt_prepare () failedFATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)" FATAL:thread_init' function failed: / usr/local/share/sysbench/oltp_common.lua:288: SQL API errorFATAL: mysql_stmt_prepare () failed copy the code

Use script

Cat Sysbenchmarking tests mysql5.7pm 8.0cycles tpsquarqps.shalls sysbench testing tps of mysql5.7 and mysql8.0 in read-write mode, read-only mode, and write-only mode Qps#nohup bash $0 > / tmp/sysbench_test 2 > & 1 & # user=adminpasswd=adminports= "8015 57222" host=127.0.0.1sysbench_test_mode= "oltp_read_write oltp_read_only oltp_write_only" sysbench_test_info_path=/tmp/sysbench-testfunction red_echo () {local what= "$*" echo-e "$(date +% Fmi% T) e [1 31m ${what} e [0m "} function check_las_comm () {if [$1-ne 0] Then red_echo $2 exit 1 fi} function restart_mysqld () {service mysqld$ {1} restart sleep 2} function purge_binlog () {port=$1mysql-u$user-p$passwd-P$port-h$host/dev/null red_echo "sysbench $thread_num threads prepare mysqld$ {port}" sysbench_with_diff_thread "$thread_num"$port"prepare"$test_mode" > / dev/null mkdir-p $sysbench_test_info_path red_ Echo "sysbench $thread_num threads run mysqld$ {port} $test_mode" sysbench_with_diff_thread "$thread_num"$port"run"$test_mode" > $sysbench_test_info_path/$ {test_mode} _ ${thread_num} _ $port # service mysqld {port} stop done donedone} main above what is the performance difference between MySQL version 5.7 vs 8.0 Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.