?今天遇到D-SMART產(chǎn)品本身的一個性能問題，我準(zhǔn)備用D-SMART給一套Oracle數(shù)據(jù)庫做個巡檢，發(fā)現(xiàn)居然任務(wù)因為一條SQL超時而異常了。通過日志發(fā)現(xiàn)是一條分析某個指標(biāo)的SQL。

執(zhí)行時間居然高達(dá)229秒，巡檢報告中設(shè)定了SQL超時時間是180秒，而如果巡檢的時間區(qū)間超過一個半月，則這條sql的執(zhí)行時間介于170秒到250秒之間，就經(jīng)常會超時了。

D-SMART的后臺數(shù)據(jù)庫是PG，這張表是一張TIMESCALEDB的表。表上也創(chuàng)建了適當(dāng)?shù)乃饕?。通過explain分析看，執(zhí)行計劃也是正常的，通過這個分區(qū)索引做范圍掃描，然后做聚合（Timescaledb會按照時間戳自動做數(shù)據(jù)分區(qū)）。通過D-SMART的PG數(shù)據(jù)庫等待事件分析工具可以發(fā)現(xiàn)，數(shù)據(jù)文件讀是排在前面的。

剛開始的時候我也沒有仔細(xì)分析，通過EXPAIN發(fā)現(xiàn)sort buffer使用量接近20M，明顯超出了WORK_MEM參數(shù)。于是我調(diào)整了WORK_MEM參數(shù)，重新執(zhí)行了這條SQL。發(fā)現(xiàn)原來需要200多秒的SQL不到50毫秒就完成了。不過我還是留了個心眼，因為D-SMART分析工具里可以看出文件讀占了比較靠前的位置。于是我重啟了一下PG數(shù)據(jù)庫，再次執(zhí)行這條SQL。比剛才稍微慢了一點，大概80多毫秒。不過比起200多秒來，也提升不少。于是我和同事說，這條SQL的性能問題解決了，加大WORK_MEM參數(shù)就可以了。

老儲還是在PG上有豐富的實戰(zhàn)經(jīng)驗，他提醒我，驗證PG的問題，重啟數(shù)據(jù)庫是沒用的，文件緩沖會影響SQL的性能。搞了二十多年Oracle，總是用Oracle的思維來思考現(xiàn)在的數(shù)據(jù)庫問題，這回又犯了類似的錯誤。于是我重新做了測試，關(guān)閉數(shù)據(jù)庫，然后使用echo 3 > drop_caches命令清除OS緩沖，然后再進行測試。

令人遺憾的是，SQL的性能又回到了從前，看樣子加大WORK_MEM并沒有有效的改善SQL性能?；剡^頭來想想也是，哪怕因為排序緩沖超了一點，做了硬盤排序，也不可能有20秒的性能影響。

公司的這套PG 14.4的環(huán)境是裝在一臺虛擬機上的，磁盤是SATA盤，性能確實不行。對于PG這樣使用DOUBLE CACHE的數(shù)據(jù)庫，文件緩沖確實可以對SQL性能有明顯的幫助。而這種特性也會讓PG數(shù)據(jù)庫的同一條SQL語句在OS的不同狀態(tài)下執(zhí)行性能有較大的波動。下面我們通過一個例子來驗證一下。

在做這個測試之前，我們先要安裝一個插件-pgfincore，對這個插件有興趣的朋友可以去https://github.com/klando/pgfincore 下載。Pgfincore是針對PG數(shù)據(jù)庫的OS緩沖分析與操作的插件，一般被用戶用來分析OS緩沖中的數(shù)據(jù)庫表或者索引，也被部分用戶用來預(yù)熱數(shù)據(jù)，讓部分熱數(shù)據(jù)總是被緩沖在FILE CACHE中，從而讓OS CACHE能夠更好的發(fā)揮作用。

Pgfincore的功能十分強大，首先可以用來查看某張表或者索引在OS緩沖中的情況。比如：

我檢查的一個timescaledb的索引分區(qū)，總共有15.7萬個page，其中13.9萬個page在OS緩沖里了。

第二個功能是把某張表或者索引的數(shù)據(jù)預(yù)熱到OS CACHE里。這里要注意的是如果表是分區(qū)表，一定要直接預(yù)熱分區(qū)，而不要使用表的名字，pgfincore不支持自動識別表分區(qū)。Timescaledb的一個表分區(qū)，原本這張表并沒有完全被緩沖到內(nèi)存里，通過調(diào)用pgfadvise_willneed函數(shù)，把這張表的所有數(shù)據(jù)都調(diào)用到OS緩沖中了。

第三個功能是備份和恢復(fù)某個場景下的OS CACHE。這對于一些十分關(guān)鍵的系統(tǒng)的預(yù)熱十分有價值。比如說某個系統(tǒng)的某些熱數(shù)據(jù)對于系統(tǒng)性能十分關(guān)鍵。當(dāng)系統(tǒng)重啟（特別是服務(wù)器重啟）后的某個時間段里，數(shù)據(jù)沒有預(yù)熱完成之前，系統(tǒng)性能是會有較大影響的。如果我們在停機重啟前，先備份OS CACHE中某些熱表的緩沖情況，系統(tǒng)重啟后立即預(yù)熱這部分?jǐn)?shù)據(jù)，則可以確保系統(tǒng)重啟后立即恢復(fù)重啟前的性能。

首先在系統(tǒng)重啟前將pgfincore的數(shù)據(jù)保存在pgfincore_snapshot表中，系統(tǒng)重啟后使用pgfadvise_loader重新裝載緩沖數(shù)據(jù)。

有了上面的基礎(chǔ)知識，我們下面就來做一個實驗。

首先對OS緩沖做一個完全的清理。然后啟動PG數(shù)據(jù)庫。執(zhí)行剛才有問題的那條SQL語句。

我只截取了部分執(zhí)行計劃，因為針對每個分區(qū)，都是相同的掃描方式，先對索引做掃描，然后再回表。這條SQL執(zhí)行了34秒多。

接下來我們先按照上面的流程再次關(guān)閉數(shù)據(jù)庫，清理緩沖，然后把所有的索引分區(qū)都先預(yù)熱一下，看看效果如何。

可以看出，現(xiàn)在所有索引的OS緩沖項都是0，說明沒有任何索引數(shù)據(jù)被緩存了。接下來預(yù)熱，然后再次執(zhí)行這條SQL。

大家可以看到，預(yù)熱后，這些索引分區(qū)都在OS緩沖里了，同樣再把所有的表的數(shù)據(jù)也預(yù)熱一下。再來執(zhí)行剛才的SQL語句：

大家可以看到，執(zhí)行時間從34秒變成了31毫秒。實際上對于使用double cache的數(shù)據(jù)庫來說，此類問題是十分常見的。此類數(shù)據(jù)庫產(chǎn)品的同一條SQL在不同時間里執(zhí)行的性能可能差異上百倍，但是其執(zhí)行計劃是完全相同的。這是因為DOUBLE CACHE的原因。Pgfincore插件為解決此類問題提供了一個很好的解決方案。利用snapshot/restore的方式，如果做一些適當(dāng)?shù)木?xì)化管理，可以起到十分好的穩(wěn)定關(guān)鍵業(yè)務(wù)SQL執(zhí)行效率的作用。希望我的這個性能故障的案例能夠給大家一些啟示。?