?前些年和搞PG的朋友交流的時候，他說PG數(shù)據(jù)庫是比較簡單的數(shù)據(jù)庫，但是用好PG數(shù)據(jù)庫并簡單。和Oracle數(shù)據(jù)庫比起來，Oracle數(shù)據(jù)庫很復雜，想管好、優(yōu)化好技術(shù)就要在管理Oracle的技術(shù)需要下大功夫，一旦掌握了，反而管理起來很簡單，很順手了。而PG數(shù)據(jù)庫相對簡單，想管好，優(yōu)化好，在技術(shù)上并不復雜，掌握技術(shù)難度不大，但是你需要更為精細的去管理它，才能用好它。我覺得他說得挺有道理，PG可以支撐超大型數(shù)據(jù)庫系統(tǒng)，不過你需要在運維與優(yōu)化上精耕細作，才能用好。

PG數(shù)據(jù)庫提供了相當多的小功能，也有大量的第三方插件，實際上都是在生產(chǎn)環(huán)境中遇到了問題而給出的一些解決方案。最近我們就用上了一個數(shù)據(jù)表預熱的插件fincore。對于Oracle這樣的數(shù)據(jù)庫來說，數(shù)據(jù)預熱在大多數(shù)場景下是不需要的，不過偶爾我們還是會使用預熱來加速某些在半夜執(zhí)行的定時統(tǒng)計任務。

PG數(shù)據(jù)庫采用DOUBLE CACHE的模式，因此預熱的應用場景會更豐富一些。在使用PG數(shù)據(jù)庫的時候，我們經(jīng)常會發(fā)現(xiàn)某條SQL執(zhí)行效率不穩(wěn)定，有時候秒出，有時候需要十多秒，查看執(zhí)行計劃，還沒啥變化，數(shù)據(jù)量也變化不大。如果遇到這種情況，那么你可能就遇到了DOUBLE CACHE的問題了。當數(shù)據(jù)都在內(nèi)存里（包括文件緩沖），SQL的執(zhí)行效率會非常高，而如果數(shù)據(jù)在緩沖中的比例不高，那么執(zhí)行效率就會嚴重下降。

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

剛開始我覺得這條SQL也沒法優(yōu)化，也沒必要優(yōu)化，分析TOP SQL本身就不是十分常用的操作。不過用戶并不認同我的觀點，他們認為如果日常運維遇到了必須分析TOP SQL的時候往往就是遇到了十分嚴重的性能問題，對于他們這種金融服務企業(yè)，這個時候定位問題解決問題的時間是十分關(guān)鍵的，這時候就需要每個操作都有十分快的響應。

要想優(yōu)化這個SQL，實際上也沒有太好的辦法，并行查詢原先就已經(jīng)啟用，而且并發(fā)度提高也沒啥用了。最后我們想到了預熱，如果對最近2天的TOP SQL表做預熱，那么這個TOP SQL查詢的執(zhí)行時間不超過50毫秒。不過因為相對于指標數(shù)據(jù)，TOP SQL表太冷了，預熱3-5分鐘后，這些CACHE就會被重新驅(qū)逐了。于是我們做了一個定時任務，每隔五分鐘預熱一次數(shù)據(jù)，用戶對這個模塊的體驗基本上滿意了。

在這個場景中，因為服務器內(nèi)存不是很大，預熱TOP SQL表會對其他業(yè)務產(chǎn)生一定的負面影響，比如查找指標會稍微慢一點，不過用戶是能夠接受的，10毫秒和15毫秒的響應時間差別，UI用戶在操作上是無感的。我們用犧牲特別快的查找指標的性能來提升相對較慢的TOP SQL查詢，這筆賬是劃算的。

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

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

還有一個數(shù)據(jù)預熱十分有效的場景就是每天的定期數(shù)據(jù)統(tǒng)計前。此時OLTP業(yè)務負載很小，內(nèi)存可以騰出來給BATCH類應用。此時如果我們在批處理統(tǒng)計開始之前，先把統(tǒng)計需要使用的主要數(shù)據(jù)都先預熱一下，那么統(tǒng)計分析任務的執(zhí)行時間可以大幅度提升。因為順序掃描文件的預熱操作是順序讀，用提前的大量順序讀來替換SQL執(zhí)行時大量的隨機讀，對于大多數(shù)應用場景來說，都是十分劃算的。在我們以往的優(yōu)化案例中，執(zhí)行效率提升十倍以上是十分常見的。

在做數(shù)據(jù)預熱的時候，我們也需要做一些分析。首先要考慮數(shù)據(jù)庫服務器的內(nèi)存情況。如果內(nèi)存十分緊張，那么對某些大表的預熱效果可能不好。其次是磁盤IO能力是否能夠支撐預熱操作。如果本身磁盤IO性能就很差，負擔就過重，那么預熱可能會給糟糕的磁盤IO帶來十分負面的影響，很可能達不到通過預熱減輕磁盤IO的作用。磁盤性能好，還可以加速預熱的時間，并且讓一些定期的預熱操作不至于對系統(tǒng)整體性能造成影響。最后就是場景，預熱操作的設計需要對系統(tǒng)十分了解，做精細化的分析后才能確定好方案的。如果對你運維的系統(tǒng)一無所知，盲目的去設計預熱方案，可能事倍功半，甚至可能引發(fā)嚴重的運維故障。

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

import os

import psycopg2

         

# 連接數(shù)據(jù)庫

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))

         

    # 獲取所有數(shù)據(jù)文件并將其預熱進入文件緩沖區(qū)

    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數(shù)據(jù)庫的fincore插件可以研究一下，使用fincore你也可以十分方便的實現(xiàn)你所需要的各種預熱操作。?

當前名稱：通過預熱來優(yōu)化PostgreSQL的查詢
鏈接地址：http://uogjgqi.cn/article/dpeihjp.html