Search Postgresql Archives

Re: How to introspect autovacuum analyze settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux