生產(chǎn)經(jīng)驗(yàn)：在生產(chǎn)環(huán)境中，如何基于機(jī)器配置來(lái)合理設(shè)置Buffer Pool？

1、生產(chǎn)環(huán)境中應(yīng)該給buffer pool設(shè)置多少內(nèi)存？

今天這篇文章我們接著上一次講解的Buffer Pool的一些內(nèi)存劃分的原理，來(lái)給大家最后總結(jié)一下，在生產(chǎn)環(huán)境中到底應(yīng)該如何設(shè)置Buffer Pool的大小呢。

首先考慮第一個(gè)問(wèn)題，我們現(xiàn)在數(shù)據(jù)庫(kù)部署在一臺(tái)機(jī)器上，這臺(tái)機(jī)器可能有個(gè)8G、16G、32G、64G、128G的內(nèi)存大小，那么此時(shí)buffer pool應(yīng)該設(shè)置多大呢？

有的人可能會(huì)想，假設(shè)我有32G內(nèi)存，那么給buffer pool設(shè)置個(gè)30GB得了，這樣的話，MySQL大量的crud操作都是基于內(nèi)存來(lái)執(zhí)行的，性能那是絕對(duì)高！

但是這么想就大錯(cuò)特錯(cuò)了，你要知道，雖然你的機(jī)器有32GB的內(nèi)存，但是你的操作系統(tǒng)內(nèi)核就要用掉起碼幾個(gè)GB的內(nèi)存！

然后你的機(jī)器上可能還有別的東西在運(yùn)行，是不是也要內(nèi)存？然后你的數(shù)據(jù)庫(kù)里除了buffer pool是不是還有別的內(nèi)存數(shù)據(jù)結(jié)構(gòu)，是不是也要內(nèi)存？所以上面那種想法是絕對(duì)不可取的！

如果你胡亂設(shè)置一個(gè)特別大的內(nèi)存給buffer，會(huì)導(dǎo)致你的mysql啟動(dòng)失敗的，他啟動(dòng)的時(shí)候就發(fā)現(xiàn)操作系統(tǒng)的內(nèi)存根本不夠用了！

所以通常來(lái)說(shuō)，我們建議一個(gè)比較合理的、健康的比例，是給buffer pool設(shè)置你的機(jī)器內(nèi)存的50%~60%左右

比如你有32GB的機(jī)器，那么給buffer設(shè)置個(gè)20GB的內(nèi)存，剩下的留給OS和其他人來(lái)用，這樣比較合理一些。

假設(shè)你的機(jī)器是128GB的內(nèi)存，那么buffer pool可以設(shè)置個(gè)80GB左右，大概就是這樣的一個(gè)規(guī)則。

2、buffer pool總大小=(chunk大小 * buffer pool數(shù)量)的2倍數(shù)

接著確定了buffer pool的總大小之后，就得考慮一下設(shè)置多少個(gè)buffer pool，以及chunk的大小了

此時(shí)要記住，有一個(gè)很關(guān)鍵的公式就是：buffer pool總大小=(chunk大小 * buffer pool數(shù)量)的倍數(shù)

比如默認(rèn)的chunk大小是128MB，那么此時(shí)如果你的機(jī)器的內(nèi)存是32GB，你打算給buffer pool總大小在20GB左右，那么你得算一下，此時(shí)你的buffer pool的數(shù)量應(yīng)該是多少個(gè)呢？

假設(shè)你的buffer pool的數(shù)量是16個(gè)，這是沒(méi)問(wèn)題的，那么此時(shí)chunk大小 * buffer pool的數(shù)量 = 16 * 128MB = 2048MB，然后buffer pool總大小如果是20GB，此時(shí)buffer pool總大小就是2048MB的10倍，這就符合規(guī)則了。

當(dāng)然，此時(shí)你可以設(shè)置多一些buffer pool數(shù)量，比如設(shè)置32個(gè)buffer pool，那么此時(shí)buffer pool總大?。?0GB）就是（chunk大小128MB * 32個(gè)buffer pool）的5倍，也是可以的。

