MySQL/Mariadb 如何設定 innodb_buffer_pool_size 增加效能

影響資料庫(MySQL/MariaDB)效能的因素很多，例如資料表的設計、索引、硬體規格、查詢語法、參數設定等都相當關鍵。而在參數優化部分，其中一個很重要的參數是「innodb_buffer_pool_size」，它配置了緩衝區的記憶體大小，所以將直接影響讀寫的效率，網路上有蠻多文章在探討這一塊的優化，在此就不多贅述，將直接教導各位如何在不同的情況下查詢以及設定此參數。

示範版本：Ubuntu 20.04 Server 英文版、MariaDB 10.3.32

1. 查詢 innodb_buffer_pool_size 的大小

進入資料庫，透過以下指令可以查詢到目前「InnoDB Buffer Pool Size」的大小

方法一

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

查詢 innodb_buffer_pool_size 的大小

方法二

SELECT @@innodb_buffer_pool_size;

你也可以在參數後面除以多個「1024」，用不同的單位來呈現，例如下圖的範例是除以「/1024/1024」，即會以MB為單位呈現，如果想用GB呈現的話，則是「/1024/1024/1024」

查詢 innodb_buffer_pool_size 的大小，以MB為單位呈現

2. 設定 innodb_buffer_pool_size

因為資料庫在安裝的時候可能是直接安裝軟體，或者是透過Docker的方式安裝，因此以下分為這二種情況來說明如何設置「innodb_buffer_pool_size」這個值

情境一：資料庫是直接安裝

若資料庫是直接安裝的狀況，可以透過修改組態檔「my.cnf」的方式來設定，該檔位於「/etc/mysql」目錄下，請參考下方步驟進行：

# 1. 至「/etc/mysql」目錄中，編輯「my.cnf」
vi /etc/mysql/my.cnf

# 2. 加入以下參數，此範例是將size設定2G
[mysqld]
innodb_buffer_pool_size = 2G (或者 innodb_buffer_pool_size = 2048*1024*1024)

設定 innodb_buffer_pool_size 的大小

設定完成存檔離開後，請重啟服務即可

systemctl restart mysql

情境二：資料庫透過Docker安裝

若你的資料庫是透過Docker來安裝的，則可以在Docker Compose中來進行設定，這邊用之前的教學範例來說明。在下圖中，Docker Compose的內容和之前的範例是一樣的，不過把原本的image的值由「mysql:5.7」改成「mariadb」，但這只是改用不同的資料庫而已，對整體的設定來說不影響，你也可以延用原本的值「mysql:5.7」。重點是在下圖劃紅線的部分，範例中加入了以下指令：

# 將值設成600MB
command: --innodb-buffer-pool-size=600M

即透過關鍵字「command」來設定「innodb_buffer_pool_size」，設定完後記得重啟docker即可。

在Docker中設定innodb_buffer_pool_size

除了「innodb_buffer_pool_size」參數可以這樣設定外，MySQL/MariaDB還有許多參數都可以用這種方式來處理，在MySQL的官方文件中有列出可以調整的參數列表，有需要的話可以參考一下。

補充資料

動態設定參數

在MySQL 5.7.5版本之後，可以直接動態的設定參數，MariaDB也適用，想透過這種方式設定的話，請先進入資料庫中，並執行以下指令進行設置，不過要特別注意，如果使用這種方式設定，在「值」的部分不能直接用G、M等字來設定GB或MB，而必需用數字來呈現，而且經過實際測試的結果顯示，只要重開機後，設定值就會恢復成原本的預設值，所以這種作法還是不推薦，大家可以注意一下你的環境中是否也有同樣的情況。

SET GLOBAL innodb_buffer_pool_size=2048*1024*1024;

設定的參考值？

網路上有些文章在討論「innodb_buffer_pool_size」這個值該如何設定才比較好，雖然網友建議是將值設定成實體記憶體的「70% ~ 80%」之間，但建議還是要考慮在系統中是否有其他軟體需要配置記憶體，不然會影響整體的運作。另外，除了這個值之外，可能也需要搭配其他的參數來將效能最佳化，關於這部分建議可以上網多查查，但以我個人的經驗來說，當設完「innodb_buffer_pool_size」的值後，其效能就有明顯提升。

靖技場