基本信息

ProxySQL 版本:v1.4.12
ProxySQL OS: CentOS7
压测工具:sysbench
后端server: mysql5.7.22, 一主一从架构
MySQL OS: CentOS6

ProxySQL 配置:

ProxySQL的变量设置均为默认。

  • mysql_users表配置

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> select * from mysql_users;
    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | 1 | 0 | 0 | | 0 | 1 | 0 | 0 | 1 | 10000 |
    | test | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | 1 | 0 | 0 | | 0 | 1 | 0 | 1 | 0 | 10000 |
    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
    2 rows in set (0.00 sec)
  • mysql_servers表配置

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> select * from mysql_servers;
    +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
    | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
    +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
    | 0 | 192.168.216.203 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
    | 1 | 192.168.216.204 | 3306 | ONLINE | 4 | 0 | 1000 | 30 | 0 | 0 | |
    +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
    6 rows in set (0.00 sec)

    主库: 192.168.216.203 对应主机组0
    从库: 192.168.216.204 对应主机组1

  • mysql_query_rules配置
    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> select * from mysql_query_rules;
    +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
    | rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
    +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
    | 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | . | NULL | NULL | 0 | CASELESS | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 0 | NULL |
    | 20 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 1 | NULL |
    | 30 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | 1 | NULL |
    +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
    3 rows in set (0.00 sec)

未配置表mysql_replication_hostgroups,禁止proxysql依据mysql的read_only属性更改主机组。主要是防止从机该属性被误改,造成写操作发送到从库。
另外需要注意,若不配置表mysql_replication_hostgroups,mysql-monitor_writer_is_also_reader=true不能正常工作。

功能测试

为验证测试语句分发,后端mysql主从都开启了general_log.

基于MySQL主从global autocommit=1。MySQLglobal autocommit=0时有诸多莫名其妙的问题

登陆ProxySQL 6033端口

1. 测试简单查询:
1
2
3
4
5
6
7
8
9
10
mysql> select * from sbtest1 limit 4;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 2 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
| 2 | 99 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
| 3 | 4989423 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
| 4 | 33 | 54133149494-75722987476-23015721680-47254589498-40242947469-55055884969-23675271222-20181439230-74473404563-55407972672 | 88488171626-98596569412-94026374972-58040528656-38000028170 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
4 rows in set (0.00 sec)

在管理界面可以看到该查询路由到了reader group。

1
2
3
4
5
6
7
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
| 1 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1541224735 | 1541224735 | 3402 | 3402 | 3402 |
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
1 row in set (0.00 sec)

2. 测试set autocommit=0

在ProxySQL Client接口执行:

1
2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

在管理界面查看路由,显示发送到了writer group:

1
2
3
4
5
6
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x2FB86F938EF30630 | set autocommit=? | 1 | 1541225055 | 1541225055 | 0 | 0 | 0 |
+-----------+------------+----------+--------------------+------------------+------------+------------+------------+----------+----------+----------+

但在主从的general log中都没有看到该语句。该语句并没有真正分发到后台。
再执行查询:

1
2
3
4
5
6
7
mysql> select * from sbtest1 limit 1;
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 2 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

查看SQL路由:

1
2
3
4
5
6
7
8
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
| 1 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1541225087 | 1541225087 | 2960 | 2960 | 2960 |
| 0 | test | test | 0x2FB86F938EF30630 | set autocommit=? | 1 | 1541225055 | 1541225055 | 0 | 0 | 0 |
+-----------+------------+----------+--------------------+-------------------------------+------------+------------+------------+----------+----------+----------+
2 rows in set (0.01 sec)

显示该语句发送到了reader group,在从库的general log中确实收到了该语句,但set autocommit=0没有随之分发。 在ProxySQL client接口执行commit; 未发送到后端任何server.

总结:对于简单查询直接发送SQL即可,执行set autocommit=0;select … from table ; commit;没有意义。

3. DML,select综合测试

接着上面继续测试,在proxysql client端执行show tables; 在mysql主库的general log中发现set autocommit=0;出现在了show tables 前面,此时连接的autocommit已经关闭了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
+----------------+
5 rows in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)

再次执行select 查询,仍然发送到从库。执行update语句,分发到主库,

1
2
3
mysql> update sbtest1 set k=9 where id =3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

同时可看到后端连接与前端绑定,至此一个事务算是开始了。后面的所有SQL都会使用同一个连接。

1
2
3
4
5
6
7
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 1 | 1025087 | test | test | 127.0.0.1 | 42042 | 0 | 192.168.216.146 | 57418 | 192.168.216.203 | 3306 | Sleep | 6150 | |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.00 sec)

