Re: pg 8.1.2 performance issue

On Sun, Mar 26, 2006 at 11:27:33AM -0500, Matthew T. O'Connor wrote:
> >>>>The table has 6800 rows over 18000 pages, and is getting a
> >>>>minimum of many tens of thousands of updates per day with
> >>>>queries like this:
> >>>If you're updating that much, how often are you running
> >>>'analyze'? Are you running autovacuum? How often?
> >>I count on the built-in autovacuum to do do analyzes (per
> >>8.1.2 docs).  I'm running autovacuum with the following
> >>non-default parameters:
> >>
> >>    autovacuum = on
> >>    autovacuum_naptime = 600 # 10 minutes
> >
> >I also have these non-default settings:
> >
> >    autovacuum_vacuum_cost_delay = 500
> >    autovacuum_vacuum_cost_limit = 200
>
> Not totally sure, but it sounds like the table isn't getting vacuumed
> often enough.  To help, you might reduce the naptime from 10 minutes to

Just look at the number of rows an the number of pages; the table has a
huge amount of bloat, and all the indexes will as well. Best bet at this
point to get things under control is a VACUUM FULL and a REINDEX.

> 5.  But I think the cost_delay settings might be the larger problem.  If
> I remember correctly, even small values here tend to greatly increase
> the time it takes vacuum commands to complete, so you might try backing
> down those settings.  Can you tell from the log files how often
> autovacuum is actually taking actions, or how long those actions are taking?
>
> Does anyone out there have any empirical data on good
> autovacuum_*_cost_* settings?  I would be curious to hear about them.

Even 5 minutes is too long to sleep in this case; I'd go with something
closer to 10 seconds. The default threasholds won't work either; I'd cut
autovacuum_*_(scale_factor|threshold) in half. And yes, that cost delay
is way too large, I'd go with 20-50, given that you've got "gobs of IO".

Finally, PostgreSQL just flat-out doesn't handle things like webapp
session tables well at all, because of the high update volume. You
should consider other alternatives. Someone did point me to a session
manager that allows you to do most work in memory, only flushing to
permanent storage (ie: the database) periodically, that would probably
be a good option for you. Another possibility is using something like
SQLite just for storing the session info (though I think it's MVCC based
as well, so it might have just as much difficulty with this as
PostgreSQL does).
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461