?前些年和搞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你也可以十分方便的實現你所需要的各種預熱操作。?