Profiling MySQL Queries – Part 1
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:
- Find the configuration file named my.cnf
- 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
- Restart MySQL and start poking around your application (or run automated tests, if you have written any).
Inspect Server Statistics
I know of two ways to view server stats:
- by running the command SHOW STATUS; in MySQL command-line,
- 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.
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.