Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E1AB02E027C for ; Fri, 7 Mar 2008 11:03:35 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 36817-01 for ; Fri, 7 Mar 2008 11:03:21 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 17CDF2E023D for ; Fri, 7 Mar 2008 11:03:29 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d37so1080662pye.29 for ; Fri, 07 Mar 2008 07:03:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=9cTRamg8pPehPdUdicygMG/lauuf5Cay+tGtJArOF0U=; b=Ee9ijoLoqg/muN2JZmQqbAqdhzCQB8jEmgMQPGQzvB5dxGbhWQlJsQXXkIwxb8BP3N2AZJEp2l2hQsl+3PVczhtnQp45GOsDD8xFC5RmbCw3ohWHpeScMM68ugRpErx2L1cMA+kZ/bNbaSKFpfvdBxc5coLPIpTY/OfzZgwJ4vU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=Rx1tjy9jem+UzgH8x162OTEl2zVoDilQKHAJD99orL1XKMyzoJpH5S9Qw4usbWRdCeSA3zB078RR+3ynQ0dpmIrB7MeSRlnTKh98lE2apclZAP4Omjo6NZc28S+xJ7uLzFp+UlzYEO9UNOX5SQiBRuODjGaxdGtHNw9Bm7Hk3Z0= Received: by 10.64.10.2 with SMTP id 2mr1180083qbj.56.1204902204434; Fri, 07 Mar 2008 07:03:24 -0800 (PST) Received: by 10.64.3.7 with HTTP; Fri, 7 Mar 2008 07:03:24 -0800 (PST) Message-ID: <3556c08c0803070703n22b2f4d8lcf879331b49fb709@mail.gmail.com> Date: Fri, 7 Mar 2008 16:03:24 +0100 From: "Pavel Rotek" To: pgsql-performance@postgresql.org Subject: Re: Toast space grows In-Reply-To: <20080307093453.110cff74.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26002_31917012.1204902204417" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/167 X-Sequence-Number: 29121 ------=_Part_26002_31917012.1204902204417 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanks to all for time and valuable help, Pavel Rotek 2008/3/7, Bill Moran : > > In response to "Pavel Rotek" : > > > > 2008/3/7, Tom Lane : > > > [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 > ------=_Part_26002_31917012.1204902204417 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_26002_31917012.1204902204417--