MySQL Group Replication+ProxySQL进行读写分离测试

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: MySQL Group Replication+ProxySQL进行读写分离测试

目前主流的中间件有很多，比如Mycat、altas、dbproxy、oneproxy。但是都有不少的问题。我司产品
采用的是基于ProxySQL的模式；因此这里我对proxysql进行一次简单测试。如下是简单安装配置和测试过程，供参考：

1. 增加基于proxysql的yum源repo文件

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/7

gpgcheck=1

gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

2. yum安装proxysql

yum clean all
yum makecache
yum install proxysql

yum clean all

yum makecache

yum install proxysql

3. proxysql安装配置完成后默认配置文件在/etc/proxysql.cnf

4. 安装完毕后可用通过如下方式来验证proxysql安装是否ok

[root@mysqldb ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 148
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye

[root@mysqldb ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 148

Server version: 5.5.30 (ProxySQL Admin Module)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit

Bye

关于默认的用户和密码信息，以及端口等内容，均可直接查看/etc/proxysql.cnf配置文件.

5. 创建相关监控和测试账户

mysql> CREATE USER 'mgrtest'@'%' IDENTIFIED BY 'mgrtest';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT USAGE ON *.* TO 'mgrtest'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'proxysql'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'mgrtest'@'%' IDENTIFIED BY 'mgrtest';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'mgrtest'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'proxysql'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

6.配置db列表(10为write节点，20表示备写,30是read节点,40表示offline)

--配置db列表
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1);
insert into mysql_users(username,password,default_hostgroup) values('mgrtest','mgrtest',10);
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(10,'172.16.29.133',3306,'write');
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'172.16.29.154',3306,'read');
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'172.16.29.132',3306,'read');
--定义读写分离规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysq_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);

--配置db列表

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1);

insert into mysql_users(username,password,default_hostgroup) values('mgrtest','mgrtest',10);

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(10,'172.16.29.133',3306,'write');

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'172.16.29.154',3306,'read');

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'172.16.29.132',3306,'read');

--定义读写分离规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);

insert into mysq_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);

7. 将持久化到配置文件中,并load到内存

save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

save mysql users to disk;

save mysql servers to disk;

save mysql query rules to disk;

save mysql variables to disk;

save admin variables to disk;

load mysql users to runtime;

load mysql servers to runtime;

load mysql query rules to runtime;

load mysql variables to runtime;

load admin variables to runtime;

操作完毕后，我们看看此时的情况：

mysql> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_collations                           |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
20 rows in set (0.00 sec)
mysql> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.29.133 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | write   |
| 30           | 172.16.29.132 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | read    |
| 30           | 172.16.29.154 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | read    |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| mgrtest  | mgrtest  | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

mysql> show tables;

+--------------------------------------------+

| tables |

+--------------------------------------------+

| global_variables |

| mysql_collations |

| mysql_group_replication_hostgroups |

| mysql_query_rules |

| mysql_query_rules_fast_routing |

| mysql_replication_hostgroups |

| mysql_servers |

| mysql_users |

| proxysql_servers |

| runtime_checksums_values |

| runtime_global_variables |

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules |

| runtime_mysql_query_rules_fast_routing |

| runtime_mysql_replication_hostgroups |

| runtime_mysql_servers |

| runtime_mysql_users |

| runtime_proxysql_servers |

| runtime_scheduler |

| scheduler |

+--------------------------------------------+

20 rows in set (0.00 sec)

mysql> select * from mysql_servers;

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 10 | 172.16.29.133 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | write |

| 30 | 172.16.29.132 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | read |

| 30 | 172.16.29.154 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | read |

+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

mysql> select * from mysql_users;

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

| mgrtest | mgrtest | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |

+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

1 row in set (0.00 sec)

7. 下面通过sysbench来压测对比一下，看看加了一层ProxySQL，是否有较大的性能影响

