Innodb_buffer_pool_size設(shè)置方式

緩沖池是用于存儲(chǔ)InnoDB表，索引和其他輔助緩沖區(qū)的緩存數(shù)據(jù)的內(nèi)存區(qū)域。緩沖池的大小對(duì)于系統(tǒng)性能很重要。更大的緩沖池可以減少磁盤I/O來多次訪問同一表數(shù)據(jù)。在專用數(shù)據(jù)庫(kù)服務(wù)器上，可以將緩沖池大小設(shè)置為計(jì)算機(jī)物理內(nèi)存大小的80%

緩沖池相關(guān)參數(shù)說明

1）系統(tǒng)變量參數(shù)

• Innodb_page_size
• InnoDB頁面大?。J(rèn)為16KB）。頁面中包含許多值，頁面大小使它們可以輕松轉(zhuǎn)換為字節(jié)。
• Innodb_buffer_pool_chunk_size
• innodb_buffer_pool_chunk_size 定義InnoDB緩沖池大小調(diào)整操作的塊大小。默認(rèn)128M。最大值可設(shè)置innodb_buffer_pool_size / innodb_buffer_pool_instances
• innodb_buffer_pool_instances
• InnoDB 緩沖池劃分為的區(qū)域數(shù)。
• Innodb_buffer_pool_pages_data
• 數(shù)頁在 InnoDB 緩沖池中包含的數(shù)據(jù)。該數(shù)字包括 臟頁和干凈頁。使用壓縮表時(shí)，報(bào)告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （錯(cuò)誤＃59550）。
• Innodb_buffer_pool_pages_total
• InnoDB 緩沖池 的總大小（以頁為單位）。使用壓縮表時(shí)，報(bào)告的 Innodb_buffer_pool_pages_data 值可能大于 Innodb_buffer_pool_pages_total （錯(cuò)誤＃59550）

2）運(yùn)行狀態(tài)變量

• Innodb_buffer_pool_pages_flushed
• 從緩沖池刷新頁面的請(qǐng)求數(shù) 。
• Innodb_buffer_pool_read_requests
• 表示從內(nèi)存中讀取邏輯的請(qǐng)求數(shù)。
• Innodb_buffer_pool_reads
• InnoDB 不能從緩沖池滿足的邏輯讀取的數(shù)量，必須直接從磁盤讀取。
• Innodb_buffer_pool_wait_free
• 通常，對(duì)InnoDB緩沖池的寫入是在后臺(tái)進(jìn)行的。當(dāng)InnoDB需要讀取或創(chuàng)建一個(gè)頁面而沒有可用的干凈頁面時(shí)，InnoDB會(huì)首先刷新一些臟頁面并等待該操作完成。此計(jì)數(shù)器統(tǒng)計(jì)這些等待的實(shí)例。如果innodb_ buffer_pool_size設(shè)置正確，這個(gè)值應(yīng)該很小。

合理的設(shè)置緩存池相關(guān)參數(shù)

1、innodb_buffer_pool_size 默認(rèn)設(shè)置系統(tǒng)內(nèi)存百分之80%,后按如下規(guī)則配合實(shí)際情況調(diào)整

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 1894874 |
+-------------------------------+---------+
1 row in set (0.00 sec)

mysql>  show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| Innodb_buffer_pool_pages_total | 1965960 |
+--------------------------------+---------+
1 row in set (0.00 sec)

# 計(jì)算是否應(yīng)該添加內(nèi)存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
當(dāng)結(jié)果 > 95% 則增加 innodb_buffer_pool_size
當(dāng)結(jié)果 < 95% 則減少 innodb_buffer_pool_size， 可適當(dāng)較少，當(dāng)然獨(dú)享業(yè)務(wù)機(jī)器多了也沒啥問題

2、innodb_buffer_pool_instances 默認(rèn)設(shè)置為8,最小1，最大64

