Skip to main content

MySQL Slow Log

Use MySQL Slow Log to troubleshoot and identify serious performance issues.

Discuss in Slack
Edit this page on GitHub Report an issue with this doc

This section provides information on how to analyze your MySQL Slow Log to improve site performance.

Analyzing the MySQL Slow Log is an important part of troubleshooting client issues before and after launch.

Requirements

  • SFTP command line interface (CLI)
  • MySQL command line interface
  • A working knowledge of SQL queries and MySQL

Download the MySQL Slow Log via SFTP

  1. Access the Site Dashboard and navigate to the desired environment (Multidev, Dev, Test, or Live).

  2. Click Connection Info and copy the SFTP Command Line command.

  3. Edit and execute the command by replacing appserver with dbserver:

From:

To:

  1. Run the following SFTP command in your terminal:

You now have a local copy of the logs directory, which contains the following:

Below is an example output.

Analyze the MySQL Slow Log

There are several different tools you can use to analyze a MySQL Slow Log:

  • Percona Toolkit

    Recommended. This toolkit is actively maintained and includes a number of database utilities, including a Slow Query log analyzer, pt-query-digest.

  • MySQL Slow Query Log Filter

    This filter has not been updated since 2007. Although still useful, using this filter will throw warnings with newer versions of PHP.

These tools provide summaries of the most commonly called, poor performing, SQL queries called by your website without manually going through the MySQL Slow Log. Refer to each tool's documentation for more information.

Percona Toolkit's pt-query-digest

The example commands below generate a report using pt-query-digest from a MySQL Slow Log file. In this example, one query meets the threshold for reporting as slow: a SELECT COUNT query on the node table that returns a total of results from a nested SELECT query on the node table.

You can use this output to copy the offending query and run it through EXPLAIN on the MySQL server to find out why the query runs slowly. In this case, EXPLAIN shows that the table is missing an index, so adding an index could improve performance.

MySQL Slow Query Log Filter

Below is an example usage of MySQL Slow Query Log Filter, with a minimum execution time of 1 second, sorted by execution count and a no duplicates flag:

Here are the contents of site_name_slow-1s_noDupes.txt:

This particular query is examining 132,363 records to return 5, while taking a full second to do so. That would make it a fairly good candidate for refactoring, since most sites prefer queries to execute in milliseconds.

Look at Slow Queries by Hour

Another method is to look at slow queries by the hour to see if there are spikes in slow queries that correspond to site traffic patterns:

The output above shows that there were 70 slow queries between 10 and 11AM (UTC). That is a roughly even distribution, which probably means there are a few slow queries that keep repeating.

Refer to MySQL Troubleshooting with New Relic® Performance Monitoring for an in-depth look at finding serious MySQL performance issues using New Relic Pro and MySQL Slow Logs.

More Resources