建站
咨詢
售后
建站咨詢
新聞中心
?前些年和搞PG的朋友交流的時(shí)候，他說(shuō)PG數(shù)據(jù)庫(kù)是比較簡(jiǎn)單的數(shù)據(jù)庫(kù)，但是用好PG數(shù)據(jù)庫(kù)并簡(jiǎn)單。和Oracle數(shù)據(jù)庫(kù)比起來(lái)，Oracle數(shù)據(jù)庫(kù)很復(fù)雜，想管好、優(yōu)化好技術(shù)就要在管理Oracle的技術(shù)需要下大功夫，一旦掌握了，反而管理起來(lái)很簡(jiǎn)單，很順手了。而PG數(shù)據(jù)庫(kù)相對(duì)簡(jiǎn)單，想管好，優(yōu)化好，在技術(shù)上并不復(fù)雜，掌握技術(shù)難度不大，但是你需要更為精細(xì)的去管理它，才能用好它。我覺得他說(shuō)得挺有道理，PG可以支撐超大型數(shù)據(jù)庫(kù)系統(tǒng)，不過(guò)你需要在運(yùn)維與優(yōu)化上精耕細(xì)作，才能用好。
PG數(shù)據(jù)庫(kù)提供了相當(dāng)多的小功能，也有大量的第三方插件，實(shí)際上都是在生產(chǎn)環(huán)境中遇到了問(wèn)題而給出的一些解決方案。最近我們就用上了一個(gè)數(shù)據(jù)表預(yù)熱的插件fincore。對(duì)于Oracle這樣的數(shù)據(jù)庫(kù)來(lái)說(shuō)，數(shù)據(jù)預(yù)熱在大多數(shù)場(chǎng)景下是不需要的，不過(guò)偶爾我們還是會(huì)使用預(yù)熱來(lái)加速某些在半夜執(zhí)行的定時(shí)統(tǒng)計(jì)任務(wù)。
PG數(shù)據(jù)庫(kù)采用DOUBLE CACHE的模式，因此預(yù)熱的應(yīng)用場(chǎng)景會(huì)更豐富一些。在使用PG數(shù)據(jù)庫(kù)的時(shí)候，我們經(jīng)常會(huì)發(fā)現(xiàn)某條SQL執(zhí)行效率不穩(wěn)定，有時(shí)候秒出，有時(shí)候需要十多秒，查看執(zhí)行計(jì)劃，還沒(méi)啥變化，數(shù)據(jù)量也變化不大。如果遇到這種情況，那么你可能就遇到了DOUBLE CACHE的問(wèn)題了。當(dāng)數(shù)據(jù)都在內(nèi)存里（包括文件緩沖），SQL的執(zhí)行效率會(huì)非常高，而如果數(shù)據(jù)在緩沖中的比例不高，那么執(zhí)行效率就會(huì)嚴(yán)重下降。
前陣子我們的D-SMART就遇到了這樣一個(gè)場(chǎng)景，因?yàn)檫@個(gè)用戶比較大，D-SMART納管了500多套Oracle數(shù)據(jù)庫(kù)。這就導(dǎo)致PG數(shù)據(jù)庫(kù)的共享緩沖區(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也沒(méi)法優(yōu)化，也沒(méi)必要優(yōu)化，分析TOP SQL本身就不是十分常用的操作。不過(guò)用戶并不認(rèn)同我的觀點(diǎn)，他們認(rèn)為如果日常運(yùn)維遇到了必須分析TOP SQL的時(shí)候往往就是遇到了十分嚴(yán)重的性能問(wèn)題，對(duì)于他們這種金融服務(wù)企業(yè)，這個(gè)時(shí)候定位問(wèn)題解決問(wèn)題的時(shí)間是十分關(guān)鍵的，這時(shí)候就需要每個(gè)操作都有十分快的響應(yīng)。
要想優(yōu)化這個(gè)SQL，實(shí)際上也沒(méi)有太好的辦法，并行查詢?cè)染鸵呀?jīng)啟用，而且并發(fā)度提高也沒(méi)啥用了。最后我們想到了預(yù)熱，如果對(duì)最近2天的TOP SQL表做預(yù)熱，那么這個(gè)TOP SQL查詢的執(zhí)行時(shí)間不超過(guò)50毫秒。不過(guò)因?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è)場(chǎng)景中，因?yàn)榉?wù)器內(nèi)存不是很大，預(yù)熱TOP SQL表會(huì)對(duì)其他業(yè)務(wù)產(chǎn)生一定的負(fù)面影響，比如查找指標(biāo)會(huì)稍微慢一點(diǎn)，不過(guò)用戶是能夠接受的，10毫秒和15毫秒的響應(yīng)時(shí)間差別，UI用戶在操作上是無(wú)感的。我們用犧牲特別快的查找指標(biāo)的性能來(lái)提升相對(duì)較慢的TOP SQL查詢，這筆賬是劃算的。
一般來(lái)說(shuō)，預(yù)熱需求往往是對(duì)經(jīng)常被查詢或者要重點(diǎn)保障查詢性能的大型數(shù)據(jù)表的，如果所有需要查詢的數(shù)據(jù)都需要從硬盤中讀取，會(huì)導(dǎo)致查詢速度變慢。有很多種需要預(yù)熱數(shù)據(jù)的場(chǎng)景。首選，數(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ù)表的查詢不頻繁，或者反過(guò)來(lái)說(shuō)，某些特別熱的小表，其數(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ù)熱十分有效的場(chǎng)景就是每天的定期數(shù)據(jù)統(tǒng)計(jì)前。此時(shí)OLTP業(yè)務(wù)負(fù)載很小，內(nèi)存可以騰出來(lá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ù)熱操作是順序讀，用提前的大量順序讀來(lái)替換SQL執(zhí)行時(shí)大量的隨機(jī)讀，對(duì)于大多數(shù)應(yīng)用場(chǎng)景來(lái)說(shuō)，都是十分劃算的。在我們以往的優(yōu)化案例中，執(zhí)行效率提升十倍以上是十分常見的。
在做數(shù)據(jù)預(yù)熱的時(shí)候，我們也需要做一些分析。首先要考慮數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存情況。如果內(nèi)存十分緊張，那么對(duì)某些大表的預(yù)熱效果可能不好。其次是磁盤IO能力是否能夠支撐預(yù)熱操作。如果本身磁盤IO性能就很差，負(fù)擔(dān)就過(guò)重，那么預(yù)熱可能會(huì)給糟糕的磁盤IO帶來(lái)十分負(fù)面的影響，很可能達(dá)不到通過(guò)預(yù)熱減輕磁盤IO的作用。磁盤性能好，還可以加速預(yù)熱的時(shí)間，并且讓一些定期的預(yù)熱操作不至于對(duì)系統(tǒng)整體性能造成影響。最后就是場(chǎng)景，預(yù)熱操作的設(shè)計(jì)需要對(duì)系統(tǒng)十分了解，做精細(xì)化的分析后才能確定好方案的。如果對(duì)你運(yùn)維的系統(tǒng)一無(wú)所知，盲目的去設(shè)計(jì)預(yù)熱方案，可能事倍功半，甚至可能引發(fā)嚴(yán)重的運(yùn)維故障。
最后就說(shuō)說(shuō)如何預(yù)熱了。我今天早上就這個(gè)問(wèn)題和CHATGPT聊了聊，根據(jù)我的引導(dǎo)，它給出了一個(gè)PYTHON腳本。我看了一下，大體上是靠譜的，根據(jù)這個(gè)思路，我們稍加修改，就可以自己寫一個(gè)預(yù)熱工具。
import os
import psycopg2
# 連接數(shù)據(jù)庫(kù)
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ù)庫(kù)的fincore插件可以研究一下，使用fincore你也可以十分方便的實(shí)現(xiàn)你所需要的各種預(yù)熱操作。?
新聞名稱：通過(guò)預(yù)熱來(lái)優(yōu)化PostgreSQL的查詢
當(dāng)前地址：http://www.dlmjj.cn/article/dpeihjp.html
