1

We want to run standard vacuum process on our production database which is over 100 GB and have millions of dead tuples.

Can anyone suggest what system parameters we need to keep in mind for setting cost-based vacuum settings? I mean like CPU/IO/Memory/Disk.

We cannot run vacuum full as the database should be up and running continuously so we just want to attain most appropriate value without affecting the system much.

Improve this question
7
  • You might get more mileage out of pg_repack (github.com/reorg/pg_repack) or create-table-as-select-from-src-then-rename-table steps.
    – bma
    Commented Jul 29, 2013 at 20:50
  • @bma The documentation of pg_repack contains a small line: 'You cannot perform DDL commands of the target table(s) except VACUUM and ANALYZE during pg_repack. In many cases pg_repack will fail and rollback correctly, but there are some cases which may result in data corruption.' Now this means for me that I wouldn't use it under any circumstances, not to mention a production system. CLUSTER and VACUUM FULL take their locks with a reason.
    – András Váczi
    Commented Jul 31, 2013 at 10:05
  • Which PostgreSQL version do you use?
    – András Váczi
    Commented Jul 31, 2013 at 10:22
  • @dezso I agree with your assessment of pg_repack. However, I know people who have used it in production (I wouldn't), but they were only working with databases in the 500-800GB range. My other suggestion is the route I would normally take if I couldn't afford the long blocking operation that ensues from using CLUSTER.
    – bma
    Commented Jul 31, 2013 at 14:34
  • 1
    @bma Well, since writing my comment I was warned that there will be DDL triggers in 9.3 - that way it can be a viable option.
    – András Váczi
    Commented Jul 31, 2013 at 16:32

1 Answer 1

Reset to default
1

The default settings will probably be fine.

That will limit the IO write usage to about 4MB/sec, that is, 4kB/msec = (8KB * vacuum_cost_limit / vacuum_cost_page_dirty / autovacuum_vacuum_cost_delay)

And RAM usage to about 48 MB (autovacuum_max_workers * maintenance_work_mem)

Improve this answer

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.