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

From: Mikhail Balayan <mv(dot)balayan(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Automatic aggressive vacuum on almost frozen table takes too long
Date: 2023-02-17 01:23:03
Message-ID: CAC2oM1bLKRC69A56NShPGYJJBns4fACtiM5OF2nuMC_8wSXetA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Mikhail Balayan 2023-02-17 01:40:22 Re: Automatic aggressive vacuum on almost frozen table takes too long
Previous Message Ken Tanzer 2023-02-16 22:47:20 Re: How to avoid Trigger ping/pong / infinite loop