We have a huge database on work, and i think the combinasion of bad hardware and bad indexing are the reason for our backend are not working so perfect as we work on its shut do.

So are there are perfect or great way to work with index and query optimizion in MySQL and not only use the slow-log and test every single query out?

hope there are way around so we can find all the bad querys and fix them.


Quick and Cheap

The quickest method for query review is indeed the slow query log, combined with a tool called pt-query-digest

An example process is to first set long_query_time=1. Set it to 0 for most accurate results, but it might affect IO depending on load of the system.

Let the slow query log run for a period of time, such as 1 week, to capture a significant number of query samples.

Then run pt-query-digest against the slow log.

This tool is free to use.

Continual process

To use a similar process, but have it behind a GUI to know when queries showed up or have been checked, you can set up Anemometer

An enterprise solution

The previous tools mentioned come without much cost, but only focus on queries.

A tool like VividCortex will provide you a great view of the performance of your system, and help you drill down into the queries in many different ways.

The method VividCortex uses to capture queries is lower impact than enabling the slow query log.

  • 1
    I usually set long_query_time=0 for 10-15 minutes and then use the pt-query-digest on the output, so I have fuller image about what is being run. – ypercubeᵀᴹ Sep 30 '16 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.