How to enable, work with and analyse with Slow Query Log in MySQL?

Question

The following questions will be answered.

1. How to enable slow query log in MySQL
2. How to set slow query time
3. How to read the logs generated by MySQL

Log analysis is becoming a menace day-by-day. Most tech companies have started using ELK stack or similar tools for Log analysis. But what if you don't have hours to spend on the set up of ELK and just want to spend some time on analysing the logs by your on (manually, that is).

Although, it is not the best way but don't underestimate the power of analysing the logs from the terminal. From the terminal too, we can efficiently analyse the logs but there are limitations to what we can or cannot do. I am posting about the basic process of analysing a MySQL log.

Answer 1

(In addition to the 'setup' provided by @MontyPython...)

Run pt-query-digest, or mysqldumpslow -s t

Either will give you the details of 'worst' query first, so stop the output after a few dozen lines.

I prefer long_query_time=1. It's in seconds; you can specify less than 1.

Also, in more recent versions, you need log_output = FILE.

Answer 2

show variables like '%slow%';

+---------------------------+-----------------------------------+
| Variable_name             | Value                             |
+---------------------------+-----------------------------------+
| log_slow_admin_statements | OFF                               |
| log_slow_slave_statements | OFF                               |
| slow_launch_time          | 2                                 |
| slow_query_log            | OFF                               |
| slow_query_log_file       | /var/lib/mysql/server-slow.log    |
+---------------------------+-----------------------------------+

And then,

show variables like '%long_query%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

Change the long query time to whatever you want. Queries taking more than this will be captured in the slow query log.

set global long_query_time = 2.00;

Now, switch on the slow query log.

set global slow_query_log = 'ON';

flush logs;

Go to the terminal and check the directory where the log file is supposed to be.

cd /var/lib/mysql/

la -lah | grep slow

-rw-rw----  1 mysql mysql 4.6M Apr 24 08:32 server-slow.log

Opening the file - use one of the following commands

cat server-slow.log
tac server-slow.log
less server-slow.log
more server-slow.log
tail -f server-slow.log

How many unique slow queries have been logged during a day?

grep 'Time: 160411.*' server-slow.log | cut -c2-18 | uniq -c