From: Kevin Grittner <kgrittn@ymail.com>
To: Roberto Grandi <roberto.grandi@trovaprezzi.it>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: COPY TO and VACUUM
Date: Wed, 4 Sep 2013 01:10:41 -0700 (PDT)
Message-ID: <1378282241.27940.YahooMailNeo@web162905.mail.bf1.yahoo.com> (raw)
In-Reply-To: <1680047223.718929.1378275308275.JavaMail.root@trovaprezzi.it>
References: <535715181.700956.1378223895627.JavaMail.root@trovaprezzi.it>
<441706222.701556.1378224973704.JavaMail.root@trovaprezzi.it>
<1378240470.79541.YahooMailNeo@web162905.mail.bf1.yahoo.com>
<1680047223.718929.1378275308275.JavaMail.root@trovaprezzi.it>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Roberto Grandi <roberto.grandi@trovaprezzi.it> wrote:
> we are using postgres 8.3.
> my scenario is:
>
> - Delete all products record for a vendor
> - Reload all products record (from new listing) for the same
> vendor.
>
> Obviously we repeat this process continously and table space is
> growing really fast.
>
> Can you suggest me an approach for autovacuum within this
> scenario and, if you want, suggest me an appropriate version of
> postgres that help solving my problem?
At this point I would recommend the latest minor release of 9.2 for
production use. If you were early in a development cycle I would
suggest considering the soon-to-be-released 9.3.0. Be sure to stay
current on minor releases.
http://www.postgresql.org/support/versioning/
If your table space is growing fast with this usage pattern, it
suggests that autovacuum is not configured to be aggressive enough.
My suggestions:
Make sure autovacuum is on.
Decrease autovacuum_naptime to 15s, so that it will notice deletes
sooner.
You could consider reducing autovacuum_scale_factor below the
default of 0.2 so that it triggers based on fewer deletes.
You should probably set autovacuum_vacuum_cost_limit to 400 and
incrementally increase it until autovacuum is able to keep up with
the activity you describe. It defaults to 200 and I have had to
set it to 650 on some systems to allow it to keep up. It wouldn't
be surprising if some systems need a higher setting. Higher
settings may cause autovacuum activity to have a more noticeable
impact on foreground processes; but if it is too low, you will
develop bloat which will harm performance and eat disk space.
If all autovacuum workers are sometimes busy with big tables for
extended periods and you see other tables neglected for too long,
you should boost autovacuum_max_workers until that problem is
solved.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: kgrittn@ymail.com, roberto.grandi@trovaprezzi.it
Subject: Re: COPY TO and VACUUM
In-Reply-To: <1378282241.27940.YahooMailNeo@web162905.mail.bf1.yahoo.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