Skip to content

ProxySQL Read Write Split (HOWTO)

Jump to bottom
malongshuai edited this page Jul 7, 2018 · 4 revisions

对查询语句进行路由是ProxySQL的一个核心功能。

读/写分离可能算是查询路由最常用的了，分片(sharding)是另一个比较常用的查询路由功能。

Read/write split using different ports

(配置基于端口的读/写分离)

如果你使用的是类似于HAProxy的代理软件，那么可以将其配置监听在两个不同的端口上：一个端口用于写，一个端口用于读。

人们经常询问ProxySQL如何实现这样的功能：基于不同端口对不同查询语句进行路由。

以下是一个基于不同端口进行读写分离的示例。在ProxySQL的Admin管理接口下运行下面的步骤。这里我假设你已经将master和slave进行了正确的分组：写组为hostgroup 10，读组为hostgroup 20。如果使用的是Galera或者MySQL的组复制，实现方式是类似的。步骤如下：

  • 配置ProxySQL监听在两个不同的端口上并重启ProxySQL：mysql-interfaces是少数几个无法在线修改的变量之一，修改后需要重启ProxySQL。
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;
  • 添加基于不同端口的路由：
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端口的查询都将路由给hostgroup 10中的MySQL节点，流入到6402端口的查询都将路由给hostgroup 20中的MySQL节点。

Limitation of read/write split based on incoming port

(基于端口进行读写分离的局限性)

在前面我已经说过，人们经常会问如何配置ProxySQL让其基于不同端口进行路由。尽管某些时候着是一个可行的方案，但在我看来，它有一个很大的缺点：应用程序需要内置读/写分离的能力，以便区分读和写

绝大多数的应用程序都不会附带这样的功能。应用程序通常只会使用一个连接端口连接到后端，也就是MySQL的master节点。如果使用了ProxySQL，则可以通过唯一的端口将所有的语句交给ProxySQL，让ProxySQL对语句进行分析并依据查询类型(或者查询规则)进行读/写分离。

显然这很方便，因为再也不需要对应用程序做任何修改了。

尽管如此，使用ProxySQL的主要优点不在于能够不用修改应用程序。主要的优点在于DBA可以通过这个工具控制发送给后端MySQL节点的流量。DBA都是在半夜进行操作的，因为白天DB服务器的负荷较大，这个时候没有开发人员陪在旁边，想要修改应用程序显然不太可能：但有了ProxySQL，DBA可以不需要开发人员的参与直接控制MySQL请求的路由方式。

basic read/write split using regex

(使用正则表达式实现基本的读/写分离)

在这一部分，我将通过一个示例来演示如何通过正则表达式来实现读/写分离。

首先，我们需要将之前创建的查询规则删除：

DELETE FROM mysql_query_rules;

然后，为读/写分别创建对应的基本规则：

UPDATE mysql_users SET default_hostgroup=10; # 所有的查询将默认路由到 HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;     # 如果想要将配置持久化保存到磁盘，就执行该语句
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;    # 如果想要将配置持久化保存到磁盘，就执行该语句

现在，将按照如下规则对查询语句进行路由：

  • 所有SELECT FOR UPDATE语句将路由给HG10。
  • 其它所有的SELECT语句都将路由给HG20。
  • 其它所有查询也都将路由给HG10(这是默认主机组)

注意，我并不认为上面的读/写分离规则是多好的方法。我经常使用这个例子来描述如何配置规则，但它经常被误解为配置读/写分离的方式 。稍后我将演示更好的读/写方法。

现在，将上面的规则全部移除：

DELETE FROM mysql_query_rules;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # 如果想将配置持久化到磁盘，执行该语句

read/write split using regex and digest

(使用正则表达式和digest配置读写分离)

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

  • 首先配置ProxySQL，让所有的请求都发送给单个MySQL节点master(既读又写)
  • 检查stats_mysql_query_digest表中开销最大的SELECT语句
  • 然后决定这些大开销的语句中哪些允许路由给负责读的节点
  • 然后配置mysql_query_rules表(创建规则)，将大开销的SELECT语句路由给读组

这个方案的本质非常简答：只发送那些想要发送给slaves/readers的查询，而不仅仅通过SELECT语句来判断。

Find expensive queries using stats_mysql_query_digest

(从stats_mysql_query_digest表中找出开销较大的查询语句)

这里给出了几个示例，用来解释如何找出哪些应该路由给读组的潜在查询。

由于ProxySQL将所有的统计数据和指标都导出到了各种表中，因此可以根据这些表创建复杂的查询来收集想要的信息。

下面是在一个非常繁忙的ProxySQL实例下收集的数据，这个实例已经持续运行了几个月，处理的数据有上千亿。

  • 首先查找出总执行时间排在前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)
  • 再找出执行次数排在前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)

这些查询可以被缓存吗？ProxySQL的查询缓存可以实现该功能。

  • 找出最长执行时间排在前5的查询(译注：每个查询都有最长执行时间max_time和最短执行时间min_time)：
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)

这个具体的结果表明，一些查询具有非常高的最大执行时间，而最短执行时间却非常小，并且平均执行速度也相当慢。

例如，digest值为0x36CE5295726DB5B4的查询，平均执行时间为1.27秒，但最小执行时间才0.4毫秒，最大时间久到了237.34秒。对于这样的查询，也许有必要研究一下为什么执行时间不均匀。

  • 找出总执行时间排在前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   | 999226   | 1383     | 7905811  |
+--------------------+--------------------------+------------+-------------+----------+----------+----------+
5 rows in set (0.01 sec)
  • 找出总执行时间排在前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)
  • 找出总执行时间排在前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秒以上，那么答案可能是no。对于某些非常繁忙且对性能要求较高的应用程序，甚至可以考虑将平均执行时间在15毫秒的查询路由给slave。

例如，我们可以决定将digest=0x38BE36BDFFDBE638的查询路由给slave：

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(*)开头的语句路由给slave：

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;

Conclusion

(结论：)

ProxySQL可以非常有效、非常弹性地对各种查询进行选择性地路由。

虽然对于某些应用程序来说，将所有SELECT语句路由给slaves/readers，其余都路由给writers/masters是可以接受的，但对于很多应用程序来说，其实并没这么简单。作为DBA，应当具备"使用复杂的规则配置ProxySQL，只将那些不应该路由给master的查询路由给slaves，而不需要更改任何应用程序"的能力。

Translator：马龙帅
Blog：http://www.cnblogs.com/f-ck-need-u

Clone this wiki locally