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

Verifying Query Performance Using ProxySQL

DZone's Guide to

Verifying Query Performance Using ProxySQL

Learn how you can even collect and graph metrics from the stats.stats_mysql_query_digest table in ProxySQL while verifying query performance.

· Database Zone ·
Free Resource

Slow SQL Server? These SentryOne resources share tips and tricks for not only troubleshooting SQL Server performance issues, but also preventing them before they hit your production environment.

In this post, we'll look at how you can verify query performance using ProxySQL.

In the previous post, I showed you how much 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 two to three times per second. By adding the index, it went up to 400-500. We can see the results immediately on the graph.

Let's see the average execution time:

Without the index, it took 600,000-700,000 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.

Conclusion

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.

Database monitoring tools letting you down? See how SentryOne empowers Enterprises to go faster.

Topics:
database ,database performance ,queries ,optimization ,tutorial ,proxysql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}