执行Select , 发送到主库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> select * from sbtest1 where id=3;
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 3 | 9 | 51185622598-89397522786-28007882305-52050087550-68686337807-48942386476-96555734557-05264042377-33586177817-31986479495 | 00592560354-80393027097-78244247549-39135306455-88936868384 |
+----+---+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.01 sec)

第三条记录,显示select发送到了writer group,同时主库的general log中收到了此SQL。

mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x0724CA2D4ED1C95A | update sbtest1 set k=? where id =? | 1 | 1541226725 | 1541226725 | 5319 | 5319 | 5319 |
| 0 | test | test | 0xB06310E983BD5E0B | show variables like ? | 1 | 1541226250 | 1541226250 | 6335 | 6335 | 6335 |
| 0 | test | test | 0x5662D7CF0442E794 | select * from sbtest1 where id=? | 1 | 1541226804 | 1541226804 | 2848 | 2848 | 2848 |
| 1 | test | test | 0x5662D7CF0442E794 | select * from sbtest1 where id=? | 2 | 1541226103 | 1541226577 | 5370 | 2470 | 2900 |
| 0 | test | test | 0x99531AEFF718C501 | show tables | 3 | 1541226077 | 1541226224 | 4420 | 479 | 2905 |
| 0 | test | test | 0x02033E45904D3DF0 | show databases | 1 | 1541226077 | 1541226077 | 1386 | 1386 | 1386 |
| 1 | information_schema | test | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1541226077 | 1541226077 | 1968 | 1968 | 1968 |
| 0 | information_schema | test | 0x02033E45904D3DF0 | show databases | 1 | 1541226076 | 1541226076 | 3595 | 3595 | 3595 |
| 0 | information_schema | test | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1541226072 | 1541226072 | 0 | 0 | 0 |
| 0 | test | test | 0xDB3A841EF5443C35 | commit | 2 | 1541225672 | 1541226112 | 0 | 0 | 0 |
| 1 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1541225086 | 1541225086 | 2960 | 2960 | 2960 |
| 0 | test | test | 0x2FB86F938EF30630 | set autocommit=? | 2 | 1541225054 | 1541226095 | 0 | 0 | 0 |
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
12 rows in set (0.00 sec)

查看连接,前后端依然处于绑定状态:
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 1 | 1025087 | test | test | 127.0.0.1 | 42042 | 0 | 192.168.216.146 | 57418 | 192.168.216.203 | 3306 | Sleep | 197602 | |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.00 sec)

执行commit;发送到主库,事务结束了,连接绑定断开了

1
2
3
4
5
6
7
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| 1 | 1025087 | test | test | 127.0.0.1 | 42042 | 0 | | | | | Sleep | 5249 | |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
1 row in set (0.00 sec)

再次查看autocommit连接属性,发现依然是关闭状态。说明后端连接没有随事务的结束而销毁。

1
2
3
4
5
6
7
8
9
10
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)

退出proxysql client接口,重新登陆后再次查看autocommit属性,发现是开启状态,与全局默认一致。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Your MySQL connection id is 1029467
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, 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> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

说明此时连接被销毁或重置!

总结:set autocommit=0;不会自动开启事务。该语句不会立刻分发到后端mysql,它会在proxysql中暂存,等待下一条语句。如果下一条语句是select,则不会随之分发,该select语句仍然会受路由规则的支配发送reader group。直到遇到show .., dml语句,call procedure等,set autocommit=0;才会随之分发。但只有遇到dml语句,才会发生后端连接与前端连接绑定(mysql_users.transaction_persistent=1),一个隐性事务开启了,后面的无论什么语句都会用这同一个连接发送到同一台后端server,直到commit事务结束。

4. 测试start transaction.

在ProxySQL client接口中执行 start transaction:

1
2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

在ProxySQL admin interface即可看到,该语句被发送到主库。同时在主库的general_log中也可确认收到了此语句。

1
2
3
4
5
6
7
mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+-------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+-------------------+------------+------------+------------+----------+----------+----------+
| 0 | information_schema | test | 0x326F4F2B935EC266 | start transaction | 1 | 1541231534 | 1541231534 | 1590 | 1590 | 1590 |
+-----------+--------------------+----------+--------------------+-------------------+------------+------------+------------+----------+----------+----------+
1 row in set (0.00 sec)

后端连接与前端连接也发生了绑定

1
2
3
4
5
6
7
mysql> show full processlist;
+----------+-----------+------+--------------------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+--------------------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 3 | 1030130 | test | information_schema | 127.0.0.1 | 43974 | 0 | 192.168.216.146 | 57418 | 192.168.216.203 | 3306 | Sleep | 8487 | |
+----------+-----------+------+--------------------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.00 sec)

