0

On our production database autovacuum runs whenever it's needed. The problem is sometime it runs during the day which is the peak time. Looked at lots of difficult articles on web, we should always have it enabled. But just want to see what's the best practice, should I schedule the manual vacuum during the night when the load is low on the server? Instead of let autovacuum start at random time during the day. Or solely rely on autovacuum? Only concern is while the server load is high and autovacuum started at the same time, it may cause the performance issue during that period of time.

Improve this question
4
  • You might want to configure it to run more often, so that each run needs to do less work. But are you just concerned that it "may" cause performance issues, or are do you really have an issue?
    – user1822
    Commented Sep 23, 2022 at 21:43
  • It ran few times during our busy hours. CPU usage spiked normal around 50%, during autovacuum it went up to 80% with high io reads. It has not cause any impart on the performance yet, but I am afraid it may in the future.
    – Ricky Ying
    Commented Sep 24, 2022 at 4:00
  • Hi, and welcome to dba.se! You should be "happy" that your CPU is hitting 50-80% - you've paid for your CPUs - you might as well use them. What you don't want is permanently around 100% - "cruising" between 50 and 80 sounds alright to me. There's not enough space in a comment (or an answer), to go through all of the possibilities for auto-vacuum, but tuned it can be. I suggest you search for tuning autovacuum, optimising autovacuum and similar (check out EnterpriseDB, Cybertec and Percona in particular). You may even want to switch if off during peak I/O times, but be careful!
    – Vérace
    Commented Sep 25, 2022 at 6:12
  • Thanks Vérace. We will do some research on optimising autovacuum.
    – Ricky Ying
    Commented Sep 30, 2022 at 17:30

0

Reset to default

Your Answer

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