MyISAM的key_buffer_size

MyISAM的索引方式是非聚集索引,主索引和其他索引沒有本質(zhì)區(qū)別,在data域都是存儲了具體記錄行的地址.key_buffer_size規(guī)定了系統(tǒng)將多少內(nèi)存用作MyISAM的索引緩存.如果內(nèi)存足夠大,又不想去計算,一個簡單的計算辦法就是將所有的索引文件加起來作為key_buffer_size的大小(當然這會存在大量的浪費),但確實一種簡便的辦法.

為了最小化磁盤I/O，MyISAM將最頻繁訪問的索引塊（“indexblock”）都放在內(nèi)存中，這樣的內(nèi)存緩沖區(qū)我們稱之為Key Cache，它的大小可以通過參數(shù)key_buffer_size來控制。在MyISAM的索引文件中（MYI），連續(xù)的單元（contiguous unit）組成一個Block，Index block的大小等于該BTree索引節(jié)點的大小。Key Cache就是以Block為單位的。

1.key cache只存放索引,對于數(shù)據(jù),是讀取操作系統(tǒng)緩存的數(shù)據(jù)文件(如果操作系統(tǒng)能緩存),如果你將key_buffer_size設(shè)置為0,對于索引,將和數(shù)據(jù)文件的讀取方式一致.如果一個讀請求到達,能從key cache中找到數(shù)據(jù),那么就不再訪問myi文件,直接根據(jù)data域去找對應(yīng)的數(shù)據(jù).當然這個前提是查詢能有效用到索引才可以.可以根據(jù)linux的stat查看myi和myd文件,發(fā)現(xiàn)myd被訪問,而myi沒有被訪問.如果在key cache中找不到,則讀取myi中的對應(yīng)block放入key cache的LRU鏈的頭部.

ps:目前大部分的linux下默認使用relatime,#cat /proc/mounts查看,看是否能看到realtime,如果能看到,需要重新掛載該目錄.#mount -o remount,strictatime /

ps:mysql每次只能用到一個索引.

示意圖:

我們先來分析一下與MyISAM 索引緩存相關(guān)的幾個系統(tǒng)參數(shù)和狀態(tài)參數(shù)：

key_buffer_size，索引緩存大??；

這個參數(shù)用來設(shè)置整個MySQL 中的常規(guī)Key Cache 大小。一般來說，如果我們的MySQL 是運行在32 位平臺紙上，此值建議不要超過2GB 大小。如果是運行在64 位平臺紙上則不用考慮此限制，但也最好不要超過4GB也就是4096MB。

key_buffer_block_size，索引緩存中的Cache Block Size；

在前面我們已經(jīng)介紹了，在Key Cache 中的所有數(shù)據(jù)都是以Cache Block 的形式存在，而 key_buffer_block_size 就是設(shè)置每個Cache Block 的大小，實際上也同時限定了我們將 “.MYI”文件中的Index Block 被讀入時候的File Block 的大小。

key_cache_division_limit，LRU 鏈表中的Hot Area 和Warm Area 分界值；

實際上，在MySQL 的Key Cache 中所使用的LRU 算法并不像傳統(tǒng)的算法一樣僅僅只是通過訪問頻率以及最后訪問時間來通過一個唯一的鏈表實現(xiàn)，而是將其分成了兩部分。一部分用來存放使用比較頻繁的Hot Cacke Lock（Hot Chain），被成為Hot Area，另外一部分則用來存放使用不是太頻繁的Warm Cache Block（Warm Chain），被成為Warm Area。這樣做的目的主要是為了保護使用比較頻繁的Cache Block 更不容易被換出。而key_cache_division_limit 參數(shù)則是告訴MySQL該如何劃分整個Cache Chain劃分為Hot Chain和Warm Chain 兩部分，參數(shù)值為WarmChain 占整個Chain 的百分比值。設(shè)置范圍1～100，系統(tǒng)默認為100，也就是只有Warm Chain。

key_cache_age_threshold，控制Cache Block 從Hot Area 降到Warm Area 的限制；

key_cache_age_threshold參數(shù)控制Hot Area 中的Cache Block 何時該被降級到Warm Area 中。系統(tǒng)默認值為300，最小可以設(shè)置為100。值越小，被降級的可能性越大。

對于key_buffer_size 的設(shè)置我們一般需要通過三個指標來計算，第一個是系統(tǒng)索引的總大小，第二個是系統(tǒng)可用物理內(nèi)存，第三個是根據(jù)系統(tǒng)當前的Key Cache 命中率。對于一個完全從零開始的全新系統(tǒng)的話，可能出了第二點可以拿到很清楚的數(shù)據(jù)之外，其他的兩個數(shù)據(jù)都比較難獲取，第三點是完全沒有。當然，我們可以通過MySQL 官方手冊中給出的一個計算公式粗略的估算一下我們系統(tǒng)將來的索引大小，不過前提是要知道我們會創(chuàng)建哪些索引，然后通過各索引估算出索引鍵的長度，以及表中存放數(shù)據(jù)的條數(shù)，公式如下： Key_Size = key_number * (key_length+4)/0.67
Max_key_buffer_size < Max_RAM - QCache_Usage - Threads_Usage - System_Usage Threads_Usage = max_connections * (sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + thread_stack)

