Mysql Mgr 用sysbench压力测试

        测试环境：vmware虚拟机3台，操作系统版本centos 7.5，mysql版本5.7.30，使用单主两备架构，使用mysql-router进行读写分离的负载。

#####分别是1个读写和2个只读节点
mysql> select * from backends;
+-------------+---------------------+-------+------+
| backend_ndx | address             | state | type |
+-------------+---------------------+-------+------+
|           1 | 192.168.170.17:3306 | up    | rw   |
|           2 | 192.168.170.18:3306 | up    | ro   |
|           3 | 192.168.170.19:3306 | up    | ro   |
+-------------+---------------------+-------+------+
3 rows in set (0.00 sec)

1、修改优化myslq参数，有一些buffer、thread等参数进行了调整

#####以下是在3个节点中进行优化调整的参数
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
tmp_table_size = 32M
max_heap_table_size = 32M
key_buffer_size = 32M

#replication settings
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
#可以设置为逻辑CPU数量的2倍
slave_parallel_workers = 8
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 4

2、使用sysbench内置的脚本，构造一些数据，分别是10张100万记录的测试表

#####创建sysbench库
mysql> create database sysbench;
Query OK, 1 row affected (0.12 sec)

######构造测试数据
[root@rac1 sysbench-master]# ./sysbench ./src/lua/oltp_common.lua --mysql-host=192.168.170.18 --mysql-port=3306 --mysql-user=xhh --mysql-password=123456 --mysql-db=sysbench --tables=10 --table-size=1000000 --db-driver=mysql --report-interval=1 prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...

3、使用sysbench进行oltp的压力测试，120秒内10个并发

[root@rac1 sysbench-master]# ./sysbench --threads=10 ./src/lua/oltp_read_write.lua --table-size=1000000 --tables=10 --point_selects=2  --index_updates=2 --non_index_updates=1 --delete_inserts=1 --report-interval=1 --mysql-host=192.168.170.19 --mysql-port=7001 --mysql-user=xhh  --mysql-password=123456 --mysql-db=sysbench --time=120  run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 1s ] thds: 10 tps: 21.93 qps: 277.13 (r/w/o: 63.80/159.50/53.83) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 10 tps: 19.01 qps: 164.08 (r/w/o: 38.02/88.04/38.02) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 10 tps: 17.00 qps: 159.99 (r/w/o: 34.00/92.00/34.00) lat (ms,95%): 1013.60 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 10 tps: 10.00 qps: 81.00 (r/w/o: 20.00/41.00/20.00) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
[ 116s ] thds: 10 tps: 22.00 qps: 240.00 (r/w/o: 45.00/151.00/44.00) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 117s ] thds: 10 tps: 31.00 qps: 278.99 (r/w/o: 62.00/155.00/62.00) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00
[ 118s ] thds: 10 tps: 20.00 qps: 176.01 (r/w/o: 40.00/95.00/41.00) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
[ 119s ] thds: 10 tps: 20.00 qps: 181.00 (r/w/o: 40.00/101.00/40.00) lat (ms,95%): 861.95 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 10 tps: 18.00 qps: 165.00 (r/w/o: 36.00/93.00/36.00) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            5412
        write:                           13528
        other:                           5414
        total:                           24354
    transactions:                        2706   (22.52 per sec.)
    queries:                             24354  (202.67 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1666s
    total number of events:              2706

Latency (ms):
         min:                                154.26
         avg:                                444.00
         max:                               2239.88
         95th percentile:                    773.68
         sum:                            1201460.23

Threads fairness:
    events (avg/stddev):           270.6000/2.84
    execution time (avg/stddev):   120.1460/0.04

备注：mysql-route暂不支持读写分离的自动识别，本次测试的性能略有偏低。