In an effort to fine-tune my table storage parameters so tables are analyzed
at the optimal time, I have written a query to show how soon my tables will
be auto-analyzed. But my results to not jive with what I see autovacuum
doing, i.e. there are tables that are millions of rows past the threshold
that haven't been analyzed for 2 weeks, and autovacuum does not want to auto
analyze them, so I conclude that my query must be wrong.
The docs say that tables will be auto analyzed when (paraphrasing) the
number of tuples inserted or updated since the last analyze exceeds the
analyze base threshold plus the product of the analyze scale factor and the
number of tuples.
After a reading of the some of the code in autovacuum.c, it appears the
formula used is something like this. (Note that I'm not quite sure how
"number of tuples inserted or updated since the last analyze" translates in
code as "n_live_tup + n_dead_tup - [tuples at last analyze]", but I'll trust
the code is correct, and I need only understand how to get the values of the
variables.)
T = bt + (sf * rt)
X = lt + dt - at
will analyze when X > T
T: Threshold
X: test value
bt: base threshold
sf: scale factor
rt: rel tuples
lt: live tuples
dt: dead tuples
at: last analysis tuples
The next step was to get these values from the system tables. Here's what I
used:
bt: base threshold = current_setting('autovacuum_analyze_threshold') or
table override setting
sf: scale factor = current_setting('autovacuum_analyze_scale_factor') or
table override setting
rt: rel tuples = pg_class.reltuples
lt: live tuples = pg_stat_user_tables.n_live_tup
dt: dead tuples = pg_stat_user_tables.n_dead_tup
at: last analysis tuples = pg_class.reltuples ????
I'm the least confident about the last one -- tuples as of last analyze.
Can anyone confirm or correct these?
Version: PostgreSQL 8.4.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
Thanks!
--gordon
--
View this message in context:
http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28610247.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.