MariaDB性能優(yōu)化，我終于搞清楚了！
原創(chuàng)【51CTO.com原創(chuàng)稿件】Query Profiling，即查詢分析技術(shù)，是 MySQL 數(shù)據(jù)庫提供的一種診斷 SQL 性能的方法，同時也被視為分析數(shù)據(jù)庫整體性能的有效技術(shù)。
圖片來自 Pexels
用戶可以在開啟 Profiling 的情況下，查看當(dāng)前會話中 SQL 執(zhí)行時間消耗分布，系統(tǒng)時間，CPU 用戶時間，以及過程中涉及到的關(guān)鍵函數(shù)在源代碼文件中的定位等。
由于單個大中型應(yīng)用程序可以在單位時間內(nèi)完成多個查詢，因此 Query Profiling 是數(shù)據(jù)庫優(yōu)化調(diào)整的重要組成部分，它既可作為數(shù)據(jù)庫性能優(yōu)化的積極主動措施，亦可用于診當(dāng)前斷數(shù)據(jù)庫性能是否存在問題。
在實(shí)際工作場景中，如果不采用可靠的查詢分析技術(shù)，相關(guān)技術(shù)人員往往很難定位數(shù)據(jù)庫中性能瓶頸及性能不佳問題的根源所在。
作為 MySQL 的一個分支，MariaDB Server 自帶的內(nèi)置工具中為我們提供了 Query Profiling 相關(guān)的查詢概要分析技術(shù)。
我們以 Slow Query Log(慢速查詢?nèi)罩?和 Performance Schema(性能策略模型)這兩類 MariaDB Server 內(nèi)置工具為例，深入探索查詢分析技術(shù)的價值。
MariaDB vs MySQL
首先讓我們來回顧一下 MariaDB 和 MySQL 這兩種產(chǎn)品間的親屬關(guān)系。
早在 2010 甲骨文宣布收購 Sun 公司的那天，MySQL 之父 Michael“Monty”Widenius就派生了 MySQL，進(jìn)而推出 MariaDB，從此便吸引了一大批 MySQL 開發(fā)人員為之效力。
如今 MariaDB 已經(jīng)成為了 MySQL 發(fā)展最快的一個分支，相較于 MySQL 本身，具有更豐富的功能及更優(yōu)越的性能。
MariaDB 并非孤立的一個分支，它是基于相應(yīng)的 MySQL 版本而存在的。例如，MariaDB 5.1.53 是在 MySQL 5.1.53 基礎(chǔ)上，修復(fù)了之前的 Bug，添加了存儲引擎，新功能等，性能方面也做了相應(yīng)改進(jìn)。
Slow Query Log(慢速查詢?nèi)罩?
MariaDB 和 MySQL 都有 Slow Query Log(慢速查詢?nèi)罩?這一功能。該日志中記錄了一些被認(rèn)為執(zhí)行速度非常緩慢且可能存在問題的查詢語句。
這里的“慢速”查詢定義為運(yùn)行時間比 [long_query_time] 全局系統(tǒng)變量值(默認(rèn)為 10 秒)長的查詢語句。
值得一提的是在文件記錄中允許使用“微秒”，而在表記錄中卻不行，因而這里的時間單位為“秒”。
通過全局系統(tǒng)變量配置慢查詢?nèi)罩?/span>
除了上面提到的 [long_query_time] 全局系統(tǒng)變量外，還有一些其他變量用來確定 Slow Query Log(慢查詢?nèi)罩?的行為狀態(tài)。
在默認(rèn)情況下，Slow Query Log 是禁用的，若要啟用，則需要將 [slow_query_log] 系統(tǒng)變量值設(shè)置為 1。
此外“log_output”服務(wù)器系統(tǒng)變量決定了輸出是以什么形式被寫入的，這個變量值也可以設(shè)置為禁用。在默認(rèn)情況下，日志允許被寫入文件，也可以寫入表。
[log_output] 服務(wù)器系統(tǒng)變量的有效取值為 [TABLE”，“FILE”或“NONE]。
該文件的默認(rèn)名稱為 [host_name-slow.log]，也可以使用 [–slow_query_log_file = file_name] 選項進(jìn)行設(shè)置，這里使用的表是 MySQL 系統(tǒng)數(shù)據(jù)庫中的 [slow_log] 表。
建議這些變量最好在“my.cnf”或“mariadb.cnf”配置文件中進(jìn)行設(shè)置，這類文件通常存儲在 Linux 的“/ etc / mysql /”目錄。
如果是 Windows 系統(tǒng)，那么就存儲在 Windows 系統(tǒng)目錄(通常為 C：\ Windows\System)中。
在配置文件中做如下設(shè)置：
- 啟用慢查詢?nèi)罩荆簊low_query_log = 1
- 以秒/微秒為單位設(shè)置定義慢查詢的時間：long_query_time = 5
- 提供慢速查詢?nèi)罩疚募拿Q：slow_query_log_file = /var/log/mysql/slow-query.log
- 需要記錄不使用索引的查詢語句：log_queries_not_using_indexes
以上設(shè)置在服務(wù)器重啟后生效。
查看 Slow Query Log(慢查詢?nèi)罩?
已寫入文件的慢查詢?nèi)罩究梢酝ㄟ^任何文本編輯器打開進(jìn)行查看，下面是一則慢查詢?nèi)罩镜氖纠齼?nèi)容：
通過文本編輯器來查看慢查詢?nèi)罩究此品浅７奖悖S著日志內(nèi)容(數(shù)據(jù)量)的增長，很可能存在內(nèi)容丟失的情況。
即顯示不完整，這是由于文本編輯器自身無法承載越來越大的日志容量，而造成日志中部分內(nèi)容解析缺失的風(fēng)險。
為了避免這類情況的發(fā)生，MariaDB 為我們提供了 mysqldumpslow 工具，該工具可以通過匯總信息來簡化過程，從而更可靠且有效地展示日志內(nèi)容。
“mysqldumpslow”的可執(zhí)行文件與 MariaDB 是捆綁在一起的，所以只需通過命令行將需要顯示的日志路徑傳遞給它即可。
從下面的 Demo 中可以獲悉，通過“mysqldumpslow”呈現(xiàn)的日志內(nèi)容可讀性更強(qiáng)，并且還支持分組顯示。
“mysqldumpslow”命令可以通過指定不同的參數(shù)來定制化輸出格式，如下示例中將顯示按平均查詢時間排序的前 5 個查詢：
- [ mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log ]
slow_log 一覽表
如果你對上述 log 日志中顯示的內(nèi)容不熟悉，可以結(jié)合 slow_log 表來幫助理解。
如下是日志中每個字段對應(yīng)的詳情描述：
下圖所示是針對 slow_log 表的 SELECT ALL 示例結(jié)果：
還可以通過 slow_log 表來模擬 Linux 的“ tail -100 log-slow.log”命令，列出最新查詢記錄(最后 100 個查詢)，如下圖所示：
為了方便日后頻繁調(diào)用，我們也可以專門創(chuàng)建一個存儲過程(如SHOW_LATEST_SLOW_QUERIES)，需要顯示的查詢個數(shù)可以通過輸入?yún)?shù)傳遞給這個存儲過程。
這樣一來當(dāng)我們需要列出指定數(shù)量的查詢記錄時，就不需要每次都重復(fù)鍵入相同的 SELECT 語句了。
測試 Slow Query Log(慢查詢?nèi)罩?
為了更有效地在生產(chǎn)環(huán)境中獲取我們想要的慢查詢?nèi)罩拘畔?，通常情況下，我們需要做一些設(shè)置，例如規(guī)定哪些查詢必須被寫入 Slow Query Log(慢查詢?nèi)罩?。
正如上文中提到，在啟用日志記錄后，根據(jù) log_output 變量值的設(shè)定，運(yùn)行時間比 [long_query_time] 全局系統(tǒng)變量值長的那些查詢將記錄在 Slow Query Log(慢查詢?nèi)罩?或 slow_log 表中。
除了指定 [long_query_time] 時間外，我們還可以通過 select 語句根據(jù)不同的需求指定相應(yīng)的可變時間。
這個操作需要結(jié)合 sleep() 函數(shù)使用，該函數(shù)(根據(jù)傳入的 duration 參數(shù)值 N)會暫停當(dāng)前查詢 N 秒，然后返回 0， 如果 sleep() 函數(shù)被中斷，則返回 1。
如下所示，假設(shè)尚未指定 [long_query_time] 全局系統(tǒng)變量的值，那么默認(rèn)值為 10 秒。
因此，[SELECT SLEEP(11);] 這條 select 語句會被記錄到慢日志中：
通過 Performance Schema 進(jìn)行查詢分析
我們可以通過另一種服務(wù)器性能工具 Performance Schema 來監(jiān)視服務(wù)器性能。
Performance Schema 是 MariaDB 5.5 中被引入的，以存儲引擎的方式實(shí)現(xiàn);因此，在 MariaDB 的存儲引擎列表中可以找到 Performance Schema。
圖中的“Performance Schema”的功能默認(rèn)情況下是禁用的，我們可以通過如下設(shè)置逐一開啟：
①在 my.cnf 或 my.ini 文件的 [mysqld] 部分中添加以下行：
- performance_schema = on
需要注意的是，“performance schema”無法在運(yùn)行時被激活，它必須在服務(wù)器啟動時通過配置文件進(jìn)行設(shè)置。
Performance Schema 存儲引擎包含一個名為 performance_schema 的數(shù)據(jù)庫，該數(shù)據(jù)庫又由許多表組成，可以使用常規(guī) SQL 語句查詢這些表以獲取各種性能信息。
②消費(fèi)者數(shù)據(jù)設(shè)置
為了收集數(shù)據(jù)，我們需要對收集哪些消費(fèi)者觸發(fā)的數(shù)據(jù)進(jìn)行設(shè)置，這些設(shè)置可以在服務(wù)器啟動時或在運(yùn)行時進(jìn)行。
通過以下語句在運(yùn)行時對所需數(shù)據(jù)進(jìn)行設(shè)置并檢測：
- UPDATE performance_schema.setup_consumers
- SET ENABLED = 'YES';
- UPDATE performance_schema.setup_instruments
- SET ENABLED = ‘YES’, TIMED = ‘YES’;
通過 WHERE NAME 啟用/禁用對應(yīng)的查詢語句，通過將 ENABLED 設(shè)置為“ NO”來禁用檢測。
以下將啟用配置文件中所有階段的所有檢測：
通過更新 setup_instruments 表，確保啟用了語句和階段檢測：
- UPDATE performance_schema.setup_instruments
- SET ENABLED = 'YES', TIMED = 'YES'
- WHERE NAME LIKE '%statement/%';
- UPDATE performance_schema.setup_instruments
- SET ENABLED = 'YES', TIMED = 'YES'
- WHERE NAME LIKE '%stage/%';
啟用 events_statements_ * 和 events_stages_ * 使用者：
- UPDATE performance_schema.setup_consumers
- SET ENABLED = 'YES'
- WHERE NAME LIKE '%events_statements_%';
- UPDATE performance_schema.setup_consumers
- SET ENABLED = 'YES'
- WHERE NAME LIKE '%events_stages_%';
在縮小了感興趣的范圍后，有兩種方法可以進(jìn)行監(jiān)控：
- 在摘要視圖中查看原始數(shù)據(jù)，從而全面了解實(shí)例的用法。
- 快照數(shù)據(jù)，并計算隨時間變化的增量，進(jìn)而了解事件的變化率。
下面我們以查看原始摘要數(shù)據(jù)為例：
運(yùn)行要分析的語句：
通過查詢“events_statements_history_long”這張表，來標(biāo)識語句的 EVENT_ID。此步驟類似于運(yùn)行 SHOW PROFILES 來標(biāo)識 Query_ID。
以下查詢將產(chǎn)生類似于 SHOW PROFILES 的輸出：
查詢“events_stages_history_long”這張表，來檢索語句的階段事件。階段使用事件嵌套鏈接到語句。
每個階段事件記錄都有一個 NESTING_EVENT_ID 列，其中包含父語句的 EVENT_ID。
Monyog 監(jiān)控工具
現(xiàn)在我們已經(jīng)知道 Slow Query Log(慢查詢?nèi)罩?專門用于記錄那些運(yùn)行時間過久且被認(rèn)為存在問題的查詢語句，即運(yùn)行時間超過 long_query_time 全局系統(tǒng)變量值的查詢語句。
而 Performance Schema 是一個存儲引擎，可用于在摘要視圖中查看原始數(shù)據(jù)以及隨時間推移的過程中涉及到的性能狀態(tài)。
這兩種工具都有其自身的長處。例如，Slow Query Log(慢查詢?nèi)罩?易于使用，且可以通過任何文本編輯器進(jìn)行查看。
Performance Schema 允許我們使用常規(guī)的 SQL 語句對其一系列表進(jìn)行查詢，以獲取各類性能信息。
然而，此二者都不可避免會產(chǎn)生大量的數(shù)據(jù)信息，從而導(dǎo)致冗余且加重各自數(shù)據(jù)處理的負(fù)擔(dān)。
慶幸的是，Monyog 監(jiān)控工具可以有效地為我們緩解這個問題，不僅如此，作為專業(yè)的監(jiān)控工具，它還能給我們帶來巨大的價值。
Monyog 是一款優(yōu)秀的 MySQL 監(jiān)控工具，可以實(shí)時監(jiān)測 MySQL 服務(wù)器，查看 MySQL 服務(wù)器的運(yùn)行狀態(tài)，支持查詢分析功能，還可以幫助用戶掌握服務(wù)器的運(yùn)行狀態(tài)，查看在任意時間點(diǎn)繪制的具有詳細(xì)查詢信息的圖表。
Monyog 不僅是實(shí)時監(jiān)視工具，還具有 RDS OS 和基于文件的日志監(jiān)視功能，包括在單個視圖中的常規(guī)查詢，慢速查詢和錯誤日志，還能通過 CloudWatch API 查看 RDS OS 指標(biāo)，例如 CPU 利用率，RAM 利用率等。
由于在 MariaDB 中，默認(rèn)情況下禁用慢查詢?nèi)罩?。必須?slow_query_log 全局系統(tǒng)變量設(shè)置為 1 來啟用它。
此外還有一些其他系統(tǒng)變量的應(yīng)用設(shè)置，如：
- 以秒/微秒為單位設(shè)置定義慢查詢的時間
- 是否寫入文件或表
- 用于提供慢速查詢?nèi)罩疚募拿Q
- 記錄那些不使用索引的查詢
在 Monyog 中的 Slow Query Log(慢查詢?nèi)罩?設(shè)置，可以直接通過“Server Settings dialog”對話框的“ADVANCED ”選項卡配置上述所有設(shè)置。
步驟如下：
- 點(diǎn)擊服務(wù)器圖標(biāo)
- 點(diǎn)擊服務(wù)器摘要框上的省略號
- 彈出“編輯服務(wù)器”窗口
- 點(diǎn)擊 ADVANCED
- 點(diǎn)擊 MySQL 查詢?nèi)罩卷?/strong>
MySQL 查詢?nèi)罩卷椀?ADVANCED 選項卡包含常規(guī)查詢，慢速查詢和錯誤日志的設(shè)置。
該 Server Settings dialog (服務(wù)器設(shè)置對話框)可以讓我們把 Slow Query Log(慢查詢?nèi)罩?的設(shè)置應(yīng)用到當(dāng)前服務(wù)器，或與當(dāng)前服務(wù)器擁有相同標(biāo)簽的其余服務(wù)器。
最后單擊“Save”按鈕關(guān)閉對話框，并保存 Slow Query Log(慢查詢?nèi)罩?設(shè)置。
Monyog 監(jiān)控圖表
①Dashboard Metrics
DBA 通過 Dashboard 顯示的一組圖表，就可以輕松了解所有 MySQL 服務(wù)器的安全性，可用性以及性能狀況。
Monyog 自帶默認(rèn)的 Dashboard“Performance metrics”性能指標(biāo)，DBA 也可以為一個或多個服務(wù)器指定數(shù)據(jù)庫和操作系統(tǒng)指標(biāo)，創(chuàng)建一組自己的專屬圖表。
例如查詢性能指標(biāo)中包含“Queries Executed”，“ Statements”和“Query Cache Efficiency”。
Dashboard 上顯示的所有圖表均可以 PDF/JPG/PNG 格式導(dǎo)出：
②查看 MySQL 日志詳細(xì)信息
Monyog Monitors 頁面顯示服務(wù)器參數(shù)和指標(biāo)的詳細(xì)顯示。
單擊“ MONITOR GROUP”下的“MySQL Logs”項，將會顯示被監(jiān)控下的服務(wù)器對應(yīng)的“常規(guī)查詢”，“慢速查詢”(紅色框標(biāo)注)和錯誤日志的詳細(xì)信息。
Slow Query 慢查詢信息包括：
- 慢日志——是否已啟用?(是/否)
- 最慢查詢執(zhí)行時間，以秒為單位
- 慢查詢的數(shù)
- 是否記錄那些不使用索引的查詢?(是/否)
③趨勢值圖
與原始數(shù)據(jù)相比，利用圖表顯示大量數(shù)據(jù)以及數(shù)據(jù)間不同部分的相關(guān)性，更加簡潔明了，易于理解，可讀性也更強(qiáng)。
趨勢值圖就是這樣一類圖表，用于顯示一段時間內(nèi)數(shù)據(jù)的變化趨勢。由于數(shù)據(jù)的波動，單點(diǎn)測量可能會不準(zhǔn)確，產(chǎn)生誤差。
因而，隨著時間推移來呈現(xiàn)數(shù)據(jù)的趨勢走向，可以使我們更有效地獲取實(shí)際性能，有針對性地基于已建立的目標(biāo)監(jiān)控實(shí)際性能狀況。
下圖是某主服務(wù)器趨勢圖示例：
上圖中“SERVERS”圖例列出了 SQL 日志中的所有服務(wù)器。每個服務(wù)器圖例都有各自的顏色，以便在圖表中能輕松識別。
由于當(dāng)前圖中只顯示了主服務(wù)器趨勢數(shù)據(jù)，其余對應(yīng)服務(wù)器的趨勢值均未出現(xiàn)在圖中，所以呈灰色。通過單擊服務(wù)器圖例，可以隨意切換需要顯示的服務(wù)器數(shù)據(jù)趨勢。
④顯示特定時間范圍內(nèi)的趨勢值
在上面的趨勢圖中，僅僅顯示了時間段內(nèi)某服務(wù)器上所有的趨勢數(shù)據(jù)。
在 Monyog Professional，Enterprise 和 Ultimate 版本中，我們還可以通過 TIMEFRAME 下拉列表中的“History”選項來指定特定時間的范圍。
可選擇的時間范圍包含多個時間間隔，例如“Today”，“Yesterday”和“Last 2 Days” ，也可以自定義范圍，設(shè)置開始和結(jié)束字段;單擊任何一個自定義范圍字段都會顯示日歷，用于選擇確切的日期時間。
下圖所示基于特定時間范圍內(nèi)的各服務(wù)器慢查詢數(shù)量趨勢值：
⑤查詢分析器
在“查詢分析器”選項卡中，選擇所需的 MySQL 服務(wù)器以及要分析的日志類型(包括慢查詢?nèi)罩?，單擊分析按鈕開始分析。
幾秒鐘后，將顯示如下分析結(jié)果，頁面上半部分包含基于總時間的“熱門查詢”，而下半部分顯示了使用結(jié)果分頁的所有查詢：
基于總時間的“熱門查詢”部分顯示排名靠前的查詢，以便最慢的查詢可以在頂部顯示。
包括：
- 查詢語句。
- COUNT：該語句在日志中出現(xiàn)多少次。
- 總時間：執(zhí)行查詢所需的時間，格式為 hh：mm：ss：ms。
- 平均延遲：平均查詢執(zhí)行時間，格式為 hh：mm：ss：ms。
- USER @ HOST：執(zhí)行查詢的用戶及主機(jī)。
每條語句在查詢數(shù)據(jù)最上方以條形圖的形式顯示，因此每個查詢都對應(yīng)唯一的顏色。
每個查詢的總執(zhí)行時間按照從左到右的方式顯示，最慢的顯示在最左邊。條形圖有助于快速評估每個慢查詢語句間的對比。
在上圖中，我們可以看到最慢的查詢比所有其他慢查詢時間的總和慢了好幾個數(shù)量級。
單擊某一行將顯示對應(yīng)慢查詢的詳細(xì)信息，例如查詢首次和最后一次執(zhí)行的時間點(diǎn)，查詢所花費(fèi)的最大時間：
⑥Query 查詢面板中的過濾設(shè)置
“查詢”部分為我們提供了更完整的已分析查詢列表，除了通過分頁導(dǎo)航遍歷所有查詢外，還可以自定義過濾條件，從而將顯示列表的內(nèi)容縮小到我們感興趣的范圍。
過濾條件共有以下四種選項：
- Containing：包含。
- Not containing：不包含。
- Matching regex：匹配的正則表達(dá)式。
- Not matching regex：不匹配的正則表達(dá)式。
例如，將結(jié)果限制為匹配正則表達(dá)式“ sakila *”語句的過濾器：
通過單擊標(biāo)題，按任意列進(jìn)行排序，箭頭顯示排序順序(即升序，降序)：
通過單擊標(biāo)題，按任意列進(jìn)行排序，箭頭顯示排序順序(即升序，降序)：
⑦以 CSV 形式導(dǎo)出
單擊 Query 面板上的“Export as CSV”可以將查詢數(shù)據(jù)保存到“.csv”文件中：
保存后的 CSV 文件可以通過 EXCEL 打開預(yù)覽：
總結(jié)
查詢分析是用于分析數(shù)據(jù)庫整體性能的有效技術(shù)，文中該技術(shù)采用了 MariaDB 服務(wù)器內(nèi)置工具：Slow Query Log 慢查詢?nèi)罩竞?Performance Schema 性能模式。
慢查詢?nèi)罩就ㄟ^設(shè)置 long_query_time 全局系統(tǒng)變量值，來跟蹤記錄運(yùn)行超時且存在問題的查詢語句。
性能模式則是一個存儲引擎，其中 Performance_schema 數(shù)據(jù)庫，又由多個表組成，我們可以使用常規(guī) SQL 語句查詢這些表以獲取更廣泛的性能信息。
然而以上這兩種工具都會產(chǎn)生大量的數(shù)據(jù)，引起繁瑣的工作，Monyog 的引入為我們很好地解決此類問題，利用 Monyog 來監(jiān)視 MariaDB 慢查詢?nèi)罩竞托阅苣Ｊ绞亲钣行У姆椒ㄖ弧?/p>
作者：羅小羅
簡介：英國 TOP10 計算機(jī)專業(yè)，計算機(jī)科學(xué)與技術(shù)碩士，先后就職于匯豐，JPMorgan，HP，交行，阿里等國內(nèi)外知名企業(yè)。涉及項目領(lǐng)域主要有：互聯(lián)網(wǎng)金融，電商，教育，醫(yī)療等。現(xiàn)任就職于某世界 500 強(qiáng)公司，擔(dān)任測試開發(fā)團(tuán)隊負(fù)責(zé)人，帶領(lǐng)團(tuán)隊構(gòu)建并持續(xù)優(yōu)化自動化測試框架，研發(fā)自動化測試輔助類工具;擅長領(lǐng)域：單元/接口/性能/安全/自動化測試/CD/CI/DevOps;個人持續(xù)研究領(lǐng)域：自動化測試模型/數(shù)據(jù)分析/算法/機(jī)器學(xué)習(xí)等。
編輯：陶家龍
征稿：有投稿、尋求報道意向技術(shù)人請聯(lián)絡(luò)小編微信：gordonlonglong
【51CTO原創(chuàng)稿件，合作站點(diǎn)轉(zhuǎn)載請注明原文作者和出處為51CTO.com】
