pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Florian Helmberger <fh@25th-floor.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Fri, 27 May 2011 11:07:05 -0400
Message-ID: <BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.com> (raw)
In-Reply-To: <9007.1306446600@sss.pgh.pa.us>
References: <4DD28F73.6010108@25th-floor.com>
	<25017.1306291669@sss.pgh.pa.us>
	<4DDCA801.70805@25th-floor.com>
	<19738.1306338472@sss.pgh.pa.us>
	<BANLkTi=973Arh-nXLO6YFP7_ZTCeNp-X7w@mail.gmail.com>
	<22497.1306341699@sss.pgh.pa.us>
	<BANLkTi=jcKSU5zR+JG2BvfLNCtezC9sb+g@mail.gmail.com>
	<15043.1306423500@sss.pgh.pa.us>
	<BANLkTikrCaoZCVrda6SNCUQFiYvvZX75BA@mail.gmail.com>
	<25097.1306426982@sss.pgh.pa.us>
	<BANLkTikxM6V5DUsXnDEs84z=oRMbyeoOgA@mail.gmail.com>
	<4DDE475B020000250003DD63@gw.wicourts.gov>
	<BANLkTikTbZ8gibj+X++vkdqkxxJCU6trWw@mail.gmail.com>
	<4DDE5033020000250003DD71@gw.wicourts.gov>
	<BANLkTikGu30z1rwYeN0NwuO8jMFWHzus+Q@mail.gmail.com>
	<4DDE7EB8020000250003DDA8@gw.wicourts.gov>
	<9007.1306446600@sss.pgh.pa.us>

On Thu, May 26, 2011 at 5:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> When we prune or vacuum a page, I don't suppose we have enough
>> information about that page's previous state to calculate a tuple
>> count delta, do we?  That would allow a far more accurate number to
>> be maintained than anything suggested so far, as long as we tweak
>> autovacuum to count inserts toward the need to vacuum.
>
> Well, that was the other direction that was suggested upthread: stop
> relying on reltuples at all, but use the stats collector's counts.
> That might be a good solution in the long run, but there are some
> issues:
>
> 1. It's not clear how using a current count, as opposed to
> time-of-last-vacuum count, would affect the behavior of the autovacuum
> control logic.  At first glance I think it would break it, since the
> basic logic there is "how much of the table changed since it was last
> vacuumed?".  Even if the equations could be modified to still work,
> I remember enough feedback control theory from undergrad EE to think that
> this is something to be seriously scared of tweaking without extensive
> testing.  IMO it is far more risky than what Robert is worried about.

Yeah, I think that would be broken.

> 2. You still have the problem that we're exposing inaccurate (or at
> least less accurate than they could be) counts to the planner and to
> onlooker clients.  We could change the planner to also depend on the
> stats collector instead of reltuples, but at that point you just removed
> the option for people to turn off the stats collector.  The implications
> for plan stability might be unpleasant, too.
>
> So that's not a direction I want to go without a significant amount
> of work and testing.

FWIW, I agree.  Your proposed solution is certainly better than trying
to do this; but it still seems a bit shaky to me.

Still, maybe we don't have a better option.  If it were me, I'd add an
additional safety valve: use your formula if the percentage of the
relation scanned is above some threshold where there's unlikely to be
too much skew.  But if the percentage scanned is too small, then don't
use that formula.  Instead, only update relpages/reltuples if the
relation is now larger; set relpages to the new actual value, and
scale up reltuples proportionately.

However, I just work here.  It's possible that I'm worrying about a
problem that won't materialize in practice.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



view thread (48+ messages)  latest in thread

Message-ID: <BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.com>
Permalink:  ../BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.com/
Also on:    postgresql.org/message-id/BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.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-admin@postgresql.org
  Cc: robertmhaas@gmail.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, fh@25th-floor.com, alvherre@commandprompt.com, gsstark@mit.edu, pgsql-hackers@postgresql.org
  Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
  In-Reply-To: <BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.com>
  References: <4DD28F73.6010108@25th-floor.com> <25017.1306291669@sss.pgh.pa.us> <4DDCA801.70805@25th-floor.com> <19738.1306338472@sss.pgh.pa.us> <BANLkTi=973Arh-nXLO6YFP7_ZTCeNp-X7w@mail.gmail.com> <22497.1306341699@sss.pgh.pa.us> <BANLkTi=jcKSU5zR+JG2BvfLNCtezC9sb+g@mail.gmail.com> <15043.1306423500@sss.pgh.pa.us> <BANLkTikrCaoZCVrda6SNCUQFiYvvZX75BA@mail.gmail.com> <25097.1306426982@sss.pgh.pa.us> <BANLkTikxM6V5DUsXnDEs84z=oRMbyeoOgA@mail.gmail.com> <4DDE475B020000250003DD63@gw.wicourts.gov> <BANLkTikTbZ8gibj+X++vkdqkxxJCU6trWw@mail.gmail.com> <4DDE5033020000250003DD71@gw.wicourts.gov> <BANLkTikGu30z1rwYeN0NwuO8jMFWHzus+Q@mail.gmail.com> <4DDE7EB8020000250003DDA8@gw.wicourts.gov> <9007.1306446600@sss.pgh.pa.us> <BANLkTinREie2cX2zqZPSEfUsZsdM+GjymQ@mail.gmail.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