From: Cédric Villemain <cedric@2ndquadrant.com>
To: pgsql-admin@postgresql.org
Cc: Baptiste LHOSTE <blhoste@alaloop.com>
Cc: Sylvain CAILLET <scaillet@alaloop.com>
Subject: Re: Autoanalyze of the autovacuum daemon ...
Date: Mon, 5 Nov 2012 11:32:46 +0100
Message-ID: <201211051132.50591.cedric@2ndquadrant.com> (raw)
In-Reply-To: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com>
References: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com>
Hi,
> second one on which we insert some new data every five minutes (avg~200
> rows) and delete old data about every 1 hour (avg~1000 rows). For complete
> understanding, we need up-to-date stats for the second one because the
> recurrent deletion might take a long time, (~1mn for less than 1000
> deleted rows because planer uses seq scan instead of index scan).
>
> The autovacuum perform autoanalyze tasks on first kind as soon as the
> process (truncate + copy) is done.
>
> But the autoanalyze is not that effective for second kind.
>
> We tried to reduce autovacuum_analyze_threshold (50 => 10) and
> autovacuum_analyze_scale_factor (0.1 => 0.005) for the second kind of
> tables (ALTER TABLE ... SET PARAMETERS ...) without any conclusive effect.
>
> We can not find where is stored the total number of tuples inserted or
> updated since the last ANALYZE. Could someone give us the answer ?
in pg_stat_user_tables, not since the last time ANALYZE run, but you have the
number of reltuples from pg_class that is used to calculate the ratio.
> We take a look at the relfrozenxid but our tables do not have a big value
> of relfrozenxid (< 10000000).
>
> Most of the time there is no autovacuum analyze query in the
> pg_stat_activity, althought we set the autovacuum_naptime to 15s to try to
> start new analyze task more often.
>
> We do not understand why we can't obtain some improvments with previous
> changes. Did we do something wrong ?
Everything is relative to the size of the table, what is the content of
pg_class for the second kind of tables ? (relpages/reltuples)
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Attachments:
signature.asc (application/pgp-signature, 489B) download
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-admin@postgresql.org
Cc: cedric@2ndquadrant.com, blhoste@alaloop.com, scaillet@alaloop.com
Subject: Re: Autoanalyze of the autovacuum daemon ...
In-Reply-To: <201211051132.50591.cedric@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