My friends and family are under attack in Ukraine. Donate to protect them directly or help international organizations.

Profiling MySQL Queries

February 21st, 2012

You website is slow. You suspect MySQL queries are slow, but don't know where to begin. This article, aimed at intermediate developers, will present my methodology and serve as a starting point for first-timers. I won't go in-depth, only cover some of the basics.

Turn On the Slow Query Log

I once had a server with 7.1K slow queries. Here's how to see which queries are slow:

  1. Find the configuration file named my.cnf
  2. Configure the location of the file where to log the queries, and set the minimum time, in seconds, that the query should run to be considered "slow".
    log-slow-queries = /var/log/mysql/mysql-slow.log
    long_query_time = 1
  3. Restart MySQL and start poking around your application (or run automated tests, if you have written any).
As you use your application, the log file will start filling. I usually document the query variations I try and clock the query with each change to see which ones had the most impact. Using EXPLAIN EXTENDED can help you pinpoint where you are losing most of your performance in a query. I will cover this in detail in the next article.
Note: if you ever see queries like these SELECT /*!40001 SQL_NO_CACHE */ * FROM table_name, they are usually associated with a mysqldump command, and not with a query from within your application.

Inspect Server Statistics

I know of two ways to view server stats:

  1. by running the command SHOW STATUS; in MySQL command-line,
  2. by clicking on the Status tab in phpMyAdmin

Example: select_full_join must be 0. If not, the description recommends me to check my table indices. Try checking these stats on a production server, where the load is more realistic, and some of the red flags will only be raised when you have many concurrent users.

If you run into high numbers in the innodb_row_lock group, you can use the InnoDB monitor to check in real-time which tables are locked. Note that if too few users are using the application, this monitor may not show any rows. SHOW INNODB STATUS;

If you are not sure why certain values are too high or how you can improve them, search for the variable names online. I always document the values before and after I make optimization changes.

Conclusion

This should give you a starting point to begin profiling your queries. Not all the problems can be solved with proper queries; be ready to look into hardware, configuration and table structure/indices. Happy profiling!

In my next article, I will talk about the usage of EXPLAIN.

Previous: jQuery Performance Pitfalls Next: Deleting From Self-Referencing Tables