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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
