Search Postgresql Archives

Re: autoanalyze criteria

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 02/23/2013 05:10 PM, Jeff Janes wrote:
On Saturday, February 23, 2013, Stefan Andreatta wrote:
            
Thanks Jeff, that helped a lot (as did a careful rereading of http://www.postgresql.org/docs/9.1/static/monitoring-stats.html and http://www.postgresql.org/docs/9.1/static/catalog-pg-class.html ;-)

However, to estimate whether autoanalyze should be triggered, I am still missing something: the analyze threshold is compared to the "total number of tuples inserted, updated, or deleted since the last ANALYZE." (according to http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html).

pg_stat_user_tables.n_live tup - pg_class.reltuples should give something like the sum of rows inserted minus rows deleted since the last ANALYZE. But according to the documentation we would need the sum of those values. And we are still missing a number for rows updated since the last analyze. pg_stat_usert_tables. n_dead_tup, on the other hand, is only set back by successful VACUUM. autoanalyzing a table with more than 10% dead rows would therefore keep autoanalyze in a loop until the ratio rises beyond 20% (default configuration) and autovacuum kicks in. So that wouldn't make a lot of sense.


Hi Stefan,

Sorry, I got tunnel vision about the how the threshold was computed, and forgot about the thing it was compared to.  There is a "secret" data point in the stats collector called changes_since_analyze.  This is not exposed in the pg_stat_user_tables.  But I think it should be as I often have wanted to see it.


Cheers,

Jeff

Sounds like a very good idea to me - any way I could help to make such a thing happen?

Stefan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux