On 03/05/2017 03:01 AM, Peter J. Holzer wrote:
On 2017-03-03 06:39:35 -0800, Adrian Klaver wrote:
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 ]───────┬─────────────────────────
[...]
n_tup_ins │ 47128
[...]
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
All the values in the autovacuum section of postgresql.conf are
commented out, so they should be the default values:
Just to be sure here's the output of show for each of the parameters:
wdsah=> show autovacuum; on
wdsah=> show log_autovacuum_min_duration; -1
wdsah=> show autovacuum_max_workers; 3
wdsah=> show autovacuum_naptime; 1min
wdsah=> show autovacuum_vacuum_threshold; 50
wdsah=> show autovacuum_analyze_threshold; 50
wdsah=> show autovacuum_vacuum_scale_factor; 0.2
wdsah=> show autovacuum_analyze_scale_factor; 0.1
wdsah=> show autovacuum_freeze_max_age; 200000000
wdsah=> show autovacuum_multixact_freeze_max_age; 400000000
wdsah=> show autovacuum_vacuum_cost_delay; 20ms
wdsah=> show autovacuum_vacuum_cost_limit; -1
Have the storage parameters for the table been altered?:
https://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
No.
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.
True. But 50 is negligible compared to 47128*0.1. So that shouldn't make
much of a difference.
But now that I look closer, I notice that the number in n_tup_ins for
that table is exactly the number of records inserted since
2017-02-08T13:00 and there were no records inserted between 09:00 and
13:00 on that day.
Are you getting the above from querying the records themselves?
So it is likely that something happened on that day (disk full?) which
wiped out the contents of pg_stat_user_tables.
Are there any logs from that time, either Postgres or system?
I would think a full disk would have been noticed at the time so
alternate theories:
https://www.postgresql.org/docs/9.5/static/monitoring-stats.html
"... When the server shuts down cleanly, a permanent copy of the
statistics data is stored in the pg_stat subdirectory, so that
statistics can be retained across server restarts. When recovery is
performed at server start (e.g. after immediate shutdown, server crash,
and point-in-time recovery), all statistics counters are reset.
..."
Or:
Table 27-16. Additional Statistics Functions
pg_stat_reset*
Looking into the source code, I find that
reltuples = classForm->reltuples;
Am I correct to assume that this is pg_class.reltuples? That would
explain why analyze hasn't run yet: This is 862378, which is exactly
correct. 862378 * 0.1 + 50 is 86287.8, which is larger than
pg_stat_user_tables.n_mod_since_analyze. At the current rate of inserts,
this threshold will be reached on March 24nd. I'll check whether the
table is analyzed then.
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
