作者：游子興 / 臺灣大學計算機及資訊網路中心網路組幹事
MySQL 資料庫是個使用廣泛的 Open Source 資料庫，本文以實際的範例搭配淺顯的說明與實作，一步一步進行效能之調教，而使大部分非科班出生之 MySQL 資料庫管理人員得以輕鬆應付與日遽增大量的資料。
前言
MySQL 是一個Open Source的中小型關連式資料庫，因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package，因此許多學術單位與中小型企業十分愛用。但隨著時間增加，資料量也持續增加，如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA，則成為一個棘手的問題。 本文將介紹兩種方法來改善資料庫之
MySQL
是一個Open Source的中小型關連式資料庫，因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL
Package，因此許多學術單位與中小型企業十分愛用。但隨著時間增加，資料量也持續增加，如何作效能之調教與改善 (Performance
Tuning) 對於可能非科班出生之資料庫管理員 DBA，則成為一個棘手的問題。 MySQL 是一個Open Source的中小型關連式資料庫，因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package，因此許多學術單位與中小型企業十分愛用。但隨著時間增加，資料量也持續增加，如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA，則成為一個棘手的問題。 本文將介紹兩種方法來改善資料庫之
本文將介紹兩種方法來改善資料庫之效能：
- 使用 MySQLTuner 進行 MySQL 之整體參數改善分析
- 使用 Explain Plan 進行個別 SQL 調教
一. 使用 MySQLTuner 進行 MySQL 之整體參數改善分析
官方網站: http://mysqltuner.pl/mysqltuner.pl
目前的版本Version 1.2.0，支援 MySQL 資料庫版本：
MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
MySQL 5.4 (not fully tested, partially supported)
MySQL 6.0 (partial support)
此程式主要的功能在於蒐集運行中的MySQL 之 STATUS 與 Variable 相關變數來判斷並提供改善建議，程式本身使用 Perl Script Language 撰寫，因此不需安裝，僅需下載執行即可。
Step1. 指令: wget http://mysqltuner.pl/mysqltuner.pl
登入 Linux 後下載 MySQLTuner 之執行檔案 mysqltuner.pl
圖1. 下載 MySQLTuner 之執行檔案 mysqltuner.pl
Step2. 指令: chmod +x mysqltuner.pl
因下載檔案預設無 Execute 執行權限，使用 chmod +x 增加 Execute 權限。
圖2. 使用 chmod +x 增加 Execute 權限
Step3. 指令: ./mysqltuner.pl
不需安裝，直接執行程式即可進行測試。程式會先要求輸入 MySQL 具有管理權限之帳號密碼，在此輸入 root 及其密碼。
圖3. 執行 mysqltuner.pl 並輸入帳號密碼
Step4. 分析執行結果
完全不需設定與安裝就可產生分析結果，是不是非常簡單？
圖4. mysqltuner.pl 執行結果報告
接著我們將逐步分析執行的結果，各項目分成綠色[OK]與紅色[!!]來顯示，我們僅需針對 紅色[!!] 的結果來做改善與調教：
(1)[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
因為32bit 之定址能力最高只到2^32= 4,294,967,296 (4G)，若該 Server設備已安裝超過4G 之實體記憶體，則建議使用 64bit 之作業系統才能使用所有的記憶體。
(2)[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
上述兩行的意思是 InnoDB 與 BDB 狀態為啟用，但並未使用，因此可搭配報告中最後的建議：
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Add skip-bdb to MySQL configuration to disable BDB
增加兩行設定到 MySQL 之設定檔 ( 以CentOS 5.5 為例，預設檔案為: /etc/my.cnf)
skip-innodb
skip-bdb
圖5. 編輯設定檔 /etc/my.cnf
之後需重新啟動 MySQL
~# service mysqld restart
(3)[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.
上述兩行是指資料庫中有兩個帳號沒有設定密碼，可能有 Security Issue，經檢查Table: mysql.user 後發現，此兩個帳號預設為MySQL 安裝時所建立，並無 User 名稱也無設定權限，如下圖，因此可忽略此訊息。
圖6. 觀察 Table: mysql.user 所設定之帳號密碼
(4)[!!] Key buffer size / total MyISAM indexes: 8.0M/1.1G
此處點出了一個很重要的問題，就是 Key buffer size 太小(僅有 8.0M)，參考報告中最後的建議：
-------- Recommendations -----------------------------------------------------
Variables to adjust:
key_buffer_size (> 1.1G)
建議將 key_buffer_size 由 8.0 M 調整至 1.1G，原因在於 Key buffer size 的作用在於 Cache Table中的 indexes(索引)，目前的索引佔據的空間已有 1.1G ( /var/lib/mysql/ 目錄中索引檔案 *.MYI 之檔案大小加總)，因此建議至少調高與目前之 indexes 大小相符。設定方法是增加一行設定到設定檔: /etc/my.cnf
key_buffer_size= 1100000000
設定後一樣需重新啟動 MySQL。
(5)[!!] Query cache is disabled
Query cache的作用在於 Cache 查詢(Query) 之結果以供後續相同的查詢使用，由變數query_cache_size 所控制，預設值為0，因此預設為 disabled，參考報告中最後的建議：
-------- Recommendations -----------------------------------------------------
Variables to adjust:
query_cache_size (>= 8M)
增加一行設定到 /etc/my.cnf
query_cache_size = 8000000
設定後一樣需重新啟動 MySQL。
(6)[!!] Thread cache is disabled
Thread cache的作用在於每次建立新的連線 (Thread) 時，會先看Thread cache 中是否有可用的 Thread，若有則直接取用，若無才重新建立新的連線，如此可減少CPU Loading。若系統常有大量且短暫的連線發生，則適當的設定此參數非常重要。由變數thread_cache_size 所控制，預設值為0，因此預設為 disabled，參考報告中最後的建議：
-------- Recommendations -----------------------------------------------------
Variables to adjust:
thread_cache_size (start at 4)
增加一行設定到 /etc/my.cnf
thread_cache_size=4
設定後一樣需重新啟動 MySQL，之後可用指令:
SHOW STATUS like 'Threads_%';
觀察變數 Threads_created 若已不會再持續增加，表示thread_cache_size已經足夠。
圖7. 觀察 STATUS 中 Threads 開頭之變數
(7)[!!] Connections aborted: 14%
此訊息搭配報告中最後的建議：
-------- Recommendations -----------------------------------------------------
General recommendations:
Your applications are not closing MySQL connections properly
表示時常有連上 MySQL 之連線不正常斷線，一般正常的斷線程式需呼叫mysql_close() 來正常關閉，可搭配指令：
SHOW STATUS like 'Aborted_%';
圖8. 觀察 STATUS 中 Aborted 開頭之變數
可觀察變數Aborted_clients, Aborted_connects 是否已經不再持續增加，來判斷是否已經改善。
(8)General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
報告中最後還有兩個建議：
1.因為MySQLTuner 是利用 MySQL 系統中之 STATUS 與 Variable 相關變數來判斷並提供建議，因此必須有足夠的執行時間來蒐集資料庫運作的效能，因此若資料庫運作時間不到 24小時，則會出現警告訊息。
2.系統建議可開啟slow query log 之功能來記錄執行特別久的 SQL，以供後續的分析，方法為增加兩行設定到 /etc/my.cnf
log_slow_queries= mysqld-slow.log
long_query_time= 10
設定後一樣需重新啟動 MySQL。
log_slow_queries 指定 slow query log 存放之檔名，預設存在 /var/lib/mysql/ 目錄下。
long_query_time 設定超過幾秒之 SQL Query 會被記錄在 log_slow_queries 之log 檔中，系統預設值為 10秒。
二. 使用 Explain Plan 進行個別 SQL 調教
當資料庫的效能不符需求時，大部分發生在特定的 SQL 語句拖慢整個系統效能，蒐集這些 SQL 的方法本文提供兩種，一種就是用先前介紹的slow query log 方法記錄執行超過特定時間的 SQL，另一種方法是即時觀察系統目前之 Performance。
Step1. 指令: top
圖9. 使用 top 指令觀察系統資源使用情形
觀察CPU Usage%排名第一就是 mysqld，而且佔用了 99.9% 的 CPU Resource。
Step2. login MySQL，使用指令：
SHOW FULL PROCESSLIST;
圖10. 使用 SHOW FULL PROCESSLIST指令之執行結果
可看到目前正在執行之所有 SQL 語句，特別注意其中Process Id 8,Command 顯示 Query 表示正在執行，Time 欄位顯示已經執行了 117秒，Info 中有正在執行的 SQL 語句。
在得到了 SQL 語句之後，接著使用 Explain 觀察是否有可以改善之處.
圖11. 使用 Explain指令之執行結果
簡單說明各欄位之意義：
(1)select_type: SELECT 使用之語法型態。SIMPLE表示為簡單的查詢語句，也就是沒有 UNION、Subquery 等語法在此查詢中。
(2)table: 此 SQL 查詢所用到的 table，此案例因為使用了 Join因此用到了兩個 table。
(3)type: 使用何種類型進行查詢。最優至最差的類型為:
system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> ALL
此案例值為”ALL”，也就是最差之狀況。
(4)possible_keys：可能使用的索引欄位。若為NULL 表示無索引可使用。
(5)key：實際使用的索引欄位。若為NULL 表示無索引可使用。
(6)key_len：使用的索引長度。
(7)rows: SQL 必須搜尋的資料筆數。
(8)Extra：
Using where: SQL 中有使用WHERE 語句來限制查詢的範圍。
Using temporary : MySQL 必須建立一個臨時的 Table 來儲存查詢結果，通常發生在 SQL 語法中有 Order By 或 GROUP BY。
Using filesort：MySQL必須進行額外的步驟來進行 ORDER BY 之排序作業。
此段 SQL 執行之狀況，也可參考前面第一部分之介紹啟用slow query log，log 記錄檔中顯示：
/var/lib/mysql/mysqld-slow.log
圖12. slow query log 記錄之內容
此 SQL 共花了 121 秒才執行完成。
由上述的Explain 分析可知，此段 SQL 並未使用任何的索引，我們再次分析此段 SQL 中 JOIN 與 WHERE 條件中所出現的欄位：
LEFT JOIN … ON aruba.ArubaLocationID = arubalocationmap.ArubaLocationID
WHERE (YEAR(`AcctStartTime`) > '2012'…..
AND aruba.`ArubaLocationID` LIKE '113.%' ….
其中特別注意的是 JOIN 語法中兩個 Table 的對應欄位都必須加上索引才有作用，因此我們決定針對
Table: aruba 增加 ArubaLocationID, AcctStartTime 兩個欄位的索引
Table: arubalocationmap增加 ArubaLocationID 一個欄位的索引
建立索引的語法如下：
CREATE Index ArubaLocationID On aruba (ArubaLocationID);
CREATE Index AcctStartTime On aruba (AcctStartTime);
CREATE Index ArubaLocationID On arubalocationmap (ArubaLocationID);
索引建立後，我們再次使用 Explain 觀察此段 SQL 執行之情況:
圖13. 使用 Explain指令之執行結果
由 type, key 欄位發現新建的索引已經發揮作用。
再次檢查 slow query log，log 記錄檔中顯示：
/var/lib/mysql/mysqld-slow.log
圖14. slow query log 記錄之內容
執行時間由 121 秒減少為 13秒，僅需原來 1/10的時間，由此可知建立正確的索引將有非常大幅度之改善。
結論
MySQL 是個功能強大的資料庫，尤其搭配 PHP 程式後可做出各式的動態網頁資料庫進行各類的應用，本文拋磚引玉使用簡單的範例來調整並改善資料庫的效能，投入的時間與改善並不需要太久，但卻能有十倍之 執行效能改善，這樣的改善效益是非常令人期待的。
參考資料
1. MySQLTuner http://mysqltuner.pl/
2. Explain plan http://dev.mysql.com/doc/refman/5.0/en/explain-output.html 3.query cache size (query_cache_size) http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
沒有留言:
張貼留言