接下来的任何语句都会发送到主库,直到commit:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x0724CA2D4ED1C95A | update sbtest1 set k=? where id =? | 1 | 1541231786 | 1541231786 | 10978 | 10978 | 10978 |
| 0 | test | test | 0x99531AEFF718C501 | show tables | 2 | 1541231767 | 1541231770 | 2137 | 651 | 1486 |
| 0 | test | test | 0x02033E45904D3DF0 | show databases | 1 | 1541231767 | 1541231767 | 4067 | 4067 | 4067 |
| 0 | information_schema | test | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1541231767 | 1541231767 | 1239 | 1239 | 1239 |
| 0 | information_schema | test | 0x38AB7C0A6E61A415 | select * from test.sbtest1 limit ? | 1 | 1541231758 | 1541231758 | 4678 | 4678 | 4678 |
| 0 | information_schema | test | 0x326F4F2B935EC266 | start transaction | 1 | 1541231535 | 1541231535 | 1590 | 1590 | 1590 |
+-----------+--------------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
6 rows in set (0.00 sec)

总结:start transaction … commit是没有问题的。同一事务的SQL都能保证发送到一个server执行。

5. 测试start transaction read only

在ProxySQL中执行start transaction read only;会被发送到writer group(主库),并且发生与start transaction一样的事情。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x9ED889E5AC870104 | start transaction read only | 1 | 1541232046 | 1541232046 | 817 | 817 | 817 |
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 3 | 1030130 | test | test | 127.0.0.1 | 43974 | 0 | 192.168.216.146 | 57418 | 192.168.216.203 | 3306 | Sleep | 41541 | |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.00 sec)

这意味着客户端使用start transaction read only 开启只读事务时,ProxySQL不会自动将它发送到reader group,而是按照我们既定的路由规则,依然发送到了writer group(主库)。一种迂回的办法是新建一条rule, 指定start transaction read only,发送到从库。这个办法是可行的。

管理界面插入规则:

1
2
insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'start transaction read only',1,1);
load mysql query rules to run;

在ProxySQL客户端执行start transaction read only,后查看路由:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
第一条,走向了(reader group)从库
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
| 1 | test | test | 0x9ED889E5AC870104 | start transaction read only | 1 | 1541232482 | 1541232482 | 1407 | 1407 | 1407 |
| 0 | test | test | 0xDB3A841EF5443C35 | commit | 1 | 1541232196 | 1541232196 | 1492 | 1492 | 1492 |
| 0 | test | test | 0x9ED889E5AC870104 | start transaction read only | 1 | 1541232045 | 1541232045 | 817 | 817 | 817 |
+-----------+------------+----------+--------------------+-----------------------------+------------+------------+------------+----------+----------+----------+
连接也与从库的连接发生了绑定。
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 3 | 1030130 | test | test | 127.0.0.1 | 43974 | 1 | 192.168.216.146 | 58392 | 192.168.216.204 | 3306 | Sleep | 7869 | |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.00 sec)

接下来的任何语句都会路由到同一个server(从库)(当然dml语句会报错)直到commit .

6. 测试begin/commit;

begin/commit,能够正常工作,依照路由规则,begin会被立刻发送到writer group(主库)并发送后端与前端的连接绑定,直到commit,绑定释放。其间的所有SQL都会使用同一个连接发送。

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+---------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+---------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0xDB3A841EF5443C35 | commit | 1 | 1541233741 | 1541233741 | 4749 | 4749 | 4749 |
| 0 | test | test | 0x267506D910073804 | update sbtest1 set k = ? where id = ? | 1 | 1541233734 | 1541233734 | 2599 | 2599 | 2599 |
| 0 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1541233713 | 1541233713 | 2848 | 2848 | 2848 |
| 0 | test | test | 0x99531AEFF718C501 | show tables | 1 | 1541233707 | 1541233707 | 2703 | 2703 | 2703 |
| 0 | test | test | 0xA592C94A099E89DC | begin | 1 | 1541233702 | 1541233702 | 862 | 862 | 862 |
+-----------+------------+----------+--------------------+---------------------------------------+------------+------------+------------+----------+----------+----------+
5 rows in set (0.01 sec)

7. 测试set transaction read write [read only]

