七月 28, 2018 | 伺服器和Devops

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

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

前言

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

前置作業

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

基本指令

  1. 查看系統參數

    mysql> show variables;
    
  2. 查看系統當前狀態

    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>

備註:

  1. 若你想要或需要這方面的功能,可以考慮使用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>

備註:

  1. 這兩個參數主要是在做MYSQL的Master與Slave的主從備援機制,若有興趣可以參考MySQL Master-Master Replication Manager(1) - 簡介Mysql Master/Slave Replication With Docker 這兩篇文章,有具體描述觀念與作法。
  2. 若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>

備註:

  1. 想像在一間可以容納100人吃飯的餐廳,若餐廳已經客滿的狀態下,其他的人必須要在餐廳外面排隊,當然,排隊的長度也不能夠太長,back_log就是來決定這個長度。而這個數值不能無限制的往上,通常跟伺服器環境有關。MYSQL官方建議排隊的長度可由以下公式算出。

    50 + (max_connections / 5)
    
  2. 若設定成為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>

備註:

  1. 太短容易造成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>

GTID系列

這個部分用在Master與Slave的環境配置,這裡不多做著墨,未來再補上這個部分相關的文章

小結

本來想要一篇文章介紹完畢全部的系統變數,真的認真寫下來之後,才知道篇幅有多可怕。因此,到這裡還是先暫緩一下吧。下一篇文章,將會開始介紹Innodb的系統變數,若有興趣再請繼續觀看。這一系列文章,將會從認識資料庫系統變數,到調整最佳化資料庫做一個完整的記錄。