對(duì)于緩沖池在數(shù)千兆字節(jié)范圍內(nèi)的系統(tǒng)，通過減少爭(zhēng)用不同線程讀寫緩存頁面的爭(zhēng)用，將緩沖池劃分為多個(gè)單獨(dú)的實(shí)例可以提高并發(fā)性。此功能通常用于緩沖池大小在數(shù)GB范圍內(nèi)的系統(tǒng)。使用innodb_buffer_pool_instances 配置選項(xiàng)配置了多個(gè)緩沖池實(shí)例 ，您也可以調(diào)整該innodb_buffer_pool_size值。

當(dāng)InnoDB緩沖池很大時(shí)，可以通過從內(nèi)存中檢索來滿足許多數(shù)據(jù)請(qǐng)求。您可能會(huì)遇到多個(gè)線程試圖立即訪問緩沖池的瓶頸。您可以啟用多個(gè)緩沖池以最小化此爭(zhēng)用。使用散列函數(shù)，將存儲(chǔ)在緩沖池中或從緩沖池中讀取的每個(gè)頁面隨機(jī)分配給其中一個(gè)緩沖池。每個(gè)緩沖池管理自己的空閑列表，刷新列表，LRU和連接到緩沖池的所有其他數(shù)據(jù)結(jié)構(gòu)，并受其自己的緩沖池互斥量保護(hù)。

要啟用多個(gè)緩沖池實(shí)例，請(qǐng)將innodb_buffer_pool_instances配置選項(xiàng)設(shè)置為 大于1（默認(rèn)）的值，最大為64（最大）。僅當(dāng)您將innodb_buffer_pool_size大小設(shè)置為1GB或更大時(shí)，此選項(xiàng)才生效 。您指定的總大小將分配給所有緩沖池。為了獲得最佳效率，指定的組合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每個(gè)緩沖池實(shí)例是至少為1GB。

總結(jié)：

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系統(tǒng)內(nèi)存%80
innodb_buffer_pool_instances = 大于8的情況下，每個(gè)緩沖池實(shí)例至少1GB
......

Reference：

• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
• https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.htm
• https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

設(shè)置innodb_buffer_pool_size參數(shù)

用于緩存索引和數(shù)據(jù)的內(nèi)存大小，這個(gè)當(dāng)然是越多越好， 數(shù)據(jù)讀寫在內(nèi)存中非常快， 減少了對(duì)磁盤的讀寫。

當(dāng)數(shù)據(jù)提交或滿足檢查點(diǎn)條件后才一次性將內(nèi)存數(shù)據(jù)刷新到磁盤中。

然而內(nèi)存還有操作系統(tǒng)或數(shù)據(jù)庫(kù)其他進(jìn)程使用， 根據(jù)經(jīng)驗(yàn)，推薦設(shè)置innodb-buffer-pool-size為服務(wù)器總可用內(nèi)存的75%。 若設(shè)置不當(dāng)， 內(nèi)存使用可能浪費(fèi)或者使用過多。

對(duì)于繁忙的服務(wù)器， buffer pool 將劃分為多個(gè)實(shí)例以提高系統(tǒng)并發(fā)性， 減少線程間讀寫緩存的爭(zhēng)用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影響， 當(dāng)然影響較小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool緩存池中包含數(shù)據(jù)的頁的數(shù)目，包括臟頁。單位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total： innodb buffer pool的頁總數(shù)目。單位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小，單位字節(jié)

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字節(jié)轉(zhuǎn)為G

4.在線調(diào)整InnoDB緩沖池大小，如果不設(shè)置，默認(rèn)為128M

set global innodb_buffer_pool_size = 4227858432; ##單位字節(jié)

計(jì)算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 當(dāng)結(jié)果 > 95% 則增加 innodb_buffer_pool_size， 建議使用物理內(nèi)存的 75%
• 當(dāng)結(jié)果 < 95% 則減少 innodb_buffer_pool_size， 建議設(shè)置大小為： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論