set transaction 有session和global两种scope, 它用来设置当前session or global事务的属性(Global scope 会设置数据库变量:transaction_read_only,会影响新连接;Session scope则只影响下一个事务。)。

  • set transaction read write会设置当前session后续的事务可读可写;而set transaction read only则当前session后续的事务必须是只读,直到commit,它的作用才结束(与start transaction read only是一样的)。
  • set global transaction read only[read write], 则设置了除本session 外的的其它后续连接session的事务属性。该语句执行后,新建的连接只能查询,无法执行dml语句,虽然read_only=OFF!

因为发现有些应用的数据层框架会给数据库发送这类语句,故在此也测一下。

在ProxySQL client 端执行set transaction read write。

1
2
3
4
5
6
7
8
9
10
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x0724CA2D4ED1C95A | update sbtest1 set k=? where id =? | 1 | 1541234408 | 1541234408 | 5583 | 5583 | 5583 |
| 1 | test | test | 0xD70A9BB359FD8BDB | select * from sbtest1 where id =? | 1 | 1541234349 | 1541234349 | 3292 | 3292 | 3292 |
| 0 | test | test | 0x99531AEFF718C501 | show tables | 1 | 1541234337 | 1541234337 | 1070 | 1070 | 1070 |
| 0 | test | test | 0x44AF4E6A00440DCD | set transaction read write | 1 | 1541234318 | 1541234318 | 991 | 991 | 991 |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
4 rows in set (0.01 sec)

set transaction read write 会被立刻发送到主库,并不发生前后端的连接绑定,后续的SQL也都按照既定的路由规则分发,因为autocommit=1。它不起什么作用,因为一个session中的事务属性默认就是读写。

set transaction read only 同样会立刻发送到主库,后续执行update则报错。

1
2
3
4
5
mysql> set transaction read only;
Query OK, 0 rows affected (0.01 sec)

mysql> update sbtest1 set k=1 where id=1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

它与start transaction read only问题相同,ProxySQL不能依此识别只读事务,并路由到从库。set transaction read only会设置本session下一个事务是只读的,commit后作用结束!

结合set autocommit=0, set transaction read write可以实现与start transaction的作用

1
2
3
4
5
6
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

执行完此句后,set autocommit=0与之一起发送到主库
mysql> set transaction read write;
Query OK, 0 rows affected (0.01 sec)

此时已发生前后端连接绑定,后续所有query都使用同一连接发送到后端同一server!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
| 1 | 47 | test | test | 127.0.0.1 | 56982 | 0 | 192.168.216.101 | 40146 | 192.168.216.203 | 3306 | Sleep | 56539 | |
+----------+-----------+------+------+-----------+----------+-----------+-----------------+------------+-----------------+----------+---------+---------+------+
1 row in set (0.01 sec)

mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x0724CA2D4ED1C95A | update sbtest1 set k=? where id =? | 1 | 1541382746 | 1541382746 | 159859 | 159859 | 159859 |
| 0 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 2 | 1541382722 | 1541382749 | 35264 | 917 | 34347 |
| 0 | test | test | 0x44AF4E6A00440DCD | set transaction read write | 1 | 1541382603 | 1541382603 | 11176 | 11176 | 11176 |
| 0 | test | test | 0x2FB86F938EF30630 | set autocommit=? | 1 | 1541382572 | 1541382572 | 0 | 0 | 0 |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
4 rows in set (0.01 sec)

执行commit 后事务结束, 但发现前后端连接依然处于绑定状态。后续执行的查询依然使用这一连接发送到后端同一server.

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | test | test | 0x086920192A3AEE28 | select * from sbtest2 limit ? | 1 | 1541382912 | 1541382912 | 24196 | 24196 | 24196 |
| 0 | test | test | 0xDB3A841EF5443C35 | commit | 1 | 1541382773 | 1541382773 | 36238 | 36238 | 36238 |
| 0 | test | test | 0x0724CA2D4ED1C95A | update sbtest1 set k=? where id =? | 1 | 1541382746 | 1541382746 | 159859 | 159859 | 159859 |
| 0 | test | test | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 4 | 1541382722 | 1541382897 | 37871 | 917 | 34347 |
| 0 | test | test | 0x44AF4E6A00440DCD | set transaction read write | 1 | 1541382603 | 1541382603 | 11176 | 11176 | 11176 |
| 0 | test | test | 0x2FB86F938EF30630 | set autocommit=? | 1 | 1541382572 | 1541382572 | 0 | 0 | 0 |
+-----------+------------+----------+--------------------+------------------------------------+------------+------------+------------+----------+----------+----------+
6 rows in set (0.00 sec)

执行set autocommit=1后,通过主库的general log发现该语句立刻发送到了主库(并不在ProxySQL中暂存,因为前后端连接处于绑定状态,ProxySQL有充分的自信发送这条set autocommit=1;),而且发送了两次!并且ProxySQL中前后端连接绑定状态断开了。
主库的general log片段:

