跳到主要內容區塊

ntuccepaper2019

技術論壇

MySQL資料庫簡易效能調教
  • 卷期:v0025
  • 出版日期:2013-06-20

作者:游子興 / 臺灣大學計算機及資訊網路中心網路組幹事


MySQL 資料庫是個使用廣泛的 Open Source 資料庫,本文以實際的範例搭配淺顯的說明與實作,一步一步進行效能之調教,而使大部分非科班出生之 MySQL 資料庫管理人員得以輕鬆應付與日遽增大量的資料。

 

前言

MySQL 是一個Open Source的中小型關連式資料庫,因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package,因此許多學術單位與中小型企業十分愛用。但隨著時間增加,資料量也持續增加,如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA,則成為一個棘手的問題。 本文將介紹兩種方法來改善資料庫之

 

MySQL 是一個Open Source的中小型關連式資料庫,因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package,因此許多學術單位與中小型企業十分愛用。但隨著時間增加,資料量也持續增加,如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA,則成為一個棘手的問題。
本文將介紹兩種方法來改善資料庫之效能:

  1. 使用 MySQLTuner 進行 MySQL 之整體參數改善分析
  2. 使用 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