ProxySQL讀寫分離

網(wǎng)站建設(shè)公司,為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計及定制網(wǎng)站建設(shè)服務(wù),專注于企業(yè)網(wǎng)站建設(shè),高端網(wǎng)頁制作,對圍欄護欄等多個行業(yè)擁有豐富的網(wǎng)站建設(shè)經(jīng)驗的網(wǎng)站建設(shè)公司。專業(yè)網(wǎng)站設(shè)計,網(wǎng)站優(yōu)化推廣哪家好,專業(yè)網(wǎng)站推廣優(yōu)化,H5建站,響應(yīng)式網(wǎng)站。

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

讀/寫分離可能是最常用的查詢路由之一，而另一種最常用的查詢路由是分片。

一、使用不同的端口進行讀寫分離

如果使用像HAProxy這樣的代理，可以將其配置為偵聽兩個端口:一個端口作為寫入端，而第二個端口作為讀取端。人們經(jīng)常詢問如何使用相同的方法配置proxysql，以及基于傳入端口查詢路由。

下面是一個關(guān)于如何實現(xiàn)基于傳入端口的查詢路由的示例，在proxysql的Admin上運行以下命令。

假設(shè)已經(jīng)在正確的主機組中配置了主服務(wù)器和從服務(wù)器:

主機組10中的MySQL寫入

主機組20中的MySQL讀取

如果使用Galera或組復(fù)制，也可以使用類似的方法。步驟如下

1. 配置proxysql偵聽兩個端口并重新啟動: mysql-interfaces是少數(shù)幾個在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

完成了!現(xiàn)在，所有到端口6401的查詢將發(fā)送到主機組10中的MySQL服務(wù)器，而所有到端口6402的查詢將發(fā)送到主機組20中的MySQL服務(wù)器中的一個。

基于傳入端口的讀/寫分離限制

在前一段中，我寫到，人們經(jīng)常詢問如何配置proxysql來使用基于傳入端口的路由。

雖然有時這是一種有效的方法，但在我看來，它有一個很大的缺點:應(yīng)用程序需要內(nèi)置讀/寫分離功能，以便區(qū)分讀和寫。

(以下是介紹使用ProxySQL的好處 balabala....)

但實際生產(chǎn)環(huán)境中并非如此。通常，應(yīng)用程序連接串只配置一個鏈接(不區(qū)分讀寫)，而這個鏈接就是MySQL主機。如果使用了proxysql，則可以接受單個端口中的所有流量，并可以分析流量，以便根據(jù)查詢類型執(zhí)行讀/寫分離。

這非常方便，因為它不需要任何應(yīng)用程序更改。

盡管如此，它的主要優(yōu)勢并不在于能夠在不更改應(yīng)用程序的情況下路由流量。主要優(yōu)點是DBA現(xiàn)在擁有了控制發(fā)送到數(shù)據(jù)庫的流量的工具。DBA在半夜被叫醒由于DB服務(wù)器超載，在沒有開發(fā)人員的情況下，不會選擇更改應(yīng)用程序配置，他現(xiàn)在擁有控制流量的選項(即DBA可以通過ProxySQL控制語句發(fā)送至哪些MySQL服務(wù)器，非常好)。

二、基于正則表達式的讀/寫分離

在這一段中，將展示一個關(guān)于如何使用正則表達式執(zhí)行讀/寫分離的例子。

首先，應(yīng)該刪除之前創(chuàng)建的查詢規(guī)則:

DELETE FROM mysql_query_rules;

然后，為讀/寫創(chuàng)建基本規(guī)則:

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

現(xiàn)在路由將工作如下:

1. 所有SELECT FOR UPDATE將會發(fā)送到 HG10

2. 所有其他的SELECT將會發(fā)送到HG20

3. 其他所有內(nèi)容都將發(fā)送到HG10(默認值)

注意，我(作者)認為上面的方法不是一個好的讀寫分離方法。

我(作者)經(jīng)常用這個例子來描述如何配置規(guī)則，但它經(jīng)常被誤解為配置讀/寫分離的方法(貌似看網(wǎng)上博客也是這樣)。

