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

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

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.

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:
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 }}