On 03/03/2017 12:33 AM, Peter J. Holzer wrote:
This is with PostgreSQL 9.5.6 on Debian Linux.
I noticed that according to pg_stat_user_tables autoanalyze has never
run on a lot of tables. Here is one example:
wdsah=> select * from pg_stat_user_tables where schemaname='public' and relname='facttable_wds_indexstats';
─[ RECORD 1 ]───────┬─────────────────────────
relid │ 112723
schemaname │ public
relname │ facttable_wds_indexstats
seq_scan │ 569
seq_tup_read │ 474779212
idx_scan │ 59184
idx_tup_fetch │ 59184
n_tup_ins │ 47128
n_tup_upd │ 0
n_tup_del │ 0
n_tup_hot_upd │ 0
n_live_tup │ 47128
n_dead_tup │ 0
n_mod_since_analyze │ 47128
last_vacuum │ (∅)
last_autovacuum │ (∅)
last_analyze │ (∅)
last_autoanalyze │ (∅)
vacuum_count │ 0
autovacuum_count │ 0
analyze_count │ 0
autoanalyze_count │ 0
wdsah=> select count(*) from facttable_wds_indexstats;
count
────────
857992
(1 row)
So, n_live_tup is way off, and n_tup_ins and n_mod_since_analyze also
seem to be wrong. Looks like this hasn't been updated in a year or so.
But track_counts is on:
wdsah=> show track_counts;
track_counts
──────────────
on
(1 row)
What are your settings for autovacuum?:
https://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html
Have the storage parameters for the table been altered?:
https://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
And even if it wasn't, shouldn't the autovacuum daemon notice that
n_mod_since_analyze is greater than n_live_tup *
autovacuum_analyze_scale_factor and run an autoanalyze?
That value is added to autovacuum_analyze_threshold:
autovacuum_analyze_scale_factor (floating point)
Specifies a fraction of the table size to add to
autovacuum_analyze_threshold when deciding whether to trigger an
ANALYZE. The default is 0.1 (10% of table size). This parameter can only
be set in the postgresql.conf file or on the server command line; but
the setting can be overridden for individual tables by changing table
storage parameters.
But the really weird thing is that pg_stats seems to be reasonably
current: I see entries in most_common_vals which were only inserted in
January. Is it possible that autoanalyze runs without updating
pg_stat_user_tables?
hp
--
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
