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