1

Autovacuum does not clear the database. Databases middle-db Postgres 10.18, AWS RDS(vCPU 2, RAM 8Gb, SSD(gp2) 1100Gib)

Table "spree_datafeed_products"

relid               | 16556
schemaname          | public
relname             | spree_datafeed_products
seq_scan            | 20
seq_tup_read        | 365522436
idx_scan            | 962072108
idx_tup_fetch       | 9929276855
n_tup_ins           | 2846455
n_tup_upd           | 35778058
n_tup_del           | 284291955
n_tup_hot_upd       | 0
n_live_tup          | 3546840
n_dead_tup          | 338790851
n_mod_since_analyze | 307930753
last_vacuum         | 
last_autovacuum     | 
last_analyze        | 
last_autoanalyze    | 2022-04-29 13:01:43.985749+00
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 1

Table and indexes sizes:

indexname                           | size  
index_spree_datafeed_products_on_updated_at                  | 48 GB
index_spree_datafeed_products_on_state                       | 35 GB
index_spree_datafeed_products_on_size_variant_field          | 40 GB
index_spree_datafeed_products_on_product_id                  | 32 GB
index_spree_datafeed_products_on_original_id                 | 31 GB
index_spree_datafeed_products_on_datafeed_id                 | 42 GB
index_spree_datafeed_products_on_datafeed_id_and_original_id | 31 GB
index_spree_datafeed_products_on_data_hash                   | 39 GB
spree_datafeed_products_pkey                                 | 18 GB

 pg_size_pretty  - 419 GB

Worker:

datid            | 16404
datname          | milanstyle_production
pid              | 2274
backend_start    | 2022-05-01 19:52:00.066097+00
xact_start       | 2022-05-01 19:52:00.23692+00
query_start      | 2022-05-01 19:52:00.23692+00
state_change     | 2022-05-01 19:52:00.236921+00
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 1301636863
query            | autovacuum: VACUUM ANALYZE public.spree_datafeed_products
backend_type     | autovacuum worker

Settings:

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

The garbage cleaning script has accumulated a lot of deleted entries. We have been waiting for more than a week (autoclearance). What is the problem? Why is the database failing?

1

1 Answer 1

2

It will take a very long time to vacuum a table of that size with the those settings (assuming the ones you don't show are at their defaults).

If the live tuples are really 1% of the dead tuples, the easiest way out of this hole might be a VACUUM FULL of the table.

To avoid getting back into that hole again, you should at least drop autovacuum_vacuum_cost_delay to 2 and increase autovacuum_work_mem to at least 256MB (but I would probably do 1GB).

I would also look through the logs to see if autovacs have a history of getting cancelled before finishing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.