一、环境:京东云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
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
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
-
- [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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30393770/viewspace-2150102/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30393770/viewspace-2150102/