Re: DB grow overtime with autovacuum (postgres 9.6.9)

Thank you Graham,

i ran vacuum verbose analyze to one of the problematic table , also
SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

1) i dont find xmin details in "vacuum verbose analyze"
2) i have one "session" return from the above select which is our DB

Any thoughts?
Unfortunately we dont have plan to upgrade postgresql

Thank you for all your support !

Avihai

manager=# vacuum verbose analyze cm_file;
INFO: vacuuming "public.cm_file"
INFO: scanned index "cm_file_pkey" to remove 8626 row versions
DETAIL: CPU 0.05s/0.49u sec elapsed 0.54 sec
INFO: scanned index "cm_file_cur_job_lock_holder_type_multi_idx" to remove
8626 row versions
DETAIL: CPU 0.10s/0.41u sec elapsed 0.52 sec
INFO: scanned index "cm_file_file_name_idx" to remove 8626 row versions
DETAIL: CPU 0.14s/0.54u sec elapsed 0.69 sec
INFO: scanned index "cm_file_format_idx" to remove 8626 row versions
DETAIL: CPU 0.13s/0.42u sec elapsed 0.55 sec
INFO: scanned index "cm_file_fs_id_bucket_id_multi_idx" to remove 8626 row
versions
DETAIL: CPU 0.14s/0.48u sec elapsed 0.63 sec
INFO: scanned index "cm_file_fs_id_index_multi_idx" to remove 8626 row
versions
DETAIL: CPU 0.06s/0.51u sec elapsed 0.57 sec
INFO: scanned index "cm_file_id_idx" to remove 8626 row versions
DETAIL: CPU 0.06s/0.50u sec elapsed 0.57 sec
INFO: scanned index "cm_file_state_idx" to remove 8626 row versions
DETAIL: CPU 0.11s/0.38u sec elapsed 0.50 sec
INFO: scanned index "cm_file_type_idx" to remove 8626 row versions
DETAIL: CPU 0.10s/0.39u sec elapsed 0.49 sec
INFO: "cm_file": removed 8626 row versions in 6054 pages
DETAIL: CPU 0.00s/0.04u sec elapsed 0.04 sec
INFO: index "cm_file_pkey" now contains 3689623 row versions in 31036 pages
DETAIL: 8275 index row versions were removed.
409 index pages have been deleted, 405 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_cur_job_lock_holder_type_multi_idx" now contains
3689624 row versions in 56319 pages
DETAIL: 8626 index row versions were removed.
677 index pages have been deleted, 670 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_file_name_idx" now contains 3689639 row versions in
57169 pages
DETAIL: 7709 index row versions were removed.
1261 index pages have been deleted, 1245 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_format_idx" now contains 3689640 row versions in
67308 pages
DETAIL: 8626 index row versions were removed.
370 index pages have been deleted, 366 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_fs_id_bucket_id_multi_idx" now contains 3689644 row
versions in 85170 pages
DETAIL: 8626 index row versions were removed.
510 index pages have been deleted, 503 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_fs_id_index_multi_idx" now contains 3689644 row
versions in 43025 pages
DETAIL: 8626 index row versions were removed.
1271 index pages have been deleted, 1257 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_id_idx" now contains 3689646 row versions in 40192
pages
DETAIL: 8561 index row versions were removed.
653 index pages have been deleted, 647 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_state_idx" now contains 3689647 row versions in 57357
pages
DETAIL: 8626 index row versions were removed.
640 index pages have been deleted, 630 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "cm_file_type_idx" now contains 3689649 row versions in 56323
pages
DETAIL: 8626 index row versions were removed.
683 index pages have been deleted, 673 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "cm_file": found 34184 removable, 2378138 nonremovable row versions
in 130243 out of 194141 pages
DETAIL: 585 dead row versions cannot be removed yet.
There were 1595823 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1.24s/5.08u sec elapsed 6.35 sec.
INFO: vacuuming "pg_toast.pg_toast_17260"
INFO: index "pg_toast_17260_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_17260": found 0 removable, 0 nonremovable row versions in
0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.cm_file"
INFO: "cm_file": scanned 30000 of 194141 pages, containing 568983 live
rows and 147 dead rows; 30000 rows in sample, 3682098 estimated total rows
VACUUM

manager=# SELECT pid, datname, usename, state, backend_xmin
manager-# FROM pg_stat_activity
manager-# WHERE backend_xmin IS NOT NULL
manager-# ORDER BY age(backend_xmin) DESC;
pid | datname | usename | state | backend_xmin
-------+---------+---------+--------+--------------
33333 | manager | fabrix | active | 698159508
(1 row)

On Tue, Mar 1, 2022 at 3:23 PM Graham Myers <gmyers(at)retailexpress(dot)com>
wrote:

