五、测试读写分离
-- 测试读写分离
mysql -uwr -plhr -h192.168.66.35 -P26033
create database test_proxysql;
use test_proxysql;
create table test_tables(name varchar(20),age int(4));
insert into test_tables values('lhr','33');
select * from test_tables;
select * from stats_mysql_query_digest;
MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 10 | 192.168.68.131 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 165 | 92 | 369 |
| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 8 | 0 | 142 | 130 | 301 |
| 20 | 192.168.68.133 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 97 | 67 | 341 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.07 sec)
-- 可以看到写操作被分配到131主机上。
-- 其它表的统计信息
SELECT * FROM stats.stats_mysql_connection_pool;
SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1,60) FROM stats_mysql_query_digest where schemaname='lhrmysqlslap' ORDER BY sum_time DESC;
-- 清空数据，查询stats_mysql_query_digest_reset表时，会自动从stats_mysql_query_digest中临时抓取数据，并truncate。
SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;
-- 使用sysbench继续读写分离测试
-- 配置MySQL最大连接数：set global max_connections=1000;
sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=100000 --tables=20 --threads=100 --events=999999999 prepare
sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
sysbench /usr/share/sysbench/oltp_read_only.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
-- 查询读写分离的监控数据
MySQL [(none)]> SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1,60) FROM stats_mysql_query_digest where schemaname='sbtest' ORDER BY sum_time DESC;
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1,60) |
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
| 10 | sbtest | wr | 0x76607360EFEAC208 | 84059739 | 37 | INSERT INTO sbtest14(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x02834C12D1767CBF | 81396186 | 37 | INSERT INTO sbtest19(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xF175422CAEB5052B | 80172428 | 37 | INSERT INTO sbtest5(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0xD264943870461B52 | 78250552 | 37 | INSERT INTO sbtest9(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x2F9D0B4C12C50457 | 75761155 | 37 | INSERT INTO sbtest13(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x0482F61CCAD957B8 | 74841767 | 37 | INSERT INTO sbtest16(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x2BA639A0C593250B | 74744554 | 37 | INSERT INTO sbtest20(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x131C045B3F7FC633 | 74710835 | 37 | INSERT INTO sbtest12(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x9677C76C4DF88251 | 74446715 | 37 | INSERT INTO sbtest6(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x36760936592E8530 | 74289312 | 37 | INSERT INTO sbtest2(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0xEFBA5FC0C8412297 | 72425766 | 37 | INSERT INTO sbtest18(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xDF47484FFE945EDD | 70572836 | 37 | INSERT INTO sbtest8(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x27D376AC1710C980 | 66516942 | 37 | INSERT INTO sbtest11(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x4D433FDEA1B945C1 | 65829143 | 37 | INSERT INTO sbtest10(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xFF6D9C6F32545951 | 65739929 | 37 | INSERT INTO sbtest1(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x772AE6B66160E91E | 65271284 | 37 | INSERT INTO sbtest7(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x4F8D29910113CAE6 | 64822306 | 37 | INSERT INTO sbtest15(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x7502AE74F4B0113E | 64133812 | 37 | INSERT INTO sbtest4(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x4B083B064FF4F9D9 | 61931719 | 37 | INSERT INTO sbtest17(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x3C6D29F861CD6572 | 60387247 | 37 | INSERT INTO sbtest3(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x8965AD5701787BC0 | 33638613 | 1 | CREATE INDEX k_3 ON sbtest3(k) |
| 10 | sbtest | wr | 0xB12065B0D79AC0DD | 33288092 | 1 | CREATE INDEX k_17 ON sbtest17(k) |
| 10 | sbtest | wr | 0xC46D61BAA605D7D3 | 32926000 | 1 | CREATE INDEX k_1 ON sbtest1(k) |
| 10 | sbtest | wr | 0x2BF8C8E7084502D5 | 32904517 | 1 | CREATE INDEX k_7 ON sbtest7(k) |
| 10 | sbtest | wr | 0x6F22CFB8FF512B02 | 32625659 | 1 | CREATE INDEX k_4 ON sbtest4(k) |
| 10 | sbtest | wr | 0x14A86D647A425E21 | 31622991 | 1 | CREATE INDEX k_15 ON sbtest15(k) |
| 10 | sbtest | wr | 0xF9D03F580356BB68 | 31573312 | 1 | CREATE INDEX k_11 ON sbtest11(k) |
| 10 | sbtest | wr | 0xA43F49E4ADA080FB | 31346640 | 1 | CREATE INDEX k_10 ON sbtest10(k) |
| 10 | sbtest | wr | 0x922B9C1E888EB4C9 | 29435206 | 1 | CREATE INDEX k_8 ON sbtest8(k) |
| 10 | sbtest | wr | 0x409A0DA0B5B6EEF9 | 28482669 | 1 | CREATE INDEX k_2 ON sbtest2(k) |
| 10 | sbtest | wr | 0xE4300864715B3688 | 28000903 | 1 | CREATE INDEX k_20 ON sbtest20(k) |
| 10 | sbtest | wr | 0x4DE9E56B5EF734F2 | 27918352 | 1 | CREATE INDEX k_6 ON sbtest6(k) |
| 10 | sbtest | wr | 0x82CE0656182236D8 | 27909444 | 1 | CREATE INDEX k_12 ON sbtest12(k) |
| 10 | sbtest | wr | 0xFE8EAD5ACC9FEEDE | 27273704 | 1 | CREATE INDEX k_18 ON sbtest18(k) |
| 10 | sbtest | wr | 0x35A492B2AB47EB41 | 26754664 | 1 | CREATE INDEX k_16 ON sbtest16(k) |
| 10 | sbtest | wr | 0xF5B52253F5260086 | 21401807 | 1 | CREATE INDEX k_9 ON sbtest9(k) |
| 10 | sbtest | wr | 0xA1B769A0F4E9637C | 21271034 | 1 | CREATE INDEX k_13 ON sbtest13(k) |
| 10 | sbtest | wr | 0xFBE6F4A5E871D069 | 21202873 | 1 | CREATE INDEX k_5 ON sbtest5(k) |
| 10 | sbtest | wr | 0x004D21922AA0CC4C | 19075137 | 1 | CREATE INDEX k_19 ON sbtest19(k) |
| 10 | sbtest | wr | 0x6057175824222B09 | 18110480 | 1 | CREATE INDEX k_14 ON sbtest14(k) |
| 10 | sbtest | wr | 0x5143272478FE391F | 5837193 | 1 | INSERT INTO sbtest13(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xD9E2214392AB9E0D | 2791511 | 1 | INSERT INTO sbtest9(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x4AC5841F976F5A1A | 1686763 | 2 | CREATE TABLE sbtest5( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x0E320961406063D2 | 1608309 | 1 | INSERT INTO sbtest3(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x1886E479A84E6EF3 | 1364378 | 1 | INSERT INTO sbtest19(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xDC0701A550CF81E1 | 1315637 | 2 | CREATE TABLE sbtest14( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xDE42F97C93E70D2F | 1213739 | 2 | CREATE TABLE sbtest9( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x69426F34842FCBCB | 1167716 | 1 | INSERT INTO sbtest16(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x3455DC796FFE13FF | 1096589 | 2 | CREATE TABLE sbtest19( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xCB672EA01B2BCC66 | 1059624 | 2 | CREATE TABLE sbtest18( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x68A680665F3A4F7F | 1046881 | 1 | INSERT INTO sbtest18(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x988823E25FA87160 | 1008422 | 2 | CREATE TABLE sbtest7( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x630131CEA842636C | 941575 | 1 | INSERT INTO sbtest17(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xCFABCFBA3338DFE6 | 883756 | 2 | CREATE TABLE sbtest13( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x38C679BD1A2B850D | 800858 | 2 | CREATE TABLE sbtest20( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xA8C074E066D84361 | 745750 | 2 | CREATE TABLE sbtest2( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x644F2A01D1AEE6F0 | 618114 | 1 | INSERT INTO sbtest8(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0xA6A432C624F814B1 | 588508 | 1 | INSERT INTO sbtest7(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0xAC072AB4E74DCA04 | 587709 | 1 | INSERT INTO sbtest1(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x6A5DF2EE9E492E4E | 544548 | 1 | INSERT INTO sbtest4(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0xB483ABDF2ACB307D | 523796 | 2 | CREATE TABLE sbtest16( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xDD3B26A209175EF7 | 520238 | 1 | INSERT INTO sbtest10(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x1061831367EE99C7 | 453881 | 1 | INSERT INTO sbtest14(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x7F93BDE97051D79A | 440020 | 2 | CREATE TABLE sbtest6( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x806A9CCB80119BE2 | 417349 | 2 | CREATE TABLE sbtest1( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x43CA6E3D0072BCBE | 402073 | 2 | CREATE TABLE sbtest12( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xA204975AD230A23B | 400655 | 2 | CREATE TABLE sbtest8( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x11E6BAC23207DD78 | 381162 | 1 | INSERT INTO sbtest2(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x42B1839D8797EDCB | 379488 | 1 | INSERT INTO sbtest12(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0x583CBA28271C4365 | 357866 | 2 | CREATE TABLE sbtest4( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x6ACC6500F6722004 | 340867 | 2 | CREATE TABLE sbtest15( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x5408EB0F722B3B6F | 324340 | 2 | CREATE TABLE sbtest10( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xCD3FA57950F3E362 | 293837 | 1 | INSERT INTO sbtest5(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x4196561D18B78360 | 285839 | 1 | INSERT INTO sbtest20(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xE7DCF83C81EDEA8D | 265476 | 2 | CREATE TABLE sbtest11( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xFA3A3817BE19ABB3 | 250778 | 2 | CREATE TABLE sbtest3( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x6B8F384E1250D83C | 247460 | 2 | CREATE TABLE sbtest17( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x26E4B187688CC6BE | 218255 | 1 | INSERT INTO sbtest6(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
| 10 | sbtest | wr | 0x9E18B2E0420BA351 | 200861 | 1 | INSERT INTO sbtest15(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
| 10 | sbtest | wr | 0xAB7D4ACFF578DC61 | 199109 | 1 | INSERT INTO sbtest11(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
80 rows in set (0.71 sec)
MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN | 2793013652 | 15681 | 95 | 888 | 1864 | 5806 | 2671 | 2882 | 447 | 266 | 33 | 533 | 190 | 6 |
| COMMIT | 4360624460 | 15575 | 3 | 31 | 58 | 191 | 138 | 1031 | 2337 | 9827 | 1369 | 590 | 0 | 0 |
| CREATE_DATABASE | 99871 | 4 | 0 | 0 | 0 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_INDEX | 556762097 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 |
| CREATE_TABLE | 13849772 | 43 | 0 | 0 | 1 | 0 | 0 | 21 | 1 | 11 | 4 | 5 | 0 | 0 |
| DELETE | 603363949 | 14940 | 45 | 197 | 1637 | 5575 | 2424 | 2997 | 771 | 1081 | 136 | 77 | 0 | 0 |
| INSERT | 1734742355 | 15812 | 38 | 339 | 1872 | 6251 | 2484 | 2887 | 586 | 590 | 138 | 605 | 21 | 1 |
| SELECT | 3704563235 | 319925 | 965 | 12937 | 109961 | 96434 | 40564 | 44484 | 7667 | 6460 | 376 | 77 | 0 | 0 |
| UPDATE | 1788596841 | 29900 | 45 | 166 | 2336 | 9589 | 4487 | 6847 | 2123 | 3682 | 445 | 180 | 0 | 0 |
| SHOW | 58391 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 152706 | 3 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
11 rows in set (0.11 sec)
可以观察到读写分离的数据。
About Me
● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在个人微 信公众号（DB宝）上有同步更新
● QQ群号： 230161599 、618766405，微信群私聊
● 个人QQ号（646634621），微 信号（db_bao），注明添加缘由
● 于 2021年3月 在西安完成
● 最新修改时间：2021年3月
● 版权所有，欢迎分享本文，转载请保留出处
●小麦苗的微店： https://weidian.com/?userid=793741433
●小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/
●小麦苗OCP、OCM、高可用、DBA学习班（Oracle、MySQL、NoSQL）： http://blog.itpub.net/26736162/viewspace-2148098/
●数据库笔试面试题库及解答： https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（DB宝）及QQ群（DBA宝典）、添加小麦苗微信， 学习最实用的数据库技术。
