All of us are very good in writing good queries, we know that ;) but sometimes a bad query may escape our control and hit (badly) our database.

There is also that probie, who just join the company and is writing all his code with SELECT * and no WHERE.

We have told him millions of time that doing this is bad, but he seems not listening.

Ah the other day we had another code injection that developers will take some time to fix, and that take them some time to isolate the part of the code sending the killing query to our database.

All the above are true stories, things that happen every day in at least few environments.

The main problem in that case is not to isolate the bad query, that is something that we can do very fast, but to identify the code that is generating it and disable that part of the code without killing the whole application.

That part can takes days.

ProxySQL allow us to act fast and stop any offending query in seconds.

I will show you how.

Let us say we have an offending query that does this:

SELECT * FROM history;

Where history is a table of 2 Tb partitioned by year in our DWH.

A query like that will certainly create some issue on the database, it is obviously bad design, and easy to identify.

Unfortunately it was inserted in the ETL process that use multi thread approach and autorecovery.

As such whenever you kill it, the process will restart it, and the developers will take some time to stop that.

In the meantime your reporting system serving your company real time is so slooow or down.

With ProxySQL you will stop that query in 1 second:

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 DB will never receive that query again, and the application will get a message saying that the query is not allowed.

But it is possible to do things even better:

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 will check for any query having SELECT * FROM history.

If the query has a WHERE clause then it will redirect to the server for execution.

If the query does not have a WHERE it will be stop and an error message sent to the application.

Conclusion

The one above is a very simple almost basic example of offending query.

But I am sure it makes clear how ProxySQL can come in help to any DBA to stop them quickly in case of emergency.

Giving 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