← ServerPilot Docs

How to Read the MySQL Slow Query Log

Introduction

For applications you are building where you've designed your own database schema, it is common to need to debug performance issues when databases grow very large. Often due to a lack of indexes, queries that were extremely fast when a database table had only ten thousand rows will become quite slow when the table has millions of rows.

The MySQL slow query log is where the MySQL database server registers all queries that exceed a given threshold of execution time. This can often be a good starting place to see which queries are slowest and how often they are slow.

You will need to understand the details of your application to determine whether a logged query is of any concern. For example, a query that takes 20 seconds to run but is only run once a month by an administrative script is probably not a source of concern. A query that takes 2 seconds but runs thousands of times an hour as a result of users interacting with your application may be a concern.

Reading the MySQL Slow Query Log

The slow query log is located at:

/var/log/mysql/mysql-slow.log

For security reasons, you can only access this file as root using sudo. You will not be able to read this file when SSH'd in as an app's system user.

Entries in the slow log file look like this:

# Time: 140905  6:33:11
# User@Host: dbuser[dbname] @ hostname [1.2.3.4]
# Query_time: 0.116250  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 20878
use dbname;
SET timestamp=1409898791;
...SLOW QUERY HERE...

Using mysqldumpslow

Rather than reading the slow query log directly, it is often better to use the command mysqldumpslow to parse and summarize the slow query log.

You can run the command like this while SSH'd in as root:

sudo -i mysqldumpslow /var/log/mysql/mysql-slow.log

See the mysqldumpslow documentation for more information.