Catching Slow and Frequent Queries with ProxySQL

Catching Slow and Frequent Queries with ProxySQL

PREVIOUS POST
NEXT POST

Slow and Frequent Queries with ProxySQLIn this blog post,  I’ll look at how to catch slow and frequent queries with ProxySQL.

More and more people are using ProxySQL because it is a great tool and it can help DBAs a lot. But many people do not realize that it is more powerful than it looks. It has many features and possibilities. I am going to show you one of my favorite “tricks” / use cases.

There are plenty of blog posts explaining how ProxySQL works. I am not going to that again. Instead, let’s jump straight to the point. There is a table in ProxySQL called “stats.stats_mysql_query_digest”. It is one of my favorite tables because it basically records all the queries that were running against ProxySQL. Without collecting any queries on the MySQL server, I can find bad queries and much useful information just running a single select on ProxySQL.

Let’s see what the table looks like:

What these fields are (copy/paste from the ProxySQL manual):

  • hostgroup – the hostgroup to which the query was sent. A value of -1 represent a query hitting the Query Cache
  • schemaname – the schema that is currently being queried
  • username – the username with which the MySQL client connected to ProxySQL
  • 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 (with different values for the parameters)
  • first_seen – Unix timestamp, the first moment when the query was routed through the proxy
  • 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. This is particularly useful to figure out where the most time is spent in your application’s workload, and provides a good starting point for where to improve
  • min_time, max_time – the range of durations to expect when executing such a query. min_time is the minimal execution time seen so far, while max_time represents the maximal execution time, both in microseconds.

What can we see here?

We can easily tell which queries are running the most:

The numbers are from a production server, but of course I can’t share the real queries. I just replaced them.

Why is this useful? You can make sure to optimize those queries well, as they are ones running most often. They could have a huge impact on general performance. You can see if a query is running hundreds or thousands times per second, maybe it is a good candidate for caching. Don’t forget ProxySQL also has a Query Cache.

You can even collect these metrics and graph your query distribution. You could see if an individual query is running more than usual or if you deploy a new app or release, you can see the query number changes.

Another use case: imagine that you have a customer, he says the MySQL server is very slow and running a query takes a long time, etc.

If the customer does not have a proper monitoring tool or the slow query log enabled (if long_query_time=2 you already missed a lot of queries), you first have to start collecting queries and metrics from the server.

But if the customer has ProxySQL, we have a field called “sum_time” which basically tells us how long it took a query from the application point of view, which is great.

I can just run the following query (queries that ran more than 100 times):

I can immediately tell which query took the most time on average since restarting ProxySQL. Or I can tell the customer there weren’t any slow queries at all.

Of course, we can filter on queries that were running in the last 10 minutes, or were running more than 1000 times, or on an individual query. The point is ProxySQL already collects all of this information, it would be a waste to not use them.

Can we reset the counters?

Yes, we can. Example resetting the stats in “stats_mysql_query_digest” table we have to run the following query:

Running a query against “stats_mysql_query_digest_reset” table truncates all the statistics from “stats_mysql_query_digest_reset” table.

Conclusion

Don’t forget to use all the ProxySQL’s features. There are other useful tables like “stats_mysql_query_rules” as well.

PREVIOUS POST
NEXT POST

Share this post

Leave a Reply