MyRocks 非正式压测
“非正式”：开发环境有个docker容器要过期了，刚好对MyRocks和MySQL性能对比感兴趣，就拿来简单不专业地测下，仅对比下同样环境下两个版本的结果，测试工具是sysbench，场景是olap_read_write.lua。
简单的压测发现，同样配置下，MyRocks包括读写、事务的性能是MySQL的3倍。
percona官方的一个性能对比：MyRocks Performance
1. 配置
机器配置：32core,32GB,ubuntu 16.04,docker容器
sysbench: sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
my.cnf:
[mysqld]
bind-address = 0.0.0.0
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
innodb = FORCE
# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 100000
server-id = 1
open_files_limit = 65535
max_connections = 10000
innodb_buffer_pool_size = 26G
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
# query_response_time_stats = on
# innodb_monitor_enable = all
innodb_log_buffer_size = 512M
innodb_log_file_size = 1024M
innodb_flush_log_at_trx_commit = 0
sync_binlog = 100000
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_flush_method=O_DIRECT
1.1 MySQL
版本：mysql Ver 14.14 Distrib 5.7.31-34, for debian-linux-gnu (x86_64) using 6.3
percona server 5.7
1.2 MyRocks
sudo apt-get install -y wget gnupg2 lsb-release curl
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt-get install --fix-broken
sudo percona-release setup ps80
sudo apt-get update
sudo apt-get install percona-server-rocksdb-5.7
版本: mysql Ver 14.14 Distrib 5.7.32-35, for debian-linux-gnu (x86_64) using 6.3
2. 测试 oltp_read_write.lua
2.1 prepare
首先创建test db，然后执行：
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=32 --mysql-host=127.0.0.1 --mysql-db=test --tables=20 --table_size=1000000 --mysql-user=root --mysql-password=passw0rd prepare
2.2 run
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=32 --mysql-host=127.0.0.1 --mysql-db=test --tables=20 --table_size=1000000 --mysql-user=root --mysql-password=passw0rd run
2.3 cleanup
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=32 --mysql-host=127.0.0.1 --mysql-db=test --tables=20 --table_size=1000000 --mysql-user=root --mysql-password=passw0rd cleanup
3. 测试结果
3.1 percona MySQL 5.7 (innodb_buffer_pool_size=26G)
oltp read write
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 32
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 53088
write: 15168
other: 7584
total: 75840
transactions: 3792 (374.42 per sec.)
queries: 75840 (7488.42 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.1240s
total number of events: 3792
Latency (ms):
min: 2.60
avg: 84.53
max: 520.82
95th percentile: 186.54
sum: 320521.06
Threads fairness:
events (avg/stddev): 118.5000/7.16
execution time (avg/stddev): 10.0163/0.01
3.3 MyRocks (innodb_buffer_pool_size=26G)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 32
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 135940
write: 38840
other: 19420
total: 194200
transactions: 9710 (962.99 per sec.)
queries: 194200 (19259.75 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0805s
total number of events: 9710
Latency (ms):
min: 1.89
avg: 32.85
max: 345.30
95th percentile: 94.10
sum: 318990.98
Threads fairness:
events (avg/stddev): 303.4375/49.73
execution time (avg/stddev): 9.9685/0.03
3.3 MyRocks (innodb_buffer_pool_size=4G)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 32
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 140994
write: 40284
other: 20142
total: 201420
transactions: 10071 (1001.71 per sec.)
queries: 201420 (20034.23 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0523s
total number of events: 10071
Latency (ms):
min: 1.80
avg: 31.67
max: 332.13
95th percentile: 90.78
sum: 318995.75
Threads fairness:
events (avg/stddev): 314.7188/71.47
execution time (avg/stddev): 9.9686/0.02
3.4 MyRocks (innodb_buffer_pool_size=16G)
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 32
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 165508
write: 47288
other: 23644
total: 236440
transactions: 11822 (1173.39 per sec.)
queries: 236440 (23467.75 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0721s
total number of events: 11822
Latency (ms):
min: 1.83
avg: 26.81
max: 687.19
95th percentile: 71.83
sum: 316940.56
Threads fairness:
events (avg/stddev): 369.4375/29.95
execution time (avg/stddev): 9.9044/0.05
2021-03-27 14:49:15 +0800 yajw Copy old posts A