From: Pavel Rotek <pavel.rotek@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Toast space grows
Date: Fri, 7 Mar 2008 16:03:24 +0100
Message-ID: <3556c08c0803070703n22b2f4d8lcf879331b49fb709@mail.gmail.com> (raw)
In-Reply-To: <20080307093453.110cff74.wmoran@collaborativefusion.com>
References: <3556c08c0803070035p6d8b4aa8m6318f54728f61022@mail.gmail.com>
<47D10722.4080508@archonet.com>
<3556c08c0803070129y6ffabf32i1c8bae445ec460e0@mail.gmail.com>
<20080307065314.1248665b.wmoran@collaborativefusion.com>
<3556c08c0803070459i8598423t64e0704e48af533@mail.gmail.com>
<11848.1204896799@sss.pgh.pa.us>
<3556c08c0803070611p542eb317vfaf9b783172b6c3@mail.gmail.com>
<20080307093453.110cff74.wmoran@collaborativefusion.com>
Thanks to all for time and valuable help,
Pavel Rotek
2008/3/7, Bill Moran <wmoran@collaborativefusion.com>:
>
> In response to "Pavel Rotek" <pavel.rotek@gmail.com>:
>
>
> > 2008/3/7, Tom Lane <tgl@sss.pgh.pa.us>:
>
>
> [snip]
>
>
> > > If you have to do it that way, you'll need very frequent vacuums on
> this
> > > table (not vacuum full, as noted already) to keep the toast space from
> > > bloating too much. And make sure you've got max_fsm_pages set high
> > > enough.
> >
> > i'll set max_fsm_pages to 1 000 000. It should be enough and set
> > autovacuum_naptime to 10 minutes. May it be?
>
>
> No. Keep naptime at 1 minute. If it comes around and there's nothing
> to do, the overhead is minimal. If you set the naptime too high, it might
> have too much to do on the next cycle and then it'll bog things down.
> Also, it only checks 1 database per cycle, so setting it to 10 minutes
> means a _minimum_ of 40 minutes between checks (because you have a
> template0,
> template1, postgres, and your database minimum)
>
> Also, keep an eye on your database bloat to ensure the various
> autovacuum_*_scale_factor and related settings are appropriate.
> It's been found that these are often not aggressive enough for
> good maintenance. If you see bloat even with autovacuum running,
> reduce those values.
>
> Personally, I'd recommend running a MRTG graph that graphs the size
> of this table so you can easily watch to see if your config tweaks
> are getting the job done or not. And remember that _some_ bloat is
> expected and normal for operation.
>
>
> --
>
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pavel.rotek@gmail.com
Subject: Re: Toast space grows
In-Reply-To: <3556c08c0803070703n22b2f4d8lcf879331b49fb709@mail.gmail.com>
* 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