0

On some tables, an autovacuum: VACUUM ANALYZE public.some_table process will run for 30 min to as long as 10 hours. During this time, updates to the table in question are often locked for 10+ minutes.

As soon as I cancel the auto-vac process, the code that depends on updating those tables is suddenly able to finish executing.

The tables being affected range from 78 GB to 7 GB with millions of rows each on an AWS RDS db.m4.10xlarge with PostgreSQL 9.6.3.

What settings do I need on a per table or system basis to make sure we get a performance boost from auto-vac analyze without interfering with critical query execution speed? If that is too much of a shot in the dark, what query could I run to tell what settings would be optimal for our setup?

These tables have a LOT of updates/deletes/inserts every minute, would changing FILLFACTOR help here as well to reduce the need for auto-vac? I've been iffy on this because some of the most commonly updated columns have indexes and I also don't know how to analyze the tables to tell if fill factor would really be beneficial.

Currently, fill factor is 100% on all tables. Here are other relevant performance settings for the database (let me know if I missed anything salient):

autovacuum = on
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 3
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_naptime = 30
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_work_mem = -1
effective_io_concurrency = 1
log_autovacuum_min_duration = -1
maintenance_work_mem = 2702336
max_parallel_workers_per_gather = 0
max_worker_processes = 8
rds.force_autovacuum_logging_level = disabled
shared_buffers = 5151945
temp_buffers = 1024
vacuum_cost_delay = 0
vacuum_cost_limit = 200
vacuum_cost_page_dirty = 20
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_defer_cleanup_age = 0
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
work_mem = 4096
  • Is the autovac "for wraparound", or normal? Is the problem literally locks (visible in the server log file, assuming log_lock_waits is on and deadlock_timeout is reasonable), or is it just stalls due to IO congestion? – jjanes Oct 10 '17 at 2:08
  • The autovac is for both wraparound and "normal" depending on the size of the table. log_lock_waits = off and deadlock_timeout = 1000 and it appears to be internal as it shows very simple sub-ms queries get stuck. – eComEvo Oct 10 '17 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.