主持人 > 博客 > 管理 > 使用建構者的 B2B 服務範例最佳化資料庫查詢

使用建構者的 B2B 服務範例最佳化資料庫查詢

如何在不遷移到生產力更高的伺服器的情況下將資料庫查詢數量增加 10 倍並保持系統功能？我將告訴你我們如何應對資料庫效能下降的情況，如何優化 SQL 查詢以服務盡可能多的使用者而不增加計算資源的成本。

我提供管理建築公司業務流程的服務。大約有 3 家公司與我們合作。每天有超過 10 人使用我們的系統工作 4-10 小時。它解決了規劃、通知、警告、驗證的各種問題...我們使用PostgreSQL 9.6。我們的資料庫中有大約 300 個表，每天會收到多達 200 億個查詢（10 萬個不同的查詢）。平均每秒有 3-4 個請求，在最活躍的時刻每秒有超過 10 個請求。大多數查詢都是 OLAP。增刪改查的次數少很多，OLTP負載相對較輕。我提供了所有這些數字，以便您可以評估我們專案的規模並了解我們的經驗對您有多大用處。

圖一。抒情的

當我們開始開發時，我們並沒有真正考慮資料庫會承受什麼樣的負載，以及如果伺服器停止拉取我們會做什麼。在設計資料庫時，我們遵循一般建議，盡量不要搬起石頭砸自己的腳，但超出了一般建議，例如“不要使用模式” 實體屬性值 我們沒有進去。我們基於規範化的原則進行設計，避免資料冗餘並且不關心加快某些查詢的速度。第一批用戶到達後，我們就遇到了效能問題。像往常一樣，我們對此完全沒有準備。事實證明，第一個問題很簡單。通常，所有問題都可以透過新增索引來解決。但曾經有一段時間，簡單的補丁不再起作用。意識到我們缺乏經驗，並且越來越難以理解導致問題的原因，我們聘請了專家來幫助我們正確設定伺服器、連接監控，並告訴我們從哪裡可以找到 統計數據.

圖二。統計

因此，我們每天在資料庫上執行約 10 個不同的查詢。在這10萬個中，有執行2-3百萬次、平均執行時間為0.1-0.3毫秒的怪物，也有平均執行時間為30秒、每天被調用100次的查詢。

優化所有 10 個查詢是不可能的，因此我們決定弄清楚我們的努力方向，以便正確提高資料庫的效能。經過幾次迭代，我們開始對請求進行類型分割。

最高請求數

這些是佔用最多時間（總時間）的最重查詢。這些查詢要么經常調用，要么需要很長時間才能執行（長而頻繁的查詢在速度鬥爭的第一次迭代中進行了優化）。因此，伺服器在執行上花費的時間最多。此外，重要的是按總執行時間和 IO 時間分開頂級請求。優化此類查詢的方法略有不同。

所有公司的慣常做法都是與 TOP 請求合作。它們很少；即使優化一個查詢也可以釋放 5-10% 的資源。然而，隨著專案的成熟，優化 TOP 查詢變得越來越重要。所有簡單的方法都已經想出來了，最「重」的請求「只」佔用3-5%的資源。如果 TOP 查詢總共花費的時間少於 30-40%，那麼您很可能已經努力讓它們快速運行，是時候繼續優化下一組查詢了。
仍有待回答該組中應包含多少個熱門查詢的問題。我通常至少取10個，但不超過20個。我盡量保證TOP組中第一個和最後一個的時間相差不超過10倍。也就是說，如果查詢執行時間從第 1 位急劇下降到第 10 位，那麼我就取 TOP-10，如果下降更緩慢，那麼我將組大小增加到 15 或 20。
使用建構者的 B2B 服務範例最佳化資料庫查詢

中農

這些都是緊接著 TOP 之後發出的請求，最後 5-10% 除外。通常，優化這些查詢有機會大大提高伺服器效能。這些請求的權重可能高達 80%。但即使他們的份額已經超過了 50%，那麼也是時候更仔細地審視他們了。

尾巴

如前所述，這些查詢出現在最後，佔用 5-10% 的時間。只有當您不使用自動查詢分析工具時，您才可以忘記它們，然後優化它們也可以很便宜。

如何評價各組？

我使用一個 SQL 查詢來幫助對 PostgreSQL 進行這樣的評估（我確信可以為許多其他 DBMS 編寫類似的查詢）

用於估計 TOP-MEDIUM-TAIL 群組大小的 SQL 查詢

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

查詢的結果是三列，每列包含處理該群組查詢所花費的時間百分比。在請求中，有兩個數字（在我的例子中是 20 和 800）將一組請求與另一組請求分開。

