先來看看document對這個參數(shù)的解釋:

緩存myisam表的索引塊大小，可以被所有進(jìn)程所共享。當(dāng)設(shè)置key_buffer_size，操作系統(tǒng)不會馬上分配key_buffer_size設(shè)置的值，而是在需要的時候，再分配的?？梢栽O(shè)置多個key_buffer，當(dāng)設(shè)置不是默認(rèn)key_buffer為0時，mysql會把緩存的索引塊移到默認(rèn)的key_buffer中去并刪除不再使用的索引塊。Myisam表中只能cache索引塊，不能cache數(shù)據(jù)塊。

原本描述：

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、預(yù)先裝載表的索引塊

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)

下面我們來看一下，如果計(jì)算命中率及key buffer的使用率

Cache命中率：
100 – ( (Key_reads * 100) / Key_read_requests )

Key buffer的使用率
100 – ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )在mysql數(shù)據(jù)庫中，mysql key_buffer_size是對MyISAM表性能影響最大的一個參數(shù)，下面就將對mysql Key_buffer_size參數(shù)的設(shè)置進(jìn)行詳細(xì)介紹，供您參考。

下面一臺以MyISAM為主要存儲引擎服務(wù)器的配置：

mysql> show variables like 'key_buffer_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+

分配了512MB內(nèi)存給mysql key_buffer_size，我們再看一下key_buffer_size的使用情況：
mysql> show global status like 'key_read%';

+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+

一共有27813678764個索引讀取請求，有6798830個請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計(jì)算索引未命中緩存的概率：

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬盤，已經(jīng)很BT了，key_cache_miss_rate在0.1%以下都很好(每1000個請求有一個直接讀硬盤)，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當(dāng)減少。
MySQL服務(wù)器還提供了key_blocks_*參數(shù)：

mysql> show global status like 'key_blocks_u%';
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+------------------------+-------------+

Key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，Key_blocks_used表示曾經(jīng)用到的最大的blocks數(shù)，比如這臺服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置：

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

key_buffer_size設(shè)置注意事項(xiàng)
1.單個key_buffer的大小不能超過4G，如果設(shè)置超過4G，就有可能遇到下面3個bug:

http://bugs.mysql.com/bug.php?id=29446

http://bugs.mysql.com/bug.php?id=29419

http://bugs.mysql.com/bug.php?id=5731

2.建議key_buffer設(shè)置為物理內(nèi)存的1/4(針對MyISAM引擎)，甚至是物理內(nèi)存的30%~40%，如果key_buffer_size設(shè)置太大，系統(tǒng)就會頻繁的換頁，降低系統(tǒng)性能。因?yàn)镸ySQL使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，所以我們得為系統(tǒng)留夠足夠的內(nèi)存；在很多情況下數(shù)據(jù)要比索引大得多。

3.如果機(jī)器性能優(yōu)越，可以設(shè)置多個key_buffer,分別讓不同的key_buffer來緩存專門的索引

上面只是對"新手"來說的，我們還可以更深入地優(yōu)化key_buffer_size，使用"show status"來查看"Key_read_requests, Key_reads, Key_write_requests 以及Key_writes "，以調(diào)整到更適合你的應(yīng)用的大小，Key_reads/Key_read_requests的大小正常情況下得小于0.01

參考資料：

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_key_buffer_size

優(yōu)化mysql之key_buffer_size

key_buffer_size
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads /key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%'獲得）。
key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。
對于1G內(nèi)存的機(jī)器，如果不使用MyISAM表，推薦值是16M（8-64M）

提升性能的建議:
1.如果opened_tables太大,應(yīng)該把my.cnf中的table_cache變大
2.如果Key_reads太大,則應(yīng)該把my.cnf中key_buffer_size變大.可以用Key_reads/Key_read_requests計(jì)算出cache失敗率
3.如果Handler_read_rnd太大,則你寫的SQL語句里很多查詢都是要掃描整個表,而沒有發(fā)揮鍵的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections計(jì)算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于內(nèi)存的臨時表代替基于磁盤的 

MySQL優(yōu)化小案例：key_buffer_size
key_buffer_size是對MyISAM表性能影響最大的一個參數(shù)，下面一臺以MyISAM為主要存儲引擎服務(wù)器的配置：

mysql> SHOW VARIABLES LIKE '%key_buffer_size%';
　　
下面查看key_buffer_size的使用情況：

mysql> SHOW GLOBAL STATUS LIKE '%key_read%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| Key_read_requests | 2454354135490   |
| Key_reads         | 23490           |
+-------------------+-----------------+
2 rows in set (0.00 sec)
一共有Key_read_requests個索引請求，一共發(fā)生了Key_reads次物理IO

Key_reads/Key_read_requests ≈ 0.1%以下比較好。

經(jīng)過對比，針對我的內(nèi)存是64G的，所以我把Key_buffer_size設(shè)置為2048M，感覺好多了，么有了內(nèi)存溢出情況。解決了問題。后續(xù)有什么情況腳本之家小編繼續(xù)補(bǔ)充。

最新評論