mysql Key_buffer_size参数的优化设置

先来看看document对这个参数的解释:

缓存myisam表的索引块大小，可以被所有进程所共享。当设置key_buffer_size，操作系统不会马上分配key_buffer_size设置的值，而是在需要的时候，再分配的。可以设置多个key_buffer，当设置不是默认key_buffer为0时，mysql会把缓存的索引块移到默认的key_buffer中去并删除不再使用的索引块。Myisam表中只能cache索引块，不能cache数据块。

原本描述：

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less.

Increase the value to get better index handling (for all reads and multiple writes) to as much as you can afford. Using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common. However, if you make the value too large (for example, more than 50% of your total memory) your system might start to page and become extremely slow. MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. Consider also the memory requirements of other storage engines.

1、建立缓存索引 ：

mysql> set global key_buffer_1.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)
mysql> set global key_buffer_2.key_buffer_size=8384512;
Query OK, 0 rows affected (0.01 sec)

2、把指定表放到key buffer中

mysql> cache index t1,t2 in key_buffer_1;
+————+——————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——————–+———-+———-+
| luoxuan.t1 | assign_to_keycache | status | OK |
| luoxuan.t2 | assign_to_keycache | status | OK |
+————+——————–+———-+———-+
2 rows in set (0.00 sec)

3、预先装载表的索引块

mysql> load index into cache t1,t2;
+————+————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+————–+———-+———-+
| luoxuan.t1 | preload_keys | status | OK |
| luoxuan.t2 | preload_keys | status | OK |
+————+————–+———-+———-+
2 rows in set (0.00 sec)

下面我们来看一下，如果计算命中率及key buffer的使用率