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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
