MySQL之Innodb_buffer_pool_size設定方式

Innodb_buffer_pool_size設定方式

緩衝池是用於儲存InnoDB表，索引和其他輔助緩衝區的快取資料的記憶體區域。緩衝池的大小對於系統效能很重要。更大的緩衝池可以減少磁碟I/O來多次存取同一表資料。在專用資料庫伺服器上，可以將緩衝池大小設定為計算機實體記憶體大小的80%

緩衝池相關引數說明

1）系統變數引數

• Innodb_page_size
• InnoDB頁面大小（預設為16KB）。頁面中包含許多值，頁面大小使它們可以輕鬆轉換為位元組。
• Innodb_buffer_pool_chunk_size
• innodb_buffer_pool_chunk_size 定義InnoDB緩衝池大小調整操作的塊大小。預設128M。最大值可設定innodb_buffer_pool_size / innodb_buffer_pool_instances
• innodb_buffer_pool_instances
• InnoDB 緩衝池劃分為的區域數。
• Innodb_buffer_pool_pages_data
• 數頁在 InnoDB 緩衝池中包含的資料。該數位包括 髒頁和乾淨頁。使用壓縮表時，報告的 Innodb_buffer_pool_pages_data 值可能大於 Innodb_buffer_pool_pages_total （錯誤＃59550）。
• Innodb_buffer_pool_pages_total
• InnoDB 緩衝池 的總大小（以頁為單位）。使用壓縮表時，報告的 Innodb_buffer_pool_pages_data 值可能大於 Innodb_buffer_pool_pages_total （錯誤＃59550）

2）執行狀態變數

• Innodb_buffer_pool_pages_flushed
• 從緩衝池重新整理頁面的請求數 。
• Innodb_buffer_pool_read_requests
• 表示從記憶體中讀取邏輯的請求數。
• Innodb_buffer_pool_reads
• InnoDB 不能從緩衝池滿足的邏輯讀取的數量，必須直接從磁碟讀取。
• Innodb_buffer_pool_wait_free
• 通常，對InnoDB緩衝池的寫入是在後臺進行的。當InnoDB需要讀取或建立一個頁面而沒有可用的乾淨頁面時，InnoDB會首先重新整理一些髒頁面並等待該操作完成。此計數器統計這些等待的範例。如果innodb_ buffer_pool_size設定正確，這個值應該很小。

合理的設定快取池相關引數

1、innodb_buffer_pool_size 預設設定系統記憶體百分之80%,後按如下規則配合實際情況調整

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)

# 計算是否應該新增記憶體
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
當結果 > 95% 則增加 innodb_buffer_pool_size
當結果 < 95% 則減少 innodb_buffer_pool_size， 可適當較少，當然獨享業務機器多了也沒啥問題

2、innodb_buffer_pool_instances 預設設定為8,最小1，最大64

對於緩衝池在數千兆位元組範圍內的系統，通過減少爭用不同執行緒讀寫快取頁面的爭用，將緩衝池劃分為多個單獨的範例可以提高並行性。此功能通常用於緩衝池大小在數GB範圍內的系統。使用innodb_buffer_pool_instances 設定選項設定了多個緩衝池範例 ，您也可以調整該innodb_buffer_pool_size值。

當InnoDB緩衝池很大時，可以通過從記憶體中檢索來滿足許多資料請求。您可能會遇到多個執行緒試圖立即存取緩衝池的瓶頸。您可以啟用多個緩衝池以最小化此爭用。使用雜湊函數，將儲存在緩衝池中或從緩衝池中讀取的每個頁面隨機分配給其中一個緩衝池。每個緩衝池管理自己的空閒列表，重新整理列表，LRU和連線到緩衝池的所有其他資料結構，並受其自己的緩衝池互斥量保護。

要啟用多個緩衝池範例，請將innodb_buffer_pool_instances設定選項設定為 大於1（預設）的值，最大為64（最大）。僅當您將innodb_buffer_pool_size大小設定為1GB或更大時，此選項才生效 。您指定的總大小將分配給所有緩衝池。為了獲得最佳效率，指定的組合 innodb_buffer_pool_instances 和innodb_buffer_pool_size，使得每個緩衝池範例是至少為1GB。

總結：

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系統記憶體%80
innodb_buffer_pool_instances = 大於8的情況下，每個緩衝池範例至少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

設定innodb_buffer_pool_size引數

用於快取索引和資料的記憶體大小，這個當然是越多越好， 資料讀寫在記憶體中非常快， 減少了對磁碟的讀寫。

當資料提交或滿足檢查點條件後才一次性將記憶體資料重新整理到磁碟中。

然而記憶體還有作業系統或資料庫其他程序使用， 根據經驗，推薦設定innodb-buffer-pool-size為伺服器總可用記憶體的75%。 若設定不當， 記憶體使用可能浪費或者使用過多。

對於繁忙的伺服器， buffer pool 將劃分為多個範例以提高系統並行性， 減少執行緒間讀寫快取的爭用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影響， 當然影響較小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool快取池中包含資料的頁的數目，包括髒頁。單位是page。

show global status like 'Innodb_buffer_pool_pages_data';

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

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 檢視@@innodb_buffer_pool_size大小，單位位元組

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #位元組轉為G

4.線上調整InnoDB緩衝池大小，如果不設定，預設為128M

set global innodb_buffer_pool_size = 4227858432; ##單位位元組

計算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 當結果 > 95% 則增加 innodb_buffer_pool_size， 建議使用實體記憶體的 75%
• 當結果 < 95% 則減少 innodb_buffer_pool_size， 建議設定大小為： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。