MySQL 参数配置 key_buffer_size

• key_buffer_size

  Command-Line Format	--key-buffer-size=#
  System Variable	key_buffer_size
  Scope	Global
  Dynamic	Yes
  Type	Integer
  Default Value	8388608
  Minimum Value	8
  Maximum Value (64-bit platforms)	OS_PER_PROCESS_LIMIT
  Maximum Value (32-bit platforms)	4294967295

  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.

        MyISAM表的索引块被缓存了，并由所有线程共享。key_buffer_size是用于索引块的缓冲区的大小。密钥缓冲区也称为密钥缓存。

        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.

        32位平台的最大  key_buffer_size 是4GB。64位的系统可以设置更大。想获得最佳的效果可能需要设置小一点，取决于物理机RAM 和操作系统，硬件平台对每个进程的RAM的限制。这个值表示的是内存的申请大小。在服务内部，服务会尽量用满这个数值。

        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.

        为了在读写场景写更好地处理索引，可以增大一点这个值；在使用MyISAM存储引擎时，占物理机总量25%的内存是一个可以接受的值。但是，如果你把值设置的太大，比如大于50%，你的系统会很慢。这是因为MySQL进行数据读取时依赖于操作系统来执行文件系统缓存，因此必须为文件系统缓存留出一些空间。除了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”.

想要在写多行的时候更快的速度，用LOCK TABLES 命令。

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:

可以用SHOW STATUS命令，并检查 Key_read_requests, Key_reads, Key_write_requests, and Key_writes 的值，来确认性能表现。 Key_reads/Key_read_requests 的比率应该小于0.01.

如果用的是修改和删除，Key_writes/Key_write_requests 通常接近1.如果要更改时会同时修改很多的行记录，或者使用DELAY_KEY_WRITE  表的选项 ，可以把值设置的更小一点。

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).

这是个近似值，因为key buffer 中的部分空间是分配给服务管理结构的。影响这些结构的开销包括块大小，指针大小。随着块大小的增加，这个r比率会下降。大的块会导致读的次数减小（每次读到的key 会变多）。但是相反，对不相关的key的读取次数也会增加。

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”.