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

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

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

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

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

令人遺憾的是，SQL的性能又回到了從前，看樣子加大WORK_MEM并沒有有效的改善SQL性能?；剡^頭來想想也是，哪怕因?yàn)榕判蚓彌_超了一點(diǎn)，做了硬盤排序，也不可能有20秒的性能影響。

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

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

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

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

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

第三個(gè)功能是備份和恢復(fù)某個(gè)場(chǎng)景下的OS CACHE。這對(duì)于一些十分關(guān)鍵的系統(tǒng)的預(yù)熱十分有價(jià)值。比如說某個(gè)系統(tǒng)的某些熱數(shù)據(jù)對(duì)于系統(tǒng)性能十分關(guān)鍵。當(dāng)系統(tǒng)重啟（特別是服務(wù)器重啟）后的某個(gè)時(shí)間段里，數(shù)據(jù)沒有預(yù)熱完成之前，系統(tǒng)性能是會(huì)有較大影響的。如果我們?cè)谕C(jī)重啟前，先備份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ǔ)知識(shí)，我們下面就來做一個(gè)實(shí)驗(yàn)。

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

我只截取了部分執(zhí)行計(jì)劃，因?yàn)獒槍?duì)每個(gè)分區(qū)，都是相同的掃描方式，先對(duì)索引做掃描，然后再回表。這條SQL執(zhí)行了34秒多。

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

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

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

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