Обсуждение: Help writing a query to predict auto analyze
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.
Excerpts from Gordon Shannon's message of mié may 19 11:49:45 -0400 2010: > 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? In 8.4 it's number of dead + lives tuples that there were in the previous analyze. See pgstat_recv_analyze in src/backend/postmaster/pgstat.c. (In 9.0 it's been reworked a bit.) --
alvherre wrote: > > Excerpts from Gordon Shannon's message of mié may 19 11:49:45 -0400 2010: > >> 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? > > In 8.4 it's number of dead + lives tuples that there were in the previous > analyze. See pgstat_recv_analyze in src/backend/postmaster/pgstat.c. > (In 9.0 it's been reworked a bit.) > > I'm sorry, I'm not following you. Are you saying that "last analysis tuples" is "number of dead + live tuples from the previous anlyze"? If so, that would really confuse me because X would always be 0: X = lt + dt - at X = pg_stat_user_tables.n_live_tup + n_dead_tup - (n_live_tup + n_dead_tup) X = 0 ....or is there something else wrong with the formula? --gordon -- View this message in context: http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28614875.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Excerpts from Gordon Shannon's message of mié may 19 18:02:51 -0400 2010: > I'm sorry, I'm not following you. Are you saying that "last analysis > tuples" is "number of dead + live tuples from the previous anlyze"? If so, > that would really confuse me because X would always be 0: > > X = lt + dt - at > X = pg_stat_user_tables.n_live_tup + n_dead_tup - (n_live_tup + n_dead_tup) > X = 0 > > ....or is there something else wrong with the formula? Hmm? n_live_tup and n_dead_tup corresponds to the current numbers, whereas "last analysis tuples" are the values from back when the previous analyze ran. These counters keep moving per updates, deletes, inserts, they are not static. If there are no update/ins/del then indeed the difference is 0, which is why we choose not do analyze. --
alvherre wrote: > > n_live_tup and n_dead_tup corresponds to the current numbers, > whereas "last analysis tuples" are the values from back when the > previous analyze ran. These counters keep moving per updates, deletes, > inserts, they are not static. > > OK. Do you know how can I get the values from back when the previous analyze ran? -- View this message in context: http://old.nabble.com/Help-writing-a-query-to-predict-auto-analyze-tp28610247p28616817.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Excerpts from Gordon Shannon's message of mié may 19 23:32:07 -0400 2010: > > alvherre wrote: > > > > n_live_tup and n_dead_tup corresponds to the current numbers, > > whereas "last analysis tuples" are the values from back when the > > previous analyze ran. These counters keep moving per updates, deletes, > > inserts, they are not static. > > OK. Do you know how can I get the values from back when the previous > analyze ran? I don't think we expose those values to SQL. I guess you could create a C function to get it, modelled after pg_stat_get_live_tuples and friends (see src/backend/utils/adt/pgstatfuncs.c). --