Skip site navigation (1) Skip section navigation (2)

Re: max_fsm_pages, shared_buffers and checkpoint_segments

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Y Sidhu <ysidhu(at)gmail(dot)com>
Cc: Ben <bench(at)silentmedia(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: max_fsm_pages, shared_buffers and checkpoint_segments
Date: 2007-05-24 04:27:27
Message-ID: 4655142F.5010002@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: AndyDate: 2007-05-24 07:03:42
Subject: Re: LIKE search and performance
Previous:From: Leandro GuimarĂ£es dos SantosDate: 2007-05-24 02:01:24
Subject: Memory allocation and Vacuum abends

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group