前陣子一個朋友和我討論一個PG性能問題，他們最近把幾個小的PG數(shù)據(jù)庫整合為一個大系統(tǒng)，換了臺新服務(wù)器，搞了超豪華配置，有512GB的物理內(nèi)存。他們配置了一個128GB的SHARED_BUFFERS，然后應(yīng)用就出問題了。因為這套系統(tǒng)中經(jīng)常要用到臨時表，他們的臨時表都是物理表，一般是create/insert/select/drop，一串操作。系統(tǒng)升級后，系統(tǒng)就變得特別慢了，經(jīng)過分析，發(fā)現(xiàn)主要問題出在drop table上。在一頓亂試中，他們恢復(fù)了shared_buffers原來的配置后，系統(tǒng)性能居然恢復(fù)了！

在PG數(shù)據(jù)庫中，過大的shared_buffers會影響DROP TABLE/TRUNCATE TABLE等的性能嗎？這個問題乍一聽好像天方夜譚，不過細(xì)想起來確實是有道理的。按理說PG數(shù)據(jù)庫DROP TABLE直接刪除文件系統(tǒng)中的文件，修改數(shù)據(jù)字典就OK了，應(yīng)該比Oracle還更利索一些，不應(yīng)該很慢。而DROP TABLE和SHARED BUFFERS的大小有關(guān)就更離譜了。

我在cybertec上找到了一篇Hans-Jürgen Sch?nig的博客，提供了一個測試用例，在我們實驗室的一套PG 12.6上做了一個測試，還真的挺有意思的。先介紹一下這個測試用例。這個測試用例分為run.sql和tps.sh兩個腳本，通過pgbench工具對DROP TABLE做了一個測試。先看run.sql的代碼：

SET synchronous_commit TO off;
BEGIN;
CREATE TABLE x(id int);
INSERT INTO x VALUES (1);
DROP TABLE x;
COMMIT;

 關(guān)閉同步提交的目的是為了盡可能降低IO性能對測試的影響。然后執(zhí)行一個建表，寫數(shù)據(jù)，刪除表這個操作。tps.sh的代碼如下：

#/bin/sh


for x in '8 MB' '32 MB' '128 MB' '1 GB' '8 GB'
do
      pg_ctl  -l /dev/null -o "--shared_buffers='$x'" start
      sleep 1
      echo tps for $x
      psql -c "SHOW shared_buffers"
      pgbench --file=run.sql -j 1 -c 1 -T 10  2> /dev/null
      pg_ctl -D /tmp/db stop
      sleep 1
done

根據(jù)不同的測試環(huán)境，大家需要對tps.sh做一些修改，比如psql和pgbench的參數(shù)。我做了一些調(diào)整。測試了一下。    

圖片

可以看出，隨著shared_buffers的加大，TPS出現(xiàn)了明顯的下降，最后居然降到了不足200。在本次測試中，系統(tǒng)中有足夠的物理內(nèi)存，確保系統(tǒng)不會出現(xiàn)SWAP的情況。在這種情況下，還是出現(xiàn)了此類情況，這是什么原因呢？

問題一般不會出在其他地方，我這個測試是單獨(dú)進(jìn)行的，也沒有其他會話干擾，也不會有鎖的問題。唯一出問題的地方是在shared_buffers上了。當(dāng)drop table完成后，需要做一個類似checkpoint的工作，把shared_buffers中和這個表相關(guān)的所有buffer全部清理掉。正是這個工作拖慢了drop table的性能。

我不知道一些老的Oracle DBA還有沒有這方面的印象，以前運(yùn)維Oracle 9i或者更早版本的數(shù)據(jù)庫的時候，有些時候truncate操作會特別慢，只要在等待object reuse。實際上就是在等待對象級的CKPT完成。為了解決這個問題，我們會用truncate table keep storage語法。為了解決這個問題，Oracle 也是折騰了很久的。

