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

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

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

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

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

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

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

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

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

還有一個(gè)數(shù)據(jù)預(yù)熱十分有效的場景就是每天的定期數(shù)據(jù)統(tǒng)計(jì)前。此時(shí)OLTP業(yè)務(wù)負(fù)載很小，內(nèi)存可以騰出來給BATCH類應(yīng)用。此時(shí)如果我們?cè)谂幚斫y(tǒng)計(jì)開始之前，先把統(tǒng)計(jì)需要使用的主要數(shù)據(jù)都先預(yù)熱一下，那么統(tǒng)計(jì)分析任務(wù)的執(zhí)行時(shí)間可以大幅度提升。因?yàn)轫樞驋呙栉募念A(yù)熱操作是順序讀，用提前的大量順序讀來替換SQL執(zhí)行時(shí)大量的隨機(jī)讀，對(duì)于大多數(shù)應(yīng)用場景來說，都是十分劃算的。在我們以往的優(yōu)化案例中，執(zhí)行效率提升十倍以上是十分常見的。

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

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

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ù)文件并將其預(yù)熱進(jìn)入文件緩沖區(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'  # 替換成需要預(yù)熱的表名

    preload_table(table_name)

         

如果不想自己寫程序的朋友，PG數(shù)據(jù)庫的fincore插件可以研究一下，使用fincore你也可以十分方便的實(shí)現(xiàn)你所需要的各種預(yù)熱操作。?