先來(lái)看看document對(duì)這個(gè)參數(shù)的解釋:

緩存myisam表的索引塊大小，可以被所有進(jìn)程所共享。當(dāng)設(shè)置key_buffer_size，操作系統(tǒng)不會(huì)馬上分配key_buffer_size設(shè)置的值，而是在需要的時(shí)候，再分配的?？梢栽O(shè)置多個(gè)key_buffer，當(dāng)設(shè)置不是默認(rèn)key_buffer為0時(shí)，mysql會(huì)把緩存的索引塊移到默認(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)

下面我們來(lái)看一下，如果計(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ù)庫(kù)中，mysql key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，下面就將對(duì)mysql Key_buffer_size參數(shù)的設(shè)置進(jìn)行詳細(xì)介紹，供您參考。

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

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

分配了512MB內(nèi)存給mysql key_buffer_size，我們?cè)倏匆幌耴ey_buffer_size的使用情況：
mysql> show global status like 'key_read%';

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

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

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

比如上面的數(shù)據(jù)，key_cache_miss_rate為0.0244%，4000個(gè)索引讀取請(qǐng)求才有一個(gè)直接讀硬盤(pán)，已經(jīng)很BT了，key_cache_miss_rate在0.1%以下都很好(每1000個(gè)請(qǐng)求有一個(gè)直接讀硬盤(pán))，如果key_cache_miss_rate在0.01%以下的話(huà)，key_buffer_size分配的過(guò)多，可以適當(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ù)，比如這臺(tái)服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過(guò)渡索引了，把緩存占滿(mǎn)了。比較理想的設(shè)置：

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

key_buffer_size設(shè)置注意事項(xiàng)
1.單個(gè)key_buffer的大小不能超過(guò)4G，如果設(shè)置超過(guò)4G，就有可能遇到下面3個(gè)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(針對(duì)MyISAM引擎)，甚至是物理內(nèi)存的30%~40%，如果key_buffer_size設(shè)置太大，系統(tǒng)就會(huì)頻繁的換頁(yè)，降低系統(tǒng)性能。因?yàn)镸ySQL使用操作系統(tǒng)的緩存來(lái)緩存數(shù)據(jù)，所以我們得為系統(tǒng)留夠足夠的內(nèi)存；在很多情況下數(shù)據(jù)要比索引大得多。

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

上面只是對(duì)"新手"來(lái)說(shuō)的，我們還可以更深入地優(yōu)化key_buffer_size，使用"show status"來(lái)查看"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ū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過(guò)檢查狀態(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只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤(pán)表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。
對(duì)于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太大,則你寫(xiě)的SQL語(yǔ)句里很多查詢(xún)都是要掃描整個(gè)表,而沒(méi)有發(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)存的臨時(shí)表代替基于磁盤(pán)的 

MySQL優(yōu)化小案例：key_buffer_size
key_buffer_size是對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)，下面一臺(tái)以MyISAM為主要存儲(chǔ)引擎服務(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個(gè)索引請(qǐng)求，一共發(fā)生了Key_reads次物理IO

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

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

最新評(píng)論