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
Attachment:
signature.asc
Description: This is a digitally signed message part.
