|
Here some more details related to the toast table: { "analyze_count": 0, "autoanalyze_count": 0, "autovacuum_count": 22, "idx_scan": 1464881, "idx_tup_fetch": 363681753, "last_analyze": null, "last_autoanalyze": null, "last_autovacuum": "2024-06-19T17:45:02.564937+00:00", "last_vacuum": null, "n_dead_tup": 12, "n_live_tup": 819294, "n_mod_since_analyze": 225250407, "n_tup_del": 112615126, "n_tup_hot_upd": 0, "n_tup_ins": 112635281, "n_tup_upd": 0, "relid": "27240", "relname": "pg_toast_27236", "schemaname": "pg_toast", "seq_scan": 0, "seq_tup_read": 0, "vacuum_count": 0 } From: Shenavai, Manuel <manuel.shenavai@xxxxxxx>
Hi, Thanks for the suggestions. I found the following details to our autovacuum (see below). The related toast-table of my table shows some logs related the vacuum. This toast seems to consume all the data (27544451 pages
* 8kb ≈ 210GB ) Any thoughts on this? Best regards, Autovacuum details Details from pg_stat_all_tables: { "analyze_count": 0, "autoanalyze_count": 11, "autovacuum_count": 60, "idx_scan": 1925218, "idx_tup_fetch": 1836820, "last_analyze": null, "last_autoanalyze": "2024-06-19T09:39:50.680818+00:00", "last_autovacuum": "2024-06-19T09:41:50.58592+00:00", "last_vacuum": null, "n_dead_tup": 120, "n_live_tup": 9004, "n_mod_since_analyze": 474, "n_tup_del": 84, "n_tup_hot_upd": 5, "n_tup_ins": 118, "n_tup_upd": 15180, "relid": "27236", "relname": "my_tablename", "schemaname": "public", "seq_scan": 2370, "seq_tup_read": 18403231, "vacuum_count": 0 } From the server logs, I found autocacuum details for my toast table (pg_toast_27236): { "category": "PostgreSQLLogs", "operationName": "LogEvent", "properties": { "errorLevel": "LOG", "message": "2024-06-19 17:45:02 UTC-66731911.22f2-LOG: automatic vacuum of table \"0ecf0241-aab3-45d5-b020-e586364f810c.pg_toast.pg_toast_27236\":
index scans: 1 pages: 0 removed, 27544451 remain, 0 skipped due to pins, 27406469 skipped frozen tuples: 9380 removed, 819294 remain, 0 are dead but not yet removable, oldest xmin: 654973054 buffer usage: 318308 hits, 311886 misses, 2708 dirtied avg read rate: 183.934 MB/s, avg write rate: 1.597 MB/s system usage: CPU: user: 1.47 s, system: 1.43 s, elapsed: 13.24 s", "processId": 8946, "sqlerrcode": "00000", "timestamp": "2024-06-19 17:45:02.564 UTC" }, "time": "2024-06-19T17:45:02.568Z" } Best regards, Manuel From: Achilleas Mantzios <a.mantzios@xxxxxxxxxxxxxxxxxxxx>
Στις 20/6/24 19:46, ο/η Shenavai, Manuel έγραψε:
Your only assumption should be the official manual, and other material such as books, articles from reputable sources, even reading the source as a last resort could be considered. For starters : do you have autovacuum enabled ? If not, then enable this. Then monitor for vacuum via pg_stat_user_tables, locate the tables that you would expect vacuum to have happened but did not, then consider autovacuum tuning. Watch the logs for lines such as :
<N> dead row versions cannot be removed yet, oldest xmin: <some xid> those are held from being marked as removed, due to being visible by long running transactions. Monitor for those transactions. You have to monitor (if this is the case) about autovacuum being killed and not allowed to do its job.
-- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only) |
