Обсуждение: vacuum TOAST tables
Hi All,
PostgreSQL v11.19-1PGDG.rhel7 (upgrading to 12+ is so far only talk)
CentOS 7.9
I see various recommendation for queries to return the tables most in need of a vacuum that exclude the TOAST tables with something like:
...AND n.nspname NOT IN ('pg_toast')
My real issue is autovacuum not keeping up in spite of seeming to have the resources to do so. My question at the moment is whether it is best to include TOAST tables when scripting a manual vacuum of oldest tables. I'm currently querying for a list of oldest X number of tables and feeding to 5-20 "threads" and monitoring resources. if it's in pg_toast namespace (which is all of them), I execute a vacuum freeze on the main table. Repeating this as necessary. All are TOAST and they belong to sets of tables that are created over a day and never updated after. These tables are months old. I've asked the developers to initiate vacuums so at the moment I'm more interested in understanding best practice in this scenario.
If I understand correctly, autovacuum handles tables and their associated TOAST tables separately but a manual vacuum will also vacuum the TOAST. When manually vacuuming does it matter whether it's the main table or TOAST?
I'm not where I have access but I this is close to what I'm using. I've toggled between using limit and relfrozedxid greater than X. I want to use the least resource intensive combination of query and vacuum.
SELECT c.oid::regclass, age(c.relfrozenxid), o.relname, o.relnamespace
FROM pg_class c
LEFT JOIN pg_class o on (c.relfilenode != 0 AND c.relfilenode = o.reltoastrelid)
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE c.relkind IN ('r', 't', 'p')
AND n.nspname IN ('public','pg_toast')
AND age(c.relfrozenxid) > ${max_age}
ORDER BY 2 DESC 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.
I appreciate any advice.
Thanks
Senor
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
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.albe@cybertec.at>
Sent: Thursday, April 20, 2023 11:39 PM
To: senor <frio_cervesa@hotmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: vacuum TOAST tables
Sent: Thursday, April 20, 2023 11:39 PM
To: senor <frio_cervesa@hotmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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
> 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