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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
