Message-ID: <4C9CE86E.6020100@2ndquadrant.com>
Date: Fri, 24 Sep 2010 14:05:34 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tobias Brox <tobixen@gmail.com>
CC: Brad Nicholson <bnichols@ca.afilias.info>,
	pgsql-performance@postgresql.org
Subject: Re: Memory usage - indexes
References: <AANLkTimd0Y7KhnwkSXz4FYjsxC8ocDeRRH=PXvU1a3vv@mail.gmail.com>
	<865426.21884.qm@web39701.mail.mud.yahoo.com>
	<AANLkTinK6jGN5+q=1FYjD8EkTJTB24rc-dd3_YnNgg=5@mail.gmail.com>
	<4C9CDCDD.8000603@ca.afilias.info>
	<AANLkTikWhZRZvJGJyNb03KJ3oqmMNju0FNtHxMb5bact@mail.gmail.com>
In-Reply-To: <AANLkTikWhZRZvJGJyNb03KJ3oqmMNju0FNtHxMb5bact@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tobias Brox wrote:
> We do have some bloat-problems as well - every now and then we decide
> to shut down the operation, use pg_dump to dump the entire database to
> an sql file and restore it.  The benefits are dramatic, the space
> requirement goes down a lot, and often some of our
> performance-problems goes away after such an operation.
>   

You can do the same thing with far less trouble if you just CLUSTER the 
table.  It takes a lock while it runs so there's still downtime needed, 
but it's far faster than a dump/reload and safer too.

> If it was to me, we would have had autovacuum turned on.  We've had
> one bad experience when the autovacuumer decided to start vacuuming
> one of the biggest table at the worst possible moment - and someone
> figured autovacuum was a bad idea.  I think we probably still would
> need regular vacuums to avoid that happening, but with autovacuum on,
> maybe we could have managed with regular vacuums only once a week or
> so.
>   

The answer to "we once saw autovacuum go mad and cause us problems" is 
never the knee-jerk "disable autovacuum", it's usually "change 
autovacuum so it runs far more often but with lower intesity".  
Sometimes it's "keep autovacuum on but proactively hit the biggest 
tables with manual vacuums at slow times" too.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book