---直接压测mgr节点
[root@killdb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=172.16.29.133 --mysql-port=3306 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 78.92 qps: 1597.84 (r/w/o: 1122.14/267.72/207.98) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 93.08 qps: 1866.38 (r/w/o: 1304.80/317.47/244.11) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 73.40 qps: 1466.48 (r/w/o: 1026.99/245.80/193.70) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 74.19 qps: 1483.40 (r/w/o: 1038.63/249.18/195.59) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 89.50 qps: 1790.19 (r/w/o: 1252.99/300.70/236.50) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 87.86 qps: 1758.66 (r/w/o: 1231.78/296.77/230.10) lat (ms,95%): 272.27 err/s: 0.10 reconn/s: 0.00
[ 70s ] thds: 16 tps: 93.24 qps: 1867.17 (r/w/o: 1307.61/315.15/244.41) lat (ms,95%): 248.83 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 16 tps: 85.70 qps: 1717.27 (r/w/o: 1201.68/293.79/221.80) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 74.40 qps: 1484.32 (r/w/o: 1039.81/251.20/193.30) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 84.90 qps: 1701.14 (r/w/o: 1190.46/291.89/218.79) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 78.79 qps: 1571.22 (r/w/o: 1099.88/263.77/207.58) lat (ms,95%): 369.77 err/s: 0.20 reconn/s: 0.00
[ 120s ] thds: 16 tps: 73.31 qps: 1472.83 (r/w/o: 1032.06/251.84/188.93) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 16 tps: 84.99 qps: 1694.76 (r/w/o: 1184.30/290.68/219.78) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 16 tps: 60.30 qps: 1209.36 (r/w/o: 846.94/205.91/156.51) lat (ms,95%): 411.96 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 16 tps: 45.90 qps: 916.91 (r/w/o: 642.50/155.50/118.90) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 16 tps: 44.08 qps: 884.08 (r/w/o: 619.81/151.33/112.95) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 16 tps: 54.62 qps: 1087.71 (r/w/o: 760.08/187.27/140.35) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 16 tps: 66.81 qps: 1333.72 (r/w/o: 934.29/225.82/173.62) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 16 tps: 82.30 qps: 1648.87 (r/w/o: 1152.88/278.89/217.10) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 87.21 qps: 1745.26 (r/w/o: 1223.21/293.83/228.22) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 72.70 qps: 1451.01 (r/w/o: 1015.03/248.38/187.59) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 84.80 qps: 1703.82 (r/w/o: 1192.31/293.70/217.80) lat (ms,95%): 320.17 err/s: 0.10 reconn/s: 0.00
[ 230s ] thds: 16 tps: 99.50 qps: 1988.24 (r/w/o: 1393.03/338.01/257.20) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 100.30 qps: 2008.11 (r/w/o: 1405.64/344.78/257.69) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 68.48 qps: 1370.72 (r/w/o: 959.76/232.82/178.14) lat (ms,95%): 419.45 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 71.73 qps: 1435.21 (r/w/o: 1003.26/244.09/187.87) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 36.50 qps: 722.52 (r/w/o: 507.02/120.40/95.10) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 32.70 qps: 664.41 (r/w/o: 463.21/114.70/86.50) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 31.02 qps: 607.81 (r/w/o: 425.69/102.43/79.69) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 34.48 qps: 690.58 (r/w/o: 485.08/116.08/89.42) lat (ms,95%): 960.30 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read:                            300692
write:                           72949
other:                           55898
total:                           429539
transactions:                        21473  (71.51 per sec.)
queries:                             429539 (1430.53 per sec.)
ignored errors:                      5      (0.02 per sec.)
reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          300.2648s
total number of events:              21473
Latency (ms):
min:                                   39.42
avg:                                  223.67
max:                                 2681.44
95th percentile:                      434.83
sum:                              4802945.03
Threads fairness:
events (avg/stddev):           1342.0625/12.91
execution time (avg/stddev):   300.1841/0.02
[root@killdb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_only.lua --mysql-host=172.16.29.132 --mysql-port=3306 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=24 --report-interval=10 --time=300 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 24
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 24 tps: 194.17 qps: 3127.56 (r/w/o: 2736.83/0.00/390.73) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 24 tps: 152.11 qps: 2436.76 (r/w/o: 2132.64/0.00/304.12) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 24 tps: 117.59 qps: 1878.14 (r/w/o: 1642.86/0.00/235.28) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 24 tps: 152.21 qps: 2436.21 (r/w/o: 2131.80/0.00/304.41) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 24 tps: 174.00 qps: 2783.67 (r/w/o: 2435.68/0.00/348.00) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 24 tps: 192.99 qps: 3089.32 (r/w/o: 2703.43/0.00/385.89) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 24 tps: 191.30 qps: 3064.60 (r/w/o: 2682.00/0.00/382.60) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 24 tps: 169.86 qps: 2715.87 (r/w/o: 2376.15/0.00/339.72) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 24 tps: 199.45 qps: 3194.13 (r/w/o: 2795.14/0.00/398.99) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 24 tps: 157.90 qps: 2516.83 (r/w/o: 2201.43/0.00/315.40) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 24 tps: 145.00 qps: 2322.74 (r/w/o: 2032.53/0.00/290.20) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 24 tps: 191.08 qps: 3062.64 (r/w/o: 2680.27/0.00/382.37) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 24 tps: 213.12 qps: 3410.62 (r/w/o: 2984.48/0.00/426.14) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 24 tps: 169.61 qps: 2713.64 (r/w/o: 2374.32/0.00/339.32) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 24 tps: 172.20 qps: 2750.04 (r/w/o: 2405.65/0.00/344.39) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 24 tps: 180.98 qps: 2897.95 (r/w/o: 2536.00/0.00/361.96) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 24 tps: 167.32 qps: 2672.77 (r/w/o: 2338.32/0.00/334.45) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 24 tps: 151.90 qps: 2436.71 (r/w/o: 2132.71/0.00/304.00) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 24 tps: 151.17 qps: 2417.87 (r/w/o: 2115.53/0.00/302.33) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 24 tps: 197.03 qps: 3150.61 (r/w/o: 2756.65/0.00/393.96) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 24 tps: 201.65 qps: 3228.66 (r/w/o: 2825.35/0.00/403.31) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 24 tps: 185.41 qps: 2962.30 (r/w/o: 2591.58/0.00/370.73) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 24 tps: 118.12 qps: 1896.89 (r/w/o: 1660.44/0.00/236.45) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 24 tps: 100.69 qps: 1604.12 (r/w/o: 1402.73/0.00/201.39) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 24 tps: 151.52 qps: 2427.09 (r/w/o: 2124.14/0.00/302.95) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 24 tps: 150.97 qps: 2414.70 (r/w/o: 2112.66/0.00/302.04) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 24 tps: 139.71 qps: 2235.71 (r/w/o: 1956.28/0.00/279.43) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 24 tps: 151.16 qps: 2421.21 (r/w/o: 2118.88/0.00/302.33) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 24 tps: 143.44 qps: 2294.27 (r/w/o: 2007.50/0.00/286.77) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 24 tps: 183.30 qps: 2933.48 (r/w/o: 2566.98/0.00/366.50) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read:                            695716
write:                           0
other:                           99388
total:                           795104
transactions:                        49694  (165.59 per sec.)
queries:                             795104 (2649.48 per sec.)
ignored errors:                      0      (0.00 per sec.)
reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          300.0963s
total number of events:              49694
Latency (ms):
min:                                   51.73
avg:                                  144.90
max:                                 2482.04
95th percentile:                      253.35
sum:                              7200898.71
Threads fairness:
events (avg/stddev):           2070.5833/21.72
execution time (avg/stddev):   300.0374/0.02
--通过proxysql的压测
[root@mysqldb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 66.66 qps: 1354.18 (r/w/o: 949.38/215.47/189.34) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 69.73 qps: 1397.35 (r/w/o: 978.66/223.99/194.70) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 83.32 qps: 1665.71 (r/w/o: 1166.22/270.75/228.74) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 89.10 qps: 1775.48 (r/w/o: 1242.88/288.10/244.50) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 83.91 qps: 1675.61 (r/w/o: 1172.68/272.92/230.02) lat (ms,95%): 325.98 err/s: 0.10 reconn/s: 0.00
[ 60s ] thds: 16 tps: 82.60 qps: 1656.01 (r/w/o: 1159.81/269.50/226.70) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 79.19 qps: 1581.47 (r/w/o: 1106.51/259.28/215.68) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 71.09 qps: 1417.53 (r/w/o: 991.38/230.77/195.38) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 74.70 qps: 1501.47 (r/w/o: 1051.15/247.01/203.31) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 78.60 qps: 1576.47 (r/w/o: 1104.68/256.60/215.20) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 88.11 qps: 1754.93 (r/w/o: 1228.36/289.34/237.23) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 54.00 qps: 1084.46 (r/w/o: 759.17/178.29/146.99) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 16 tps: 51.00 qps: 1016.78 (r/w/o: 711.36/168.41/137.01) lat (ms,95%): 580.02 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 16 tps: 58.87 qps: 1184.77 (r/w/o: 831.13/199.21/154.43) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 16 tps: 56.11 qps: 1117.77 (r/w/o: 781.99/185.94/149.84) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 16 tps: 46.31 qps: 924.82 (r/w/o: 646.48/154.72/123.62) lat (ms,95%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 16 tps: 65.51 qps: 1316.85 (r/w/o: 922.00/218.32/176.52) lat (ms,95%): 475.79 err/s: 0.20 reconn/s: 0.00
[ 180s ] thds: 16 tps: 79.50 qps: 1581.13 (r/w/o: 1105.45/265.19/210.49) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 16 tps: 81.89 qps: 1639.93 (r/w/o: 1148.71/274.35/216.86) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 64.81 qps: 1300.92 (r/w/o: 911.28/218.62/171.02) lat (ms,95%): 427.07 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 68.98 qps: 1377.46 (r/w/o: 964.76/232.54/180.16) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 76.12 qps: 1518.19 (r/w/o: 1062.04/253.38/202.77) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 16 tps: 73.80 qps: 1475.21 (r/w/o: 1031.64/245.98/197.59) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 56.80 qps: 1141.05 (r/w/o: 799.83/191.01/150.21) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 78.87 qps: 1577.07 (r/w/o: 1104.63/266.51/205.93) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 77.33 qps: 1542.87 (r/w/o: 1078.10/263.80/200.97) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 77.40 qps: 1545.25 (r/w/o: 1081.76/258.99/204.49) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 86.40 qps: 1736.59 (r/w/o: 1216.86/290.51/229.21) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 79.16 qps: 1582.19 (r/w/o: 1108.40/264.28/209.51) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 84.93 qps: 1702.25 (r/w/o: 1191.49/285.19/225.57) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read:                            306138
write:                           72434
other:                           58760
total:                           437332
transactions:                        21864  (72.84 per sec.)
queries:                             437332 (1456.97 per sec.)
ignored errors:                      3      (0.01 per sec.)
reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          300.1637s
total number of events:              21864
Latency (ms):
min:                                   35.02
avg:                                  219.60
max:                                 1455.33
95th percentile:                      411.96
sum:                              4801349.57
Threads fairness:
events (avg/stddev):           1366.5000/69.06
execution time (avg/stddev):   300.0843/0.04
[root@mysqldb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 114.57 qps: 1846.86 (r/w/o: 1616.12/0.00/230.73) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 129.71 qps: 2072.79 (r/w/o: 1813.38/0.00/259.41) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 147.20 qps: 2354.86 (r/w/o: 2060.47/0.00/294.40) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 153.66 qps: 2462.13 (r/w/o: 2154.92/0.00/307.22) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 149.27 qps: 2385.42 (r/w/o: 2086.88/0.00/298.54) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 114.04 qps: 1827.69 (r/w/o: 1599.61/0.00/228.07) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 16 tps: 119.22 qps: 1904.07 (r/w/o: 1665.54/0.00/238.53) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 16 tps: 111.07 qps: 1776.87 (r/w/o: 1554.73/0.00/222.15) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 16 tps: 118.86 qps: 1901.43 (r/w/o: 1663.80/0.00/237.63) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 139.95 qps: 2240.74 (r/w/o: 1960.85/0.00/279.89) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 163.23 qps: 2611.96 (r/w/o: 2285.39/0.00/326.57) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 163.30 qps: 2614.77 (r/w/o: 2288.17/0.00/326.60) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 16 tps: 158.62 qps: 2538.53 (r/w/o: 2221.29/0.00/317.24) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 16 tps: 168.98 qps: 2703.10 (r/w/o: 2365.14/0.00/337.96) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 16 tps: 164.60 qps: 2634.11 (r/w/o: 2305.01/0.00/329.10) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 16 tps: 164.70 qps: 2633.55 (r/w/o: 2304.15/0.00/329.41) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 16 tps: 156.39 qps: 2503.92 (r/w/o: 2191.03/0.00/312.89) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 16 tps: 165.39 qps: 2644.56 (r/w/o: 2313.87/0.00/330.68) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 16 tps: 142.21 qps: 2278.81 (r/w/o: 1994.30/0.00/284.51) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 16 tps: 159.00 qps: 2539.67 (r/w/o: 2221.66/0.00/318.01) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 16 tps: 122.90 qps: 1965.56 (r/w/o: 1720.06/0.00/245.51) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 16 tps: 138.89 qps: 2223.72 (r/w/o: 1945.64/0.00/278.08) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 16 tps: 112.16 qps: 1792.44 (r/w/o: 1568.12/0.00/224.32) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 16 tps: 121.93 qps: 1952.77 (r/w/o: 1709.01/0.00/243.76) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 16 tps: 124.41 qps: 1993.00 (r/w/o: 1744.28/0.00/248.73) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 16 tps: 130.11 qps: 2079.93 (r/w/o: 1819.51/0.00/260.42) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 16 tps: 141.71 qps: 2264.80 (r/w/o: 1981.39/0.00/283.41) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 16 tps: 137.89 qps: 2208.05 (r/w/o: 1932.47/0.00/275.58) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 16 tps: 117.09 qps: 1872.65 (r/w/o: 1638.27/0.00/234.38) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 16 tps: 118.11 qps: 1891.39 (r/w/o: 1655.27/0.00/236.12) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read:                            583898
write:                           0
other:                           83414
total:                           667312
transactions:                        41707  (138.98 per sec.)
queries:                             667312 (2223.75 per sec.)
ignored errors:                      0      (0.00 per sec.)
reconnects:                          0      (0.00 per sec.)
General statistics:
total time:                          300.0822s
total number of events:              41707
Latency (ms):
min:                                   21.34
avg:                                  115.09
max:                                  772.68
95th percentile:                      219.36
sum:                              4800223.99
Threads fairness:
events (avg/stddev):           2606.6875/679.58
execution time (avg/stddev):   300.0140/0.02

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

---直接压测mgr节点

[root@killdb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=172.16.29.133 --mysql-port=3306 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 16

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 78.92 qps: 1597.84 (r/w/o: 1122.14/267.72/207.98) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 16 tps: 93.08 qps: 1866.38 (r/w/o: 1304.80/317.47/244.11) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 16 tps: 73.40 qps: 1466.48 (r/w/o: 1026.99/245.80/193.70) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 16 tps: 74.19 qps: 1483.40 (r/w/o: 1038.63/249.18/195.59) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 16 tps: 89.50 qps: 1790.19 (r/w/o: 1252.99/300.70/236.50) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 16 tps: 87.86 qps: 1758.66 (r/w/o: 1231.78/296.77/230.10) lat (ms,95%): 272.27 err/s: 0.10 reconn/s: 0.00

[ 70s ] thds: 16 tps: 93.24 qps: 1867.17 (r/w/o: 1307.61/315.15/244.41) lat (ms,95%): 248.83 err/s: 0.10 reconn/s: 0.00

[ 80s ] thds: 16 tps: 85.70 qps: 1717.27 (r/w/o: 1201.68/293.79/221.80) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 16 tps: 74.40 qps: 1484.32 (r/w/o: 1039.81/251.20/193.30) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 16 tps: 84.90 qps: 1701.14 (r/w/o: 1190.46/291.89/218.79) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 16 tps: 78.79 qps: 1571.22 (r/w/o: 1099.88/263.77/207.58) lat (ms,95%): 369.77 err/s: 0.20 reconn/s: 0.00

[ 120s ] thds: 16 tps: 73.31 qps: 1472.83 (r/w/o: 1032.06/251.84/188.93) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 130s ] thds: 16 tps: 84.99 qps: 1694.76 (r/w/o: 1184.30/290.68/219.78) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00

[ 140s ] thds: 16 tps: 60.30 qps: 1209.36 (r/w/o: 846.94/205.91/156.51) lat (ms,95%): 411.96 err/s: 0.00 reconn/s: 0.00

[ 150s ] thds: 16 tps: 45.90 qps: 916.91 (r/w/o: 642.50/155.50/118.90) lat (ms,95%): 694.45 err/s: 0.00 reconn/s: 0.00

[ 160s ] thds: 16 tps: 44.08 qps: 884.08 (r/w/o: 619.81/151.33/112.95) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00

[ 170s ] thds: 16 tps: 54.62 qps: 1087.71 (r/w/o: 760.08/187.27/140.35) lat (ms,95%): 502.20 err/s: 0.00 reconn/s: 0.00

[ 180s ] thds: 16 tps: 66.81 qps: 1333.72 (r/w/o: 934.29/225.82/173.62) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00

[ 190s ] thds: 16 tps: 82.30 qps: 1648.87 (r/w/o: 1152.88/278.89/217.10) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00

[ 200s ] thds: 16 tps: 87.21 qps: 1745.26 (r/w/o: 1223.21/293.83/228.22) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00

[ 210s ] thds: 16 tps: 72.70 qps: 1451.01 (r/w/o: 1015.03/248.38/187.59) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00

[ 220s ] thds: 16 tps: 84.80 qps: 1703.82 (r/w/o: 1192.31/293.70/217.80) lat (ms,95%): 320.17 err/s: 0.10 reconn/s: 0.00

[ 230s ] thds: 16 tps: 99.50 qps: 1988.24 (r/w/o: 1393.03/338.01/257.20) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00

[ 240s ] thds: 16 tps: 100.30 qps: 2008.11 (r/w/o: 1405.64/344.78/257.69) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00

[ 250s ] thds: 16 tps: 68.48 qps: 1370.72 (r/w/o: 959.76/232.82/178.14) lat (ms,95%): 419.45 err/s: 0.00 reconn/s: 0.00

[ 260s ] thds: 16 tps: 71.73 qps: 1435.21 (r/w/o: 1003.26/244.09/187.87) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00

[ 270s ] thds: 16 tps: 36.50 qps: 722.52 (r/w/o: 507.02/120.40/95.10) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00

[ 280s ] thds: 16 tps: 32.70 qps: 664.41 (r/w/o: 463.21/114.70/86.50) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00

[ 290s ] thds: 16 tps: 31.02 qps: 607.81 (r/w/o: 425.69/102.43/79.69) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00

[ 300s ] thds: 16 tps: 34.48 qps: 690.58 (r/w/o: 485.08/116.08/89.42) lat (ms,95%): 960.30 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

write: 72949

other: 55898

total: 429539

transactions: 21473 (71.51 per sec.)

queries: 429539 (1430.53 per sec.)

ignored errors: 5 (0.02 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 300.2648s

total number of events: 21473

Latency (ms):

min: 39.42

avg: 223.67

max: 2681.44

95th percentile: 434.83

sum: 4802945.03

Threads fairness:

events (avg/stddev): 1342.0625/12.91

execution time (avg/stddev): 300.1841/0.02

[root@killdb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_only.lua --mysql-host=172.16.29.132 --mysql-port=3306 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=24 --report-interval=10 --time=300 run

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 24

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 24 tps: 194.17 qps: 3127.56 (r/w/o: 2736.83/0.00/390.73) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 24 tps: 152.11 qps: 2436.76 (r/w/o: 2132.64/0.00/304.12) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 24 tps: 117.59 qps: 1878.14 (r/w/o: 1642.86/0.00/235.28) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 24 tps: 152.21 qps: 2436.21 (r/w/o: 2131.80/0.00/304.41) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 24 tps: 174.00 qps: 2783.67 (r/w/o: 2435.68/0.00/348.00) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 24 tps: 192.99 qps: 3089.32 (r/w/o: 2703.43/0.00/385.89) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 24 tps: 191.30 qps: 3064.60 (r/w/o: 2682.00/0.00/382.60) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 24 tps: 169.86 qps: 2715.87 (r/w/o: 2376.15/0.00/339.72) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 24 tps: 199.45 qps: 3194.13 (r/w/o: 2795.14/0.00/398.99) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 24 tps: 157.90 qps: 2516.83 (r/w/o: 2201.43/0.00/315.40) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 24 tps: 145.00 qps: 2322.74 (r/w/o: 2032.53/0.00/290.20) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 24 tps: 191.08 qps: 3062.64 (r/w/o: 2680.27/0.00/382.37) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00

[ 130s ] thds: 24 tps: 213.12 qps: 3410.62 (r/w/o: 2984.48/0.00/426.14) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00

[ 140s ] thds: 24 tps: 169.61 qps: 2713.64 (r/w/o: 2374.32/0.00/339.32) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00

[ 150s ] thds: 24 tps: 172.20 qps: 2750.04 (r/w/o: 2405.65/0.00/344.39) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00

[ 160s ] thds: 24 tps: 180.98 qps: 2897.95 (r/w/o: 2536.00/0.00/361.96) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00

[ 170s ] thds: 24 tps: 167.32 qps: 2672.77 (r/w/o: 2338.32/0.00/334.45) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00

[ 180s ] thds: 24 tps: 151.90 qps: 2436.71 (r/w/o: 2132.71/0.00/304.00) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00

[ 190s ] thds: 24 tps: 151.17 qps: 2417.87 (r/w/o: 2115.53/0.00/302.33) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00

[ 200s ] thds: 24 tps: 197.03 qps: 3150.61 (r/w/o: 2756.65/0.00/393.96) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00

[ 210s ] thds: 24 tps: 201.65 qps: 3228.66 (r/w/o: 2825.35/0.00/403.31) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00

[ 220s ] thds: 24 tps: 185.41 qps: 2962.30 (r/w/o: 2591.58/0.00/370.73) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00

[ 230s ] thds: 24 tps: 118.12 qps: 1896.89 (r/w/o: 1660.44/0.00/236.45) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00

[ 240s ] thds: 24 tps: 100.69 qps: 1604.12 (r/w/o: 1402.73/0.00/201.39) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00

[ 250s ] thds: 24 tps: 151.52 qps: 2427.09 (r/w/o: 2124.14/0.00/302.95) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00

[ 260s ] thds: 24 tps: 150.97 qps: 2414.70 (r/w/o: 2112.66/0.00/302.04) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00

[ 270s ] thds: 24 tps: 139.71 qps: 2235.71 (r/w/o: 1956.28/0.00/279.43) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00

[ 280s ] thds: 24 tps: 151.16 qps: 2421.21 (r/w/o: 2118.88/0.00/302.33) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00

[ 290s ] thds: 24 tps: 143.44 qps: 2294.27 (r/w/o: 2007.50/0.00/286.77) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00

[ 300s ] thds: 24 tps: 183.30 qps: 2933.48 (r/w/o: 2566.98/0.00/366.50) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

write: 0

other: 99388

total: 795104

transactions: 49694 (165.59 per sec.)

queries: 795104 (2649.48 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 300.0963s

total number of events: 49694

Latency (ms):

min: 51.73

avg: 144.90

max: 2482.04

95th percentile: 253.35

sum: 7200898.71

Threads fairness:

events (avg/stddev): 2070.5833/21.72

execution time (avg/stddev): 300.0374/0.02

--通过proxysql的压测

[root@mysqldb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 16

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 66.66 qps: 1354.18 (r/w/o: 949.38/215.47/189.34) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 16 tps: 69.73 qps: 1397.35 (r/w/o: 978.66/223.99/194.70) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 16 tps: 83.32 qps: 1665.71 (r/w/o: 1166.22/270.75/228.74) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 16 tps: 89.10 qps: 1775.48 (r/w/o: 1242.88/288.10/244.50) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 16 tps: 83.91 qps: 1675.61 (r/w/o: 1172.68/272.92/230.02) lat (ms,95%): 325.98 err/s: 0.10 reconn/s: 0.00

[ 60s ] thds: 16 tps: 82.60 qps: 1656.01 (r/w/o: 1159.81/269.50/226.70) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 16 tps: 79.19 qps: 1581.47 (r/w/o: 1106.51/259.28/215.68) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 16 tps: 71.09 qps: 1417.53 (r/w/o: 991.38/230.77/195.38) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 16 tps: 74.70 qps: 1501.47 (r/w/o: 1051.15/247.01/203.31) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 16 tps: 78.60 qps: 1576.47 (r/w/o: 1104.68/256.60/215.20) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 16 tps: 88.11 qps: 1754.93 (r/w/o: 1228.36/289.34/237.23) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 16 tps: 54.00 qps: 1084.46 (r/w/o: 759.17/178.29/146.99) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00

[ 130s ] thds: 16 tps: 51.00 qps: 1016.78 (r/w/o: 711.36/168.41/137.01) lat (ms,95%): 580.02 err/s: 0.00 reconn/s: 0.00

[ 140s ] thds: 16 tps: 58.87 qps: 1184.77 (r/w/o: 831.13/199.21/154.43) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00

[ 150s ] thds: 16 tps: 56.11 qps: 1117.77 (r/w/o: 781.99/185.94/149.84) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00

[ 160s ] thds: 16 tps: 46.31 qps: 924.82 (r/w/o: 646.48/154.72/123.62) lat (ms,95%): 733.00 err/s: 0.00 reconn/s: 0.00

[ 170s ] thds: 16 tps: 65.51 qps: 1316.85 (r/w/o: 922.00/218.32/176.52) lat (ms,95%): 475.79 err/s: 0.20 reconn/s: 0.00

[ 180s ] thds: 16 tps: 79.50 qps: 1581.13 (r/w/o: 1105.45/265.19/210.49) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00

[ 190s ] thds: 16 tps: 81.89 qps: 1639.93 (r/w/o: 1148.71/274.35/216.86) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00

[ 200s ] thds: 16 tps: 64.81 qps: 1300.92 (r/w/o: 911.28/218.62/171.02) lat (ms,95%): 427.07 err/s: 0.00 reconn/s: 0.00

[ 210s ] thds: 16 tps: 68.98 qps: 1377.46 (r/w/o: 964.76/232.54/180.16) lat (ms,95%): 450.77 err/s: 0.00 reconn/s: 0.00

[ 220s ] thds: 16 tps: 76.12 qps: 1518.19 (r/w/o: 1062.04/253.38/202.77) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 230s ] thds: 16 tps: 73.80 qps: 1475.21 (r/w/o: 1031.64/245.98/197.59) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 240s ] thds: 16 tps: 56.80 qps: 1141.05 (r/w/o: 799.83/191.01/150.21) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00

[ 250s ] thds: 16 tps: 78.87 qps: 1577.07 (r/w/o: 1104.63/266.51/205.93) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00

[ 260s ] thds: 16 tps: 77.33 qps: 1542.87 (r/w/o: 1078.10/263.80/200.97) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 270s ] thds: 16 tps: 77.40 qps: 1545.25 (r/w/o: 1081.76/258.99/204.49) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 280s ] thds: 16 tps: 86.40 qps: 1736.59 (r/w/o: 1216.86/290.51/229.21) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00

[ 290s ] thds: 16 tps: 79.16 qps: 1582.19 (r/w/o: 1108.40/264.28/209.51) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 300s ] thds: 16 tps: 84.93 qps: 1702.25 (r/w/o: 1191.49/285.19/225.57) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

write: 72434

other: 58760

total: 437332

transactions: 21864 (72.84 per sec.)

queries: 437332 (1456.97 per sec.)

ignored errors: 3 (0.01 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 300.1637s

total number of events: 21864

Latency (ms):

min: 35.02

avg: 219.60

max: 1455.33

95th percentile: 411.96

sum: 4801349.57

Threads fairness:

events (avg/stddev): 1366.5000/69.06

execution time (avg/stddev): 300.0843/0.04

[root@mysqldb lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=enmotech --mysql-user=mgrtest --mysql-password=mgrtest --table_size=100000 --tables=10 --threads=16 --report-interval=10 --time=300 run

sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 16

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 114.57 qps: 1846.86 (r/w/o: 1616.12/0.00/230.73) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 16 tps: 129.71 qps: 2072.79 (r/w/o: 1813.38/0.00/259.41) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 16 tps: 147.20 qps: 2354.86 (r/w/o: 2060.47/0.00/294.40) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 16 tps: 153.66 qps: 2462.13 (r/w/o: 2154.92/0.00/307.22) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 16 tps: 149.27 qps: 2385.42 (r/w/o: 2086.88/0.00/298.54) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 16 tps: 114.04 qps: 1827.69 (r/w/o: 1599.61/0.00/228.07) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 16 tps: 119.22 qps: 1904.07 (r/w/o: 1665.54/0.00/238.53) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 16 tps: 111.07 qps: 1776.87 (r/w/o: 1554.73/0.00/222.15) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 16 tps: 118.86 qps: 1901.43 (r/w/o: 1663.80/0.00/237.63) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 16 tps: 139.95 qps: 2240.74 (r/w/o: 1960.85/0.00/279.89) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 16 tps: 163.23 qps: 2611.96 (r/w/o: 2285.39/0.00/326.57) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 16 tps: 163.30 qps: 2614.77 (r/w/o: 2288.17/0.00/326.60) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00

[ 130s ] thds: 16 tps: 158.62 qps: 2538.53 (r/w/o: 2221.29/0.00/317.24) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00

[ 140s ] thds: 16 tps: 168.98 qps: 2703.10 (r/w/o: 2365.14/0.00/337.96) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00

[ 150s ] thds: 16 tps: 164.60 qps: 2634.11 (r/w/o: 2305.01/0.00/329.10) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00

[ 160s ] thds: 16 tps: 164.70 qps: 2633.55 (r/w/o: 2304.15/0.00/329.41) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00

[ 170s ] thds: 16 tps: 156.39 qps: 2503.92 (r/w/o: 2191.03/0.00/312.89) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00

[ 180s ] thds: 16 tps: 165.39 qps: 2644.56 (r/w/o: 2313.87/0.00/330.68) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00

[ 190s ] thds: 16 tps: 142.21 qps: 2278.81 (r/w/o: 1994.30/0.00/284.51) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00

[ 200s ] thds: 16 tps: 159.00 qps: 2539.67 (r/w/o: 2221.66/0.00/318.01) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00

[ 210s ] thds: 16 tps: 122.90 qps: 1965.56 (r/w/o: 1720.06/0.00/245.51) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00

[ 220s ] thds: 16 tps: 138.89 qps: 2223.72 (r/w/o: 1945.64/0.00/278.08) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00

[ 230s ] thds: 16 tps: 112.16 qps: 1792.44 (r/w/o: 1568.12/0.00/224.32) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00

[ 240s ] thds: 16 tps: 121.93 qps: 1952.77 (r/w/o: 1709.01/0.00/243.76) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00

[ 250s ] thds: 16 tps: 124.41 qps: 1993.00 (r/w/o: 1744.28/0.00/248.73) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00

[ 260s ] thds: 16 tps: 130.11 qps: 2079.93 (r/w/o: 1819.51/0.00/260.42) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00

[ 270s ] thds: 16 tps: 141.71 qps: 2264.80 (r/w/o: 1981.39/0.00/283.41) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00

[ 280s ] thds: 16 tps: 137.89 qps: 2208.05 (r/w/o: 1932.47/0.00/275.58) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00

[ 290s ] thds: 16 tps: 117.09 qps: 1872.65 (r/w/o: 1638.27/0.00/234.38) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00

[ 300s ] thds: 16 tps: 118.11 qps: 1891.39 (r/w/o: 1655.27/0.00/236.12) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00

SQL statistics:

queries performed:

write: 0

other: 83414

total: 667312

transactions: 41707 (138.98 per sec.)

queries: 667312 (2223.75 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 300.0822s

total number of events: 41707

Latency (ms):

min: 21.34

avg: 115.09

max: 772.68

95th percentile: 219.36

sum: 4800223.99

Threads fairness:

events (avg/stddev): 2606.6875/679.58

execution time (avg/stddev): 300.0140/0.02

可以看上去,proxysql的效果还不错，损耗是比较低的。在压测过程中发现我这个虚拟机(只有1个cpu)
的cpu基本上要被耗尽，其中proxysql消耗了近80%的cpu。

CPU Utilisation ---------------------------------------------------------------------------------
|---------------------------+-------------------------------------------------+                   
|CPU  User%  Sys% Wait% Idle|0          |25         |50          |75       100|                   
|  1  35.7  59.1   0.0   5.3|UUUUUUUUUUUUUUUUUsssssssssssssssssssssssssssss >>|                   
|---------------------------+-------------------------------------------------+                   
| Kernel Stats -----------------------------------------------------------------------------------
| RunQueue              7   Load Average    CPU use since boot time                               
| ContextSwitch    7048.8    1 mins  2.89    Uptime Days= 24 Hours=14 Mins=59                     
| Forks               0.0    5 mins  1.55    Idle   Days= 24 Hours= 1 Mins=16                     
| Interrupts       4913.1   15 mins  1.08    Average CPU use=  2.32%                              
| Network I/O ------------------------------------------------------------------------------------
|I/F Name Recv=KB/s Trans=KB/s packin packout insize outsize Peak->Recv Trans                     
|virbr0-nic     0.0     0.0       0.0      0.0     0.0    0.0        0.0     0.0                  
|  virbr0     0.0     0.0       0.0      0.0     0.0    0.0        0.0     0.0                    
|   ens33  5928.3   464.9    7613.0   5965.5   797.4   79.8    159409.7 12942.7   .7              
|      lo  5728.4  5728.4    5634.2   5634.2  1041.1 1041.1    154408.8 154408.8   8.8            
|-------------------------------------------------------------------------------------------------
PerfTop:     770 irqs/sec  kernel:76.8%  exact:  0.0% [1000Hz cpu-clock],  (target_pid: 117862)
--------------------------------------------------------------------------------------------------
45.37%  [kernel]            [k] e1000_xmit_frame                                              
3.52%  [kernel]            [k] _raw_spin_unlock_irqrestore                                   
3.34%  [kernel]            [k] finish_task_switch                                            
1.70%  [kernel]            [k] __fget                                                        
1.47%  [kernel]            [k] e1000_clean                                                   
1.45%  proxysql            [.] _ZN12MySQL_Thread3runEv                                       
1.25%  libc-2.17.so        [.] __memcpy_ssse3_back                                           
1.22%  [kernel]            [k] sock_poll

CPU Utilisation ---------------------------------------------------------------------------------

|---------------------------+-------------------------------------------------+

|CPU User% Sys% Wait% Idle|0 |25 |50 |75 100|

| 1 35.7 59.1 0.0 5.3|UUUUUUUUUUUUUUUUUsssssssssssssssssssssssssssss >>|

|---------------------------+-------------------------------------------------+

| Kernel Stats -----------------------------------------------------------------------------------

| RunQueue 7 Load Average CPU use since boot time

| ContextSwitch 7048.8 1 mins 2.89 Uptime Days= 24 Hours=14 Mins=59

| Forks 0.0 5 mins 1.55 Idle Days= 24 Hours= 1 Mins=16

| Interrupts 4913.1 15 mins 1.08 Average CPU use= 2.32%

| Network I/O ------------------------------------------------------------------------------------

|I/F Name Recv=KB/s Trans=KB/s packin packout insize outsize Peak->Recv Trans

|virbr0-nic 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

| virbr0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

| ens33 5928.3 464.9 7613.0 5965.5 797.4 79.8 159409.7 12942.7 .7

| lo 5728.4 5728.4 5634.2 5634.2 1041.1 1041.1 154408.8 154408.8 8.8

|-------------------------------------------------------------------------------------------------

PerfTop: 770 irqs/sec kernel:76.8% exact: 0.0% [1000Hz cpu-clock], (target_pid: 117862)

--------------------------------------------------------------------------------------------------

45.37% [kernel] [k] e1000_xmit_frame

3.52% [kernel] [k] _raw_spin_unlock_irqrestore

3.34% [kernel] [k] finish_task_switch

1.70% [kernel] [k] __fget

1.47% [kernel] [k] e1000_clean

1.45% proxysql [.] _ZN12MySQL_Thread3runEv

1.25% libc-2.17.so [.] __memcpy_ssse3_back

1.22% [kernel] [k] sock_poll

主要在消耗在e1000_xmit_frame 调用上，这是Linux os调用网卡的操作函数，这也跟前面nmon的监控比较符合；ContextSwitch 是很高的。

由此可见，对于mgr+proxysql的架构；Proxysql 所在节点的机器配置也不能过低，否则还是会有所影响的。

那么能否通过proxysql来让应用对mgr节点的切换无感知呢？当节点切换后，自动连接mgr，无需修改配置。

首先在mgr节点执行相关脚本，创建响应的函数来实现这个功能，这里我直接使用了老外的脚本。链接地址：https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql

mysql> source add_to_sys.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

mysql> source add_to_sys.sql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

–配置scheduler

这里网直接使用了网友的脚本 https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker/gr_sw_mode_checker.sh

将脚本放在目录/var/lib/proxysql/即可；然后在scheduler中插入记录。

[root@mysqldb proxysql]# mysql -uadmin -padmin -h127.0.0.1 -P6032                
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 203
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> insert into scheduler(id, active, interval_ms, filename, arg1, arg2, arg3, arg4)
->   values(1, 1, 3000, '/var/lib/proxysql/mgr_node_check.sh', 10, 30, 1, '/var/lib/proxysql/check.log');
Query OK, 1 row affected (0.01 sec)
mysql> save scheduler to disk;
Query OK, 0 rows affected (0.01 sec)
mysql> load scheduler to runtime;
Query OK, 0 rows affected (0.00 sec)

[root@mysqldb proxysql]# mysql -uadmin -padmin -h127.0.0.1 -P6032

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 203

Server version: 5.5.30 (ProxySQL Admin Module)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into scheduler(id, active, interval_ms, filename, arg1, arg2, arg3, arg4)

-> values(1, 1, 3000, '/var/lib/proxysql/mgr_node_check.sh', 10, 30, 1, '/var/lib/proxysql/check.log');

Query OK, 1 row affected (0.01 sec)

mysql> save scheduler to disk;

Query OK, 0 rows affected (0.01 sec)

mysql> load scheduler to runtime;

Query OK, 0 rows affected (0.00 sec)

—我们来看下目前节点的情况

[root@killdb lua]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 18343
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;w-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
w-mgr-test-enmotech.service.consul. 0 IN A      172.16.29.132
;; ADDITIONAL SECTION:
w-mgr-test-enmotech.service.consul. 0 IN TXT    "consul-network-segment="
;; Query time: 4 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Mon Oct  9 05:56:36 2017
;; MSG SIZE  rcvd: 104

[root@killdb lua]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 18343

;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;w-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

w-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.132

;; ADDITIONAL SECTION:

w-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 4 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Mon Oct 9 05:56:36 2017

;; MSG SIZE rcvd: 104

目前132是写节点，我们现在将132节点停掉.

mysql> stop group_replication;
Query OK, 0 rows affected (9.17 sec)
mysql> 
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> stop group_replication;

Query OK, 0 rows affected (9.17 sec)

mysql>

mysql> shutdown;

Query OK, 0 rows affected (0.00 sec)

再次解析域名看看write节点是否转移。

[root@killdb lua]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 13738
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;w-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
w-mgr-test-enmotech.service.consul. 0 IN A      172.16.29.133
;; ADDITIONAL SECTION:
w-mgr-test-enmotech.service.consul. 0 IN TXT    "consul-network-segment="
;; Query time: 3 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Mon Oct  9 06:08:21 2017
;; MSG SIZE  rcvd: 104

[root@killdb lua]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 13738

;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;w-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

w-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.133

;; ADDITIONAL SECTION:

w-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 3 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Mon Oct 9 06:08:21 2017

;; MSG SIZE rcvd: 104

可以看到write节点转移到了133. 检查此时proxysql的配置，其实并没有改变：

mysql> select * from runtime_mysql_servers;
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.29.133 | 3306 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              | read    |
| 40           | 172.16.29.132 | 3306 | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              | write   |
| 30           | 172.16.29.154 | 3306 | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              | read    |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> select * from runtime_mysql_servers;

+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

| 10 | 172.16.29.133 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | read |

| 40 | 172.16.29.132 | 3306 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | write |

| 30 | 172.16.29.154 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | read |

+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

仍然显示132是write节点，133，154是read 节点。

这里其实不影响，此时check脚本运行正常。如下是日志：

[root@mysqldb proxysql]# tail -10f check.log                          
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@mysqldb proxysql]# tail -10f check.log

mysql: [Warning] Using a password on the command line interface can be insecure.

下面来测试一下，是否仍然可以进行正常的操作：

[root@mysqldb proxysql]#  mysql -umgrtest -pmgrtest -h127.0.0.1 -P6033  
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4198
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table test99(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> alter table test99 add primary key(a);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> insert into test99 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test99;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
mysql> truncate table test99;
Query OK, 0 rows affected (0.01 sec)

[root@mysqldb proxysql]# mysql -umgrtest -pmgrtest -h127.0.0.1 -P6033

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4198

Server version: 5.5.30 (ProxySQL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test99(a int);

Query OK, 0 rows affected (0.01 sec)

mysql> alter table test99 add primary key(a);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into test99 values(1);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test99;

+---+

| a |

+---+

| 1 |

+---+

1 row in set (0.01 sec)

mysql> truncate table test99;

Query OK, 0 rows affected (0.01 sec)

可见此时对于应用来说几乎是无感知的。祝大家玩得开心！

Tags: mysql group replication, proxysql
Posted in MySQL, MySQL 架构设计 on 12月 29, 2018

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

MySQL Group Replication+ProxySQL进行读写分离测试

Leave a Reply