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，只將不需要在主服務器上執行的查詢發送給從服務器，而不需要對應用程序進行任何更改。