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