Re: BUG #17399: Dead tuple number stats not updated on long running queries

From: Soni M <diptatapa(at)gmail(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #17399: Dead tuple number stats not updated on long running queries
Date: 2022-02-11 04:33:42
Message-ID: CAAMgDXmL7mrXKDzQQN940rQBZdHUCuTU5kq99zV2Ag3SLZvoBA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Ah yes, it is the dead tuple cannot be removed, because long running
transactions still see them.
Something that lead to my confusion above is pg_stat_progress_vacuum, each
time autovacuum/manual vacuum launch during long running transaction, it
reports that num_dead_tuples

On Fri, Feb 11, 2022 at 7:32 AM Andres Freund <andres(at)anarazel(dot)de> wrote:

> Hi,
>
> On 2022-02-08 01:40:32 +0000, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 17399
> > Logged by: Soni
> > Email address: diptatapa(at)gmail(dot)com
> > PostgreSQL version: 13.5
> > Operating system: Red Hat Enterprise Linux release 8.5 (Ootpa)
> > Description:
> >
> > Hello All,
> > I think I found a bug.
> >
> > While there are long running queries, a vacuum that start and end during
> the
> > long running queries, the stats of pg_stat_user_tables.n_dead_tup not
> > updated. The real dead tuple on the table is cleaned up, but not the
> > stats.
>
> Are you saying that pg_stat_user_tables.n_dead_tup is not updated for
> changes
> done by the long running transactions, or that it is not getting updated at
> all, even if other transactions that performed modifications commit?
>
> It is correct and expected that the changes by currently running
> transactions
> are not reflected in the stats subsystem - and they can't really. Whether
> changes by such transactions end up as "live" or "dead" rows differs
> between
> that transaction committing and aborting.
>
> I just verified that indeed n_dead_tup gets updated after a transaction
> commits, even if there are other long running transactions.
>
>
>
> > So, if dead tuple percentage on pg_stat_user_tables is above
> > autovacuum_vacuum_scale_factor, then the autovacuum keeps triggered
> during
> > the long running queries.
>
> The reason for this likely is not that pg_stat_user_tables.n_dead_tup is
> not
> updated, but that existing dead tuples cannot be yet be removed, because
> the
> longrunning transaction might still see them.
>
> If you enable autovacuum logging, you can see (output differs a bit in
> older
> versions, but the concept is similar) something like this:
>
> 2022-02-10 16:27:47.927 PST [61256][autovacuum worker][6/161:0] LOG:
> automatic vacuum of table "postgres.public.large": index scans: 0
> pages: 0 removed, 15610 remain, 0 skipped due to pins, 0 skipped
> frozen
> tuples: 0 removed, 2400000 remain, 1400000 are dead but not yet
> removable, oldest xmin: 739
> ...
>
> Note the "1400000 are dead but not yet removable" bit. In this case there
> are
> 1.4M dead rows that can't be cleaned up due to the longrunning transaction
> /
> query. Because of that autovacuum will be started again and again, until
> the
> dead rows can actually be cleaned up.
>
> Once the longrunning transaction ends, autovacuum will run once more:
>
> 2022-02-10 16:32:04.292 PST [61703][autovacuum worker][6/261:749] LOG:
> automatic vacuum of table "postgres.public.large": index scans: 1
> pages: 2335 removed, 13275 remain, 0 skipped due to pins, 0
> skipped frozen
> tuples: 1400000 removed, 2000000 remain, 0 are dead but not yet
> removable, oldest xmin: 749
>
> and because 1.4M tuples were removed, n_dead_tup changes to 0, and
> everyone is
> happy again.
>
> Greetings,
>
> Andres Freund
>

--
Regards,

Soni Maula Harriz

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Soni M 2022-02-11 04:37:10 Re: BUG #17399: Dead tuple number stats not updated on long running queries
Previous Message Tom Lane 2022-02-11 04:03:21 Re: ERROR: XX000: variable not found in subplan target list