当前环境是一主两从的组复制结构,今天试一下读写分离,将写操作都应用到主库,读操作都应用到两个从库。
各server说明:
qht131 172.17.61.131 master
qht132 172.17.61.132 second1
qht133 172.17.61.133 second2
qht134 172.17.61.134 proxy+sysbench
1.登入proxysql,插入配置信息
[root@qht134 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
mysql> INSERT INTO MySQL_servers(hostgroup_id, hostname, port) VALUES (0,'172.17.61.131',3306);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO MySQL_servers(hostgroup_id, hostname, port) VALUES (1,'172.17.61.132',3306);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO MySQL_servers(hostgroup_id, hostname, port) VALUES (1,'172.17.61.133',3306);
Query OK, 1 row affected (0.00 sec)
mysql> select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+---------------+------+--------+
| 0 | 172.17.61.131 | 3306 | ONLINE |
| 1 | 172.17.61.132 | 3306 | ONLINE |
| 1 | 172.17.61.133 | 3306 | ONLINE |
+--------------+---------------+------+--------+
3 rows in set (0.00 sec)
将qht131定义为group 0, 将qht132和qht133定义为group 1。
2.在数据库端建立proxysql登入需要的帐号(如之前已建立好的话直跳过此步骤)
mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected (0.41 sec)
mysql> grant usage on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> create user 'sbuser'@'%' IDENTIFIED BY 'sbpass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON * . * TO 'sbuser'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.07 sec)
3.在proxysql中增加帐号
mysql> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('sbuser','sbpass',0);
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
mysql> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL VARIABLES TO DISK;
Query OK, 94 rows affected (0.02 sec)
mysql> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.03 sec)
mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
测试一下能否正常登入数据库
[root@qht134 ~]# mysql -u sbuser -psbpass -h 127.0.0.1 -P6033 -e"select @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3306 |
+--------+
4. 用sysbench来压测mysql
[root@qht134 sysbench]# sysbench --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-host=127.0.0.1 --mysql-db=l5m oltp_read_write prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
在proxysql中用下面的语句先清空stats_mysql_query_digest的数据
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.12 sec)
开始测压:
[root@qht134 sysbench]# sysbench --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-host=127.0.0.1 --mysql-db=l5m oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 9492
write: 2113
other: 1955
total: 13560
transactions: 678 (67.71 per sec.)
queries: 13560 (1354.12 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 67.7061
time elapsed: 10.0139s
total number of events: 678
Latency (ms):
min: 7.95
avg: 14.76
max: 345.15
95th percentile: 25.74
sum: 10005.75
Threads fairness:
events (avg/stddev): 678.0000/0.00
execution time (avg/stddev): 10.0057/0.00
查看一下收集了哪些数据:
mysql> mysql> select * from stats_mysql_commands_counters where total_cnt>0;
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| 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 | 1810291 | 1230 | 1 | 1212 | 7 | 8 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
| COMMIT | 10777309 | 1230 | 1 | 0 | 0 | 684 | 357 | 172 | 7 | 9 | 0 | 0 | 0 | 0 |
| CREATE_INDEX | 1691195 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 1 | 0 | 1 | 0 | 0 |
| CREATE_TABLE | 1512616 | 6 | 0 | 0 | 1 | 0 | 0 | 2 | 2 | 0 | 0 | 1 | 0 | 0 |
| DELETE | 938340 | 1230 | 1 | 1217 | 7 | 1 | 0 | 3 | 0 | 0 | 1 | 0 | 0 | 0 |
| DROP_TABLE | 383931 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 0 | 0 | 0 | 0 |
| INSERT | 80309813 | 5685 | 2 | 1208 | 10 | 4091 | 291 | 46 | 12 | 9 | 1 | 12 | 2 | 1 |
| SELECT | 24912547 | 17216 | 12 | 16501 | 636 | 46 | 3 | 14 | 2 | 0 | 0 | 0 | 0 | 2 |
| UPDATE | 1633335 | 2460 | 3 | 2398 | 42 | 3 | 1 | 12 | 0 | 0 | 1 | 0 | 0 | 0 |
| SHOW | 248177 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
10 rows in set (0.00 sec)
非常不错的统计信息,各种命令的聚合统计,方便我们监控数据库负载趋势,一眼就可以看出读多还是写多。
还有更具体的统计信息。
mysql> select * from stats_mysql_query_digest order by sum_time DESC;
+-----------+------------+----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | l5m | sbuser | 0x695FBF255DBEB0DD | COMMIT | 679 | 1526918066 | 1526918076 | 5781949 | 2662 | 335908 |
| 0 | l5m | sbuser | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=? | 6781 | 1526918066 | 1526918076 | 1551932 | 134 | 38261 |
| 0 | l5m | sbuser | 0xC19480748AE79B4B | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 679 | 1526918066 | 1526918076 | 341165 | 289 | 2711 |
| 0 | l5m | sbuser | 0xC198E52BCCB481C7 | UPDATE sbtest1 SET k=k+? WHERE id=? | 679 | 1526918066 | 1526918076 | 251003 | 172 | 17739 |
| 0 | l5m | sbuser | 0xAC80A5EA0101522E | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 679 | 1526918066 | 1526918076 | 231798 | 231 | 1759 |
| 0 | l5m | sbuser | 0xE52A0A0210634DAC | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | 679 | 1526918066 | 1526918076 | 224630 | 180 | 29356 |
| 0 | l5m | sbuser | 0x290B92FD743826DA | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? | 679 | 1526918066 | 1526918076 | 222311 | 233 | 3284 |
| 0 | l5m | sbuser | 0xDBF868B2AA296BC5 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ? | 679 | 1526918066 | 1526918076 | 179803 | 187 | 10738 |
| 0 | l5m | sbuser | 0xFAD1519E4760CBDE | BEGIN | 679 | 1526918066 | 1526918076 | 177774 | 157 | 3793 |
| 0 | l5m | sbuser | 0xFB239BC95A23CA36 | UPDATE sbtest1 SET c=? WHERE id=? | 679 | 1526918066 | 1526918076 | 166028 | 153 | 570 |
| 0 | l5m | sbuser | 0xE365BEB555319B9E | DELETE FROM sbtest1 WHERE id=? | 679 | 1526918066 | 1526918076 | 156263 | 150 | 748 |
+-----------+------------+----------+--------------------+--------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
11 rows in set (0.00 sec)不
sysbench采集了这段时间所有sql信息,然后做出汇总信息。
不过目前还没有做读写分离,hostgroup都显示的是0,也就是所有语句都是从qht131中执行的。
5.读写分离设置
[root@qht134 sysbench]# mysql -uadmin -padmin -h 127.0.0.1 -P6032
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',0,1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.08 sec)
match_pattern的规则是基于正则表达式的,
active表示是否启用这个sql路由项,
match_pattern就是我们正则匹配项,destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去,这里我们将select转发到group 1,也就是两个slave上。
apply为1表示该正则匹配后,将不再接受其他匹配,直接转发。
首先记得清空proxysql的query统计
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
接着再进行一次压测:
[root@qht134 sysbench]# sysbench --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-host=127.0.0.1 --mysql-db=l5m oltp_read_write cleanup
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Dropping table 'sbtest1'...
[root@qht134 sysbench]# sysbench --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-host=127.0.0.1 --mysql-db=l5m oltp_read_write prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@qht134 sysbench]# sysbench --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-host=127.0.0.1 --mysql-db=l5m oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 9702
write: 2150
other: 2008
total: 13860
transactions: 693 (69.24 per sec.)
queries: 13860 (1384.73 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 69.2363
time elapsed: 10.0092s
total number of events: 693
Latency (ms):
min: 8.52
avg: 14.43
max: 389.49
95th percentile: 24.83
sum: 10003.09
Threads fairness:
events (avg/stddev): 693.0000/0.00
execution time (avg/stddev): 10.0031/0.00
查看下结果:
mysql> select hostgroup hg,sum_time,count_star,digest_text from stats_mysql_query_digest order by sum_time DESC\G
*************************** 1. row ***************************
hg: 0
sum_time: 4909546
count_star: 694
digest_text: COMMIT
*************************** 2. row ***************************
hg: 1
sum_time: 2530591
count_star: 6931
digest_text: SELECT c FROM sbtest1 WHERE id=?
*************************** 3. row ***************************
hg: 0
sum_time: 1971736
count_star: 4
digest_text: INSERT INTO sbtest1(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(
*************************** 4. row ***************************
hg: 1
sum_time: 933210
count_star: 694
digest_text: SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
*************************** 5. row ***************************
hg: 0
sum_time: 347427
count_star: 694
digest_text: UPDATE sbtest1 SET k=k+? WHERE id=?
*************************** 6. row ***************************
hg: 1
sum_time: 287341
count_star: 694
digest_text: SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
*************************** 7. row ***************************
hg: 1
sum_time: 248992
count_star: 694
digest_text: SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?
*************************** 8. row ***************************
hg: 1
sum_time: 245313
count_star: 694
digest_text: SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?
*************************** 9. row ***************************
hg: 0
sum_time: 169631
count_star: 694
digest_text: INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)
*************************** 10. row ***************************
hg: 0
sum_time: 168597
count_star: 694
digest_text: UPDATE sbtest1 SET c=? WHERE id=?
*************************** 11. row ***************************
hg: 0
sum_time: 166361
count_star: 694
digest_text: DELETE FROM sbtest1 WHERE id=?
*************************** 12. row ***************************
hg: 0
sum_time: 154297
count_star: 694
digest_text: BEGIN
*************************** 13. row ***************************
hg: 0
sum_time: 85134
count_star: 1
digest_text: DROP TABLE IF EXISTS sbtest1
*************************** 14. row ***************************
hg: 0
sum_time: 64026
count_star: 1
digest_text: CREATE INDEX k_1 ON sbtest1(k)
*************************** 15. row ***************************
hg: 0
sum_time: 48234
count_star: 1
digest_text: CREATE TABLE sbtest1( id INTEGER NOT NULL AUTO_INCREMENT, k INTEGER DEFAULT ? NOT NULL, c CHAR(?) DEFAULT ? NOT NULL, pad CHAR(?) DEFAULT ? NOT NULL, PRIMARY KEY (id) ) ENGINE = innodb
15 rows in set (0.00 sec)
可以看到,所有的非select*for update的查询语句都已经转发到slave了,也就是group 1.
参考:http://www.fordba.com/mysql_proxysql_rw_split.html