Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4B4442E005E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Mar 2008 03:29:04 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 03548-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Mar 2008 03:29:01 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mailout.abaca.com (mailout.abaca.com [75.59.196.2])
	by postgresql.org (Postfix) with ESMTP id 3A93F2E0056
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Mar 2008 03:29:01 -0300 (ADT)
Received: from v-abaca-epg.lab.abaca.com (localhost.localdomain [127.0.0.1])
	by v-abaca-epg.lab.abaca.com (Postfix-out) with ESMTP id DBF0B1100F0;
	Thu, 27 Mar 2008 23:32:39 -0700 (PDT)
X-Propel-Return-Path: <VGopal@abaca.com>
Received: from mailout.abaca.com ([172.16.83.19])
	by [127.0.0.1] ([127.0.0.1]) (port 7027) (Abaca EPG outproxy filter
	3.1.0.8749 $Rev: 8725 $)
	id 5uNZr83s6wD0; Thu, 27 Mar 2008 23:32:39 -0700
Received: from mail.abaca.com (unknown [172.16.83.4])
	by v-abaca-epg.lab.abaca.com (Postfix-out) with ESMTP id 85323110088;
	Thu, 27 Mar 2008 23:32:39 -0700 (PDT)
Received: from vinu-ubuntu.abaca (172.16.83.123) by mail.abaca.com
	(172.16.83.4) with Microsoft SMTP Server id 8.0.744.0; Thu, 27 Mar 2008
	23:30:08 -0700
Date: Thu, 27 Mar 2008 23:28:33 -0800
From: Vinubalaji Gopal <vgopal@abaca.com>
To: "Joshua D. Drake" <jd@commandprompt.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: vacuum in Postgresql 8.0.x slowing down the database
Message-ID: <20080327232833.4d84b68b@vinu-ubuntu.abaca>
In-Reply-To: <20080326130213.3797bfe1@jd-laptop>
References: <20080326124956.49e1c13d@vinu-ubuntu.abaca>
	<20080326130213.3797bfe1@jd-laptop>
X-Mailer: Claws Mail 2.10.0 (GTK+ 2.12.0; i486-pc-linux-gnu)
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Propel-ID: 5uNZr83s6wD0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200803/510
X-Sequence-Number: 29464

On Wed, 26 Mar 2008 13:02:13 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:


> The slowness is likely attributed to Vacuum's use of I/O. When vacuum
> is running what does iostat -k 10 say?

Seems to be higher than normal - here is the output with vacuum run
without the other queries and the default vacuum taking ~1 hr:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          13.30    0.00    4.50   25.91    0.00   56.29
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
md2            3356.94      2005.59     12945.45      20076     129584


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          16.20    0.00    6.32   24.89    0.00   52.59

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
md2             461.70       667.20      1512.00       6672      15120


I don't know if the output helps much since the vacuum took some time
and I lost more than half of my iostat -k screen output. (I scrolled up
- but got only some of the data)

If vacuum does affect the io what are the ways to reduce the io during
vacuum (the delay and cost parameter did not help that much - should I
consider reducing the cost even further)? Should I consider
partitioning the table?

Thank you.

--
Vinu