semantic blog

The MySQL KEY BUFFER

The most important setting, which should be made in the my.cnf of MySQL server, set the key_buffer_size.
A high key_buffer ensures that the processing can be carried out of the indexes of your tables directly with the RAM memory,
what is a speed advantage over the file system of course.

The value of these variables should be chosen so that the key_buffer used between 25% and 50% of the total system memory.
The main prerequisite for this is that there is a dedicated database server.
If in addition to the MySQL database or an Apache server is installed, the value should be kept rather small.
If the indexes of its tables to be much smaller than 25% - 50% of vefügbaren RAM, you can set the value also proportionately smaller.


Can be set to the key buffer by the way the fly with the following command (Here is an example of the key_buffer size 80MB):
mysql> SET GLOBAL key_buffer_size = 80 * 1024 * 1024;

Calculation of KEY-buffer-size:

To get an overview of their current power system, it is advisable to be viewed,
access times to the file system must be to read the key indexes.


The following figure shows Begfehl at intervals of 10 seconds. If they want to watch the process over a longer period,
For example, use-i to 100
$ Mysqladmin extended-status-uadmin-p-r-i 10 | grep Key_reads
| Key_reads | 4516933
| Key_reads | 12
| Key_reads | 10
| Key_reads | 36
| Key_reads | 4
| Key_reads | 54
| Key_reads | 37
| Key_reads | 82

Should be given the fairly small numbers, this is not a problem for the performance of your MySQL server there.
From about 75-100 Key_reads in the second it is advisable, however, the key_buffer (if possible) to adapt.


If the key_buffer is not set correctly, the key of your indexes are treated the same,
how the data blocks of your tables, which can be read using the file system. If that happens, will
the use of indexes almost superfluous. The indices have come out of RAM!

If you want to know how much memory should ultimately be allocated to the cache, key_buffer,
It might help to know how much space you are MyISAM indexes actually consume on your hard disk.


This can be determined with this command:
$ Du-sh `find / var / lib / mysql / my-database /-name" *. MYI '`

150.0 M / var/lib/mysql/meinedatenbank/tabelle1.MYI
890.0 M / var/lib/mysql/meinedatenbank/tabelle2.MYI
10.0M / var/lib/mysql/meinedatenbank/tabelle3.MYI
950.0 M / var/lib/mysql/meinedatenbank/tabelle4.MYI
Total of 2.0 G

For further calculations for key_buffer you need some variables that are incorporated into the calculations:

mysql> SHOW VARIABLES LIKE '% key_';
+ --------- + ----- +
| Variable_name | value
+ --------- + ----- +
| Key_buffer_size | 147372182
| Key_cache_age_threshold | 200
| Key_cache_block_size | 2048
| Key_cache_division_limit | 100
+ --------- + ----- +
4 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '% KEY_';
+ -------- + ------ +
| Variable_name | value
+ -------- + ------ +
| Key_blocks_not_flushed | 0
| Key_blocks_unused | 9
| Key_blocks_used | 125 235
| Key_read_requests | 19886434342
| Key_reads | 8451382
| Key_write_requests | 57643111
| Key_writes | 2473240
+ -------- + ------ +
7 rows in set (0.00 sec)

The formulas used to calculate the cache hit ratio are respectively the use Buffers:

Cache hit ratio
100 - ((Key_reads * 100) / Key_read_requests)


Percentage of buffer in use
100 - ((Key_blocks_unused key_cache_block_size *) * 100 / key_buffer_size)

Below is a sample calculation:

Cache hit ratio:
100 - ((8451382 * 100) / 19886434342) =
100-0.042498227 = 99.957501773%


This value should fall below 95%, this is definitely not good. The best values ​​are in the 99.99% - region.

Percentage of buffer in use:
100 - ((9 * 2048) * 100/147372182) =
100 - ((18432/167772160) =
100-0.000109863 = 99.999890137%!


When the buffer to 99.99 .. is used, this is a good sign, on the other hand, could be worrying increase this somewhat.
There are some test and try out something necessary to the optimal settings are found.