2019-11-18
直接上我在生産環境的配置過程:
登入到proxysql叢集的任意節點的管理控制台,執行下面的操作:
Admin> SELECT DISTINCT digest, digest_text FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%'; -- 檢視目前sql明細和摘要情況
Admin> SELECT DISTINCT 1,digest,2,0 FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%'; -- 檢視sql指紋
+---+--------------------+---+---+
| 1 | digest | 2 | 0 |
+---+--------------------+---+---+
| 1 | 0x928841C8AEDD64F9 | 2 | 0 |
| 1 | 0x9771AB06EF4857A5 | 2 | 0 |
| 1 | 0xA9919C0B1C3491E6 | 2 | 0 |
| 1 | 0xEDF9C60BDFD2F5B0 | 2 | 0 |
| 1 | 0x226CD90D52A2BA0B | 2 | 0 |
| 1 | 0x82A12D4C4E7B0A28 | 2 | 0 |
| 1 | 0xA072D1AB0FD3C016 | 2 | 0 |
| 1 | 0x7CDEEF2FF695B7F8 | 2 | 0 |
+---+--------------------+---+---+
8 rows in set (0.01 sec)
-- 在proxysql中插入sql改寫規則
Admin> INSERT INTO
mysql_query_rules(active,digest,multiplex,apply)
SELECT DISTINCT 1,digest,2,0 FROM stats_mysql_query_digest WHERE digest_text LIKE '%@%';
select * from mysql_query_rules ;
Admin> select rule_id,digest,multiplex,apply,comment from mysql_query_rules ;
+---------+--------------------+-----------+-------+---------+
| rule_id | digest | multiplex | apply | comment |
+---------+--------------------+-----------+-------+---------+
| 1 | 0x928841C8AEDD64F9 | 2 | 0 | NULL |
| 2 | 0x9771AB06EF4857A5 | 2 | 0 | NULL |
| 3 | 0xA9919C0B1C3491E6 | 2 | 0 | NULL |
| 4 | 0xEDF9C60BDFD2F5B0 | 2 | 0 | NULL |
| 5 | 0x226CD90D52A2BA0B | 2 | 0 | NULL |
| 6 | 0x82A12D4C4E7B0A28 | 2 | 0 | NULL |
| 7 | 0xA072D1AB0FD3C016 | 2 | 0 | NULL |
| 8 | 0x7CDEEF2FF695B7F8 | 2 | 0 | NULL |
+---------+--------------------+-----------+-------+---------+
8 rows in set (0.00 sec)
-- 将配置load到記憶體生效,并持久化到檔案
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Admin> SAVE MYSQL QUERY RULES TO DISK;
Admin> select * from runtime_mysql_query_rules ; -- 确認是否生效
執行完上述操作後,我們稍等片刻看下grafana監控,如下:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCM581dvRWYoNHLwEzX5xCMx8FesU2cfdGLwATMfRHLGZkRGZkRfJ3bs92YskmNhVTYykVNQJVMRhXVEF1X0hXZ0xiNx8VZ6l2cssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnL0kTNxkDOwETO4cTM0cTNx8CX5ETMxkTMwIzLcNXZnFWbp9CXvwVbvNmLvR3YxUjL0M3Lc9CX6MHc0RHaiojIsJye.png)
我這裡降低不明顯的原因是: 我pxc上的業務請求比較少,都是些内部服務,是以産生的sql指紋也比較少,如果對于一個非常繁忙的pxc叢集而言,我們在前面的proxysql做了上述的多路複用配置後,可以大幅度降低後端pxc節點的連接配接數。