From: Greg Smith <greg@2ndquadrant.com>
To: Tobias Brox <tobixen@gmail.com>
Cc: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: Memory usage - indexes
Date: Fri, 24 Sep 2010 14:05:34 -0400
Message-ID: <4C9CE86E.6020100@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTikWhZRZvJGJyNb03KJ3oqmMNju0FNtHxMb5bact@mail.gmail.com>
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>
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
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: greg@2ndquadrant.com, tobixen@gmail.com, bnichols@ca.afilias.info
Subject: Re: Memory usage - indexes
In-Reply-To: <4C9CE86E.6020100@2ndquadrant.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