Thank you Laurenz.
Current settings:
name | setting | unit | source
-------------------------------------+-----------+------+--------------------
autovacuum | on | | default
autovacuum_analyze_scale_factor | 0.1 | | default
autovacuum_analyze_threshold | 50 | | default
autovacuum_freeze_max_age | 200000000 | | default
autovacuum_max_workers | 15 | | configuration file
autovacuum_multixact_freeze_max_age | 400000000 | | default
autovacuum_naptime | 1 | s | configuration file
autovacuum_vacuum_cost_delay | 20 | ms | default
autovacuum_vacuum_cost_limit | 3000 | | configuration file
autovacuum_vacuum_scale_factor | 0.2 | | default
autovacuum_vacuum_threshold | 50 | | default
autovacuum_work_mem | -1 | kB | default
maintenance_work_mem | 2097152 | kB | configuration file
max_parallel_maintenance_workers | 2 | | default
vacuum_cleanup_index_scale_factor | 0.1 | | default
vacuum_cost_delay | 0 | ms | default
vacuum_cost_limit | 200 | | default
vacuum_cost_page_dirty | 20 | | default
vacuum_cost_page_hit | 1 | | default
vacuum_cost_page_miss | 10 | | default
vacuum_defer_cleanup_age | 0 | | default
vacuum_freeze_min_age | 50000000 | | default
vacuum_freeze_table_age | 150000000 | | default
vacuum_multixact_freeze_min_age | 5000000 | | default
vacuum_multixact_freeze_table_age | 150000000 | | default
I've compared 'vacuums per hour' with autovacuum_vacuum_cost_delay at 0 and
default with no change.
My gut says there's an issue with the stats collection. Seems like autovacuum
is not correctly updating the stats. As I mentioned in previous posts, I often
see long-running active autovacuums in pg_stat_activity on small tables and
nothing in pg_stat_progress_vacuum. I found one reference (I can't find now) to
a similar issue where the suspected problem was a huge and unintended number of
tables. I eliminated over half and it did improve. I'm working to convince the
powers that be to upgrade to pg15 for the new stats. Not holding my breath.
I'll get back to the other thread when I get time to focus on it. For now my
question is answered and I'll modify my scripts
Your help is appreciated. If you have additional ideas, I'm all ears.
Thanks,
Senor
________________________________
From: Laurenz Albe <[email protected]>
Sent: Thursday, April 20, 2023 11:39 PM
To: senor <[email protected]>; [email protected]
<[email protected]>
Subject: Re: vacuum TOAST tables
On Fri, 2023-04-21 at 04:37 +0000, senor wrote:
> PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)
> CentOS 7.9
>
> If I understand correctly, autovacuum handles tables and their associated
> TOAST tables separately
> but a manual vacuum will also vacuum the TOAST.
That is correct.
> When manually vacuuming does it matter whether it's the main table or TOAST?
It makes a difference. As superuser you can directly VACUUM a toast table, and
that will
no VACUUM the table it belongs to. However, if you VACUUM the main table, both
tables
will be vacuumed, which is more work. So if you only need VACUUM on the toast
table,
doing that directly will be cheaper.
> I've posted before about these same systems. It'll get to age(datfrozenxid) >
> 2,000,000,000 and
> is not able to keep up until I get it back down to under ~600000000. Then it
> starts humming along
> as if I "cleared" something.
That sounds unsavory. Did you set "autovacuum_freeze_max_age" to an extremely
high value?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
