Tuning MySQL Performance

This article gives a number of advises how to build fast and reliable systems with MySQL. All tips and suggestions are based on rich experience of our specialists working with very large MySQL databases.

1. OPTIMIZE TABLE

Although it's very simple, many of MySQL beginners or ever more experienced users do not use this command. So, what does it do exactly? After a lot of changes such as insert, update and delete data, physical storage of table becomes fragmented on the hard drive. OPTIMIZE TABLE allows to defragment data on high level (without using special tools for hard drives) as follows:

restore the table if it contains deleted or splitted strings
sort index pages if necessary
update statistic data if necessary

OPTIMIZE TABLE must be used after deleting large volume of data or after manipulations (INSERT,UPDATE) with variable size data (VARCHAR, BLOB or TEXT). Note, this operation will block the table.

2. Query Cache

MySQL can cache results of SELECT-queries, so next time when the same query is run it will not cause request to MySQL server. Instead MySQL extracts previously stored data from the cache. Note that MySQL does not cache queries having non-static result, for example queries with system functions (like NOW(), CURDATE(), CURRENT_USER(), CONNECTION_ID() and other), user-defined functions or stored procedures.

To set the size of the query cache, set the 'query_cache_size' system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default. Be careful not to set the size of the cache too large. Due to the need for threads to lock the cache during updates, you may see lock contention issues with a very large cache. When you set 'query_cache_size' to a nonzero value, keep in mind that the query cache needs a minimum size of about 40KB to allocate its structures.

More information about configuring MySQL query cache is here: https://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html

3. Why to Build Indexes

MySQL requires indexes to be created whenever a relationship between two tables is established on a field other than the Primary Key. For example, both fields are included in the "ON table1.field1=table2.field2" clause. Making both fields indexes allows MySQL to JOIN the two tables much more efficiently and much faster.

Recently our specialists worked with a JOIN-query involving tables with more than 1,000,000 records. The query just seemed to be frozen. After a few hours of waiting we decided to carefully investigate the query in order to find if it could be optimized. We noticed that one field involved in ON-clause was of type text. After our specialists have built an index over this field, the query ran in less than half hour.

4. How to Log Slow MySQL Queries

Sometimes it more hard to find "problem" queries than optmize it. For this purpose MySQL provides the feature known as "logging slow queries". To activate this feature open "my.cnf" configuration file and search for "slow". It will lead you to the section for logging slow queries that should look as follows:

# Here you can see queries with especially long duration
#log_slow_queries        = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

Uncomment "log_slow_queries" and "long_query_time" parameters and set "long_query_time" to the appropriate value. It greatly depends on your environment, we used 8 for our system. After you change the configuration in my.cnf, you need to restart the server. Then you will be able to find slow queries in "mysql-slow.log" file. On Linux platforms this file is located at the following path:

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

It is also possible to trace slow MySQL queries with the following command: 'show processlist'. Have questions? Contact us