MySQL之Innodb_buffer_pool_size設置方式

Posted on by

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)

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：