Re: [GENERAL] Help writing a query to predict auto analyze
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). -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help writing a query to predict auto 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help writing a query to predict auto analyze
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. -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help writing a query to predict auto analyze
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help writing a query to predict auto analyze
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.) -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general