MySQL Variables key_buffer_size 数据库 参数变量解释及正确配置使用

本站中文解释

key_buffer_size 是 MySQL 服务器用于缓存索引页的关键变量。 MySQL将数据表索引缓存到 Key_buffer 中以提高数据检索的性能。如果你主要使用MySQL进行数据检索操作，比如读取大量数据库行，就要做出调优。

可以通过以下三种方法设置 key_buffer_size：

1.通过my.cnf文件：如果在MySQL的配置文件（一般是my.cnf）中未定义key_buffer_size参数，MySQL就会使用默认值（一般是8MB）；

2.通过MySQL客户端：用户也可以使用MySQL客户端连接上MySQL服务，然后使用以下命令设置key_buffer_size：

SET GLOBAL key_buffer_size = N;

3.通过控制台：除此之外，也可以在命令行控制台中直接使用：

mysqld -–key_buffer_size=N -–v

官方英文解释

key_buffer_size

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 minimum permissible setting is 0, but you cannot set
key_buffer_size to 0
dynamically. A setting of 0 drops the key cache, which is not
permitted at runtime. Setting
key_buffer_size to 0 is
permitted only at startup, in which case the key cache is not
initialized. Changing the
key_buffer_size setting at
runtime from a value of 0 to a permitted non-zero value
initializes the key cache.

key_buffer_size can be
increased or decreased only in increments or multiples of 4096
bytes. Increasing or decreasing the setting by a nonconforming
value produces a warning and truncates the setting to a
conforming value.

The maximum permissible setting for
key_buffer_size is
4GB−1 on 32-bit platforms. Larger values are permitted
for 64-bit platforms. 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.

You can increase the value to get better index handling for
all reads and multiple writes; on a system whose primary
function is to run MySQL using the
MyISAM storage engine, 25% of the
machine’s total memory is an acceptable value for this
variable. However, you should be aware that, if you make the
value too large (for example, more than 50% of the
machine’s total memory), your system might start to page
and become extremely slow. This is because 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.
You should also consider the memory requirements of any other
storage engines that you may be using in addition to
MyISAM.

For even more speed when writing many rows at the same time,
use LOCK TABLES. See
Section 8.2.4.1, “Optimizing INSERT Statements”.

You can check the performance of the key buffer by issuing a
SHOW STATUS statement and
examining the
Key_read_requests,
Key_reads,
Key_write_requests, and
Key_writes status variables.
(See Section 13.7.5, “SHOW Statements”.) The
Key_reads/Key_read_requests ratio should
normally be less than 0.01. The
Key_writes/Key_write_requests ratio is
usually near 1 if you are using mostly updates and deletes,
but might be much smaller if you tend to do updates that
affect many rows at the same time or if you are using the
DELAY_KEY_WRITE table option.

The fraction of the key buffer in use can be determined using
key_buffer_size in
conjunction with the
Key_blocks_unused status
variable and the buffer block size, which is available from
the key_cache_block_size
system variable:

1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

This value is an approximation because some space in the key
buffer is allocated internally for administrative structures.
Factors that influence the amount of overhead for these
structures include block size and pointer size. As block size
increases, the percentage of the key buffer lost to overhead
tends to decrease. Larger blocks results in a smaller number
of read operations (because more keys are obtained per read),
but conversely an increase in reads of keys that are not
examined (if not all keys in a block are relevant to a query).

It is possible to create multiple MyISAM
key caches. The size limit of 4GB applies to each cache
individually, not as a group. See
Section 8.10.2, “The MyISAM Key Cache”.