今天遇到D-SMART產品本身的一個效能問題，我準備用D-SMART給一套Oracle資料庫做個巡檢，發現居然任務因為一條SQL超時而異常了。透過日誌發現是一條分析某個指標的SQL。執行時間居然高達229秒，巡檢報告中設定了SQL超時時間是180秒，而如果巡檢的時間區間超過一個半月，則這條sql的執行時間介於170秒到250秒之間，就經常會超時了。
D-SMART的後臺資料庫是PG，這張表是一張TIMESCALEDB的表。表上也建立了適當的索引。透過explain分析看，執行計劃也是正常的，透過這個分割槽索引做範圍掃描，然後做聚合（Timescaledb會按照時間戳自動做資料分割槽）。透過D-SMART的PG資料庫等待事件分析工具可以發現，資料檔案讀是排在前面的。剛開始的時候我也沒有仔細分析，透過EXPAIN發現sort buffer使用量接近20M，明顯超出了WORK_MEM引數。於是我調整了WORK_MEM引數，重新執行了這條SQL。發現原來需要200多秒的SQL不到50毫秒就完成了。不過我還是留了個心眼，因為D-SMART分析工具裡可以看出檔案讀佔了比較靠前的位置。於是我重啟了一下PG資料庫，再次執行這條SQL。比剛才稍微慢了一點，大概80多毫秒。不過比起200多秒來，也提升不少。於是我和同事說，這條SQL的效能問題解決了，加大WORK_MEM引數就可以了。老儲還是在PG上有豐富的實戰經驗，他提醒我，驗證PG的問題，重啟資料庫是沒用的，檔案緩衝會影響SQL的效能。搞了二十多年Oracle，總是用Oracle的思維來思考現在的資料庫問題，這回又犯了類似的錯誤。於是我重新做了測試，關閉資料庫，然後使用echo 3 > drop_caches命令清除OS緩衝，然後再進行測試。令人遺憾的是，SQL的效能又回到了從前，看樣子加大WORK_MEM並沒有有效的改善SQL效能。回過頭來想想也是，哪怕因為排序緩衝超了一點，做了硬碟排序，也不可能有20秒的效能影響。公司的這套PG 14.4的環境是裝在一臺虛擬機器上的，磁碟是SATA盤，效能確實不行。對於PG這樣使用DOUBLE CACHE的資料庫，檔案緩衝確實可以對SQL效能有明顯的幫助。而這種特性也會讓PG資料庫的同一條SQL語句在OS的不同狀態下執行效能有較大的波動。下面我們透過一個例子來驗證一下。在做這個測試之前，我們先要安裝一個外掛-pgfincore，對這個外掛有興趣的朋友可以去 下載。Pgfincore是針對PG資料庫的OS緩衝分析與操作的外掛，一般被使用者用來分析OS緩衝中的資料庫表或者索引，也被部分使用者用來預熱資料，讓部分熱資料總是被緩衝在FILE CACHE中，從而讓OS CACHE能夠更好的發揮作用。Pgfincore的功能十分強大，首先可以用來檢視某張表或者索引在OS緩衝中的情況。比如：我檢查的一個timescaledb的索引分割槽，總共有15.7萬個page，其中13.9萬個page在OS緩衝裡了。第二個功能是把某張表或者索引的資料預熱到OS CACHE裡。這裡要注意的是如果表是分割槽表，一定要直接預熱分割槽，而不要使用表的名字，pgfincore不支援自動識別表分割槽。Timescaledb的一個表分割槽，原本這張表並沒有完全被緩衝到記憶體裡，透過呼叫pgfadvise_willneed函式，把這張表的所有資料都呼叫到OS緩衝中了。第三個功能是備份和恢復某個場景下的OS CACHE。這對於一些十分關鍵的系統的預熱十分有價值。比如說某個系統的某些熱資料對於系統效能十分關鍵。當系統重啟（特別是伺服器重啟）後的某個時間段裡，資料沒有預熱完成之前，系統效能是會有較大影響的。如果我們在停機重啟前，先備份OS CACHE中某些熱表的緩衝情況，系統重啟後立即預熱這部分資料，則可以確保系統重啟後立即恢復重啟前的效能。首先在系統重啟前將pgfincore的資料儲存在pgfincore_snapshot表中，系統重啟後使用pgfadvise_loader重新裝載緩衝資料。首先對OS緩衝做一個完全的清理。然後啟動PG資料庫。執行剛才有問題的那條SQL語句。我只擷取了部分執行計劃，因為針對每個分割槽，都是相同的掃描方式，先對索引做掃描，然後再回表。這條SQL執行了34秒多。接下來我們先按照上面的流程再次關閉資料庫，清理緩衝，然後把所有的索引分割槽都先預熱一下，看看效果如何。可以看出，現在所有索引的OS緩衝項都是0，說明沒有任何索引資料被快取了。接下來預熱，然後再次執行這條SQL。大家可以看到，預熱後，這些索引分割槽都在OS緩衝裡了，同樣再把所有的表的資料也預熱一下。再來執行剛才的SQL語句：大家可以看到，執行時間從34秒變成了31毫秒。實際上對於使用double cache的資料庫來說，此類問題是十分常見的。此類資料庫產品的同一條SQL在不同時間裡執行的效能可能差異上百倍，但是其執行計劃是完全相同的。這是因為DOUBLE CACHE的原因。Pgfincore外掛為解決此類問題提供了一個很好的解決方案。利用snapshot/restore的方式，如果做一些適當的精細化管理，可以起到十分好的穩定關鍵業務SQL執行效率的作用。希望我的這個效能故障的案例能夠給大家一些啟示。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024922/viewspace-2928143/，如需轉載，請註明出處，否則將追究法律責任。
