In this blog post, we’ll look at how you can verify query performance using ProxySQL.
In the previous blog post, I showed you how many information can you get from the “stats.stats_mysql_query_digest” table in ProxySQL. I also mentioned you could even collect and graph these metrics. I will show you this is not just theory, it is possible.
These graphs could be very useful to understand the impact of the changes what you made on the query count or execution time.
I used our all-time favorite benchmark tool called Sysbench. I was running the following query:
UPDATE sbtest1 SET c=? WHERE k=?
There was no index on “k” when I started the test. During the test, I added an index. We expect to see some changes in the graphs.
I selected the “stats.stats_mysql_query_digest” into a file in every second, then I used Percona Monitoring and Management (PMM) to create graphs from the metrics. (I am going write another blog post on how can you use PMM to create graphs from any kind of metrics.)
Without the index, the update was running only 2-3 times per second. By adding the index, it went up to 400-500 hundred. We can see the results immediately on the graph.
Let’s see the average execution time:
Without the index, it took 600000-700000 microseconds, which is around 0.7s. By adding an index, it dropped to 0.01s. This is a big win, but most importantly we can see the effects on the query response time and query count if we are making some changes to the schema, query or configuration as well.
If you already have a ProxySQL server collecting and graphing these metrics, they could be quite useful when you are optimizing your queries. They can help make sure you are moving in the right direction with your tunings/modifications.