Обсуждение: [GENERAL] Autoanalyze oddity
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 ]───────┬─────────────────────────
relid │ 112723
schemaname │ public
relname │ facttable_wds_indexstats
seq_scan │ 569
seq_tup_read │ 474779212
idx_scan │ 59184
idx_tup_fetch │ 59184
n_tup_ins │ 47128
n_tup_upd │ 0
n_tup_del │ 0
n_tup_hot_upd │ 0
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)
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?
But the really weird thing is that pg_stats seems to be reasonably
current: I see entries in most_common_vals which were only inserted in
January. Is it possible that autoanalyze runs without updating
pg_stat_user_tables?
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
Вложения
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 ]───────┬───────────────────────── > relid │ 112723 > schemaname │ public > relname │ facttable_wds_indexstats > seq_scan │ 569 > seq_tup_read │ 474779212 > idx_scan │ 59184 > idx_tup_fetch │ 59184 > n_tup_ins │ 47128 > n_tup_upd │ 0 > n_tup_del │ 0 > n_tup_hot_upd │ 0 > 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 Have the storage parameters for the table been altered?: https://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS > > 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. > > But the really weird thing is that pg_stats seems to be reasonably > current: I see entries in most_common_vals which were only inserted in > January. Is it possible that autoanalyze runs without updating > pg_stat_user_tables? > > hp > > -- Adrian Klaver adrian.klaver@aklaver.com
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
Вложения
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
On 2017-03-05 08:39:05 -0800, Adrian Klaver wrote: > On 03/05/2017 03:01 AM, Peter J. Holzer wrote: > >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. > ..." Oh, of course. That was the day we found out the hard way that the bypass for the UPS didn't work. I knew that date looked familiar, but somehow couldn't place it. Mystery solved, thanks! 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
Вложения
On 2017-03-05 12:01:07 +0100, Peter J. Holzer wrote:
[...]
> At the current rate of inserts, this threshold will be reached on
> March 24nd. I'll check whether the table is analyzed then.
It was (a little earlier than expected because pg_class.reltuples didn't
increase in the meantime).
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