Re: [GENERAL] Autoanalyze oddity - Mailing list pgsql-general

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@hjp.at         | -- John Gordon
__/   | http://www.hjp.at/ |    http://downlode.org/Etext/alicebob.html