Baptiste LHOSTE
2012-10-31 15:05:13 UTC
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
***@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
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
***@alaloop.com
ALALOOP S.A.S. - Technopole Izarbel - 64210 Bidart
Téléphone : +33 (0) 5 59 41 51 10
www.alaloop.com
--
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Sent via pgsql-admin mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin