≡ Menu

What is the recommended value of key_buffer_size in MySQL?

Question – I am tuning MySQL on my Linux box and my question is what is the recommended value of the variable key_buffer_size in MySQL? Should I increase it for better performance?

The key_buffer_size indicates the size of the key cache that MySQL uses to store indexes in memory. The cache stores index blocks in memory to avoid reading the disk repeatedly. The key_buffer_size is one of the most important variables to tune to improve MySQL database performance. The index blocks of MyISAM tables are stored in the key cache and are accessible to all processes which use MySQL globally.

The maximum size of the key_buffer_size variable is 4 GB on 32 bit machines, and larger for 64 bit machines. MySQL recommends that you keep the key_buffer_size less than or equal to 25% of the RAM on your machine. This also depends on the other processes that use memory on the machine and it is wise to check if you consistently have 25% of free memory using the Linux command free. More on this later.

Should you increase the key_buffer_size?

First, we must inspect some statistics on the key buffer. Login to your mysql admin tool on the command prompt:

mysql -u databaseuser -p

And then, use the following command when you get the mysql prompt:

mysql> SHOW STATUS LIKE "key%";
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 0         |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 663954641 |
| Key_reads              | 7451404   |
| Key_write_requests     | 2189484   |
| Key_writes             | 2159580   |
+------------------------+-----------+
7 rows in set (1.42 sec)

mysql>

The two variable of interest are the key_reads and the key_read_requests.

Key_reads: This variable represents the number of reads from disk instead of from the cache.
Key_read_requests: This variable represents the number of read requests to read a block from the cache.

So the following variable represents the cache hit percentage:

key_cache_hit_percentage = (1 - Key_reads/Key_read_requests) * 100

If your key_cache_hit_percentage is high, you don’t need to increase the key_buffer_size as it will not have much effect. In my case, the key_cache_hit_percentage is about 98% which is quite high. So increasing the key_buffer_size will not help much. But I already have a key_buffer_size of 256 mega bytes. See what hit percentage you get and if the value is close to 90% to 100%, you already have an optimized key_buffer_size. But if it is less, it may help immensely to increase the key buffer.

Note again, that MySQL recommends that you keep the value of the buffer to 25% or less of your physical RAM size.

Before you decide to increase the key_buffer_size, you must inspect your free memory:

ewhathow@ewhathow.com [~]# free -mt
             total       used       free     shared    buffers     cached
Mem:          2048       2038          9          0          0       1505
-/+ buffers/cache:        532       1515
Swap:         2048        747       1300
Total:        4096       2786       1309

The above output of the free command shows that I have about 1.5 GB of memory free on my machine. So I can safely increase the key_buffer_size.

How to increase the key_buffer_size?

First locate your my.cnf file:

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

On my Linux CentOS box, the MySQL config file is located at /etc/my.cnf.

Now check what buffer size you already have in my.cnf:

ewhathow@ewhathow.com [~]# grep -i key_buffer_size /etc/my.cnf
key_buffer_size=16M
ewhathow@ewhathow.com [~]#

The key_buffer_size is set to 16 mega bytes. Now open the file /etc/my.cnf in your editor and change this value!

vi /etc/my.cnf

And, change the line to something like:

key_buffer_size=256M

Replace 256M to something that you have calculated for your machine.

So that was a quick tutorial on the key_buffer_size variable of MySQL. You must tune the MySQL default installation to improve its performance and the key buffer cache is a very important variable to tune. Follow the guidelines mentioned in this article, especially calculating your hit percentage, and then determine the optimal value of the variable. Increasing the key_buffer_size can also result in a highly reduced number of major page faults! I hope this article was useful to you!

Incoming search terms:

  • key_buffer_size (45)
  • mysql key_buffer_size (23)
  • key_buffer_size mysql (2)
  • key buffer size (1)
  • mysql key_buffer (1)

Comments on this entry are closed.

  • MAdhur February 28, 2014, 1:54 pm

    Thanx a lot,
    Very clean tutorial…

  • Javier April 30, 2014, 10:11 am

    Thanks for the explanation, but I’m sure Unix admins will not accept your explanation regarding which portion of system memory should be considered free…