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)


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的使用率

100 – ( (Key_reads * 100) / Key_read_requests )

Key buffer的使用率
100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )

觉得文章有用?立即: 和朋友一起 共学习 共进步!



电子邮件地址不会被公开。 必填项已用 * 标注


您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>