Re: How to introspect autovacuum analyze settings
От | Adrian Klaver |
---|---|
Тема | Re: How to introspect autovacuum analyze settings |
Дата | |
Msg-id | 574c3ec3-7ba7-bfb5-9e76-e1fb67ded5a8@aklaver.com обсуждение исходный текст |
Ответ на | How to introspect autovacuum analyze settings (Benedikt Grundmann <bgrundmann@janestreet.com>) |
Список | 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
В списке pgsql-general по дате отправления:
Следующее
От: Poul KristensenДата:
Сообщение: Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used