≡ Menu

Most important MySQL performance tuning variables

The question – What are the most important and useful MySQL performance tuning variables that I should be aware of? I am currently tuning my database for maximum performance and was wondering what options should I be changing.

There are several MySQL performance tuning variables that I will discuss in this tutorial. I will discuss only those variables that have the most impact on performance. This includes things like caches and slow queries.

1) key_buffer_size

The key_buffer_size is the size of the cache (also called the key cache) which MySQL uses to cache indexes in memory. This is probably the most important variable to performance tune a MySQL installation (available on both Linux and Windows). The recommended value of this variable is equal to or less than the 25% of the RAM (memory) on a machine. I have 2 GB of memory on my web hosting server and I use 256 mega bytes as the key buffer size.

key_buffer_size=256M

There are two very related system status variables that together indicate how effective the key buffer cache is:

a) key_reads: This variable indicates the number of reads from the disk instead of from the key buffer cache.
b) key_read_requests: This status variable indicates the number of total read requests from the cache

So, an important measure of how effective the cache is, is the following percentage:

key_cache_hit_percentage = (1 - Key_reads/Key_read_requests) * 100

A value of 90 to 100% is considered to be a very effective cache.

Further Reading:


2) query_cache_size

The query cache is the portion in memory where MySQL stores complete result sets of frequently used queries. This in-memory cache contains not just the indexes but full outputs of SELECT queries. This type of caching is most effective for web sites in which the database, once populated, just serves as a read only data store from which the website renders web pages. However, it could also be effective for websites in which the database is continuously getting updated, but have a few frequently occurring SELECT statements.

On my MySQL installation, I use a value of 16 mega bytes, which could be a little less, but works fine for me.

query_cache_size=16M

Further Reading:


3) sort_buffer_size

This is the amount of memory used by MySQL to sort the output of SELECT statements. This specifically refers to the ORDER BY and ORDER BY RAND SELECT statements. If you do a lot of sorting in your SELECT statements, you should increase the size of this variable.

On my machine, I use a value of 1 mega byte which is quite enough as I don’t do a lot of ORDER BY statements:

sort_buffer_size=1M

4) query_cache_limit

This is the maximum amount of memory used to cache the output of a SELECT statement. A value of 1 mega byte should be quite enough for most SQL queries. If you expect a very large number of rows in your SELECT statements that you want to be cached, you may increase this value. On my CentOS box, I use the default value of 1 mega byte:

query_cache_limit=1M

5) join_buffer_size

The join buffer size is used to allocate memory for joins that don’t use indexes. If you write joins that use a large number of tables without indexes, you may need to increase this value. I use the default value of 1 mega byte:

join_buffer_size=1M

6) low-priority-updates

This variable, when set to 1, prioritizes SELECT statements above UPDATE AND INSERT statements. This is only applicable for MyISAM tables in which there is table level locking. Enabling low priority updates resulted in a 10 times improvement in my MySQL database performance. This is because I run a lot of web crawling in the background which continuously does UPDATES and INSERTS.


7) wait_timeout

This is the number of seconds to wait for activity on a connection before closing it. The default value is 8 hours. What this means is that if the connection has no activity for 8 hours or more, MySQL will close it. I use the default value but a shorter value might improve performance by closing idle connections.


8) long_query_time

This variable, when slow query logging is enabled, denotes the number of seconds that a query runs after which it is deemed a slow query. If MySQL detects a slow query, it will be logged to a special log file (slow-query-log-file) if slow query logging is enabled (slow-query-log=1). I use a value of 5 seconds (when I am debugging my database performance). This variable should be used together with the slow-query-log variable. Three variables that should be used together are:

slow-query-log = 1
long_query_time = 5
slow-query-log-file = /var/log/slowqueries

Further reading:


Note that all of these variables can be set using your my.cnf file. To locate your my.cnf file, use the following shell session:

ewhathow@ewhathow.com [~]# mysql --help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
ewhathow@ewhathow.com [~]#

On my CentOS Linux box, my.cnf is located in the following directory:

/etc/my.cnf

So, these were 8 most important MySQL performance tuning varaibles that you should be aware of while optimizing your MySQL database. The first two variables (key_buffer_size and query_cache_size are the most important and optimizing them will have an impact on performance almost immediately! Play with them and share your comments!

{ 0 comments… add one }

Leave a Comment