那么此時(shí)你的buffer pool大小就是20GB，然后buffer pool數(shù)量是32個(gè)，每個(gè)buffer pool的大小是640MB，然后每個(gè)buffer pool包含5個(gè)128MB的chunk，算下來(lái)就是這么一個(gè)結(jié)果了。

3、一點(diǎn)總結(jié)

我們?cè)賮?lái)做一點(diǎn)總結(jié)，就是說(shuō)你的數(shù)據(jù)庫(kù)在生產(chǎn)環(huán)境運(yùn)行的時(shí)候，你必須根據(jù)機(jī)器的內(nèi)存設(shè)置合理的buffer pool的大小，然后設(shè)置buffer pool的數(shù)量，這樣的話，可以盡可能的保證你的數(shù)據(jù)庫(kù)的高性能和高并發(fā)能力。

然后在線上運(yùn)行的時(shí)候，buffer pool是有多個(gè)的，每個(gè)buffer pool里多個(gè)chunk但是共用一套鏈表數(shù)據(jù)結(jié)構(gòu)，然后執(zhí)行crud的時(shí)候，就會(huì)不停的加載磁盤上的數(shù)據(jù)頁(yè)到緩存頁(yè)里來(lái)，然后會(huì)查詢和更新緩存頁(yè)里的數(shù)據(jù)，同時(shí)維護(hù)一系列的鏈表結(jié)構(gòu)。

然后后臺(tái)線程定時(shí)根據(jù)lru鏈表和flush鏈表，去把一批緩存頁(yè)刷入磁盤釋放掉這些緩存頁(yè)，同時(shí)更新free鏈表。

如果執(zhí)行crud的時(shí)候發(fā)現(xiàn)緩存頁(yè)都滿了，沒(méi)法加載自己需要的數(shù)據(jù)頁(yè)進(jìn)緩存，此時(shí)就會(huì)把lru鏈表冷數(shù)據(jù)區(qū)域的緩存頁(yè)刷入磁盤，然后加載自己需要的數(shù)據(jù)頁(yè)進(jìn)來(lái)。

整個(gè)buffer pool的結(jié)構(gòu)設(shè)計(jì)以及工作原理，就是上面我們總結(jié)的這套東西了，大家只要理解了這個(gè)，首先你對(duì)MySQL執(zhí)行crud的時(shí)候，是如何在內(nèi)存里查詢和更新數(shù)據(jù)的，你就徹底明白了。

接著我們后面繼續(xù)探索undo log、redo log、事務(wù)機(jī)制、事務(wù)隔離、鎖機(jī)制，這些東西，一點(diǎn)點(diǎn)就把MySQL他的數(shù)據(jù)更新、事務(wù)、鎖這些原理，全部搞清楚了，同時(shí)中間再配合穿插一些生產(chǎn)經(jīng)驗(yàn)、實(shí)戰(zhàn)案例。

4、SHOW ENGINE INNODB STATUS

當(dāng)你的數(shù)據(jù)庫(kù)啟動(dòng)之后，你隨時(shí)可以通過(guò)上述命令，去查看當(dāng)前innodb里的一些具體情況，執(zhí)行SHOW ENGINE INNODB STATUS就可以了。此時(shí)你可能會(huì)看到如下一系列的東西：

Total memory allocated xxxx;

Dictionary memory allocated xxx

Buffer pool size ? xxxx

Free buffers ? ? ? xxx

Database pages ? ? xxx

Old database pages xxxx

Modified db pages ?xx

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young xxxx, not young xxx

xx youngs/s, xx non-youngs/s

Pages read xxxx, created xxx, written xxx

xx reads/s, xx creates/s, 1xx writes/s

Buffer pool hit rate xxx / 1000, young-making rate xxx / 1000 not xx / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: xxxx, unzip_LRU len: xxx

