Re: Automatic aggressive vacuum on almost frozen table takes too long

Adrian,
thanks for pointing out the fix. We are just about to update to 11.18 next
month.

Mikhael

On Thu, 16 Feb 2023 at 23:44, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>
>