介紹
LinkedIn 大量使用MySQL，公司內部 500 多個服務依賴於MySQL。 為了方便管理以及提高資源利用率，我們使用多租戶架構模式。 然而這種模式的一個主要缺點是，來自一個應用程序的查詢可能會影響到其他應用程序。
雖然我們已經通過調整 InnoDB，操作系統和 MySQL 服務器配置來優化數據庫，但我們無法控制大家的 schema 和查詢。 我們希望通過分析和優化查詢來解決這一問題。 為了做到這一點，我們拿到數據庫上所有查詢的完整信息。
為什麼我們需要查詢分析器？
為了更好地瞭解運行時應用程序動態，我們需要深入研究幾百個應用程序調用的 SQL 查詢，瞭解其性能特徵，然後進一步優化調整它們。
考慮到性能問題，我們沒有使用慢查詢日誌。 我們可以為查詢時間設置一個閾值，然後將所有跨越閾值的查詢記錄在一個文件中，用於事後進行分析。 這種方法的缺點是它無法捕獲所有的查詢。 如果將閾值設置為 0 可以捕獲所有查詢，但實際是行不通的，數百萬次查詢記錄進文件會導致海量 IO，並大大降低系統吞吐量。 所以使用慢查詢日誌是完全不行的。
我們考慮的下一個選項是 MySQL Performance Schema，可以用來在低水平監控 MySQL 服務器運行狀態（從MySQL 5.5.3開始提供）。 它提供了一種在運行時檢查服務器的內部執行情況的方法。 然而，使用此方法的主要缺點是啟用或禁用 performance_schema 需要重新啟動數據庫。您可以嘗試啟用 Performance Schema，然後關閉所有調用者，這會導致增加大約 8％ 的開銷; 如果您啟用所有的調用者，會增加大約 20-25％ 的開銷。 分析 Performance Schema 也非常複雜，為了克服這個問題，MySQL 從 MySQL 5.7.7 版本引入了sys schema。 但是為了查看歷史數據，我們仍然需要將數據從 Performance Schema 轉儲到其他服務器。
因為這兩種方法都不能滿足我們的所有需求，所以我們構建了運行在網絡層的查詢分析器，以最小化開銷並有效度量所有查詢。
查詢分析器如何工作？
查詢分析器有三個組件：
1）在數據庫服務器上運行的 agent。
2）存儲查詢信息的中心服務器。
3）中心服務器上的 UI，用於顯示 SQL 分析結果。
查詢分析器的高級體系結構
Agent 代理
Agent 代理是在 MySQL 服務器節點上運行的服務。 它使用 raw socket 捕獲 TCP 數據包，然後使用 MySQL 協議從數據包流解碼數據包並構建查詢。 然後，代理通過記錄查詢到達端口的時間和發送第一個數據包的時間（數據庫響應後）計算查詢響應時間。
查詢響應時間是第一個分組進入的時間和發送第一個響應分組的時間之差。 然後將查詢發送到 go routine，以標識查詢的指紋（我們使用 Percona GO 軟件包 [1]）。 指紋對應數據清洗後的查詢。 使用指紋哈希值作為查詢的 key。 我們可以通過其哈希值唯一地標識每個查詢。
代理將查詢的哈希值，總響應時間，計數，用戶和數據庫名稱存儲在哈希表中。 如果查詢有另一個相同哈希值，代理只需追加計數，並將查詢時間添加到總響應時間。 此外，代理還在另一個 hashmap 中維護元數據信息，其中包括查詢哈希值和指紋，最大時間，最小時間等。
代理收集一段時間查詢信息，然後將信息（查詢哈希值，sum_query_time，count 等）發送到中心主機，然後重置計數器。 元數據信息僅在其中發生變化時發送，如出現了新的查詢或查詢出現了新的最小值或最大值。 代理僅僅使用幾 MB 的內存來管理這些數據結構，用於發送查詢信息的網絡帶寬可以忽略不計。
表1：查詢指紋示例
| 詢問 | 指紋 | |
| 查詢A | SELECT * FROM table WHERE value1 ='abc' | SELECT * FROM table WHEREvalue1 ='？' |
| 查詢B | SELECT * FROM table WHEREvalue1 ='abc'AND value2 = 430 | SELECT * FROM table WHEREvalue1 ='？'AND value2 =？ |
| 查詢C | SELECT * FROM table WHEREvalue1 ='xyz'AND value2 = 123 | SELECT * FROM table WHEREvalue1 ='？'AND value2 =？ |
| 查詢D | SELECT * FROM table WHERE VALUES IN（1,2,3） | SELECT * FROM table WHERE VALUES IN（？+） |
請注意，A 和 B 的指紋不同，但 B 和 C 的指紋相同。
表2：hashmap 示例
| 查詢哈希（KEY） | 查詢時間 | 計數 | 用戶 | DB |
| 3C074D8459FDDCE3 | 6ms（1ms + 2ms + 3ms） | 3 | APP1 | DB1 |
| B414D9DF79E10545 | 9s（1s + 3s + 4s + 1s） | 4 | APP2 | DB2 |
| 791C5370A1021F19 | 12ms（5ms + 7ms） | 2 | APP3 | DB3 |
表3：元數據hashmap 示例
| 查詢哈希 | 指紋 | 第一次出現 | 最大時間的查詢 | 最小時間 | 最大時間 |
| 3C074D8459FDDCE3 | SELECT * FROM T1 WHERE a>？ | 1個月 | SELECT * FROM T1 WHERE a> 0 | 1毫秒 | 為3ms |
| B414D9DF79E10545 | SELECT * FROM T2 WHERE b =？ | 1天 | SELECT * FROM T2 WHERE b = 430 | 1秒 | 5S |
| 791C5370A1021F19 | SELECT * FROM T3 WHERE c <？ | 1小時 | SELECT * FROM T3 WHERE c <1000000 | 5ms的 | 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 進行代碼審查。
相關推薦
