Skip to content
ServerPilot

MySQL slow query logs

The MySQL service on your server is configured to log all queries that exceed 0.1 seconds in execution time to MySQL’s slow query log.

View the logs

A server’s MySQL slow query log can be viewed from within the ServerPilot dashboard or through SSH.

Log file location

The MySQL slow query log is located at:

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

Log file format

Each entry in the MySQL slow query log spans multiple lines and has the following format:

# Time: DATE_TIME
# User@Host: DB_USER[DB_USER] @ localhost []  Id: QUERY_ID
# Query_time: QUERY_TIME Lock_time: LOCK_TIME Rows_sent: ROWS_SENT Rows_examined: ROWS_EXAMINED
SET timestamp=TIMESTAMP;
SQL_QUERY

where:

  • DATE_TIME — The date and time of the query in ISO 8601 format with milliseconds.
  • DB_USER — The name of the database user that performed the query.
  • QUERY_ID — The ID that MySQL assigned the execution of the query.
  • QUERY_TIME — The execution time of the query in seconds, including the time spent returning the results to the client that ran the query.
  • LOCK_TIME — The amount of time in seconds the query spent waiting to acquire locks.
  • ROWS_SENT — The number of rows returned to the client from the executed query.
  • ROWS_EXAMINED — The number of rows MySQL read while executing the query.
  • TIMESTAMP — The unix timestamp of the query without milliseconds.
  • SQL_QUERY — The executed SQL query for which the log entry was created.

The following is an example of an entry in the MySQL slow query log. The full text of the SQL query has been abbreviated in this example.

56.179146Z
# User@Host: DB_USER[DB_USER] @ localhost []  Id: 3207186
# Query_time: 2.868424  Lock_time: 0.000343 Rows_sent: 100  Rows_examined: 172784
SET timestamp=1741281356;
SELECT * FROM ...

In the example above, the query took 2.868424 seconds to complete. In this example, part of the reason for the slowness may be due to MySQL needing to examine 172784 rows while executing the query. When MySQL needs to read a large number of rows, there may be indexes that could be added to the app’s database to improve performance by allowing MySQL to use indexes rather than having to look through the contents of many rows every time the query is executed.

Example commands

You must be logged in as root to view the MySQL slow query log.

View recent slow queries

Show the most recent 1000 lines in the MySQL slow query log.

Terminal window
sudo tail -n 1000 /var/log/mysql/mysql-slow.log

Summarize the log with mysqldumpslow

The MySQL slow query log can be summarized using the mysqldumpslow command.

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

See the mysqldumpslow documentation for more information.