How to analyse MySQL Slow Query Log or MySQL General Query Log?

Question

Analysing logs is a major problem. If you have a legacy code and hundreds and thousands of queries running, you are bound to be confused by what to improve and where to improve and how much to improve because there is always a tradeoff between how much effort what you are going to put working on a certain optimization problem.

The first step then, is to analyse where we are going wrong. This can be achieved only by properly analysing the logs. Without that, you might not have an idea what exactly is going wrong with your application.

How do you analyze the MySQL logs then?

Answer 1

One Option: Percona Toolkit

Made some huge analysis of slow logs a few months ago..

And I find Percona Toolkit to be very useful. Using this toolkit you can easily make thorough analysis of your slow logs and make database optimization with less hassle.

To name a few useful functions for analysing SLOW LOGS:

pt-query-digest

can analyze queries from MySQL slow, general, and binary logs. (Binary logs must first be converted to text, see --type). It can also use SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, the tool reports which queries are the slowest, and therefore the most important to optimize. More complex and custom-tailored reports can be created by using options like --group-by, --filter, and --embedded-attributes.

Usage example pt-query-digest slow.log

pt-index-usage

Read queries from a log and analyze how they use indexes.

For more information...

Installation

Well, not everyone can make use of this tool, but if you can this should be very helpful.

Answer 2

Search with keywords, i.e., table names or column names.

While using your mobile app or a web application or a desktop application, you will know what page is taking more than expected time to load. Many-a-times, load time is impacted by the queries that run in the background. But that is only when we are making sync calls rather than async calls to fetch the data. So, when you know the page/area which is loading slowly, you will have a list of queries that run on that page while loading and a list of queries that are fired because you perform an action on that page. Once you know the queries, you will know the tables being used and the columns being fetched.

Now, if you are searching the slow query log and you have set very high standards for yourself and set the slow query time to 400 milliseconds, there are chances that your slow query log will be loaded, unless you are a genius. So, we do this simple thing:

grep 'table_name' /var/lib/mysql/mysql-slow.log

This poses more problems than it solves because all the important questions remain unanswered as this doesn't give the query time, query date etc. So, do this:

grep -B 3 'table_name' /var/lib/mysql/mysql-slow.log

-B x gives you x number of lines before the query.

We would need this because the 3 lines prior to the actual query contain all the information about the execution of the query - when was the query executed and how much time did it take!

An alternate:

grep -A 3 'query_user' /var/lib/mysql/mysql-slow.log

Number of slow queries during the day, hour-wise

grep Time mysql-slow.log | cut -d: -f1,2 | sort -r | uniq -c

A note here: you can either use sort -u or you can use sort | uniq -c but be sure that uniq won't work independently, it will only work when you pipe it with sort. Sorting should be done prior to uniq.

Using mysqldumpslow to analyse the log

Getting the top 10 underperforming queries.

mysqldumpslow -u root -p -s c -t 10

If you need to get into detailed log analytics, you should set up the ELK stack on your machine and connect your mysql to the log analytics setup. Or, you can use NewRelic for analysing MySQL logs.