This blog focuses on how to quickly find and address badly written queries using ProxySQL without any downtime and change in application code.
When we get an incident about the high usage on a production master, then mostly it is because of unexpected spike in Traffic (QPS) or slow queries.
Below was the status when we were doing the regular weekly health check and In PMM graphs we saw master was serving more than 15K queries and both slaves were having ~6k queries.
We can also see a similar type of issues when a new deployment goes live with some application bug.
The first thing which came to my mind was why to overload master DB when we have slaves with enough resources.
There is a way to find offending queries without enabling slow query logs on master box. In ProxySQL, Table [ stats_mysql_query_digest ] contains all executed query with extra details.
I have made test case with sysbench.
Three methods have been described here.
1. Route slow queries on Slave servers :
Below given is stats table and we found top 5 slow queries which are taking maximum execution time on a master.
select * from stats_mysql_query_digest where schemaname='sbtest_rw' order by sum_time desc limit 5;
+-----------+------------+----------+--------------------+---------------------------------------------------------------------+------------+-------------+
| hostgroup | schemaname | username | digest | digest_text | count_star | sum_time |
+-----------+------------+----------+--------------------+---------------------------------------------------------------------+------------+-------------+
| 0 | sbtest_rw | sysbench | 0x04E3DD532AB9CA82 | SELECT c FROM sbtest20 WHERE id=? | 4356291 | 96502488100 |
| 0 | sbtest_rw | sysbench | 0x573033E0E6EB75F4 | SELECT DISTINCT c FROM sbtest16 WHERE id BETWEEN ? AND ? ORDER BY c | 138156 | 39145641433 |
| 0 | sbtest_rw | sysbench | 0x135C4A683DA43FBB | SELECT c FROM sbtest7 WHERE id BETWEEN ? AND ? ORDER BY c | 138955 | 34528708401 |
| 0 | sbtest_rw | sysbench | 0xBAB6CF24BA4299CF | SELECT SUM(k) FROM sbtest16 WHERE id BETWEEN ? AND ? | 138788 | 20791121721 |
+-----------+------------+----------+--------------------+---------------------------------------------------------------------+------------+-------------+
4 rows in set (0.01 sec)
For visibility copied o/p from sysbench test.
Described some Important field from stats table :
- hostgroup - the hostgroup where the query was sent.
- digest - a hexadecimal hash that uniquely represents a query with its parameters stripped
- digest_text - the actual text with its parameters stripped
- count_star - the total number of times the query has been executed.
- last_seen - unix timestamp, the last moment (so far) when the query was routed through the proxy
- sum_time - the total time in microseconds spent executing queries of this type.
From above result, we can see that all these SELECT queries are going on the master box, which is configured for hostgroup 0 . So the solution can be simple, determine the expensive statements should be moved(routed) to reader nodes and just route them on reader group.
Note: If you have multiple proxySQL nodes, so before creating query rule on all ProxySQL, create rule only on one ProxySQL server to make sure query rules is created successfully and it is getting used correctly.
Once you are confident then create a rule on all ProxySQL.
INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,digest,destination_hostgroup,apply)
VALUES (11,1,'sysbench','sbtest_rw','0x04E3DD532AB9CA82',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
MySQL [(none)]> select rule_id,active,username,schemaname,digest,match_digest,destination_hostgroup,apply from mysql_query_rules where rule_id=11;
+---------+--------+-------------+------------+--------------------+--------------+-----------------------+-------+
| rule_id | active | username | schemaname | digest | match_digest | destination_hostgroup | apply |
+---------+--------+-------------+------------+--------------------+--------------+-----------------------+-------+
| 11 | 1 | sysbench | sbtest_rw | 0x04E3DD532AB9CA82 | NULL | 1 | 1 |
+---------+--------+-------------+------------+--------------------+--------------+-----------------------+-------+
1 row in set (0.00 sec)
Now verify query rule and check for hits :
MySQL [(none)]> SELECT rule_id, hits, destination_hostgroup hg, digest ,match_digest pattern FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules where rule_id=71;
+---------+-------+----+--------------------+---------+
| rule_id | hits | hg | digest | pattern |
+---------+-------+----+--------------------+---------+
| 11 | 17389 | 1 | 0x04E3DD532AB9CA82 | NULL |
+---------+-------+----+--------------------+---------+
1 row in set (0.00 sec)
Once we get confirmation that query rule is getting hits, then we can go ahead and create the rule on all ProxySQL servers. Will write about ProxySQL clustering later.
for i in `seq 11 20` ; do
ssh 172.16.0.$i -- 'mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,digest,destination_hostgroup,apply) VALUES (11,1,\"sysbench\",\"sbtest_rw\",\"0x04E3DD532AB9CA82\",1,1); LOAD MYSQL QUERY RULES TO RUNTIME;"'
done
for i in `seq 11 20` ; do
ssh 172.16.0.$i -- 'mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SAVE MYSQL QUERY RULES TO DISK"'
done
After forwarding few SELECT queries from master to slaves, we can see gradually load got divided between master and slaves.
MySQL Questions from PMM:
Cpu Load Status from graphite :
There is another way if we have queries of similar pattern. Make use column match_digest.
match_digest : it matches the regular expression again the digest of the query.
match_pattern : it matches the regular expression again the unmodified text of the query
The digest of a query [match_digest] can be smaller than the query itself ( for example, SELECT statement with several MB of data in IN clause ), thus running a regex against a smaller string is surely faster
INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,match_digest,destination_hostgroup,apply) VALUES (21,1,"sysbench","sbtest_rw","^SELECT a.ip_id as ip_id, f.accountid as accountid,(| )f.id as fileid, f.size as size, a.service as service, a.login as login, s.ip as ip, s.type as servertype",1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
Query Rewrite feature quickly allows to isolate and correct problematic queries and improve performance.
In below example we found that somehow index was not getting used by query ( Optimiser stats might be calculated wrongly ) and Query went smoothly after trying FORCE Index.
Now in this type of scenario, we always need code change. But ProxySQL give us way, How? see below example.
insert into mysql_query_rules (rule_id,active,username,schemaname,match_pattern,replace_pattern,apply)
values (101,1,'sysbench','sbtest_rw','folder.id as folderid FROM remotedl Join folder On remotedl.folderid=folder.id Where folder.userid =','folder.id as folderid FROM remotedl FORCE INDEX (folderid) Join folder On remotedl.folderid=folder.id Where folder.userid =');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
MySQL [(none)]> select rule_id,active,username,schemaname,match_pattern,replace_pattern,apply from mysql_query_rules where rule_id=75\G
*************************** 1. row ***************************
rule_id: 101
active: 1
username: sysbench
schemaname: sbtest_rw
match_pattern: folder.id as folderid FROM remotedl Join folder On remotedl.folderid=folder.id Where folder.userid =
replace_pattern: folder.id as folderid FROM remotedl FORCE INDEX (folderid) Join folder On remotedl.folderid=folder.id Where folder.userid =
apply: 0
1 row in set (0.00 sec)
We have replaced query on-the-fly to use force index while execution.
Consider a situation when we had a spammer. parallel update on a single table for the single account. and we decided to block all update queries coming for that specific account.
mysql> SELECT LEFT(INFO,15), COUNT(*) FROM PROCESSLIST WHERE INFO LIKE 'UPDATE%' GROUP BY LEFT(INFO,15);
+-----------------+----------+
| LEFT(INFO,15) | COUNT(*) |
+-----------------+----------+
| UPDATE abc | 2262 |
| UPDATE dept | 6 |
| UPDATE card | 2 |
+-----------------+----------+
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, OK_msg, apply) VALUES (1,1,"UPDATE abc SET updated_at=.*_status=.*45335","",1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
Done ! Now database stops receiving that query.
Important to note!
There are some exceptions when query routing gets disable. When transaction_persistent is enable for a user, so that application should execute all queries in same transactions on the same host to get the more accurate result.
Tip: If you want fresh statistics to analyse, then execute below command to empty tablestats_mysql_query_digest.
select * from stats_mysql_query_digest_reset;
http://proxysql.com/blog/configure-read-write-split : This blog post contains few more examples of how you can identify potential queries from digest table by creating complex queries to gather required information.
%20vs%20systemctl%20stop%20(2).png)
.png)
.svg)