當然，考慮到活躍數(shù)據(jù)的問題，我們并不需要將key_buffer_size 設(shè)置到可以將所有的索引都放下的大小，這時候我們就需要Key Cache 的命中率數(shù)據(jù)來幫忙了。下面我們再來看一下系統(tǒng)中記錄的與KeyCache 相關(guān)的性能狀態(tài)參數(shù)變量。

•  Key_blocks_not_flushed，已經(jīng)更改但還未刷新到磁盤的Dirty Cache Block；(如果設(shè)置了delay_key_write,更新索引的時候,該值會增加)
•  Key_blocks_unused，目前未被使用的Cache Block 數(shù)目； 
• Key_blocks_used，已經(jīng)使用了的Cache Block 數(shù)目； 
• Key_read_requests，Cache Block 被請求讀取的總次數(shù)； 
• Key_reads，在Cache Block 中找不到需要讀取的Key 信息后到“.MYI”文件中讀取的次數(shù)； 
• Key_write_requests，Cache Block 被請求修改的總次數(shù)； 
• Key_writes，在Cache Block 中找不到需要修改的Key 信息后到“.MYI”文件中讀入再修改的次數(shù)；

由于上面各個狀態(tài)參數(shù)在MySQL 官方文檔中都有較為詳細的描述，所以上面僅做基本的說明。當我們的系統(tǒng)上線之后，我們就可以通過上面這些狀態(tài)參數(shù)的狀態(tài)值得到系統(tǒng)當前的Key Cache 使用的詳細情況和性能狀態(tài)

Key_buffer_UsageRatio = (1 - Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100%
Key_Buffer_Read_HitRatio = (1 - Key_reads/Key_read_requests) * 100%
Key_Buffer_Write_HitRatio = (1 - Key_writes/Key_Write_requests) * 100%

通過上面的這三個比率數(shù)據(jù)，就可以很清楚的知道我們的Key Cache 設(shè)置是否合理，尤其是Key_Buffer_Read_HitRatio 參數(shù)和Key_buffer_UsageRatio 這兩個比率。一般來說 Key_buffer_UsageRatio 應(yīng)該在99%以上甚至100%，如果該值過低，則說明我們的key_buffer_size 設(shè)置過大，MySQL 根本使用不完。Key_Buffer_Read_HitRatio 也應(yīng)該盡可能的高。如果該值較低，則很有可能是我們的key_buffer_size 設(shè)置過小， 需要適當增加key_buffer_size 值， 也有可key_cache_age_threshold和key_cache_division_limit的設(shè)置不當，造成Key Cache失效太快。

一般來說，在實際應(yīng)用場景中，很少有人調(diào)整key_cache_age_threshold 和key_cache_division_limit這兩個參數(shù)的值，大都是使用系統(tǒng)的默認值。

二.InnoDB的innodb_buffer_pool_size

1.InnoDB主索引是聚簇索引，索引與數(shù)據(jù)共用表空間.InnoDB緩存機制和MyISAM緩存機制的最大區(qū)別就是在于，InnoDB不僅僅是緩存索引，還會緩存數(shù)據(jù).InnoDB將數(shù)據(jù)和索引等信息緩存在innodb_buffer_pool中.

下面的參數(shù)規(guī)定了innodb_buffer_pool的大小和數(shù)量以及其他特性等:
innodb_buffer_pool_instances:幾個innodb_buffer_pool,默認是1個
innodb_buffer_pool_size:每個innodb_buffer_pool_size大小
innodb_additional_mem_pool_size:指定InnoDB用來存儲數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的緩存大小，默認值是2MB

2.InnoDB何時將數(shù)據(jù)加載到innodb_buffer_pool中

InnoDB在MySQL啟動一段時間后,將經(jīng)常訪問的innodb引擎表的數(shù)據(jù)放入innodb_buffer_pool.即innodb_buffer_pool保存的是熱數(shù)據(jù).然后根據(jù)一定算法淘汰不常訪問的數(shù)據(jù).

當停止MySQL服務(wù)時，所有存儲在InnoDB緩沖池中的熱數(shù)據(jù)將被全部清空.重新啟動后,再次緩存數(shù)據(jù).

從5.6版本開始，MySQL支持關(guān)閉MySQL服務(wù)時將內(nèi)存中的熱數(shù)據(jù)保存到硬盤，MySQL重啟后首先將硬盤中的如數(shù)據(jù)加載到InnoDB緩沖池中，以便縮短warmup進程的時間，提高業(yè)務(wù)繁忙高并發(fā)時的效率.

下面是其他網(wǎng)友的補充推薦閱讀

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表，也要使用該值。可以使用檢查狀態(tài)值created_tmp_disk_tables得知詳情。
對于1G內(nèi)存的機器，如果不使用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計算出cache失敗率
3.如果Handler_read_rnd太大,則你寫的SQL語句里很多查詢都是要掃描整個表,而沒有發(fā)揮鍵的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections計算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%以下比較好。

key_buffer_size設(shè)置注意事項

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)性能。因為MySQL使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，所以我們得為系統(tǒng)留夠足夠的內(nèi)存；在很多情況下數(shù)據(jù)要比索引大得多。

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

上面只是對"新手"來說的，我們還可以更深入地優(yōu)化key_buffer_size，使用"show status"來查看"Key_read_requests, Key_reads, Key_write_requests  以及

參考資料：

最新評論