在8.0和7.3版本，Oracle一共設(shè)置了3條鏈：LRU鏈、LRU-W鏈和LRU-P鏈。LRU鏈?zhǔn)俏覀儌鹘y(tǒng)所說的replacement list,用于BUFFER的LRU替代，LRU-W是需要DBWR寫入數(shù)據(jù)文件的鏈，LRU-P是當(dāng)前正在寫入的鏈表，當(dāng)時所有的BUFFER都被PIN住，等寫入完成后會降低鎖定級別，并被重新鏈入LRU。8I開始引入了5條鏈：LRU、LRU-W、LRU-P，LRU-XO、LRU-XR。其中前面三條含義沒變，LRU-XO是重用對象鏈，當(dāng)實例發(fā)布需要重用某個對象的時候（比如TRUNCATE等DDL操作后），這個對象的所有當(dāng)前塊和臟塊被鏈入這個隊列，DBWR需要將臟塊寫入文件。CKPT會檢查這個隊列，當(dāng)這個隊列變空的時候認(rèn)為本次對象級的CKPT結(jié)束。LRU-XR主要用于對某個數(shù)據(jù)文件或者表空間進(jìn)行CKPT，把一個范圍的臟塊鏈入該鏈，讓DBWR去寫盤，CKPT檢查這個鏈表，直到鏈表為空。    

Oracle 9.2發(fā)現(xiàn)這樣設(shè)計后，object reuse的性能還是無法 徹底改善，甚至性能更差了，所以把LRU-XO鏈去掉了，5條鏈變又成了4條鏈。直到Oracle 10g這個才被徹底解決。OBJECT REUSE的算法再次改進(jìn)，在WORKING SET中引入了Object hash chain結(jié)構(gòu)kcbohtab * htabkcbwds，從而把一條LRU-XO鏈變成了一個HASH TABLE加N條鏈，每個對象有自己獨(dú)立的OBJECT REUSE鏈表。這樣對于一個對象的OBJECT REUSE操作就沒有任何性能問題了。    

圖片

上面是11.1.0.7版本的Oracle的kcbwds結(jié)構(gòu)，黃色部分就是我說的object queue hash table。在沒有這個hash table的時候，如果要把某個drop/truncate的對象相關(guān)的cache清理出去的時候，就需要遍歷shared_buffers，這樣情況下，緩沖區(qū)越大性能就越差。如果有了這個hash table，那么這個操作的性能就只與該對象在緩沖區(qū)中的緩沖塊的數(shù)量有關(guān)了。

實際上，對于這個問題，2015年的時候，就有人寫了Patch，只不過當(dāng)時PG社區(qū)的大多數(shù)大佬認(rèn)為超大內(nèi)存數(shù)據(jù)庫系統(tǒng)只是小眾場景，頻繁做drop/truncate的系統(tǒng)也不多，因此沒必要在這方面做優(yōu)化。    

圖片

我圍觀了他們的討論，因為大多數(shù)大佬都認(rèn)為場景十分有限，沒必要去優(yōu)化這個操作，這件事就不了了之了。實際上在這件事上，這些PG大佬可能真的有點(diǎn)自負(fù)了，使用臨時表（不論是全局臨時表還是物理表充當(dāng)臨時表，這方面的操作模式是類似的）在一些復(fù)雜的系統(tǒng)，比如ERP、財務(wù)、供應(yīng)鏈管理、審計、工業(yè)流程管理等是很常見的操作。頻繁創(chuàng)建刪除截斷表對象也是很常見的操作。因此這個場景的優(yōu)化還是十分必要的。

要想實現(xiàn)對此的優(yōu)化其實也不難，因為PG沒有使用類似Oracle的表空間管理機(jī)制，因此不存在Object Reuse的問題，只需要不寫盤淘汰這些對象的shared buffers緩沖就可以了。這個操作完全可以做成延時的，backend刪除表后直接結(jié)束操作，淘汰臟塊的事情由CKPT后續(xù)延時完成就可以了。當(dāng)某個對象被刪除時，只要記錄一下清單，當(dāng)CKPT發(fā)現(xiàn)某個臟塊屬于這個對象時，直接拋棄就可以了。這個清單最好能寫入WAL，這樣系統(tǒng)故障恢復(fù)時可以比較快速地拋棄這些臟數(shù)據(jù)，如果WAL中沒有這些數(shù)據(jù)，也沒關(guān)系，只是效率低一點(diǎn)而已。

其實數(shù)據(jù)庫就是這樣一點(diǎn)點(diǎn)的在用戶場景中完善起來的，商用數(shù)據(jù)庫廠商十分注重收集這方面的需求，而開源數(shù)據(jù)庫因為用戶群體十分龐大，需求更為龐大，因此在實現(xiàn)某些功能的時候，選擇比較困難，無法像商用數(shù)據(jù)庫那么迅速的響應(yīng)。