透過預熱來最佳化PG資料庫的SQL效能

前些年和搞 P G 的朋友交流的時候，他說 P G 資料庫是比較簡單的資料庫，但是用好 P G 資料庫並簡單。和 Oracle資料庫比起來，Oracle資料庫很複雜，想管好，最佳化好技術就要很好，掌握管理Oracle的技術需要下大功夫，一旦掌握了，反而管理起來很簡單，很順手了。而P G 資料庫相對簡單，想管好，最佳化好，在技術上並不複雜，掌握技術難度不大，但是你需要更為精細的去管理它，才能用好它。我覺得他說的挺有道理， P G 可以支撐超大型資料庫系統，不過你需要在運維與最佳化上精耕細作，才能用好。

PG 資料庫提供了相當多的小功能，也有大量的第三方外掛，實際上都是在生產環境中遇到了問題而給出的一些解決方案。最近我們就用上了一個資料表預熱的外掛 f incore 。對於 Oracle這樣的資料庫來說，資料預熱在大多數場景下是不需要的，不過偶爾我們還是會使用預熱來加速某些晚上的定時統計任務。

P G 資料庫採用 D OUBLE CACHE 的模式，因此預熱的應用場景會更豐富一些。在使用 P G 資料庫的時候，我們經常會發現某條 S QL 執行效率不穩定，有時候秒出，有時候需要十多秒，檢視執行計劃，還沒啥變化，資料量也變化不大。如果遇到這種情況，那麼你可能就遇到了 D OUBLE CACHE 的問題了。當資料都在記憶體裡（包括檔案緩衝）， S QL 的執行效率會非常高，而如果資料在緩衝中的比例不高，那麼執行效率就會嚴重下降。

前陣子我們的 D-SMART 就遇到了這樣一個場景，因為這個使用者比較大， D -SMART 納管了 5 00 多套 Oracle資料庫。這就導致P G 資料庫的共享緩衝區中儲存的主要都是指標資料了。當使用者想分析 T OP SQL 的時候，就會覺得很慢，特別是第一次查詢，需要 7- 8 秒鐘才出結果。我們分析了執行計劃，索引使用啥的都是正常的，就是因為表中資料量太大，並且 T OPSQL 表使用頻率並不高，資料比較冷。

剛開始我覺得這條 S QL 也沒法最佳化，也沒必要最佳化，分析 T OP SQL 本身就不是十分常用的操作。不過使用者並不認同我的觀點，他們認為如果日常運維遇到了必須分析 T OP SQL 的時候往往就是遇到了十分嚴重的效能問題，對於他們這種金融服務企業，這個時候定位問題解決問題的時間是十分關鍵的，這時候就需要每個操作都有十分快的響應。

要想最佳化這個 S QL ，實際上也沒有太好的辦法，並行查詢原先就已經啟用，而且併發度提高也沒啥用了。最後我們想到了預熱，如果對最近 2天的T OP SQL 表做預熱，那麼這個 T OP SQL 查詢的執行時間不超過 5 0 毫秒。不過因為相對於指標資料， T OP SQL 表太冷了，預熱 3- 5 分鐘後，這些 C ACHE 就會被重新驅逐了。於是我們做了一個定時任務，每隔五分鐘預熱一次資料，使用者對這個模組的體驗基本上滿意了。

在這個場景中，因為伺服器記憶體不是很大，預熱 T OP SQL 表會對其他業務產生一定的負面影響，比如查詢指標會稍微慢一點，不過使用者是能夠接受的， 1 0 毫秒和 1 5 毫秒的響應時間差別， U I 使用者在操作上是無感的。我們用犧牲特別快的查詢指標的效能來提升相對較慢的 TOP SQL 查詢，這筆賬是划算的。

一般來說， 預熱需求往往是 對經常被查詢 或者要重點保障查詢效能的 大型資料表 的 ，如果 所有需要查詢的資料都 需要從硬碟中讀取，會導致查詢速度變慢。 有很多種需要預熱資料的場景。首要選， 資料表中包含大量資料，而且這些資料經常被查詢。資料表中的資料經常被修改，例如經常進行插入、更新或刪除操作。這種情況下，資料預熱可以加速查詢的同時，減少I/O操作的次數，從而提高系統的穩定性。

需要注意的是，如果資料表的大小比較小，或者該資料表的查詢不頻繁， 或者反過來說，某些特別熱的小表，其資料大部分都在共享緩衝區中， 那麼進行資料預熱的效果可能不太明顯，反而會浪費系統資源。因此，在決定是否對資料表進行預熱時，需要仔細分析資料表的特性和使用情況。

還有一個資料預熱十分有效的場景就是每天的定期資料統計前。此時 O LTP 業務負載很小，記憶體可以騰出來給 B ATCH 類應用。此時如果我們在批處理統計開始之前，先把統計需要使用的主要資料都先預熱一下，那麼統計分析任務的執行時間可以大幅度提升。因為順序掃描檔案的預熱操作是順序讀，用提前的大量順序讀來替換 S QL 執行時大量的隨機讀，對於大多數應用場景來說，都是十分划算的。在我們以往的最佳化案例中，執行效率提升十倍以上是十分常見的。

在做資料預熱的時候，我們也需要做一些事先分析。首先要考慮資料庫伺服器的記憶體情況。如果記憶體十分緊張，那麼對大表的預熱效果可能不好。其次是磁碟 I O 能力是否能夠支撐預熱操作。如果本身磁碟 I O 效能就很差，負擔就過重，那麼預熱可能會給糟糕的磁碟 I O 帶來十分負面的影響，很可能達不到透過預熱減輕磁碟 I O 的作用。磁碟效能好，還可以加速預熱的時間，並且讓一些定期的預熱操作不至於對系統整體效能造成影響。最後就是場景，預熱操作的設計需要對系統十分了解，做精細化的分析後才能確定好方案的。如果對你運維的系統一無所知，盲目的去設計預熱方案，可能事倍功半，甚至可能引發嚴重的運維故障。

最後就說說如何預熱了。我今天早上就這個問題和 C HATGPT 聊了聊，根據我的引導，它給出了一個 P YTHON 指令碼。我看了一下，大體上是靠譜的，根據這個思路，我們可以自己寫一個預熱工具。

import os

import psycopg2

 

# 連線資料庫

conn = psycopg2.connect(database='mydb', user='myuser', password='mypassword', host='localhost', port='5432')

 

def execute_sql(sql, args=None):

    with conn:

        with conn.cursor() as curs:

            curs.execute(sql, args)

            return curs.fetchall()

 

def preload_table(table_name):

    # 獲取表所在的目錄

    base_path = os.path.join(os.environ['PGDATA'], 'base')

    table_oid = execute_sql("SELECT oid FROM pg_class WHERE relname = %s", (table_name,))[0][0]

    table_dir = os.path.join(base_path, str(table_oid // 10000), str(table_oid))

 

    # 獲取所有資料檔案並將其預熱進入檔案緩衝區

    for filename in os.listdir(table_dir):

        if filename.startswith(str(table_oid) + '.') and not filename.endswith('.fsm'):

            filepath = os.path.join(table_dir, filename)

            with open(filepath, 'rb') as f:

                f.read()

 

if __name__ == '__main__':

    table_name = 'mytable'  # 替換成需要預熱的表名

    preload_table(table_name)

 

如果不想自己寫程式的朋友， P G 資料庫的 f incore 外掛可以研究一下，使用 f incore 你也可以十分方便的實現你所需要的各種預熱操作。