MySQL資料庫簡易效能調教 作者:游子興 / 臺灣大學計算機及資訊網路中心網路組幹事 MySQL 資料庫是個使用廣泛的 Open Source 資料庫,本文以實際的範例搭配淺顯的說明與實作,一步一步進行效能之調教,而使大部分非科班出生之 MySQL 資料庫管理人員得以輕鬆應付與日遽增大量的資料。 前言 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 調教
本文將在CentOS release 5.5 (Final) + MySQL 5.0.77 平台上,針對上述兩種方法進行實作與說明。 一. 使用 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 |