Re: pg_autovacuum w/ dbt2 - Mailing list pgsql-hackers

The overall throughput is better for a run like this:http://www.osdl.org/projects/dbt2dev/results/dev4-010/207/

A drop from 3865 to 2679 (31%) by just adding pg_autovacuum.  That's
what I meant by "not good". :)

I'll start with the additional debug messages, with 8.0rc2, before
I start changing the other settings, if that sounds good.

Mark

On Tue, Dec 21, 2004 at 02:33:57PM -0500, Matthew T. O'Connor wrote:
> Mark Wong wrote:
> 
> >After all this time I finally got around to vacuuming the database
> >with dbt2 with pg_autovacuum. :)
> >    http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/
> >  
> >
> Thanks!
> 
> >Doesn't look so good though, probably because I'm not using optimal
> >settings with pg_autovacuum.  So far I have only tried the default
> >settings (running without any arguments, except -D).
> >  
> >
> I don't know what you mean by "Not Good" since I don't have graphs from 
> a similar test without pg_autovacuum handy.  Do you have a link to such 
> a test?
>
> As for better pg_autovacuum settings, It appears that the little 
> performance dips are happening about once every 5 minutes, which if I 
> remember correctly is the default sleep time.  You might try playing 
> with the lazy vacuum settings to see if that smooths out the curve.  
> Beyond that all you can do is play with the thresholds to see if there 
> is a better sweet spot than the defaults (which by the way I have no 
> confidence in, they were just conservative guesses)
> 
> >The only thing that's peculiar is a number of unexpected rollbacks
> >across all of the transactions.  I suspect it was something to do with
> >these messages coming from pg_autovacuum:
> >
> >[2004-12-20 15:48:18 PST] ERROR:   Can not refresh statistics information from the database dbt2.
> >[2004-12-20 15:48:18 PST]          The error is [ERROR:  failed to re-find parent key in "pk_district"
> >]
> >  
> >
> Not sure what this is all about, but if you turn up the debug level to 4 
> or greater (pg_autovacuum -d4), pg_autovacuum will log the query that is 
> causing the problems, that would be helpful output to have.
> 
> >This is with 8.0rc1.  I can get rc2 installed since it just came out.
> >So let me know what I can try and what not.
> >  
> >
> I don't think anything has changed for pg_autovacuum between rc1 and rc2.
> 
> 
> thanks again for the good work!!!