1
2
3
4
2018-11-05T02:02:52.578436Z	  623 Query	select * from sbtest1 limit 1
2018-11-05T02:03:07.577225Z 623 Query select * from sbtest2 limit 1
2018-11-05T02:04:26.714266Z 623 Query SET autocommit=1
2018-11-05T02:04:26.715078Z 623 Query set autocommit=1

ProxySQL admin interface查看Processlist

1
2
3
4
5
6
7
mysql> show full processlist;
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| 1 | 47 | test | test | 127.0.0.1 | 56982 | 0 | | | | | Sleep | 104518 | |
+----------+-----------+------+------+-----------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
1 row in set (0.00 sec)

总结:set transaction read write 结合set autocommit=0,可以使前后端连接绑定,确保同一事务的所有SQL发送到同一server,但这种绑定并不能随事务的commit而结束,而是随下次设置set autcocommit=1而结束。ProxySQL处理set transaction read write还是有许多不确定性因素!应用端框架最好不要发送这类SQL,使用begin..commit;/start transaction .. commit; 是正规执行事务的方式,这会使ProxySQL更好的工作!

Java JDBC使用set autocommit=0来隐式开启事务,由于set autocommit 默认会在proxysql端缓存,这会导致一些不符合预期的效果。 修改proxysql参数mysql-forward_autocommit=’true’, 可使set autocommit=0,不在proxysql中缓存,立刻转发到后端server. 根据默认路由规则,转发到写组。 这会使后端连接与前端绑定,确保了一个事务的所有SQL都使用同一个连接发送。 https://github.com/sysown/proxysql/issues/1155

性能测试

后端mysql为一主一从,在各压测用例中配置均保持一致!
sysbench 压测语句

1
sysbench /usr/local/share/sysbench/oltp_read_only.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-port=6033 --mysql-db=test --mysql-user=test --mysql-password=test --table_size=10000000 --tables=5 --db-ps-mode=disable --skip-trx=on --threads=$thread  --time=120 --report-interval=20 run

1. ProxySQL query log ON 与 OFF,Read only 对比!

query log开启 ,读写完全分离,写节点不承担读请求负载!(mysql general_log=ON)

a
可以看到ProxySQL的query log的开启对性能影响不太明显!

2. 读写完全分离,写节点承担25%的读请求,与直连mysql,read only 对比

ProxySQL 的缓存是关闭的,由于主从两个节点都承担了读请求,ProxySQL的QPS与直连mysql相比有50%的提升。
a
请求Response time:
a
ProxySQL 的平均响应时间,明显小于直连MySQL,会话越多越明显!

3. TPS测试

测试采用read-write脚本,这更贴近正常的业务!在ProxySQL部署节点压测,这样可以模拟将proxySQL部署到应用节点的效果:

1
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --db-driver=mysql --mysql-port=6033 --mysql-db=test --mysql-user=test --mysql-password=test --table_size=10000000 --tables=5 --db-ps-mode=disable --skip-trx=off --threads=$thread  --time=120 --report-interval=20 run

a

测试结果显示,连接ProxySQL与直连mysqltps还高!sysbench连接ProxySQL属于本地环路连接,网络延时可以忽略。ProxySQL到后端mysql的网络延迟与sysbench直连mysql的网络延迟相同。但ProxySQL通过读写分离,将读的压力分到了从库,从而使主库的压力降低,故主库能有更高的tps. 另外ProxySQL使用连接池与后端MySQL维持一定数量的连接,这使得连接ProxySQL测试时,省掉了部分与后端mysql建立连接的开销,这也有助于提升性能。

下面将压测节点与ProxySQL部署节点分开再次压测ProxySQL,然后再次与直连mysql对比绘图。

a

这种测试情况下,ProxySQL有两个网络延时,一个是sysbench到proxysql,一个是proxysql到mysql,但直连mysql 的性能还是有些低,比不上连接proxysqlSQL,可能是因为压测mysql时未启用prepare statement,mysql的性能未充分发挥出来. 去掉–db-ps-mode=disable后压测mysql,再试下:

1
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=192.168.216.203 --db-driver=mysql --mysql-port=3306 --mysql-db=test --mysql-user=test --mysql-password=test --table_size=10000000 --tables=5 --skip-trx=off  --threads=$thread  --time=120 --report-interval=20 run

a
由上图可以看到,当线程数小于100时,mysql tps性能高,大于100后,两者旗鼓相当。
我得出的结论是ProxySQL性能极佳,对提升数据库整体性能有很大帮助! 各位看官,你们测的结果如何?