pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Mikhail Balayan <mv.balayan@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: Automatic aggressive vacuum on almost frozen table takes too long
Date: Thu, 16 Feb 2023 07:44:11 -0800
Message-ID: <9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com> (raw)
In-Reply-To: <CAC2oM1bjBcZAaJ8UwwLMQ4TVOi-q0JJs6z47vmVHfXPz7aYe5A@mail.gmail.com>
References: <CAC2oM1bjBcZAaJ8UwwLMQ4TVOi-q0JJs6z47vmVHfXPz7aYe5A@mail.gmail.com>

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@aklaver.com






view thread (9+ messages)  latest in thread

Message-ID: <9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com>
Permalink:  ../9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com/
Also on:    postgresql.org/message-id/9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, mv.balayan@gmail.com
  Subject: Re: Automatic aggressive vacuum on almost frozen table takes too long
  In-Reply-To: <9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com>
  References: <CAC2oM1bjBcZAaJ8UwwLMQ4TVOi-q0JJs6z47vmVHfXPz7aYe5A@mail.gmail.com> <9bdac359-3f27-e800-adfc-6f0d4f946406@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox