From: David Gould <daveg@sonic.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Alina Alexeeva <alexeeva@adobe.com>
Cc: Ullas Lakkur Raghavendra <lakkurra@adobe.com>
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
Date: Thu, 8 Mar 2018 00:11:34 -0800
Message-ID: <20180308001134.28498780@engels> (raw)
In-Reply-To: <CAMkU=1x420SEv60hrH=EOfvHZc_usXuK-8Yt8T3KnX7_U9zwfg@mail.gmail.com>
References: <20180117164916.3fdcf2e9@engels>
<CAMkU=1yQz4OEmoWSnY_wE03KdpoQ4zpzmjpyPDUQwz+tbsU21g@mail.gmail.com>
<20180304151857.11921586@engels>
<CAMkU=1x420SEv60hrH=EOfvHZc_usXuK-8Yt8T3KnX7_U9zwfg@mail.gmail.com>
On Wed, 7 Mar 2018 21:39:08 -0800
Jeff Janes <jeff.janes@gmail.com> wrote:
> As for preventing it in the first place, based on your description of your
> hardware and operations, I was going to say you need to increase the max
> number of autovac workers, but then I remembered you from "Autovacuum slows
> down with large numbers of tables. More workers makes it slower" (
> https://www.postgresql.org/message-id/20151030133252.3033.4249%40wrigleys.postgresql.org).
> So you are probably still suffering from that? Your patch from then seemed
> to be pretty invasive and so controversial.
We have been building from source using that patch for the worker contention
since then. It's very effective, there is no way we could have continued to
rely on autovacuum without it. It's sort of a nuisance to keep updating it
for each point release that touches autovacuum, but here we are.
The current patch is motivated by the fact that even with effective workers
we still regularly find tables with inflated reltuples. I have some theories
about why, but not really proof. Mainly variants on "all the vacuum workers
were busy making their way through a list of 100,000 tables and did not get
back to the problem table before it became a problem."
I do have a design in mind for a larger more principled patch that fixes the
same issue and some others too, but given the reaction to the earlier one I
hesitate to spend a lot of time on it. I'd be happy to discuss a way to try
to move forward though if any one is interested.
Your patch helped, but mainly was targeted at the lock contention part of the
problem.
The other part of the problem was that autovacuum workers will force a rewrite
of the stats file every time they try to choose a new table to work on.
With large numbers of tables and many autovacuum workers this is a significant
extra workload.
-dg
--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: daveg@sonic.net, jeff.janes@gmail.com, pgsql-hackers@lists.postgresql.org, alexeeva@adobe.com, lakkurra@adobe.com
Subject: Re: [patch] BUG #15005: ANALYZE can make pg_class.reltuples inaccurate.
In-Reply-To: <20180308001134.28498780@engels>
* 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