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

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@aklaver.com