DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=bqn/iZcVG9bZ0JpD1Pz4gbrfn5N/JnTwTTwtr4Jz7TC6lYgm8iHHFCzUBzOGwvFp5h
	MIwhXOeVKfltfpE+/TSihJIMdBoE88u6eWp3fAqlevdynO7esqZyDwdgFc2yAs6X0cP9
	w/+Mr8/slDLG1ESwzzJMmauqOKxneZhVZ1HgA=
MIME-Version: 1.0
In-Reply-To: <20091102125054.GA25608@pme.intranet.prolan.hu>
References: <20091029144405.GA3755@pme.intranet.prolan.hu>
	<dcc563d10910290959h24e6eecy7f46b210fb251ae3@mail.gmail.com>
	<20091102125054.GA25608@pme.intranet.prolan.hu>
Date: Mon, 2 Nov 2009 08:14:44 -0800
Message-ID: <f2fd819a0911020814u515a9341qa5f71720aa2bcbd1@mail.gmail.com>
Subject: Re: database size growing continously
From: Anj Adu <fotographs@gmail.com>
To: Peter Meszaros <pme@prolan.hu>
Cc: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I would recommend (if at all possible) to partition the table and drop
the old partitions when not needed. This will guarantee the space
free-up without VACUUM overhead. Deletes will kill you at some point
and you dont want too much of the VACUUM IO overhead impacting your
performance.

On Mon, Nov 2, 2009 at 4:50 AM, Peter Meszaros <pme@prolan.hu> wrote:
> Thank you all for the fast responses!
>
> I changed the delete's schedule from daily to hourly and I will let you
> know the result. This seems to be the most promising step.
>
> The next one is tuning 'max_fsm_pages'.
> Increasing max_fsm_pages can be also helpful, but I've read that
> 'vacuum verbose ...' will issue warnings if max_fsm_pages is too small.
> I've never seen such messag, this command is either run and finish or
> goes to an endless loop as it was written in my initial e-mail.
>
>
> On Thu, Oct 29, 2009 at 10:59:48AM -0600, Scott Marlowe wrote:
>> On Thu, Oct 29, 2009 at 8:44 AM, Peter Meszaros <pme@prolan.hu> wrote:
>> > Hi All,
>> >
>> > I use postgresql 8.3.7 as a huge queue. There is a very simple table
>> > with six columns and two indices, and about 6 million records are
>> > written into it in every day continously commited every 10 seconds fro=
m
>> > 8 clients. The table stores approximately 120 million records, because=
 a
>> > cron job daily deletes those ones are older than 20 day. Autovacuum is
>> > on and every settings is the factory default except some unrelated one=
s
>> > (listen address, authorization). But my database is growing,
>> > characteristically ~600MByte/day, but sometimes much slower (eg. 10MB,
>> > or even 0!!!).
>>
>> Sounds like you're blowing out your free space map. =A0Things to try:
>>
>> 1: delete your rows in smaller batches. =A0Like every hour delete
>> everything over 20 days so you don't delete them all at once one time
>> a day.
>> 2: crank up max fsm pages large enough to hold all the dead tuples.
>> 3: lower the autovacuum cost delay
>> 4: get faster hard drives so that vacuum can keep up without causing
>> your system to slow to a crawl while vacuum is running.
>
> --
> E-mail: pmeATprolanDOThu
> Phone: +36-20-954-3100/8139
> Mobile: +36-20-9543139
> Fax: +36-26-540420
> http://www.prolan.hu
> Mon Nov =A02 13:20:39 CET 2009
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>