在PG數據庫中 shared_buffers會影響DROP TABLE的性能嗎
前陣子一個朋友和我討論一個PG性能問題，他們最近把幾個小的PG數據庫整合為一個大系統，換了臺新服務器，搞了超豪華配置，有512GB的物理內存。他們配置了一個128GB的SHARED_BUFFERS，然后應用就出問題了。因為這套系統中經常要用到臨時表，他們的臨時表都是物理表，一般是create/insert/select/drop，一串操作。系統升級后，系統就變得特別慢了，經過分析，發現主要問題出在drop table上。在一頓亂試中，他們恢復了shared_buffers原來的配置后，系統性能居然恢復了！
在PG數據庫中，過大的shared_buffers會影響DROP TABLE/TRUNCATE TABLE等的性能嗎？這個問題乍一聽好像天方夜譚，不過細想起來確實是有道理的。按理說PG數據庫DROP TABLE直接刪除文件系統中的文件，修改數據字典就OK了，應該比Oracle還更利索一些，不應該很慢。而DROP TABLE和SHARED BUFFERS的大小有關就更離譜了。
我在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;關閉同步提交的目的是為了盡可能降低IO性能對測試的影響。然后執行一個建表，寫數據，刪除表這個操作。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根據不同的測試環境，大家需要對tps.sh做一些修改，比如psql和pgbench的參數。我做了一些調整。測試了一下。
圖片
可以看出，隨著shared_buffers的加大，TPS出現了明顯的下降，最后居然降到了不足200。在本次測試中，系統中有足夠的物理內存，確保系統不會出現SWAP的情況。在這種情況下，還是出現了此類情況，這是什么原因呢？
問題一般不會出在其他地方，我這個測試是單獨進行的，也沒有其他會話干擾，也不會有鎖的問題。唯一出問題的地方是在shared_buffers上了。當drop table完成后，需要做一個類似checkpoint的工作，把shared_buffers中和這個表相關的所有buffer全部清理掉。正是這個工作拖慢了drop table的性能。
我不知道一些老的Oracle DBA還有沒有這方面的印象，以前運維Oracle 9i或者更早版本的數據庫的時候，有些時候truncate操作會特別慢，只要在等待object reuse。實際上就是在等待對象級的CKPT完成。為了解決這個問題，我們會用truncate table keep storage語法。為了解決這個問題，Oracle 也是折騰了很久的。
在8.0和7.3版本，Oracle一共設置了3條鏈：LRU鏈、LRU-W鏈和LRU-P鏈。LRU鏈是我們傳統所說的replacement list,用于BUFFER的LRU替代，LRU-W是需要DBWR寫入數據文件的鏈，LRU-P是當前正在寫入的鏈表，當時所有的BUFFER都被PIN住，等寫入完成后會降低鎖定級別，并被重新鏈入LRU。8I開始引入了5條鏈：LRU、LRU-W、LRU-P，LRU-XO、LRU-XR。其中前面三條含義沒變，LRU-XO是重用對象鏈，當實例發布需要重用某個對象的時候（比如TRUNCATE等DDL操作后），這個對象的所有當前塊和臟塊被鏈入這個隊列，DBWR需要將臟塊寫入文件。CKPT會檢查這個隊列，當這個隊列變空的時候認為本次對象級的CKPT結束。LRU-XR主要用于對某個數據文件或者表空間進行CKPT，把一個范圍的臟塊鏈入該鏈，讓DBWR去寫盤，CKPT檢查這個鏈表，直到鏈表為空。
Oracle 9.2發現這樣設計后，object reuse的性能還是無法 徹底改善，甚至性能更差了，所以把LRU-XO鏈去掉了，5條鏈變又成了4條鏈。直到Oracle 10g這個才被徹底解決。OBJECT REUSE的算法再次改進，在WORKING SET中引入了Object hash chain結構kcbohtab * htabkcbwds，從而把一條LRU-XO鏈變成了一個HASH TABLE加N條鏈，每個對象有自己獨立的OBJECT REUSE鏈表。這樣對于一個對象的OBJECT REUSE操作就沒有任何性能問題了。
圖片
上面是11.1.0.7版本的Oracle的kcbwds結構，黃色部分就是我說的object queue hash table。在沒有這個hash table的時候，如果要把某個drop/truncate的對象相關的cache清理出去的時候，就需要遍歷shared_buffers，這樣情況下，緩沖區越大性能就越差。如果有了這個hash table，那么這個操作的性能就只與該對象在緩沖區中的緩沖塊的數量有關了。
實際上，對于這個問題，2015年的時候，就有人寫了Patch，只不過當時PG社區的大多數大佬認為超大內存數據庫系統只是小眾場景，頻繁做drop/truncate的系統也不多，因此沒必要在這方面做優化。
圖片
我圍觀了他們的討論，因為大多數大佬都認為場景十分有限，沒必要去優化這個操作，這件事就不了了之了。實際上在這件事上，這些PG大佬可能真的有點自負了，使用臨時表（不論是全局臨時表還是物理表充當臨時表，這方面的操作模式是類似的）在一些復雜的系統，比如ERP、財務、供應鏈管理、審計、工業流程管理等是很常見的操作。頻繁創建刪除截斷表對象也是很常見的操作。因此這個場景的優化還是十分必要的。
要想實現對此的優化其實也不難，因為PG沒有使用類似Oracle的表空間管理機制，因此不存在Object Reuse的問題，只需要不寫盤淘汰這些對象的shared buffers緩沖就可以了。這個操作完全可以做成延時的，backend刪除表后直接結束操作，淘汰臟塊的事情由CKPT后續延時完成就可以了。當某個對象被刪除時，只要記錄一下清單，當CKPT發現某個臟塊屬于這個對象時，直接拋棄就可以了。這個清單最好能寫入WAL，這樣系統故障恢復時可以比較快速地拋棄這些臟數據，如果WAL中沒有這些數據，也沒關系，只是效率低一點而已。
其實數據庫就是這樣一點點的在用戶場景中完善起來的，商用數據庫廠商十分注重收集這方面的需求，而開源數據庫因為用戶群體十分龐大，需求更為龐大，因此在實現某些功能的時候，選擇比較困難，無法像商用數據庫那么迅速的響應。
