From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Y Sidhu <ysidhu@gmail.com>
Cc: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: max_fsm_pages, shared_buffers and checkpoint_segments
Date: Thu, 24 May 2007 05:27:27 +0100
Message-ID: <4655142F.5010002@enterprisedb.com> (raw)
In-Reply-To: <b09064f30705230943r4f631687ub67b7a4929043e82@mail.gmail.com>
References: <b09064f30705230922j35a69ea4ie5ff38d63d199065@mail.gmail.com>
<82B28692-EF16-48F5-A40D-C4563D610B56@silentmedia.com>
<b09064f30705230943r4f631687ub67b7a4929043e82@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, ysidhu@gmail.com, bench@silentmedia.com
Subject: Re: max_fsm_pages, shared_buffers and checkpoint_segments
In-Reply-To: <4655142F.5010002@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox