Thread: Re: [COMMITTERS] pgsql: Avoid losing track of data for shared tables in pgstats.

"Alvaro Herrera" <alvherre@postgresql.org> writes:

> Log Message:
> -----------
> Avoid losing track of data for shared tables in pgstats.  Report by Michael
> Fuhr, patch from Tom Lane after a messier suggestion by me.

When does this bug date to? is it possible it's related to the performance
drop immediately following a vacuum analyze we've been seeing?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Gregory Stark wrote:
> 
> "Alvaro Herrera" <alvherre@postgresql.org> writes:
> 
> > Log Message:
> > -----------
> > Avoid losing track of data for shared tables in pgstats.  Report by Michael
> > Fuhr, patch from Tom Lane after a messier suggestion by me.
> 
> When does this bug date to?

It was in 8.1.  I didn't verify whether it affects on 8.0; I think we
separated the shared tables in pgstats in 8.1, with the advent of
autovacuum, so I assume it doesn't.  The patch doesn't apply cleanly to
8.0 anyway, and I decided not to spent much time on it seeing that
nobody has noticed it in years.


> is it possible it's related to the performance drop immediately
> following a vacuum analyze we've been seeing?

I don't think so, unless you were counting on pgstats data of shared
tables for something.  The optimizer, for one, doesn't, so I doubt it
would affect query planning.  And it would only affect you if your
queries were using shared tables, which I very much doubt ...

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> Gregory Stark wrote:
>
> > is it possible it's related to the performance drop immediately
> > following a vacuum analyze we've been seeing?
>
> I don't think so, unless you were counting on pgstats data of shared
> tables for something.  The optimizer, for one, doesn't, so I doubt it
> would affect query planning.  And it would only affect you if your
> queries were using shared tables, which I very much doubt ...

Does anything use the pgstats data for anything other than presenting feedback
to users?

Autovacuum uses it to estimate when tables should be vacuumed right? This
wouldn't have caused autovacuum to go nuts vacuuming these tables would it?
But I doubt even then that it could consume much i/o bandwidth.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Gregory Stark wrote:
> "Alvaro Herrera" <alvherre@commandprompt.com> writes:
> 
> > Gregory Stark wrote:
> >
> > > is it possible it's related to the performance drop immediately
> > > following a vacuum analyze we've been seeing?
> >
> > I don't think so, unless you were counting on pgstats data of shared
> > tables for something.  The optimizer, for one, doesn't, so I doubt it
> > would affect query planning.  And it would only affect you if your
> > queries were using shared tables, which I very much doubt ...
> 
> Does anything use the pgstats data for anything other than presenting feedback
> to users?

Not that I know of.

> Autovacuum uses it to estimate when tables should be vacuumed right?

Yep

> This wouldn't have caused autovacuum to go nuts vacuuming these tables
> would it?  But I doubt even then that it could consume much i/o
> bandwidth.

Yes but keep in mind that these are only the shared tables: pg_database,
pg_authid, pg_shdepend, etc.  Those are not tables that you're going to
use regularly, much less _bloat_ regularly that they need frequent
vacuuming.

Maybe pg_shdepend, because it would be used when creating temp tables.

-- 
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"Postgres is bloatware by design: it was built to housePhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)

On Thu, Jun 07, 2007 at 11:46:17PM +0100, Gregory Stark wrote:
> Does anything use the pgstats data for anything other than presenting feedback
> to users?
> 
> Autovacuum uses it to estimate when tables should be vacuumed right? This
> wouldn't have caused autovacuum to go nuts vacuuming these tables would it?
> But I doubt even then that it could consume much i/o bandwidth.

I discovered this problem after noticing that pg_shdepend had gotten
horribly bloated -- apparently due to heavy use of temporary tables
by an application whose performance I was investigating -- despite
autovacuum being enabled.  When I looked at the statistics for
pg_shdepend the values for n_tup_{ins,upd,del} were much lower than
I expected.  After watching the numbers grow for a few minutes I
saw them reset; after observing this behavior several times I was
able to correlate the resets with vacuums of other tables.

Since the statistics for pg_shdepend rarely got high enough to
trigger an autovacuum that table was almost never being vacuumed.
I suggested to the DBA that he vacuum it manually; after five minutes
the vacuum completed and the application's performance improved
immediately.

INFO:  "pg_shdepend": found 8475403 removable, 3907 nonremovable row versions in 76783 pages

-- 
Michael Fuhr

On Thu, Jun 07, 2007 at 06:09:36PM -0400, Alvaro Herrera wrote:
> Gregory Stark wrote:
> > When does this bug date to?
> 
> It was in 8.1.  I didn't verify whether it affects on 8.0; I think we
> separated the shared tables in pgstats in 8.1, with the advent of
> autovacuum, so I assume it doesn't.

Doesn't appear to, at least not using the test case I found for 8.1
and later.

-- 
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Thu, Jun 07, 2007 at 06:09:36PM -0400, Alvaro Herrera wrote:
>> Gregory Stark wrote:
>>> When does this bug date to?
>> 
>> It was in 8.1.  I didn't verify whether it affects on 8.0; I think we
>> separated the shared tables in pgstats in 8.1, with the advent of
>> autovacuum, so I assume it doesn't.

> Doesn't appear to, at least not using the test case I found for 8.1
> and later.

Yeah, the separate hashtable for shared rels was added for 8.1:
http://archives.postgresql.org/pgsql-committers/2005-07/msg00627.php
        regards, tom lane