LinkedIn 大量使用MySQL， 公司內部 500 多個服務依賴於MySQL。 為了方便管理以及提高資源利用率， 我們使用多租戶架構模式。 然而這種模式的一個主要缺點是， 來自一個應用程式的查詢可能會影響到其他應用程式。
雖然我們已經通過調整 InnoDB， 作業系統和 MySQL 伺服器配置來優化資料庫， 但我們無法控制大家的 schema 和查詢。 我們希望通過分析和優化查詢來解決這一問題。 為了做到這一點， 我們拿到資料庫上所有查詢的完整資訊。
為什麼我們需要查詢分析器？為了更好地瞭解運行時應用程式動態， 我們需要深入研究幾百個應用程式調用的 SQL 查詢，
考慮到性能問題， 我們沒有使用慢查詢日誌。 我們可以為查詢時間設置一個閾值， 然後將所有跨越閾值的查詢記錄在一個檔中， 用於事後進行分析。 這種方法的缺點是它無法捕獲所有的查詢。 如果將閾值設置為 0 可以捕獲所有查詢， 但實際是行不通的， 數百萬次查詢記錄進檔會導致海量 IO， 並大大降低系統輸送量。 所以使用慢查詢日誌是完全不行的。
我們考慮的下一個選項是 MySQL Performance Schema， 可以用來在低水準監控 MySQL 伺服器運行狀態（從MySQL 5.5.3開始提供）。 它提供了一種在運行時檢查伺服器的內部執行情況的方法。 然而， 使用此方法的主要缺點是啟用或禁用 performance_schema 需要重新開機資料庫。
因為這兩種方法都不能滿足我們的所有需求， 所以我們構建了運行在網路層的查詢分析器， 以最小化開銷並有效度量所有查詢。
查詢分析器如何工作？查詢分析器有三個組件：
1）在資料庫伺服器上運行的 agent。
2）存儲查詢資訊的中心伺服器。
3）中心伺服器上的 UI， 用於顯示 SQL 分析結果。
查詢分析器的高級體系結構
Agent 代理
Agent 代理是在 MySQL 伺服器節點上運行的服務。 它使用 raw socket 捕獲 TCP 資料包， 然後使用 MySQL 協定從資料包流解碼資料包並構建查詢。 然後， 代理通過記錄查詢到達埠的時間和發送第一個資料包的時間（資料庫回應後）計算查詢回應時間。
查詢回應時間是第一個分組進入的時間和發送第一個回應分組的時間之差。 然後將查詢發送到 go routine，
代理將查詢的雜湊值， 總回應時間， 計數， 使用者和資料庫名稱存儲在雜湊表中。 如果查詢有另一個相同雜湊值， 代理只需追加計數， 並將查詢時間添加到總回應時間。 此外， 代理還在另一個 hashmap 中維護中繼資料資訊， 其中包括查詢雜湊值和指紋， 最大時間， 最小時間等。
代理收集一段時間查詢資訊， 然後將資訊（查詢雜湊值， sum_query_time， count 等）發送到中心主機， 然後重置計數器。 中繼資料資訊僅在其中發生變化時發送， 如出現了新的查詢或查詢出現了新的最小值或最大值。 代理僅僅使用幾 MB 的記憶體來管理這些資料結構，
表1：查詢指紋示例
詢問指紋查詢ASELECT * FROM table WHERE value1 ='abc'SELECT * FROM table WHEREvalue1 ='？'查詢BSELECT * FROM table WHEREvalue1 ='abc'AND value2 = 430SELECT * FROM table WHEREvalue1 ='？'AND value2 =？查詢CSELECT * FROM table WHEREvalue1 ='xyz'AND value2 = 123SELECT * FROM table WHEREvalue1 ='？'AND value2 =？查詢DSELECT * FROM table WHERE VALUES IN（1,2,3）SELECT * FROM table WHERE VALUES IN（？+）請注意， A 和 B 的指紋不同， 但 B 和 C 的指紋相同。
表2：hashmap 示例
查詢雜湊（KEY）查詢時間計數用戶DB3C074D8459FDDCE36ms（1ms + 2ms + 3ms）3APP1DB1B414D9DF79E105459s（1s + 3s + 4s + 1s）4APP2DB2791C5370A1021F1912ms（5ms + 7ms）2APP3DB3表3：中繼資料hashmap 示例
查詢雜湊指紋第一次出現最大時間的查詢最小時間最大時間3C074D8459FDDCE3SELECT * FROM T1 WHERE a>？1個月SELECT * FROM T1 WHERE a> 01毫秒為3msB414D9DF79E10545SELECT * FROM T2 WHERE b =？1天SELECT * FROM T2 WHERE b = 4301秒5S791C5370A1021F19SELECT * FROM T3 WHERE c <？1小時SELECT * FROM T3 WHERE c <10000005ms的7毫秒UI
用於顯示分析的 UI 運行在中心伺服器上。 用戶可以選擇要查看查詢的主機名稱和時間範圍， 以顯示在該時間內運行的每個查詢的統計資訊， 您可以按一下任意查詢查看查詢趨勢圖。
有趣的方面是查詢負載百分比， 這是查詢在此期間在伺服器上運行的查詢總數導致的負載。 例如， 假設有3個查詢。
查詢 #1 每次花費 2 秒鐘， 執行 100 次。 它造成的負載為 2 * 100 = 200。
查詢 #2 每次花費 0.1 毫秒，執行 10M 次。 其造成的負載為 0.0001 * 10,000,000 = 1000。
查詢 #3 每次花費 10 毫秒，執行了 1M 次。 它造成的負載為 0.01 * 1,000,000 = 10000。
因此，在該間隔期間伺服器上的總負載為 200 + 1000 + 10000 = 11200。 每個查詢的負載百分比如下。
查詢＃1 為 200/11200 * 100 = 1.78％
查詢＃2 為 1000/11200 * 100 = 8.93％
查詢＃3 為 10000/11200 * 100 = 89.29％
請注意，用戶應該查看的查詢是 Query＃3，因為它導致了 89.29% 的負載，即使它每次執行只需要 10 毫秒。
UI 如下圖所示。 出於安全考慮，主機名稱和表名被遮罩。
查詢分析器UI顯示所有不同的查詢
按一下任意查詢可以顯示查詢的趨勢和更多資訊。
圖表顯示查詢趨勢
性能為了顯示對輸送量（每秒事務）的影響，我們在使用 Intel（R）Xeon（R）CPU E5-2620 0 @ 2.00GHz - 12 核心 CPU 的機器上運行 MySQL 5.6.29-76.2-log Percona 伺服器（GPL）。
運行基準測試，之後不斷增加 sysbench 執行緒並測量其性能。 我們發現，在達到 128 個併發執行緒之前，查詢分析器不會影響輸送量。 對於 256 個執行緒的情況，我們觀察到每秒事務下降了 5％，但這仍然優於 Perfomance Schema（輸送量下降了 10％）。
在我們的測試中，查詢分析器佔用不到 1％ 的 CPU，而當超過 128 個執行緒運行時，這個峰值上升到 5％，這個數量仍然可以忽略不計。 請注意，執行緒數意味著 MySQL 內的併發查詢數，其中不包括休眠連接。
使用各種工具對生產量進行基準測試
使用各種工具對CPU利用率進行基準測試
指標收集對於查詢分析器的原始版本，我們使用 MySQL 來存儲資料（基本上是時間序列資料）。 有兩個表：query_history 和 query_info。
query_history 是從查詢 hashmap 保存資訊的位置。 該表具有以下列：hostname, checksum, timestamp, count, query time, user, and db。 主鍵是（hostname, checksum, timestamp），按 timestamp 進行範圍分區，通過 hostname 上的鍵做子分區。 在（hostname, timestamp, querytime, count）和 checksum 上有索引。
query_info 表用於保存有關查詢中繼資料的資訊。 它具有以下列：hostname, checksum, fingerprint, sample, first_seen, mintime, mintime_at, maxtime, maxtime_at, is_reviewed, reviewed_by, reviewed_on, comments。 （hostname, checksum）作為主鍵並且 checksum 上有索引。
到目前為止，我們還沒有遇到任何問題。 偶爾繪製長時間範圍的查詢趨勢圖時，會有一些延遲。 為了克服這個問題，我們計畫將 MySQL 中的資料存儲到內部監控工具（稱為 inGraphs[2]） 。
安全代理需要在 sudo 下運行。 為了減輕潛在的安全問題，您可以給代理提供高級許可權 “cap_net_raw”。 此外，通過將執行許可權設置為特定用戶（chmod 100 或 500），您可以在特定使用者下運行代理而不用 sudo。 有關詳細資訊，請參閱 https://linux.die.net/man/7/capabilities 。
概要查詢分析器的優點很多。 可以讓我們的資料庫工程師一目了然地識別有問題的查詢，以便工程師環比對比每週查詢，並快速高效地排除資料庫減速。 開發人員和業務分析師能夠視覺化查詢趨勢，在進入開發之前檢查分段環境中的查詢負載，並為每個表和資料庫獲取指標，例如插入數量，更新，刪除的數量，通過它們可以分析業務。 從安全的角度來看，查詢分析器允許我們在新查詢訪問資料庫時收到警報，我們還可以審核正在訪問敏感資訊的查詢。最後，分析查詢負載使我們能夠確保查詢在伺服器間均勻分配，從而優化我們的硬體。 我們也可以更準確地進行性能規劃。
雖然還沒有定義時間表，但我們計畫最終會開源查詢分析器，並希望它對所有其他人都有用。
致謝感謝 LinkedIn MySQL 團隊： Basavaiah Thambara 和 Alex Lurthu 進行設計審查， Kishore Govindaluri 開發 UI，以及 Naresh Kumar Vudutha 進行代碼審查。
執行 100 次。 它造成的負載為 2 * 100 = 200。查詢 #2 每次花費 0.1 毫秒，執行 10M 次。 其造成的負載為 0.0001 * 10,000,000 = 1000。
查詢 #3 每次花費 10 毫秒，執行了 1M 次。 它造成的負載為 0.01 * 1,000,000 = 10000。
因此，在該間隔期間伺服器上的總負載為 200 + 1000 + 10000 = 11200。 每個查詢的負載百分比如下。
查詢＃1 為 200/11200 * 100 = 1.78％
查詢＃2 為 1000/11200 * 100 = 8.93％
查詢＃3 為 10000/11200 * 100 = 89.29％
請注意，用戶應該查看的查詢是 Query＃3，因為它導致了 89.29% 的負載，即使它每次執行只需要 10 毫秒。
UI 如下圖所示。 出於安全考慮，主機名稱和表名被遮罩。
查詢分析器UI顯示所有不同的查詢
按一下任意查詢可以顯示查詢的趨勢和更多資訊。
圖表顯示查詢趨勢
性能為了顯示對輸送量（每秒事務）的影響，我們在使用 Intel（R）Xeon（R）CPU E5-2620 0 @ 2.00GHz - 12 核心 CPU 的機器上運行 MySQL 5.6.29-76.2-log Percona 伺服器（GPL）。
運行基準測試，之後不斷增加 sysbench 執行緒並測量其性能。 我們發現，在達到 128 個併發執行緒之前，查詢分析器不會影響輸送量。 對於 256 個執行緒的情況，我們觀察到每秒事務下降了 5％，但這仍然優於 Perfomance Schema（輸送量下降了 10％）。
在我們的測試中，查詢分析器佔用不到 1％ 的 CPU，而當超過 128 個執行緒運行時，這個峰值上升到 5％，這個數量仍然可以忽略不計。 請注意，執行緒數意味著 MySQL 內的併發查詢數，其中不包括休眠連接。
使用各種工具對生產量進行基準測試
使用各種工具對CPU利用率進行基準測試
指標收集對於查詢分析器的原始版本，我們使用 MySQL 來存儲資料（基本上是時間序列資料）。 有兩個表：query_history 和 query_info。
query_history 是從查詢 hashmap 保存資訊的位置。 該表具有以下列：hostname, checksum, timestamp, count, query time, user, and db。 主鍵是（hostname, checksum, timestamp），按 timestamp 進行範圍分區，通過 hostname 上的鍵做子分區。 在（hostname, timestamp, querytime, count）和 checksum 上有索引。
query_info 表用於保存有關查詢中繼資料的資訊。 它具有以下列：hostname, checksum, fingerprint, sample, first_seen, mintime, mintime_at, maxtime, maxtime_at, is_reviewed, reviewed_by, reviewed_on, comments。 （hostname, checksum）作為主鍵並且 checksum 上有索引。
到目前為止，我們還沒有遇到任何問題。 偶爾繪製長時間範圍的查詢趨勢圖時，會有一些延遲。 為了克服這個問題，我們計畫將 MySQL 中的資料存儲到內部監控工具（稱為 inGraphs[2]） 。
安全代理需要在 sudo 下運行。 為了減輕潛在的安全問題，您可以給代理提供高級許可權 “cap_net_raw”。 此外，通過將執行許可權設置為特定用戶（chmod 100 或 500），您可以在特定使用者下運行代理而不用 sudo。 有關詳細資訊，請參閱 https://linux.die.net/man/7/capabilities 。
概要查詢分析器的優點很多。 可以讓我們的資料庫工程師一目了然地識別有問題的查詢，以便工程師環比對比每週查詢，並快速高效地排除資料庫減速。 開發人員和業務分析師能夠視覺化查詢趨勢，在進入開發之前檢查分段環境中的查詢負載，並為每個表和資料庫獲取指標，例如插入數量，更新，刪除的數量，通過它們可以分析業務。 從安全的角度來看，查詢分析器允許我們在新查詢訪問資料庫時收到警報，我們還可以審核正在訪問敏感資訊的查詢。最後，分析查詢負載使我們能夠確保查詢在伺服器間均勻分配，從而優化我們的硬體。 我們也可以更準確地進行性能規劃。
雖然還沒有定義時間表，但我們計畫最終會開源查詢分析器，並希望它對所有其他人都有用。
致謝感謝 LinkedIn MySQL 團隊： Basavaiah Thambara 和 Alex Lurthu 進行設計審查， Kishore Govindaluri 開發 UI，以及 Naresh Kumar Vudutha 進行代碼審查。