pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Meszaros <pme@prolan.hu>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: database size growing continously
Date: Mon, 2 Nov 2009 13:50:54 +0100
Message-ID: <20091102125054.GA25608@pme.intranet.prolan.hu> (raw)
In-Reply-To: <dcc563d10910290959h24e6eecy7f46b210fb251ae3@mail.gmail.com>
References: <20091029144405.GA3755@pme.intranet.prolan.hu>
	<dcc563d10910290959h24e6eecy7f46b210fb251ae3@mail.gmail.com>

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 from
> > 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 ones
> > (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.  Things to try:
> 
> 1: delete your rows in smaller batches.  Like 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  2 13:20:39 CET 2009



view thread (3+ messages)  latest in thread

Message-ID: <20091102125054.GA25608@pme.intranet.prolan.hu>
Permalink:  ../20091102125054.GA25608@pme.intranet.prolan.hu/
Also on:    postgresql.org/message-id/20091102125054.GA25608@pme.intranet.prolan.hu

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pme@prolan.hu, scott.marlowe@gmail.com
  Subject: Re: database size growing continously
  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>

* 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