ProxySQL读写分离

ProxySQL读写分离

查询路由是proxysql的核心特性之一。

读/写分离可能是最常用的查询路由之一，而另一种最常用的查询路由是分片。

一、使用不同的端口进行读写分离

如果使用像HAProxy这样的代理，可以将其配置为侦听两个端口:一个端口作为写入端，而第二个端口作为读取端。人们经常询问如何使用相同的方法配置proxysql，以及基于传入端口查询路由。

下面是一个关于如何实现基于传入端口的查询路由的示例，在proxysql的Admin上运行以下命令。

假设已经在正确的主机组中配置了主服务器和从服务器:

主机组10中的MySQL写入

主机组20中的MySQL读取

如果使用Galera或组复制，也可以使用类似的方法。步骤如下

1. 配置proxysql侦听两个端口并重新启动: mysql-interfaces是少数几个在runtime不能更改且需要重新启动的变量之一

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';

## save it on disk and restart proxysql

SAVE MYSQL VARIABLES TO DISK;

PROXYSQL RESTART;

2. 添加基于传入端口的路由

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)

VALUES (1,1,6401,10,1), (2,1,6402,20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

完成了!现在，所有到端口6401的查询将发送到主机组10中的MySQL服务器，而所有到端口6402的查询将发送到主机组20中的MySQL服务器中的一个。

基于传入端口的读/写分离限制

在前一段中，我写到，人们经常询问如何配置proxysql来使用基于传入端口的路由。

虽然有时这是一种有效的方法，但在我看来，它有一个很大的缺点:应用程序需要内置读/写分离功能，以便区分读和写。

(以下是介绍使用ProxySQL的好处 balabala....)

但实际生产环境中并非如此。通常，应用程序连接串只配置一个链接(不区分读写)，而这个链接就是MySQL主机。如果使用了proxysql，则可以接受单个端口中的所有流量，并可以分析流量，以便根据查询类型执行读/写分离。

这非常方便，因为它不需要任何应用程序更改。

尽管如此，它的主要优势并不在于能够在不更改应用程序的情况下路由流量。主要优点是DBA现在拥有了控制发送到数据库的流量的工具。DBA在半夜被叫醒由于DB服务器超载，在没有开发人员的情况下，不会选择更改应用程序配置，他现在拥有控制流量的选项(即DBA可以通过ProxySQL控制语句发送至哪些MySQL服务器，非常好)。

二、基于正则表达式的读/写分离

在这一段中，将展示一个关于如何使用正则表达式执行读/写分离的例子。

首先，应该删除之前创建的查询规则:

DELETE FROM mysql_query_rules;

然后，为读/写创建基本规则:

UPDATE mysql_users SET default_hostgroup=10; # by default, all goes to HG10

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK; # if you want this change to be permanent

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1,1,'^SELECT.*FOR UPDATE$',10,1),

