pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: database size growing continously
3+ messages / 3 participants
[nested] [flat]
* Re: database size growing continously
@ 2009-11-02 12:50 Peter Meszaros <pme@prolan.hu>
0 siblings, 2 replies; 3+ messages in thread
From: Peter Meszaros @ 2009-11-02 12:50 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: database size growing continously
@ 2009-11-02 16:14 Anj Adu <fotographs@gmail.com>
parent: Peter Meszaros <pme@prolan.hu>
1 sibling, 0 replies; 3+ messages in thread
From: Anj Adu @ 2009-11-02 16:14 UTC (permalink / raw)
To: Peter Meszaros <pme@prolan.hu>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
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 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: database size growing continously
@ 2009-11-02 20:34 Robert Haas <robertmhaas@gmail.com>
parent: Peter Meszaros <pme@prolan.hu>
1 sibling, 0 replies; 3+ messages in thread
From: Robert Haas @ 2009-11-02 20:34 UTC (permalink / raw)
To: Peter Meszaros <pme@prolan.hu>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
On Mon, Nov 2, 2009 at 7:50 AM, Peter Meszaros <pme@prolan.hu> wrote:
> 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.
I don't think it goes into an endless loop. I think it runs for a
really long time because your database is bloated, in need of
vacuuming, and probably has blown out the free space map. But since
you haven't let it run to completion you haven't seem the message.
...Robert
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-11-02 20:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-02 12:50 Re: database size growing continously Peter Meszaros <pme@prolan.hu>
2009-11-02 16:14 ` Anj Adu <fotographs@gmail.com>
2009-11-02 20:34 ` Robert Haas <robertmhaas@gmail.com>
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