Re: Autoanalyze oddity

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.

So it is likely that something happened on that day (disk full?) which
wiped out the contents of pg_stat_user_tables.

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

--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp(at)hjp(dot)at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html