From: Baptiste LHOSTE <blhoste@alaloop.com>
To: pgsql-admin@postgresql.org
Cc: Sylvain CAILLET <scaillet@alaloop.com>
Subject: Autoanalyze of the autovacuum daemon ...
Date: Wed, 31 Oct 2012 16:05:13 +0100 (CET)
Message-ID: <626919622.7634700.1351695913466.JavaMail.root@alaloop.com> (raw)
In-Reply-To: <1442070268.7614953.1351693260184.JavaMail.root@alaloop.com>
Hi All,
We are using postgreSQL since 2007 (now we use postgreSQL 8.4) and until recently we used to perform deletion of old data then vacuum and analyze tasks by ourself (every night at 0am CET).
Nevertheless we reached a point where these tasks are taking so much time that why we decide to perform deletion of old data every hour and let the autovacuum daemon manage vacuum and analyze tasks.
But we have some difficulties to understand how work the trigger of the autovacuum daemon for autoanalyze tasks.
Let me explain our database structure. We have two kind of tables :
- first one on which we perform a complete truncate and a copy to fill each (partitioned tables - 288 partitions, one per 5mn re-used every day)
- 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 ?
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 ?
Thank you all for your kind advices,
Regards, Baptiste.
---
Baptiste LHOSTE
blhoste@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
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: blhoste@alaloop.com, scaillet@alaloop.com
Subject: Re: Autoanalyze of the autovacuum daemon ...
In-Reply-To: <626919622.7634700.1351695913466.JavaMail.root@alaloop.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