I/O sum[xxx]:cur[xx], unzip sum[16xx:cur[0]

下面我們給大家解釋一下這里的東西，主要講解這里跟buffer pool相關(guān)的一些東西。

（1）Total memory allocated，這就是說(shuō)buffer pool最終的總大小是多少

（2）Buffer pool size，這就是說(shuō)buffer pool一共能容納多少個(gè)緩存頁(yè)

（3）Free buffers，這就是說(shuō)free鏈表中一共有多少個(gè)空閑的緩存頁(yè)是可用的

（4）Database pages和Old database pages，就是說(shuō)lru鏈表中一共有多少個(gè)緩存頁(yè)，以及冷數(shù)據(jù)區(qū)域里的緩存頁(yè)數(shù)量

（5）Modified db pages，這就是flush鏈表中的緩存頁(yè)數(shù)量

（6）Pending reads和Pending writes，等待從磁盤上加載進(jìn)緩存頁(yè)的數(shù)量，還有就是即將從lru鏈表中刷入磁盤的數(shù)量、即將從flush鏈表中刷入磁盤的數(shù)量

（7）Pages made young和not young，這就是說(shuō)已經(jīng)lru冷數(shù)據(jù)區(qū)域里訪問(wèn)之后轉(zhuǎn)移到熱數(shù)據(jù)區(qū)域的緩存頁(yè)的數(shù)量，以及在lru冷數(shù)據(jù)區(qū)域里1s內(nèi)被訪問(wèn)了沒(méi)進(jìn)入熱數(shù)據(jù)區(qū)域的緩存頁(yè)的數(shù)量

（8）youngs/s和not youngs/s，這就是說(shuō)每秒從冷數(shù)據(jù)區(qū)域進(jìn)入熱數(shù)據(jù)區(qū)域的緩存頁(yè)的數(shù)量，以及每秒在冷數(shù)據(jù)區(qū)域里被訪問(wèn)了但是不能進(jìn)入熱數(shù)據(jù)區(qū)域的緩存頁(yè)的數(shù)量

（9）Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，這里就是說(shuō)已經(jīng)讀取、創(chuàng)建和寫入了多少個(gè)緩存頁(yè)，以及每秒鐘讀取、創(chuàng)建和寫入的緩存頁(yè)數(shù)量

（10）Buffer pool hit rate xxx / 1000，這就是說(shuō)每1000次訪問(wèn)，有多少次是直接命中了buffer pool里的緩存的

（11）young-making rate xxx / 1000 not xx / 1000，每1000次訪問(wèn)，有多少次訪問(wèn)讓緩存頁(yè)從冷數(shù)據(jù)區(qū)域移動(dòng)到了熱數(shù)據(jù)區(qū)域，以及沒(méi)移動(dòng)的緩存頁(yè)數(shù)量

（12）LRU len：這就是lru鏈表里的緩存頁(yè)的數(shù)量

（13）I/O sum：最近50s讀取磁盤頁(yè)的總數(shù)

（14）I/O cur：現(xiàn)在正在讀取磁盤頁(yè)的數(shù)量

5、今日實(shí)踐思考題

今天留給大家的作業(yè)，就是每個(gè)人都對(duì)自己線上在運(yùn)行的數(shù)據(jù)庫(kù)執(zhí)行上述命令，然后分析一下數(shù)據(jù)庫(kù)的buffer pool的使用情況

這里要尤為關(guān)注的是free、lru、flush幾個(gè)鏈表的數(shù)量的情況，然后就是lru鏈表的冷熱數(shù)據(jù)轉(zhuǎn)移的情況，然后你的緩存頁(yè)的讀寫情況，這些代表了你當(dāng)前buffer ?pool的使用情況。

最關(guān)鍵的是兩個(gè)東西，一個(gè)是你的buffer pool的千次訪問(wèn)緩存命中率，這個(gè)命中率越高，說(shuō)明你大量的操作都是直接基于緩存來(lái)執(zhí)行的，性能越高。

第二個(gè)是你的磁盤IO的情況，這個(gè)磁盤IO越多，說(shuō)明你數(shù)據(jù)庫(kù)性能越差。

大家可以去觀察一下，把自己的分析和思考發(fā)布在評(píng)論區(qū)里一起交流

End

專欄版權(quán)歸公眾號(hào)儒猿技術(shù)窩所有

未經(jīng)許可不得傳播，如有侵權(quán)將追究法律責(zé)任