From: | Mikhail Balayan <mv(dot)balayan(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Automatic aggressive vacuum on almost frozen table takes too long |
Date: | 2023-02-16 06:57:37 |
Message-ID: | CAC2oM1bjBcZAaJ8UwwLMQ4TVOi-q0JJs6z47vmVHfXPz7aYe5A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I have a big table in the actively working system, in which nothing is
written for a long time, and nothing is read from it. Table size is 15GB
(data only), indexes 150GB.
Since the table does not change, after a while it crosses the
autovacuum_freeze_max_age and an aggressive vacuum is triggered. And it
would be OK, but vacuuming of the table takes a long time, despite the fact
that exactly the same scan was made a few days before and almost all pages
are marked as frozen, which is confirmed by data from the log:
automatic aggressive vacuum of table
"appdbname.appschemaname.applications": index scans: 1
pages: 0 removed, 2013128 remain, 0 skipped due to pins,
2008230 skipped frozen
tuples: 2120 removed, 32616340 remain, 0 are dead but not yet
removable, oldest xmin: 4111875427
buffer usage: 2005318781 hits, 19536511 misses, 23903 dirtied
avg read rate: 4.621 MB/s, avg write rate: 0.006 MB/s
system usage: CPU: user: 26398.27 s, system: 335.27 s, elapsed:
33029.00 s
That is, if I understand it correctly, it says that there were (and
actually are) 2013128 pages of which 2008230 were skipped, which leaves
4898 blocks to be scanned. I.e. it seems that the allocated 1GB
(autovacuum_work_mem) should be enough to handle that amount of blocks and
to avoid multiple scans of the indexes.
But, based on buffer usage, one can see that a huge amount of data is read,
greatly exceeding not only the number of remaining unfrozen blocks, but
also the size of the table and indexes taken together: 2 billion blocks,
more than 15TB.
Is this a bug in Postgresql or am I interpreting the log data wrong?
Just in case, I'm using Postgresql version: 11.11.
autovacuum_vacuum_cost_delay: 2ms
autovacuum_vacuum_cost_limit: 8000
Thank you.
BR,
Mikhael
From | Date | Subject | |
---|---|---|---|
Next Message | Ajin Cherian | 2023-02-16 07:54:15 | Re: Support logical replication of DDLs |
Previous Message | Ron | 2023-02-16 04:08:29 | Re: Multi-column index: Which column order |