Re: How to introspect autovacuum analyze settings - Mailing list pgsql-general

On 11/21/2016 05:44 AM, Benedikt Grundmann wrote:
> Hello all,
>
> I have a quick question.  I feel like somewhere in section 23.1.6 there
> should be the answer but I couldn't find it yet.  Namely how can I query
> the database for total number of tuples inserted, updated, or deleted
> since the last ANALYZE?  pg_stat_user_tables.n_tup_{ins,upd,del,hot_upd}
> seems to not reset after an analyze[1].  But clearly the database has
> that knowledge somewhere persistent because otherwise how could
> autovacuum do its thing.

Did you see this?:
https://www.postgresql.org/docs/9.5/static/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW

"When using the statistics to monitor collected data, it is important to
realize that the information does not update instantaneously. Each
individual server process transmits new statistical counts to the
collector just before going idle; so a query or transaction still in
progress does not affect the displayed totals. Also, the collector
itself emits a new report at most once per PGSTAT_STAT_INTERVAL
milliseconds (500 ms unless altered while building the server). So the
displayed information lags behind actual activity. However,
current-query information collected by track_activities is always
up-to-date."

Still I do see changes:

test[5432]=# select * from pg_stat_user_tables where relname='ts_tsz_test';
-[ RECORD 1 ]-------+------------------------------
relid               | 1140187
schemaname          | public
relname             | ts_tsz_test
seq_scan            | 66
seq_tup_read        | 249
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 32
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 6
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        | 2016-11-21 06:48:38.500307-08
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 5
autoanalyze_count   | 0


test[5432]=# insert into ts_tsz_test values ('11/21/2016', '11/21/2016');
INSERT 0 1
test[5432]=# select now();
-[ RECORD 1 ]----------------------
now | 2016-11-21 06:49:19.957626-08

test[5432]=# analyze ts_tsz_test ;
ANALYZE
test[5432]=# select * from pg_stat_user_tables where relname='ts_tsz_test';
-[ RECORD 1 ]-------+------------------------------
relid               | 1140187
schemaname          | public
relname             | ts_tsz_test
seq_scan            | 66
seq_tup_read        | 249
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 33
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 7
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     |
last_analyze        | 2016-11-21 06:49:22.577586-08
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 6
autoanalyze_count   | 0



So are you sure you are looking at the correct database and/or tables?



>
> Rationale for the question.  I have the strong suspicion that for some
> of our bigger tables autovacuum *analyze *is not hitting frequently
> enough (even so we already much more aggressive settings than the
> default).  So I want to set some custom settings for those tables.  But
> rather than doing it manually for the one table I found I would much
> rather write a query (ideally one taking any existing per table settings
> into account) that tells me likely candidates for tweaking.  But so far
> I fail to even get the relevant data.
>
> Cheers,
>
> Bene
>
> [1] At least it didn't when I just run analyze on a table explicitly.


--
Adrian Klaver
adrian.klaver@aklaver.com