Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Stop Offending Queries With ProxySQL

DZone's Guide to

How to Stop Offending Queries With ProxySQL

Dealing with killer queries? Is the new guy laying waste to your database with bad code? See how ProxySQL can help find and end bad queries.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

ProxySQL

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:

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:

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;


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.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
query ,database

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}