Mysql8.0.28社区版和Percona Service8.0.28性能对比

家有两宝92

已于 2023-07-18 22:36:09 修改

阅读量339

点赞数

文章标签： mysql

于 2023-07-18 15:26:19 首次发布

本文链接：https://blog.csdn.net/weixin_65195414/article/details/131788299

版权

环境准备：

vm虚拟机centos 7.5，ssd硬盘，4核心8G内存

两台机器的my.cnf配置都一样，如下，没有优化过的配置，只用于测试两都的性能对比：

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
default-character-set = utf8mb4

[mysql]
prompt="MySQL [\d]> "
#no-auto-rehash

[mysqld]
port = 13306
socket = /usr/local/mysql/mysql.sock
default_authentication_plugin = mysql_native_password

basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/mysql.pid
user = root
bind-address = 0.0.0.0
server-id = 1

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

skip-name-resolve
#skip-networking
back_log = 300

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 500M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M

thread_cache_size = 8

ft_min_word_len = 4

log_bin = mysql-bin
binlog_format = mixed
binlog_expire_logs_seconds = 604800

log_error = /usr/local/mysql/logs/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 500M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

Mysql8.0.28读写性能：

数据准备：

sysbench /usr/local/sysbench-1.0.20/src/lua/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=13306 --mysql-db=dbtest --mysql-user=wxroot --mysql-password='Renwole.com#Node' --table_size=1000000 --tables=10 --threads=20 --events=0 --report-interval=10 --time=120 --range_size=500 prepare

读写测试：

sysbench /usr/local/sysbench-1.0.20/src/lua/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=13306 --mysql-db=dbtest  --mysql-user=wxroot --mysql-password='Renwole.com#Node' --table_size=1000000 --tables=10 --threads=20 --events=0 --report-interval=10 --time=120 --range_size=500 run >>/opt/sysbench/result/oltp_read_write.log

执行结果：

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 20
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 20 tps: 267.23 qps: 5368.86 (r/w/o: 3762.69/1069.72/536.46) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 288.90 qps: 5769.06 (r/w/o: 4036.44/1154.81/577.81) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 312.47 qps: 6259.66 (r/w/o: 4384.12/1250.69/624.85) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 334.33 qps: 6689.09 (r/w/o: 4683.08/1337.24/668.77) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 353.56 qps: 7063.41 (r/w/o: 4941.65/1414.64/707.12) lat (ms,95%): 94.10 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 358.64 qps: 7178.06 (r/w/o: 5026.50/1434.27/717.29) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 20 tps: 377.90 qps: 7563.48 (r/w/o: 5293.18/1514.60/755.70) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 20 tps: 382.00 qps: 7635.74 (r/w/o: 5346.83/1524.81/764.10) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 20 tps: 369.90 qps: 7395.21 (r/w/o: 5174.74/1480.68/739.79) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 20 tps: 372.59 qps: 7460.30 (r/w/o: 5222.33/1492.78/745.19) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 20 tps: 388.07 qps: 7757.02 (r/w/o: 5430.99/1549.88/776.14) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 20 tps: 391.23 qps: 7823.86 (r/w/o: 5476.19/1565.31/782.36) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            587860
        write:                           167960
        other:                           83980
        total:                           839800
    transactions:                        41990  (349.78 per sec.)
    queries:                             839800 (6995.60 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0457s
    total number of events:              41990

Latency (ms):
         min:                                   11.79
         avg:                                   57.16
         max:                                  259.33
         95th percentile:                       97.55
         sum:                              2400242.94

Threads fairness:
    events (avg/stddev):           2099.5000/19.40
    execution time (avg/stddev):   120.0121/0.01

Percona Service8.0.28读写性能：

数据准备：

sysbench /usr/local/sysbench-1.0.20/src/lua/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=13306 --mysql-db=dbtest --mysql-user=root --mysql-password='new_password' --table_size=1000000 --tables=10 --threads=20 --events=0 --report-interval=10 --time=120 --range_size=500 prepare

读写测试：

sysbench /usr/local/sysbench-1.0.20/src/lua/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=13306 --mysql-db=dbtest  --mysql-user=wxroot --mysql-password='new_password' --table_size=1000000 --tables=10 --threads=20 --events=0 --report-interval=10 --time=120 --range_size=500 run >>oltp_read_write.log

执行结果：

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 20
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 20 tps: 294.75 qps: 5926.80 (r/w/o: 4152.83/1182.48/591.49) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 341.92 qps: 6832.40 (r/w/o: 4782.01/1366.56/683.83) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 381.09 qps: 7626.00 (r/w/o: 5338.66/1525.16/762.18) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 413.79 qps: 8271.17 (r/w/o: 5789.14/1654.45/827.58) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 432.03 qps: 8647.48 (r/w/o: 6053.71/1729.72/864.06) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 441.38 qps: 8825.09 (r/w/o: 6176.78/1765.54/882.77) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 20 tps: 442.38 qps: 8842.38 (r/w/o: 6190.57/1767.04/884.77) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 20 tps: 455.61 qps: 9120.14 (r/w/o: 6383.27/1825.65/911.22) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 20 tps: 460.11 qps: 9189.90 (r/w/o: 6433.97/1835.72/920.21) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 20 tps: 436.30 qps: 8737.66 (r/w/o: 6115.45/1749.61/872.61) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 20 tps: 460.81 qps: 9211.62 (r/w/o: 6448.98/1841.02/921.61) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 20 tps: 452.70 qps: 9060.95 (r/w/o: 6340.26/1815.29/905.39) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            702086
        write:                           200596
        other:                           100298
        total:                           1002980
    transactions:                        50149  (417.63 per sec.)
    queries:                             1002980 (8352.53 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0796s
    total number of events:              50149

Latency (ms):
         min:                                    5.89
         avg:                                   47.86
         max:                                  302.07
         95th percentile:                      121.08
         sum:                              2400034.76

Threads fairness:
    events (avg/stddev):           2507.4500/32.12
    execution time (avg/stddev):   120.0017/0.01

性能对比结论:perconal service比mysql性能提高17%，大概可以认为提高了20%性能