This blog discusses how to find and address badly written queries using ProxySQL.
All of us are very good in writing good queries. We know this to always be true! 😉
But sometimes a bad query escapes our control and hits our database. There is the new guy, the probie, who just joined the company and is writing all his code using SELECT * instead of WHERE. We’ve told him “STOP” millions of times, but he refuses to listen. Or a there is a new code injection, and it will take developers some time to fix and isolate the part of the code that is sending killing queries to our database.
The above are true stories; things that happen every day in at least few environments.
Isolating the bad query isn’t the main problem: that is something that we can do very fast. The issue is identifying the code that is generating the query, and disabling that code without killing the whole application.
That part can take days.
ProxySQL allows us to act fast and stop any offending query in seconds. I will show you how.
Let us say our offending query does this:
1 | SELECT * from history; |
Where history is a table of two Tb partitioned by year in our DWH.
That query will definitely create some issue on the database. It’s easy to identify this query as badly designed.
Unfortunately, it was inserted in the ETL process that uses a multi-thread approach and auto-recovery. Now when you kill it, the process restarts it. After, it takes developers some time to stop that code. In the meantime, your reporting system serving your company in real-time is so slooow (or down).
With ProxySQL, you can stop that query in one second:
1 2 | INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply) VALUES (89,1,'^SELECT \* from history$','Query not allowed',1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK; |
Done, your database never receives that query again! Now the application gets a message saying that the query is not allowed.
And look, it’s possible to do things even better:
1 2 3 4 | INSERT INTO mysql_query_rules (rule_id, active, match_digest, flagOUT, apply) VALUES (89,1,'^SELECT \* FROM history', 100, 0); INSERT INTO mysql_query_rules (rule_id, active, flagIN, match_digest, destination_hostgroup, apply) VALUES (1001,1, 100, 'WHERE', 502, 1); INSERT INTO mysql_query_rules (rule_id, active, flagIN, error_msg, apply) VALUES (1002,1, 100, 'Query not allowed', 1); LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK; |
In this case, ProxySQL checks for any query having SELECT * FROM history. If the query has a WHERE clause, then it redirects it to the server for execution. If the query does not have a WHERE it stops the query and sends an error message to the application.
Conclusion
This is a very basic example of offending query. But I think it makes clear how ProxySQL helps any DBA in stopping them quickly in the case of an emergency.
This gives the DBAs and the developers time to coordinate a better plan of action to permanently fix the issue.
References
https://github.com/sysown/proxysql
http://www.proxysql.com/2015/09/proxysql-tutorial-setup-in-mysql.html
https://github.com/sysown/proxysql/blob/v1.2.2/doc/configuration_howto.md
https://github.com/sysown/proxysql/blob/v1.2.2/INSTALL.md
Hi Marco,
I came here from your ProxySQL Firewalling post, very interesting! Can we use this approach to stop MySQL sleep() attacks? E.g block all case-insensitive occurrences of sleep(n) in a query (SlEep(3), SLeeP(3), …)?
I’ve blogged on this subject, and it’s a real pain in the @ss, knocking down websites (that are vulnerable to SQL injection) and MySQL servers (because hung
sleep()processes).