On 2/15/23 22:57, Mikhail Balayan wrote:
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?
Not sure if this applies but from:
https://www.postgresql.org/docs/11/release-11-18.html
Release 11.18
Avoid long-term memory leakage in the autovacuum launcher process (Reid
Thompson)
The lack of field reports suggests that this problem is only latent in
pre-v15 branches; but it's not very clear why, so back-patch the fix anyway.
Just in case, I'm using Postgresql version: 11.11.
Besides the above you are missing 8 releases of other fixes.
autovacuum_vacuum_cost_delay: 2ms
autovacuum_vacuum_cost_limit: 8000
Thank you.
BR,
Mikhael
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
