ANALYZE not being run frequently enough on tables
We recently saw two issues in production where ANALYZE table
wasn't run frequently enough on tables. As a result, some of our queries that used LIMIT 1
timed out:
-
ci_trigger_requests
was last analyzed on 2019-10-02 as of 2019-10-08 (gitlab-org/gitlab#33654 (comment 228045014)) -
notes
(https://sentry.gitlab.net/gitlab/gitlabcom/issues/1009228/)
gitlabhq_production=# SELECT schemaname, relname, last_analyze FROM pg_stat_all_tables WHERE relname = 'notes';
schemaname | relname | last_analyze
------------+---------+-------------------------------
public | notes | 2019-10-01 23:54:02.237208+00
(1 row)
11 days ago!
It looks like the frequency is dependent on analyze/autovacuum settings (https://www.postgresql.org/docs/9.6/routine-vacuuming.html#AUTOVACUUM). Currently they are:
analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * number of tuples
autovacuum_analyze_threshold = 50
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = '0.005'
autovacuum_max_workers = '6'
autovacuum_vacuum_cost_delay = '5ms'
autovacuum_vacuum_cost_limit = '6000'
autovacuum_vacuum_scale_factor = '0.005'
@NikolayS @emanuel_ongres Do we need to consider tweaking these values?
Edited by Stan Hu