Postgres - 大型/繁忙表上的真空未完成
我們在 AWS 上託管了一個相當活躍的 PG 數據庫。我們最近開始收到如下通知：
Transaction ID age reached 750 million. Autovacuum parameter values for [autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, autovacuum_naptime] are updated to make autovacuum more aggressive.我還注意到這個特定表的磁碟使用量正在快速增加。這是已用空間：
[ { "oid": "16413", "table_schema": "public", "table_name": "connections", "row_estimate": 1.01476e+07, "total_bytes": 518641270784, "index_bytes": 478458511360, "toast_bytes": 30646272, "table_bytes": 40152113152, "total": "483 GB", "index": "446 GB", "toast": "29 MB", "table": "37 GB" } ]然後我們對其他事情進行了一些分析，並註意到一個長期執行（5 天前）的真空過程：
[ { "pid": 14747, "duration": "14:11:41.259451", "query": "autovacuum: VACUUM ANALYZE public.connections (to prevent wraparound)", "state": "active" } ]（這實際上是一個新的，但看起來與這個相同，因為最後一個從未完成）。
為了確認，我看到該
connections表自 15 日以來沒有被自動清理，並且有很多事情要清理：[ { "relid": "16413", "schemaname": "public", "relname": "connections", "seq_scan": 19951154, "seq_tup_read": 226032655046, "idx_scan": 41705151351, "idx_tup_fetch": 375484186787, "n_tup_ins": 8029742, "n_tup_upd": 13217694302, "n_tup_del": 542670, "n_tup_hot_upd": 96750657, "n_live_tup": 10237553, "n_dead_tup": 887751401, "n_mod_since_analyze": 350036721, "last_vacuum": null, "last_autovacuum": "2019-06-15 17:05:51.526792+00", "last_analyze": null, "last_autoanalyze": "2019-06-15 17:06:27.310486+00", "vacuum_count": 0, "autovacuum_count": 4190, "analyze_count": 0, "autoanalyze_count": 4165 } ]我已經閱讀了很多關於為非常活躍的表配置
autovacuum_vacuum_scale_factor和不同的內容。autovacuum_analyze_scale_factor這一切都很好，但現在它似乎不會通過這個正在執行的。我還閱讀了有關優化的內容，
autovacuum_vacuum_cost_limit並autovacuum_vacuum_cost_delay使其在需要做的工作中更具侵略性。我嘗試為表格更改其中的一些內容，但是當我嘗試為該特定表格編寫任何值時，它就坐在那裡。
用吸塵器吸塵的最佳方法是什麼？
此外，重新啟動數據庫會對這一切產生影響嗎？
您沒有顯示“autovacuum_work_mem”和“maintenance_work_mem”的設置。9.4 中的預設設置非常低（64MB，每次通過索引只允許清理 11M 元組），除非 RDS（或您）更改它們。您需要將這些設置為給定您擁有的 RAM 量的最高值。
"index": "446 GB", "toast": "29 MB", "table": "37 GB"讓索引比表大 12 倍似乎很瘋狂（編輯：或者如果只有索引——我沒想過有很多索引）。這是一個普通的 btree 索引，還是 pg_trgm 之類的？索引是否以某種方式損壞？你知道你是怎麼陷入這種境地的嗎？
索引中的空間比表中的空間更難重用。一個給定的葉子頁面只能用於新的元組，如果新的元組與它已經擁有的元組接近相同的值範圍，或者如果頁面完全是空的。因此，如果鍵空間總是向一個方向移動（如序列），並且幾乎但不是全部的舊元組最終都被刪除，那麼您可能會留下一堆只包含一個元組的頁面每個，並且不能重複使用。表沒有這個問題，因為大部分為空的頁面可用於保存任何出現的元組。或者，如果您的表在某些時候非常臃腫但隨後被清理乾淨，則表可能縮小了但索引沒有。（如果表格的末尾碰巧有一大塊完全空的頁面，表格可能會縮小）。很難說這些事情中的哪一個是發生的事情。您可以使用索引上的pg_freespacemap以查看有多少頁面是完全空的，但在真空完成之前，這可能並不准確。
解決這個問題的最快方法，雖然有一些停機時間，但可能是啟動一個 VACUUM FULL 表——當你可以把它扔掉並重建時，你不想花很多時間清理一個無可救藥的臃腫索引它處於非臃腫狀態。VACUUM FULL 將阻塞，然後在另一個會話中，殺死 autovacuum worker（它持有一個阻塞 VACUUM FULL 的鎖）。您希望在殺死 autovac 工作人員時已經等待另一個命令，因為它將快速重新啟動，因此再次鎖定，除非已經有其他東西在等待抓住它。
我嘗試為表格更改其中的一些內容，但是當我嘗試為該特定表格編寫任何值時，它就坐在那裡。
更改這些特定於表的設置需要鎖定表。autovac 工作人員正在阻止該鎖。通常，autovac 會檢測到它何時阻塞其他東西並放棄鎖定，但
(to prevent wraparound)不會這樣做。因此，您需要殺死 autovac 工作人員才能進行此更改（這是此處所有內容的一般主題）。無論如何，我不建議更改特定於表的設置。如果您希望這是一次性的，只需執行手動 VACUUM 或 VACUUM FULL 即可完成。如果您希望它是永久性的，那麼至少根據給定的資訊，很難證明為什麼這個表需要與其他表不同的“autovacuum_vacuum_cost_delay”，所以只需在系統級別進行更改即可。如果您在系統級別更改它，它仍然不會在正在進行的 autovac 中間生效，您需要將其殺死以便下一個接收更改。
此外，重新啟動數據庫會對這一切產生影響嗎？
重新啟動會導致 autovac 失去它已經完成的部分工作，並重新開始。它不會完成任何事情，除非您還進行了有意義的配置更改，這些更改將在重新啟動後生效。