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.
In response to
pgsql-performance by date
|Next:||From: Andy||Date: 2007-05-24 07:03:42|
|Subject: Re: LIKE search and performance|
|Previous:||From: Leandro Guimarães dos Santos||Date: 2007-05-24 02:01:24|
|Subject: Memory allocation and Vacuum abends|