四、测试读负载均衡
[root@docker35 ~]# for i in $(seq 1 10); do mysql -uwr -plhr -h192.168.68.136 -P6033 -e 'select @@server_id;'; done | egrep '[0-9]'
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
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 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 323 |
| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 6 | 0 | 108 | 84 | 280 |
| 20 | 192.168.68.133 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 4 | 0 | 72 | 56 | 390 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.06 sec)
-- 可以看到Queries列分配到132和133的从库上。
-- 接下来使用mysqlslap来做压测
[root@docker35 ~]#
[root@docker35 ~]# mysqlslap -uwr -plhr -h192.168.68.136 -P6033 --create-schema=lhrmysqlslap --auto-generate-sql --auto-generate-sql-load-type=read --number-of-queries=100000
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 104.757 seconds
Minimum number of seconds to run all queries: 104.757 seconds
Maximum number of seconds to run all queries: 104.757 seconds
Number of clients running queries: 1
Average number of queries per client: 100000
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 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
| SELECT | 15299552 | 22293 | 263 | 1588 | 18947 | 1453 | 29 | 10 | 2 | 1 | 0 | 0 | 0 | 0 |
| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.11 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 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
| SELECT | 16494138 | 24050 | 263 | 1744 | 20488 | 1512 | 29 | 10 | 3 | 1 | 0 | 0 | 0 | 0 |
| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.07 sec)
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 | 107 | 0 | 16464 | 92 | 277 |
| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 24052 | 0 | 768920 | 335907539 | 306 |
| 20 | 192.168.68.133 | 3306 | ONLINE | 1 | 0 | 1 | 0 | 1 | 24061 | 0 | 769119 | 335923167 | 381 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.39 sec)
MySQL [(none)]> 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;
+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1,60) |
+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
| 20 | lhrmysqlslap | wr | 0x91B125A563AED6EB | 65166273 | 100000 | SELECT intcol1,charcol1 FROM t1 |
| 10 | lhrmysqlslap | wr | 0xBCFD962F4A5FFA4B | 1134087 | 99 | INSERT INTO t1 VALUES (?,?) |
| 10 | lhrmysqlslap | wr | 0x50E8C33778819FCD | 23904 | 1 | CREATE TABLE `t1` (intcol1 INT(?) ,charcol1 VARCHAR(?)) |
| 10 | lhrmysqlslap | wr | 0x41B7F05694EF426F | 21351 | 1 | DROP SCHEMA IF EXISTS `lhrmysqlslap` |
+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
4 rows in set (0.08 sec)
可以看出，负载被分别被分配到132和133上。
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宝典）、添加小麦苗微信， 学习最实用的数据库技术。
