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:
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
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:
It is also possible to trace slow MySQL queries with the following command: 'show processorlist'.