這是優化工作開始時和現在的請求份額的粗略比較。

使用建構者的 B2B 服務範例最佳化資料庫查詢

從圖中可以看出，TOP請求的佔比急劇下降，但「中農」卻增加。
起初，TOP 請求包含明顯的錯誤。隨著時間的推移，兒童疾病消失了，TOP 請求的比例下降了，必須付出越來越多的努力來加快困難請求的速度。

為了獲取請求的文本，我們使用以下請求

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

以下是幫助我們加快 TOP 查詢速度的最常用技術的清單：

  • 重新設計系統，例如，使用訊息代理重新設計通知邏輯，而不是定期查詢資料庫
  • 新增或更改索引
  • 將 ORM 查詢重寫為純 SQL
  • 重寫惰性資料載入邏輯
  • 透過資料反規範化進行快取。例如，我們有一個表連接Delivery -> Invoice -> Request -> Application。也就是說，每次交付都透過其他表與應用程式關聯。為了不連結每個請求中的所有表，我們在交付表中複製了指向該請求的連結。
  • 在程式記憶體中快取帶有參考書的靜態表和很少更改的表。

有時，這些變更相當於令人印象深刻的重新設計，但它們提供了 5-10% 的系統負載，並且是合理的。隨著時間的推移，排氣管變得越來越小，需要越來越認真的重新設計。

然後我們把目光轉向第二類訴求──中農群體。裡面的查詢還有很多，看起來要花很多時間分析整個群組。然而，大多數查詢的最佳化都非常簡單，並且許多問題以不同的變體重複了數十次。以下是我們應用於數十個類似查詢的一些典型優化的範例，每組優化查詢將資料庫卸載 3-5%。

  • 開始使用 EXISTS，而不是使用 COUNT 和全表掃描來檢查記錄是否存在
  • 擺脫了 DISTINCT（沒有通用的方法，但有時你可以透過將請求加速 10-100 倍來輕鬆擺脫它）。

    例如，代替查詢從大型交貨表 (DELIVERY) 中選擇所有驅動程式 

    SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID

    對相對較小的表 PERSON 進行查詢

    SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
FROM PERSON
WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)

    看起來我們使用了相關子查詢，但它的加速速度超過了 10 倍。

  • 在許多情況下，COUNT 被完全放棄並且
    改為近似值計算
  • 而不是 
    UPPER(s) LIKE JOHN%’

    使用 

    s ILIKE “John%”

每個特定請求有時會加速 3-1000 倍。儘管效能令人印象深刻，但起初我們認為最佳化需要 10 毫秒才能完成的查詢是沒有意義的，它是第三百個最繁重的查詢之一，並且佔用整個資料庫載入時間的百分之一。但透過將相同的方法應用於一組相同類型的查詢，我們贏回了幾個百分點。為了不浪費時間手動檢查所有數百個查詢，我們編寫了幾個簡單的腳本，使用正規表示式來尋找相同類型的查詢。因此，自動搜尋查詢組使我們能夠透過適度的努力進一步提高效能。

因此，三年來我們一直在開發相同的硬體。日均負載約30%，高峰時可達70%。請求數量以及用戶數量增加了約 10 倍。所有這一切都歸功於對這些相同組的 TOP-MEDIUM 請求的持續監控。一旦 TOP 組中出現新的請求，我們立即對其進行分析並嘗試加快速度。我們使用查詢分析腳本每週檢查一次 MEDIUM 群組。如果我們遇到已經知道如何最佳化的新查詢，我們會快速更改它們。有時我們會發現可以同時應用於多個查詢的新最佳化方法。

根據我們的預測，目前的伺服器將承受用戶數量再成長3-5倍。確實，我們還有一張王牌 - 我們仍然沒有按照建議將 SELECT 查詢傳輸到鏡像。但我們並不是有意識地這樣做，因為我們想先徹底耗盡「智慧」優化的可能性，然後再打開「重砲」。
對已完成的工作進行批判性審視可能會建議使用垂直縮放。購買更強大的伺服器，而不是浪費專家的時間。伺服器可能不會花費那麼多，特別是因為我們還沒有耗盡垂直擴展的限制。然而，僅請求數量增加了10倍。幾年來，系統的功能不斷增加，現在請求的類型也更多了。由於緩存，現有的功能可以透過更少的請求和更有效率的請求來執行。這意味著您可以安全地再乘以 5 以獲得真實的加速係數。所以，根據最保守的估計，我們可以說加速度是50倍甚至更多。垂直擺動伺服器的成本將增加 50 倍。特別是考慮到一旦進行最佳化，它就一直有效，並且每個月都會收到租用伺服器的帳單。

來源： www.habr.com

添加評論