Re: max_fsm_pages, shared_buffers and checkpoint_segments

Y Sidhu wrote:
> I cannot answer that question on the grounds that it may incriminate me.
> Hehe. I am really trying to get our vacuum times down. The cause of the
> problem, I believe, are daily mass deletes. Yes, I am working on performing
> vacuums more than once a day. No, I am not considering partitioning the
> offending table because a few scripts have to be changed. I am also turning
> the knobs as I find them.

Yudhvir, I don't think the tuning options are going to make any
difference to your vacuum times.

I don't know if this been brought up already, but the way vacuum works
in 8.1 and 8.2 is that when it scans the table for the second time, it
does a WAL flush for every block that had deleted tuples on it. That's
really expensive, in particular if you don't have a separate drive for
the WAL, and/or you don't have a battery backed up cache in your controller.

You could try turning fsync=off to see if it helps, but be warned that
that's dangerous. If you have a power failure etc. while the database is
busy, you can get data corruption. So do that to see if it helps on a
test matchine, and if it does, put WAL on another drive or get a
controller with battery backed up cache. Or wait until release 8.3,
which should fix that issue.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com