Finding and Optimizing Slow MySQL Queries
There are many things you can do to prepare your MySQL database and the queries it runs so that they are as efficient and optimized as can be. In this article, we will be going over some of the steps you can take to find slow queries, as well as how to optimize them and why these optimizations end up helping.
#1: Enabling the Slow Query Log
MySQL comes out of the box with many great tools to analyze your specific database’s situation and the queries it is running on a daily basis. One of those essential tools is the slow query log-- a list of queries that have taken more than a specified amount of time to execute (set by default to 10 seconds via the long_query_time variable), and therefore can likely be optimized.
Note: The slow query log should not be left enabled for an extended period of time. Doing so can decrease database performance as a portion of MySQL will be devoted to the I/O usage of writing and parsing these logs.
Enabling the Slow Query Log is quite simple, and by combing through it you will likely learn much about how your database is truly performing. First, log in to your server (or connect via MySQL Workbench) and run the following query:
SET GLOBAL slow_query_log = 'ON';
If you want to set the location of the Slow Query log to something other than the default /var/lib/mysql/hostname-slow.log, you can also run this:
SET GLOBAL slow_query_log_file = '/path/to/slow_queries.log';
In either case, make sure the file exists and has permissions to the MySQL user and group. You can do this by running the following command in an SSH prompt where ‘/path/to/slow_queries.log’ is the location of the log you’ve specified above.
touch /path/to/slow_queries.log && chown mysql.mysql /path/to/slow_queries.log
Once that is complete, you should begin to see any and all slow queries populating the log you’ve created. To set these variables permanently, add the following to /etc/my.cnf under the [mysqld] section:
slow_query_log=on
slow_query_log_file='/path/to/slow_queries.log'
Once these two lines are added, simply restart the MySQL service whenever it is convenient and the slow query log will be permanently enabled (unless you remove them, of course!).
#2: Avoid SELECT * Statements
The number one reason we most often see queries taking a long time to run or using quite a bit of resources is due to this statement. The SELECT * statement within a query reads ALL of the data within your table before printing the results. This causes higher disk I/O as all that data is indexed, and that eventually leads to slower website load times and a decrease in database performance.
It is always a good idea to specify which columns you want when performing your SELECT statements. For example:
// Inefficient:
$a= mysql_query( "SELECT * FROM name WHERE id = 1" );
$b = mysql_fetch_assoc( $a );
echo "Welcome {$b['firstname']}" ;
// More Efficient:
$a = mysql_query( "SELECT firstname FROM name WHERE id = 1" );
$b = mysql_fetch_assoc( $a );
echo "Welcome {$b['firstname']}" ;
Remember: the larger your table, the bigger the difference this will make, as the more data SELECT * will be combing through.
#3: Tuning the MySQL Server
You may find that your slow query log is almost empty or you’ve optimized your queries as much as you feel comfortable doing. Alternatively, if you are using a Content Management System (CMS) such as WordPress or Joomla, your databases will generally come with the proper indexes and formatting that they need right out of the box. In any case or simply just to be thorough, there is always the option of tuning the MySQL server itself-- such is an advantage of the open MySQL software.
Below are several key variables which can be tweaked for performance based on your specific server’s resources and needs. To edit these variables, simply edit the /etc/my.cnf on your server via an SSH connection and add/change them under the [mysqld] section within the file. Afterward, restart the MySQL service. As with any variable, we suggest starting with small increments to the default and then testing the new value’s performance over a few days. Setting a variable too high can have detrimental effects on a server’s resources.
Table Buffer Size (read_buffer_size 1)
Occasionally when a MySQL query performs a full table scan, a memory buffer is allocated to said query. The read_buffer_size variable controls the size of this buffer. If your MySQL queries are generally performing slowly, you may be able to better this performance by increasing the value (in bytes) attached to the variable based on your server’s total RAM. A good starting point is slightly above the default value of 131072: 147456.
Controlling the Amount of Maximum Open Tables (table_open_cache)
The table_open_cache variable controls the maximum number of tables MySQL can keep open at any one time and as such the server’s ability to respond to incoming requests. Increasing this variable will allow MySQL to keep open a larger amount of tables. Ideally, you’d like this set so that MySQL will re-open a table as infrequently as possible, e.g. with a larger number. The default of this value is 2000, as such an optimal starting point for testing would be 2300.
Altering Index Buffer Size (key_buffer_size 2)
The key_buffer_size variable manages the size of the buffer used when handling table indexes. The MySQL manual recommends this variable to be increased “as much as can be afforded” so that indexed tables receive maximum performance. A value equivalent to about 25 percent of your server’s total system memory is suggested. This is often considered a key configuration variable in regards to optimizing and improving performance, so trying different values for it may be a good place to start. The default value for key_buffer_size is a mere 8388608 bytes, or 8MB. We suggest starting with a value of 1GB (or however much 25% of your server’s RAM equals out to be) and slowly increasing the value from there.