透過預熱來最佳化POSTGRESQL的查詢

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

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

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

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

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

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

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

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

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

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

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

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

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)

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