Re: vacuum TOAST tables

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 <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Thursday, April 20, 2023 11:39 PM
To: senor <frio_cervesa(at)hotmail(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
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