Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 0658A9FB5D3 for ; Thu, 24 May 2007 01:28:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 70711-01 for ; Thu, 24 May 2007 01:28:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.228]) by postgresql.org (Postfix) with ESMTP id A2F9D9FB597 for ; Thu, 24 May 2007 01:28:07 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id f1so418795nzc for ; Wed, 23 May 2007 21:28:07 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=uYhPfn5XmXK8iIkik+uO7mRx/BuuGqtaXR0/X0b+sxJbSK864sU1yxh1SS4BLejI7vhaX2Y5PH+LybRB1rm0rabRExklIhGY5W10mhiH2xg8Y88wUVFmpvkuXG7WFxTrWv2yIa+7H9Rs6tufrFkNxyYyJ6HnEd9RuS+jNxQr/VU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=CGPFKLu47/2krwrlIJ5jQkudPHBo5p6/9msjvFL0by2n12jbKtWgaWBONQPRv2f7Y8Xs0nPqHGpILjN/i9Sx0R+USGy+iHxU+LGf1OuUqby+JxjZq592tbScsPI/2na0uWu6qACZPDuJSpyTFgkt3veMhwo+suevCDToforiVrM= Received: by 10.65.139.9 with SMTP id r9mr40261qbn.1179980887028; Wed, 23 May 2007 21:28:07 -0700 (PDT) Received: from ?10.1.252.202? ( [66.135.97.2]) by mx.google.com with ESMTP id e18sm2373345qbe.2007.05.23.21.27.58; Wed, 23 May 2007 21:28:05 -0700 (PDT) Message-ID: <4655142F.5010002@enterprisedb.com> Date: Thu, 24 May 2007 05:27:27 +0100 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: Y Sidhu CC: Ben , pgsql-performance@postgresql.org Subject: Re: max_fsm_pages, shared_buffers and checkpoint_segments References: <82B28692-EF16-48F5-A40D-C4563D610B56@silentmedia.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/474 X-Sequence-Number: 24864 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