Get expensive queries in ProxySQL

This article contains ProxySQL queries that aid in the retrieval of expensive inquiries or queries that take a lot of resources and should be modified for improved performance.

The table “stats_mysql_query_digest” in ProxySQL contains a lot of metrics collected during runtime. These will aid in the detection of slow queries, resource-intensive searches, and so on. You can optimize queries after you know which ones are causing the performance issues.

SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;
This statement returns the following output retrieved from the documentation.

+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+
| schemaname | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time   | min_time | max_time |
+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+
| test       | root     | 0x7721D69250CB40   | SELECT c FROM sbtest3 WHERE id=? | 8122800    | 1441091306 | 1441101551 | 7032352665 | 1010     | 117541   |
| test       | root     | 0x3BC2F7549D058B6F | SELECT c FROM sbtest4 WHERE id=? | 8100134    | 1441091306 | 1441101551 | 7002512958 | 101      | 102285   |
+------------+----------+--------------------+----------------------------------+------------+------------+------------+------------+----------+----------+

The count_star field indicates how many times the query has been run. Similarly, the sum time shows the total time spent performing queries of this type in microseconds. This is very important for determining where the majority of your application’s burden is spent, and it provides a solid starting point for where to improve.

Another variable to consider is min_time and max_time, which represent the range of durations to expect when running a query like this. The minimal execution time witnessed thus far is min_time, while the maximal execution time is max_time, both in microseconds.

And, sum_rows_affected column values help in identifying the total number of rows affected.

Another table “stats_mysql_processlist” helps in identifying the complete processes that are running on the server. It is equivalent to SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST of MySQL.

SELECT * FROM stats_mysql_processlist ORDER BY time_ms DESC;

Hope you find this article helpful.

Please subscribe for more interesting updates.