一、環境：京東雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知
mysql 版本是：5.7.20 ，預設rpm安裝,mysql非同步複製，一個slave測試。
壓測工具是：sysbench1.0.9
mysql壓力測試在京東雲ssd雲盤fio測試4K隨機讀寫iops是
提前說下結論是：可以看到256個執行緒的tps是742。半同步複製是1287，非同步複製是1685，單例項是2419.
二、測試1 首先測試一下iops

三、測試2 預設安裝，所有引數不改變，做一個單例項的基準測試：
資料為500萬，10個表，總數量為5000萬，表的容量為12G

測試1 單例項，引數為
log-bin=binlog
sync_binlog = 1
binlog_format = row
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit = 65535
skip_name_resolve = 1
[root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 256
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 256 tps: 2401.44 qps: 48176.55 (r/w/o: 33731.45/9633.93/4811.17) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 256 tps: 2441.54 qps: 48779.20 (r/w/o: 34141.87/9754.42/4882.91) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 2038078
write: 582308
other: 291154
total: 2911540
transactions: 145577 (2419.01 per sec.)
queries: 2911540 (48380.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.1788s
total number of events: 145577
Latency (ms):
min: 9.05
avg: 105.58
max: 532.87
95th percentile: 186.54
sum: 15370510.44
Threads fairness:
events (avg/stddev): 568.6602/20.63
execution time (avg/stddev): 60.0411/0.03

測試2：普通非同步複製，我們看到tps為1399。執行緒數為100.
master引數如下：
server_id=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=binlog
sync_binlog = 1
binlog_format = row
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit = 65535
skip_name_resolve = 1

slave 引數如下：
server_id=2
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay_log=relay
relay_log_purge=0
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit = 65535
skip_name_resolve = 1
slow_query_log=on
slow_query_log_file=slowquery.log
long_query_time=3
log_queries_not_using_indexes = ON

[root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=100 --time=60 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 100
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 100 tps: 1418.73 qps: 28423.55 (r/w/o: 19902.03/5680.74/2840.79) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1388.45 qps: 27774.89 (r/w/o: 19441.72/5556.30/2776.87) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1180690
write: 337340
other: 168670
total: 1686700
transactions: 84335 (1399.41 per sec.)
queries: 1686700 (27988.21 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.2631s
total number of events: 84335
Latency (ms):
min: 8.62
avg: 71.20
max: 514.88
95th percentile: 121.08
sum: 6004886.80
Threads fairness:
events (avg/stddev): 843.3500/17.92
execution time (avg/stddev): 60.0489/0.03

測試3 普通非同步複製，增加併發數到256，tps 變為827。反覆幾次都是這樣的數值。增加了非同步複製，tps下降很嚴重。
[root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=30 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
[ 30s ] thds: 256 tps: 820.98 qps: 16543.16 (r/w/o: 11587.44/3306.96/1648.75) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 356748
write: 101928
other: 50964
total: 509640
transactions: 25482 (827.69 per sec.)
queries: 509640 (16553.86 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.7837s
total number of events: 25482
Latency (ms):
min: 9.42
avg: 302.09
max: 6031.80
95th percentile: 746.32
sum: 7697901.37
Threads fairness:
events (avg/stddev): 99.5391/53.50
execution time (avg/stddev): 30.0699/0.06

測試4 半同步 master slave引數沒有變化，變化的由原來的非同步複製變成半同步。tps是1287。剛開始我以為我搞錯了，測試了幾遍，發現半同步複製，很穩定，tps在1200多。但是非同步複製，tps才827.後來我又測試了幾遍非同步複製，穩定在900多tps。沒有道理呀！非同步複製，需要等待從庫響應才能提交，tps應該更少的呀。但是，反而更多。這個是為什麼呢？
[root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)

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

Initializing worker threads...

Threads started!

[ 30s ] thds: 256 tps: 1367.81 qps: 27486.91 (r/w/o: 19250.05/5492.84/2744.02) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 256 tps: 1227.47 qps: 24575.83 (r/w/o: 17200.88/4920.04/2454.90) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1094058
write: 312588
other: 156294
total: 1562940
transactions: 78147 (1287.63 per sec.)
queries: 1562940 (25752.63 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.6886s
total number of events: 78147

Latency (ms):
min: 10.64
avg: 196.74
max: 2116.58
95th percentile: 344.08
sum: 15374511.74

Threads fairness:
events (avg/stddev): 305.2617/18.94
execution time (avg/stddev): 60.0567/0.05

測試5 非同步複製，重啟後再次測試，tps為1685.看來重啟之後測試的值比測試3高很多。比半同步複製tps高點。這才正常。
[root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 256
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 256 tps: 1854.50 qps: 37237.93 (r/w/o: 26079.43/7440.98/3717.52) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 256 tps: 1533.39 qps: 30644.69 (r/w/o: 21446.68/6132.50/3065.52) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1429932
write: 408552
other: 204276
total: 2042760
transactions: 102138 (1685.63 per sec.)
queries: 2042760 (33712.64 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.5918s
total number of events: 102138
Latency (ms):
min: 13.28
avg: 150.66
max: 1693.21
95th percentile: 282.25
sum: 15388303.96
Threads fairness:
events (avg/stddev): 398.9766/23.66
execution time (avg/stddev): 60.1106/0.08

測試6 組複製3個節點，引數和非同步複製一樣。採取的單主模式。我們來看下期中一個節點的引數：
server_id=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sync_binlog = 1
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit = 65535
skip_name_resolve = 1
gtid_mode=on
enforce_gtid_consistency=on
master_info_repository=table
relay_log_info_repository=table
binlog_checksum=none
log_slave_updates=on
log_bin=binlog
binlog_format=row
relay_log=relay-log
log_timestamps=SYSTEM

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.1.3.24:24901"
loose-group_replication_group_seeds="10.1.3.24:24901,10.1.3.25:24902,10.1.3.23:24903"
loose-group_replication_bootstrap_group=off

可以看到100個執行緒的tps是683。半同步複製是1287，非同步複製是1685，單例項是2419.
[root@mysql1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=100 --time=60 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 100
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 100 tps: 142.46 qps: 2912.41 (r/w/o: 2041.02/211.02/660.37) lat (ms,95%): 580.02 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 1232.57 qps: 24625.49 (r/w/o: 17236.89/1869.27/5519.33) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 578424
write: 62418
other: 185478
total: 826320
transactions: 41316 (683.42 per sec.)
queries: 826320 (13668.46 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.4528s
total number of events: 41316
Latency (ms):
min: 8.61
avg: 145.81
max: 22889.55
95th percentile: 356.70
sum: 6024263.73
Threads fairness:
events (avg/stddev): 413.1600/14.56
execution time (avg/stddev): 60.2426/0.21

測試7 在上面測試基礎上，加引數set global innodb_flush_log_at_trx_commit=0;

因為組複製這個是安全的。
[root@mysql1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 run
sysbench 1.0.9 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 256
Report intermediate results every 30 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads
[ 30s ] thds: 256 tps: 1131.73 qps: 22796.67 (r/w/o: 15963.64/2456.99/4376.04) lat (ms,95%): 623.33 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 256 tps: 705.84 qps: 14103.58 (r/w/o: 9872.01/1562.96/2668.61) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 775334
write: 120639
other: 211647
total: 1107620
transactions: 55381 (914.03 per sec.)
queries: 1107620 (18280.60 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.5878s
total number of events: 55381
Latency (ms):
min: 3.90
avg: 278.94
max: 12261.22
95th percentile: 623.33
sum: 15448099.10
Threads fairness:
events (avg/stddev): 216.3320/7.12
execution time (avg/stddev): 60.3441/0.12

mysql叢集壓力測試在京東雲盤：sysbench版本

相關文章