升級到 postgresql 9.6 後的 autovacuum 挑戰
我們有一個包含幾十個數據庫的集群。當我們上個月升級到 9.6 時，它在 9.3 上執行了一年（隨著時間的推移而增長）。從那時起，數據庫將凍結大型表（具有 100k+ 行的表）的更新，從一分鐘到 30-60 分鐘。
具體來說，我們的一個程序在這樣一個表中一次更新幾列，在程式碼日誌中，我可以看到它在哪裡暫停和掛起，長達 30 分鐘，然後再繼續，好像什麼都沒有發生了，並在幾秒鐘內更新了許多連續的列，沒有這樣的掛起。
增加數據庫中的日誌記錄一開始並沒有顯示任何結果——伺服器和 postgres 中的記憶體使用分析顯示資源使用沒有什麼異常。最終，我能夠看到，使用調試級日誌記錄，postgresql 將通過清理所有數據庫，並且在 autovacuumer 命中該數據庫後，有問題的查詢將取消粘連，儘管不是在所有數據庫的第一個循環中，但經過幾次。從那時起，我一直在搞亂自動真空設置。我嘗試關閉自動吸塵器，因為它在 9.3 上已關閉，但不知何故我們活了下來——但這並沒有幫助。所以我打開了它，並且在了解它們時一直在更改各種設置。我得到了它，這樣生產伺服器就不會通過頻繁的自動清理、添加 16 個工作人員等方式掛起。
我正在粘貼我們的數據庫伺服器的一般設置 - 正常記憶體設置是使用 pgtune 創建的，我基本上一直在修補的 autovacuum 設置。
我正在學習，但我在黑暗中拍攝 - 如果有人可以就如何最好地處理分析（通過日誌等）和優化這個 9.6 實例的自動清理提供指導，這就是我所追求的。
官方網站和此處旨在顯示鎖的各種查詢根本不會出現任何等待程序 - 但我確實在 pg admin iv 儀表板中看到每個程序的每個條目都具有“AccessShareLock”和“RowExclusiveLock” ，在 Locks 下，當更新列的查詢正在執行時。
再一次，令人沮喪的是——我看不出一次更新幾十萬條記錄的列有什麼複雜的地方——應該很容易看出是什麼導致了掛起（關於自動清理） ，以及如何解決它。雖然下面的設置似乎在生產中起作用，但我不清楚原因，不知道如何改進它們，也不知道如何在開發伺服器上創建一組工作配置。感謝您的幫助。
autovacuum = on log_autovacuum_min_duration = 200 autovacuum_max_workers = 16 autovacuum_naptime = 1min autovacuum_vacuum_threshold = 5000 #autovacuum_analyze_threshold = 500 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 200000000 #autovacuum_multixact_freeze_max_age = autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = 2000 default_statistics_target = 100 maintenance_work_mem = 2920MB checkpoint_completion_target = 0.8 effective_cache_size = 22GB work_mem = 260MB wal_buffers = 16MB shared_buffers = 7680MB min_wal_size = 80MB max_wal_size = 2GB
我建議你改變你的策略。
具體來說，我們的一個程序在這樣一個表中一次更新幾列，並且在程式碼日誌中，我可以看到它在哪裡暫停和掛起，最多 30 分鐘，然後稍後再恢復，好像什麼都沒有發生了，並在幾秒鐘內更新了許多連續的列，沒有這樣的掛起。
一個這樣的更新可以很容易地使你的表的大小翻倍，因為每個
UPDATE基本上相當於一個DELETEoneINSERT，這使得每個刪除的行佔據無用的空間，直到它被VACUUM編輯並且該空間可以被重用於下一輪UPDATEs。你可以做：
- 更改策略並一次更新盡可能多的列。您的過程會更快，因為訪問數據的次數會少很多。
VACUUM在每一輪之後從您的程式碼中發出 aUPDATE，以便未使用的空間可用於下UPDATE一輪。- 在某些情況下，
UPDATE分批（比方說）1.000 執行 s，並在每批之後送出可能會緩解這種情況。這三種技術的任何組合都可能會簡化更新。