> You need to find why the autovacuum is not completing. Use the following
> to find the tables that have not been updated - its more than likely to be
> the xmin horizon issue due to long running uncommitted transactions.
>
>
>
> WITH constants
>
> AS (SELECT
>
>
> current_setting('autovacuum_vacuum_scale_factor')::float8 AS
> autovacuum_vacuum_scale_factor,
>
> current_setting('autovacuum_vacuum_threshold')::float8 AS
> autovacuum_vacuum_threshold,
>
> 1000000 AS
> autovacuum_vacuum_threshold_trigger
>
> )
>
> SELECT
>
> t.schemaname AS
> "SchemaName",
>
> t.relname AS
> "TableName",
>
> TO_CHAR(t.n_tup_ins, 'fm999G999G999G990') AS
> "Inserts",
>
> TO_CHAR(t.n_tup_upd, 'fm999G999G999G990') AS
> "Updates",
>
> TO_CHAR(t.n_tup_del, 'fm999G999G999G990') AS
> "Deletes",
>
> TO_CHAR(c.reltuples, 'fm999G999G999G990') AS
> "AnalyzedTuples",
>
> CASE WHEN c.reltuples < t.n_live_tup THEN '<' WHEN c.reltuples >
> t.n_live_tup THEN '>' ELSE '' END
>
> AS
> "Stale",
>
> TO_CHAR(t.n_live_tup,'fm999G999G999G990') AS
> "LiveTuples",
>
> TO_CHAR(t.n_dead_tup,'fm999G999G999G990') AS
> "DeadTuples",
>
>
> TO_CHAR(LEAST(99.999,n_dead_tup::float4/GREATEST(1,n_live_tup)::float4*100),'fm990D00%')
>
>
> AS
> "DeadRatio", --Limit to 1000%
>
> TO_CHAR(c.reltuples *
> COALESCE(SPLIT_PART(c.reloptions[1],'=',2)::float8,autovacuum_vacuum_scale_factor)
> + autovacuum_vacuum_threshold,'fm999G999G990')
>
> AS
> "AutoVacuumTrigger",
>
> CASE WHEN t.n_dead_tup > c.reltuples *
> COALESCE(SPLIT_PART(c.reloptions[1],'=',2)::float8,autovacuum_vacuum_scale_factor)
> + autovacuum_vacuum_threshold THEN '*' ELSE '' END
>
> AS
> "ShouldVacuum",
>
>
> TO_CHAR(COALESCE(SPLIT_PART(c.reloptions[1],'=',2)::float8,autovacuum_vacuum_scale_factor)*100,'fm999G990D00%')
>
> AS
> "CurrentScaleFactor",
>
> CASE WHEN c.reltuples *
> COALESCE(SPLIT_PART(c.reloptions[1],'=',2)::float8,autovacuum_vacuum_scale_factor)
> + autovacuum_vacuum_threshold > autovacuum_vacuum_threshold_trigger
>
> THEN
> TO_CHAR(autovacuum_vacuum_threshold_trigger,'fm999G999G990') --threshold
> instead of scale factor
>
> ELSE
> TO_CHAR(COALESCE(SPLIT_PART(c.reloptions[1],'=',2)::float8,autovacuum_vacuum_scale_factor)*100,'fm999G990D00%')
>
> END AS
> "SuggestedScaleFactor",
>
> t.last_analyze,
>
> t.last_autoanalyze,
>
> t.last_vacuum,
>
> t.last_autovacuum,
>
> t.analyze_count AS analyzecount,
>
> t.autoanalyze_count AS autoanalyzecount,
>
> t.vacuum_count AS vacuumcount,
>
> t.autovacuum_count AS autovacuumcount,
>
> pg_size_pretty(pg_total_relation_size(c.oid)) AS
> totalSize,
>
> pg_size_pretty(pg_indexes_size(c.oid)) AS
> indexSize,
>
> pg_size_pretty(COALESCE(pg_total_relation_size(c.reltoastrelid),0))
> AS ToastSize
>
> FROM pg_stat_user_tables AS t
>
> JOIN pg_class AS c ON c.relname=t.relname
>
> JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace AND
> n.nspname=t.schemaname
>
> CROSS JOIN constants
>
> WHERE (t.n_live_tup > 0 AND
>
> t.n_dead_tup > GREATEST(100000,c.reltuples *
> autovacuum_vacuum_scale_factor + autovacuum_vacuum_threshold) --more than
> 10k dead tuples or dead tuples exceed threshold
>
> )
>
> -- OR c.reltuples * autovacuum_vacuum_scale_factor +
> autovacuum_vacuum_threshold > autovacuum_vacuum_threshold_trigger -- the
> autovacuum threshold is over threshold - might need reducing
>
> ORDER BY 1,2;
>
>
>
> If you manually vacuum verbose analyze for one of the tables this reports
> on, it should then tell you the minimum xmin value (which is the horizon).
>
>
>
> If you then look in pg_stat_activity it should tell you the transactions
> that are preventing the horizon from moving forward
>
> select * from pg_stat_activity where backend_xmin::text::bigint <= <*minimum_xmin
> value here*> order by xact_start
>
> eg
>
> select * from pg_stat_activity where backend_xmin::text::bigint <=
> 422050991 order by xact_start
>
>
>
>
>
> regards
>
> Captain.Glumbo 😊
>
>
>
> Graham Myers​
>
>
> *From:* Avihai Shoham <avihai(dot)shoham(at)gmail(dot)com>
> *Sent:* 01 March 2022 12:21
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* DB grow overtime with autovacuum (postgres 9.6.9)
>
>
>
> Hi All,
>
>
>
> We use postgres 9.6.9
>
> we set the following autovacuum setting , but still the DB grow overtime
>
>
>
> autovacuum = on
> work_mem= 50MB
> wal_buffers= -1
> synchronous_commit=off
> log_autovacuum_min_duration = 0
> autovacuum_vacuum_scale_factor = 0.1
> autovacuum_analyze_scale_factor = 0.05
> autovacuum_vacuum_threshold = 25
> autovacuum_max_workers= 6
> autovacuum_naptime = 10s
> autovacuum_vacuum_cost_delay = 10ms
> autovacuum_vacuum_cost_limit = -1
> autovacuum_freeze_max_age = 1000000000
> autovacuum_multixact_freeze_max_age = 400000000
> autovacuum_work_mem = -1
> autovacuum_analyze_threshold = 10
>
>
>
> we have
>
>
>
> any idea ?
>
> DB size grew from 11G to 25G in 2wks.
>
> I read that we may need to increase the max_fsm_pages setting. not sure
> if it is needed if we have autovacuum or how to do it if needed?
>
>
>
> Thank you all
>
> Avihai
>