How to introspect autovacuum analyze settings
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]At least it didn't when I just run analyze on a table explicitly.. But clearly the database has that knowledge
somewhere persistent because otherwise how could autovacuum do its thing.
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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2016-11-21 14:44 GMT+01:00 Benedikt Grundmann <bgrundmann@janestreet.com>:
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.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.
What you are looking for is the n_mod_since_analyze column of the
pg_stat_user_tables view. It's available since the 9.4 release. If you have
an earlier version, you need this extension:
http://pgxn.org/dist/mods_since_analyze/1.0.0/
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
On 21 November 2016 at 14:57, Guillaume Lelarge <guillaume@lelarge.info>
wrote:
2016-11-21 14:44 GMT+01:00 Benedikt Grundmann <bgrundmann@janestreet.com>:
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.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.What you are looking for is the n_mod_since_analyze column of the
pg_stat_user_tables view. It's available since the 9.4 release. If you have
an earlier version, you need this extension: http://pgxn.org/dist/mods_
since_analyze/1.0.0/
Ah excellent. Thanks. Sigh updating to 9.x with x > 2 has been a long
time coming.
Show quoted text
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com