本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger的Oracle&MySQL技术博客
目前主流的中间件有很多，比如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
2. yum安装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
关于默认的用户和密码信息，以及端口等内容，均可直接查看/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)
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);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;
操作完毕后，我们看看此时的情况：
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)
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可以看上去,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主要在消耗在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)
–配置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@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)
再次解析域名看看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
可以看到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)
仍然显示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]# 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)
可见此时对于应用来说几乎是无感知的。祝大家玩得开心！
