Re: Autovacuum loose ends

From: Tom Lane
Subject: Re: Autovacuum loose ends
Date: ,
Msg-id: 21268.1121364772@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Autovacuum loose ends  ("Matthew T. O'Connor")
Responses: Re: Autovacuum loose ends  ("Matthew T. O'Connor")
Re: Autovacuum loose ends  (Greg Stark)
Re: Autovacuum loose ends  (Hannu Krosing)
Re: Autovacuum loose ends  (Ron Mayer)
List: pgsql-hackers

Tree view

Autovacuum loose ends  (Tom Lane, )
 Re: Autovacuum loose ends  ("Matthew T. O'Connor", )
  Re: Autovacuum loose ends  (Tom Lane, )
   Re: Autovacuum loose ends  ("Matthew T. O'Connor", )
   Re: Autovacuum loose ends  (Greg Stark, )
   Re: Autovacuum loose ends  (Hannu Krosing, )
   Re: Autovacuum loose ends  (Ron Mayer, )
    Re: Autovacuum loose ends  (Tom Lane, )
 Re: Autovacuum loose ends  (Alvaro Herrera <-ip.org>, )
  Re: Autovacuum loose ends  (Tom Lane, )
  Re: Autovacuum loose ends  (Tom Lane, )
 Re: Autovacuum loose ends  (Alvaro Herrera <-ip.org>, )
  Re: Autovacuum loose ends  (Tom Lane, )
   Re: Autovacuum loose ends  (Alvaro Herrera <-ip.org>, )

"Matthew T. O'Connor" <> writes:
> Speaking of which, I think I mentioned this to Alvaro, but I guess it 
> just didn't make it in.  The pg_autovacuum table should have a few 
> additional columns that allow setting vacuum delay settings on a per 
> table basis.  I also think that there should be GUC settings for the 
> default autovacuum delay settings which an admin might want to be 
> separate from the system wide default vacuum delay settings.

I was thinking GUC settings only; is there a real use-case for
table-specific delay parameters?  ISTM the point of the delay parameters
for autovac is to put a lid on its impact on interactive response.  Seen
in that light, you do not care exactly which table it's hitting at the
moment.

>> * I'm still pretty concerned about the handling of shared catalogs.

> This was handled in the contrib version by only vacuuming shared 
> catalogs inside template1, however it would then analyze those tables in 
> each and every database.  Is there a reason this solution is not 
> adequate?

The problem is that now that we've invented the default postgres
database, it becomes more plausible to think about installations that
haven't got a template1 at all.  I'd prefer a solution that does not
assume the presence of any specific database.  ISTM reasonable to
process the shared catalogs symmetrically in every DB: look to see
if they need vacuuming or not.  The problem (which was also a problem
for the contrib version) is that the stats system fails to maintain
a single set of stats for a shared catalog --- operations get counted
under whichever DB they were issued from.  This means that autovac
will underestimate the need for vacuuming of a shared catalog, since
no matter where it looks from, it will see only a portion of the
true update activity.
        regards, tom lane



pgsql-hackers by date:

From: Simon Riggs
Date:
Subject: Re: [Bizgres-general] A Guide to Constraint Exclusion
From: "Luke Lonergan"
Date:
Subject: Re: multibyte regression tests