(2,1,'^SELECT',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

现在路由将工作如下:

1. 所有SELECT FOR UPDATE将会发送到 HG10

2. 所有其他的SELECT将会发送到HG20

3. 其他所有内容都将发送到HG10(默认值)

注意，我(作者)认为上面的方法不是一个好的读写分离方法。

我(作者)经常用这个例子来描述如何配置规则，但它经常被误解为配置读/写分离的方法(貌似看网上博客也是这样)。

不要在生产中使用上面的例子(来自作者的强调，也就是不要单纯的使用上面两个规则就作为生产环境的读写分离配置，应该是有什么坑的...反正在这里说了，谁爱这样用就这样用(网上很多博客也是这样写的，单用^SELECT.*FOR UPDATE$、^SELECT实现读写分离)，死道友不死贫道...)

在下一段中，我(作者)将展示一种更好的方法(使用ProxySQL做读写分离的正确方式)。

现在，让我们删除所有规则:

DELETE FROM mysql_query_rules;

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

三、使用正则表达式和摘要进行读/写分离

一个有效地设置读/写分离的配置过程如下:

1. 配置proxysql将所有流量只发送到一个MySQL节点，即主节点(包括写和读)

2. 在stats_mysql_query_digest中查出哪些SELECT最消耗性能

3. 确定哪些最消耗性能的语句应该移动到读节点

4. 配置mysql_query_rules(创建规则)，只向读节点发送最消耗性能的SELECT语句

因此，这个想法非常简单:只发送您想发送的SELECT语句给从库或读节点，而不是任何SELECT语句。(这才是ProxySQL的正确使用方式...)

使用stats_mysql_query_digest查找最消耗性能的查询语句

下面是一些示例，说明如何识别可以发送给读者的潜在查询。

因为proxysql导出表中的所有指标，所以可以创建复杂的查询来收集信息。

这些结果基于一个运行了几个月的非常繁忙的proxysql实例，到目前为止，该实例已经处理了大约千亿次查询。

1. 根据总执行时间查找前5个查询:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+---------------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |

+--------------------+--------------------------+------------+---------------+

| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 |

| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 |

| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 |

| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  |

| 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  |

+--------------------+--------------------------+------------+---------------+

5 rows in set (0.01 sec)

2. 根据count查找前5个查询:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

+--------------------+--------------------------+------------+---------------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |

+--------------------+--------------------------+------------+---------------+

| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |

| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 |

| 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  |

| 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  |

| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  |

+--------------------+--------------------------+------------+---------------+

5 rows in set (0.00 sec)

3. 根据最大执行时间查找前5个查询:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

+--------------------+--------------------------+------------+--------------+----------+----------+-----------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  |

+--------------------+--------------------------+------------+--------------+----------+----------+-----------+

| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 |

| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 |

| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 |

| 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 |

| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 |

+--------------------+--------------------------+------------+--------------+----------+----------+-----------+

5 rows in set (0.01 sec)

这个特定的结果表明，有些查询的最大执行时间非常高，而最小执行时间非常小，平均速度也相当慢。

例如，使用摘要0x36CE5295726DB5B4查询的平均执行时间为1.27秒，最小执行时间为0.4ms，最大执行时间为237.34秒。也许有必要研究一下为什么执行时间不均匀。

4. 查找按总执行时间排序的前5个查询，最小执行时间至少为1毫秒:

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+-------------+----------+----------+----------+

| digest             | SUBSTR(digest_text,0,20) | count_star | sum_time    | avg_time | min_time | max_time |

+--------------------+--------------------------+------------+-------------+----------+----------+----------+

| 0x9EED412C6E63E477 | SELECT a.id as acco      | 961733     | 24115349801 | 25074    | 10994    | 7046628  |

| 0x8DDD43A9EA37750D | Select ( Coalesce((      | 107069     | 3156179256  | 29477    | 1069     | 24600674 |

| 0x9EED412C6E63E477 | SELECT a.id as acco      | 91996      | 1883354396  | 20472    | 10095    | 497877   |

| 0x08B23A268C35C08E | SELECT id as reward      | 49401      | 244088592   | 4940     | 1237     | 1483791  |

| 0x437C846F935344F8 | SELECT Distinct i.e      | 164        | 163873101   | ×××26   | 1383     | 7905811  |

+--------------------+--------------------------+------------+-------------+----------+----------+----------+

5 rows in set (0.01 sec)

5. 查找按总执行时间排序的前5个查询，平均执行时间至少为1秒。还显示总执行时间的百分比:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+--------------+----------+-------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | pct   |

+--------------------+--------------------------+------------+--------------+----------+-------+

| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 2.11  |

| 0xD38895B4F4D2A4B3 | SELECT instance.name as  | 9783       | 12409642528  | 1268490  | 0.141 |

| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 0.015 |

+--------------------+--------------------------+------------+--------------+----------+-------+

3 rows in set (0.00 sec)

6. 查找按总执行时间排序的前5个查询，平均执行时间至少为15毫秒。还显示总执行时间的百分比:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+---------------+----------+--------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      | avg_time | pct    |

+--------------------+--------------------------+------------+---------------+----------+--------+

| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59360371   | 1096562204931 | 18472    | 13.006 |

| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994  | 1270249  | 2.205  |

| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592281     | 40215136635   | 67898    | 0.477  |

| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265   | 562935   | 0.295  |

| 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768     | 24116011513   | 25074    | 0.286  |

+--------------------+--------------------------+------------+---------------+----------+--------+

5 rows in set (0.00 sec)

所有这些查询都需要在master上执行吗?如果一个查询的平均执行时间超过1秒，那么答案很可能是否定的。

对于某些应用程序，甚至平均执行时间为15ms的查询也可能变为从属查询。

例如，在与应用程序所有者进行检查后，我们可以决定将使用摘要0x38BE36BDFFDBE638查询可以发送到从库:

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)

VALUES

(1,1,'0x38BE36BDFFDBE638',20,1);

同样，在检查这个输出后:

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT COUNT%' ORDER BY sum_time DESC;

我们同意所有以SELECT COUNT(*)开头的查询都可以发送到从库:

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)

VALUES

(1,1,'^SELECT COUNT\(\*\)',20,1);

最后，将每个规则加载到runtime:

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

proxysql对有选择性的查询路由是非常有效的。

虽然对于某些应用程序，将所有SELECT发送给读库/从库是可以接受的，而将其他所有发送给写库/主库是可以接受的，但是对于许多其他应用程序/工作负载，情况就不那么简单了。

DBA应该能够使用复杂的规则配置proxysql，只将不需要在主服务器上执行的查询发送给从服务器，而不需要对应用程序进行任何更改。