不要在生產(chǎn)中使用上面的例子(來自作者的強調(diào)，也就是不要單純的使用上面兩個規(guī)則就作為生產(chǎn)環(huán)境的讀寫分離配置，應(yīng)該是有什么坑的...反正在這里說了，誰愛這樣用就這樣用(網(wǎng)上很多博客也是這樣寫的，單用^SELECT.*FOR UPDATE$、^SELECT實現(xiàn)讀寫分離)，死道友不死貧道...)

在下一段中，我(作者)將展示一種更好的方法(使用ProxySQL做讀寫分離的正確方式)。

現(xiàn)在，讓我們刪除所有規(guī)則:

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

三、使用正則表達式和摘要進行讀/寫分離

一個有效地設(shè)置讀/寫分離的配置過程如下:

1. 配置proxysql將所有流量只發(fā)送到一個MySQL節(jié)點，即主節(jié)點(包括寫和讀)

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

3. 確定哪些最消耗性能的語句應(yīng)該移動到讀節(jié)點

4. 配置mysql_query_rules(創(chuàng)建規(guī)則)，只向讀節(jié)點發(fā)送最消耗性能的SELECT語句

因此，這個想法非常簡單:只發(fā)送您想發(fā)送的SELECT語句給從庫或讀節(jié)點，而不是任何SELECT語句。(這才是ProxySQL的正確使用方式...)

使用stats_mysql_query_digest查找最消耗性能的查詢語句

下面是一些示例，說明如何識別可以發(fā)送給讀者的潛在查詢。

因為proxysql導(dǎo)出表中的所有指標，所以可以創(chuàng)建復(fù)雜的查詢來收集信息。

這些結(jié)果基于一個運行了幾個月的非常繁忙的proxysql實例，到目前為止，該實例已經(jīng)處理了大約千億次查詢。

1. 根據(jù)總執(zhí)行時間查找前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. 根據(jù)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. 根據(jù)最大執(zhí)行時間查找前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)

這個特定的結(jié)果表明，有些查詢的最大執(zhí)行時間非常高，而最小執(zhí)行時間非常小，平均速度也相當慢。

例如，使用摘要0x36CE5295726DB5B4查詢的平均執(zhí)行時間為1.27秒，最小執(zhí)行時間為0.4ms，最大執(zhí)行時間為237.34秒。也許有必要研究一下為什么執(zhí)行時間不均勻。

4. 查找按總執(zhí)行時間排序的前5個查詢，最小執(zhí)行時間至少為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. 查找按總執(zhí)行時間排序的前5個查詢，平均執(zhí)行時間至少為1秒。還顯示總執(zhí)行時間的百分比:

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. 查找按總執(zhí)行時間排序的前5個查詢，平均執(zhí)行時間至少為15毫秒。還顯示總執(zhí)行時間的百分比:

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上執(zhí)行嗎?如果一個查詢的平均執(zhí)行時間超過1秒，那么答案很可能是否定的。

對于某些應(yīng)用程序，甚至平均執(zhí)行時間為15ms的查詢也可能變?yōu)閺膶俨樵儭?/p>

例如，在與應(yīng)用程序所有者進行檢查后，我們可以決定將使用摘要0x38BE36BDFFDBE638查詢可以發(fā)送到從庫:

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(*)開頭的查詢都可以發(fā)送到從庫:

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

VALUES

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

最后，將每個規(guī)則加載到runtime:

LOAD MYSQL QUERY RULES TO RUNTIME;

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

proxysql對有選擇性的查詢路由是非常有效的。

雖然對于某些應(yīng)用程序，將所有SELECT發(fā)送給讀庫/從庫是可以接受的，而將其他所有發(fā)送給寫庫/主庫是可以接受的，但是對于許多其他應(yīng)用程序/工作負載，情況就不那么簡單了。

DBA應(yīng)該能夠使用復(fù)雜的規(guī)則配置proxysql，只將不需要在主服務(wù)器上執(zhí)行的查詢發(fā)送給從服務(wù)器，而不需要對應(yīng)用程序進行任何更改。

本文標題：ProxySQL讀寫分離
當前鏈接：http://aaarwkj.com/article24/igdpje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計、網(wǎng)站建設(shè)、域名注冊、虛擬主機、電子商務(wù)、App開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)