七月 28, 2018 | 伺服器和Devops

【網站優化】MYSQL系統參數說明與調整ABCDEFG

當你的網站從小流量到越來越大流量的時候，往往就會開始面對到要調整資料庫配置。但如何有效的調整是一個頭痛的問題，雖然MYSQL官方有提供一些範例，但對於一些特殊的案子與情況，還是會需要深度瞭解，如何有效的調整，才能夠讓資源做最有效的運用，而不是直接選擇升級伺服器作為唯一考量。

前言

前置作業

在看這篇文章前，先假設你已經安裝了MYSQL，並且大致瞭解了MYSQL COMMAND

基本指令

查看系統參數
```
mysql> show variables;
```
查看系統當前狀態
```
mysql> show status;
```

查看系統參數 Server System Variable

當你打入了show vairables的指令後，看到的參數就是系統參數，這個參數不會隨著系統的開始而更改，算是系統資源的各種限制與調整參數，而這些參數都是定義在mysqld裡面。

舉例來說，可以從參數裡面看到max_connections這個參數，其數值為500（每個環境不一樣），代表系統預設同時可以連線資料庫的數量不能超過500個。但這個參數並非表示當前的連線數量。

由於MYSQL的官方文件寫的很詳細，但從另外一個角度來說，就是超多的，而且都是英文，閱讀性相對來說覺得比較辛苦。因此，這裡整理了一下，把覺得重要需要知道的參數列出來，並且針對網站優化要用到的參數，會用 :santa: 來表明

Audit系列參數

audit系列為企業版，社群版沒有提供，主要提供系統審計的功能。若你想要或需要這方面的功能，可以考慮使用mariadb的audit插件，這裡不多做描述。

<table> <thead> <tr> <th>參數名稱</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td>audit系列參數</td> <td>提供資料庫審計功能</td> <td>社群版沒有提供</td> </tr> </tbody> </table>

備註：

若你想要或需要這方面的功能，可以考慮使用mariadb的audit插件，同樣可以使用在MYSQL的環境內。

Authentication LDAP系列參數

Authentication參數主要是用來串接LDAP伺服器，讓LDAP使用者可以Access資料庫，這個系列參數則需要安裝LDAP插件，同樣也是屬於企業版的功能。

<table> <thead> <tr> <th>參數名稱</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td>authentication_ldap系列參數</td> <td>提供與LDAP認證相關功能</td> <td>社群版沒有提供</td> </tr> </tbody> </table>

Auto系列參數

<table> <thead> <tr> <th>參數名稱</th> <th>型態</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td>auto_generate_certs</td> <td>布林</td> <td>是否自動產生OpenSSL的KEY與憑證</td> <td></td> </tr> <tr> <td>auto_increment_increment</td> <td>數字</td> <td>使用在Master-Master-Replication時，需要設置的增長參數</td> <td>請見備註1</td> </tr> <tr> <td>auto_increment_offset</td> <td>數字</td> <td>使用在Master-Master-Replication時，需要設置的增長參數</td> <td>請見備註1</td> </tr> <tr> <td>autocommit</td> <td>布林</td> <td>預設啟用，代表變更系統可直接成功</td> <td>請見備註2</td> </tr> <tr> <td>automatic_sp_privileges</td> <td>布林</td> <td>自動給與使用者更新資料表的權限(CREATE、ALTER)</td> <td></td> </tr> </tbody> </table>

備註：

這兩個參數主要是在做MYSQL的Master與Slave的主從備援機制，若有興趣可以參考MySQL Master-Master Replication Manager(1) - 簡介或 Mysql Master/Slave Replication With Docker 這兩篇文章，有具體描述觀念與作法。
若autocommit設定為0，代表所有的變更都要額外再加入COMMIT還有ROLLBACK才能夠成功。更進一步的作法，可以參考這裡

B系列參數

<table> <thead> <tr> <th>優化</th> <th>參數名稱</th> <th>型態</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td><img alt=":santa:" class="emoji" src="https://hackmd.io/build/emojify.js/dist/images/basic/santa.png" title=":santa:"></td> <td>back_log</td> <td>數字</td> <td>有多少的Request可以排隊進入MYSQL的連線數內</td> <td>解釋請見備註1</td> </tr> <tr> <td></td> <td>big-tables</td> <td>布林</td> <td>預設：關閉，用於存取大量的暫存資料為檔案</td> <td>請見備註2</td> </tr> </tbody> </table>

BinLog系列參數

<table> <thead> <tr> <th>參數名稱</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td>binlog系列參數</td> <td>用於記錄所有更新或潛在更新資料庫的記錄（例如，DELETE一個不存在的資料指令）的指令。這裡用事件的方式進行保存。</td> <td></td> </tr> </tbody> </table>

備註：

想像在一間可以容納100人吃飯的餐廳，若餐廳已經客滿的狀態下，其他的人必須要在餐廳外面排隊，當然，排隊的長度也不能夠太長，back_log就是來決定這個長度。而這個數值不能無限制的往上，通常跟伺服器環境有關。MYSQL官方建議排隊的長度可由以下公式算出。
```
50 + (max_connections / 5)
```
若設定成為1，那所有的暫存Table都會存在硬碟內，而不是存在記憶體內，速度會超慢。非必要別啟用。

C系列參數

這裡有眾多Character還有Collation系列參數，這個部分都已經非常晚上且不太需要做額外的調整，因此這裡只介紹有需要的變數 <table> <thead> <tr> <th>優化</th> <th>參數名稱</th> <th>型態</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td><img alt=":santa:" class="emoji" src="https://hackmd.io/build/emojify.js/dist/images/basic/santa.png" title=":santa:"></td> <td>connect_timeout</td> <td>數字</td> <td>資料庫等候連線包的時間，預設10秒。</td> <td>備註1</td> </tr> <tr> <td></td> <td>connection_control_failed_connections_threshold</td> <td>數字</td> <td>連續失敗連線嘗試次數</td> <td></td> </tr> <tr> <td></td> <td>connection_control_max_connection_delay</td> <td>數字</td> <td>失敗後，系統再次回應延遲的最大時間</td> <td></td> </tr> <tr> <td></td> <td>connection_control_min_connection_delay</td> <td>數字</td> <td>失敗後，系統再次回應延遲的最小時間</td> <td></td> </tr> </tbody> </table>

備註：

太短容易造成Bad handshake，若系統常出現Lost Connection，可以藉由這個數值調高而改善。

DEF系列參數

<table> <thead> <tr> <th>優化</th> <th>參數名稱</th> <th>型態</th> <th>描述</th> <th>備註</th> </tr> </thead> <tbody> <tr> <td></td> <td>default_password_lifetime</td> <td>數字</td> <td>密碼淘汰時間，預設：0（不啟用）</td> <td>單位：天</td> </tr> <tr> <td></td> <td>disconnect_on_expired_password</td> <td>布林</td> <td>與上面參數對應，預設啟用</td> <td></td> </tr> </tbody> </table>