Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am running into problems where Postgres autovacuum processes are interfering with the overall performance of my database. It is making my select, insert, and update queries slower than usual. What are some settings, and optimization strategies that will make autovacuum have less of an impact on my database performance?

Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?

share|improve this question
3  
Please supply your exact PostgreSQL version (select version()) and platform, along with some info on the hardware - CPU, disks, raid controller if any, etc. You should also include info on how autovacuum is configured right now. select name, boot_val, reset_val from pg_settings where name like '%autovacuum%'; Edit your question to add this info and comment here when done. – Craig Ringer Aug 29 '13 at 2:22

From: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server :

"The answer to almost all vacuuming problems is to vacuum more often, not less, so that each individual vacuum operation has less to clean up."

Some basic tuning suggestions at: http://www.postgresql.org/docs/current/static/routine-vacuuming.html#AUTOVACUUM

(Auto)Vaccum details: http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT

share|improve this answer
    
Please fix your last two links above - and, when done, you could also add the main points of those pages here (like you did with the first one). – dezso Sep 2 '13 at 5:22

Few basic points could alleviate your fear.

  1. Running autovaccum will eventually improve the performance of queries. It is not something to harm the performance. This is because autovaccum does the garbage collection of dead tuples in table. so queries need to scan though less number of tuples. In your question, it is not clear how you analysed that autovaccum is creating performance problem. if possible please provide details.

  2. Autovaccum algorithms/logic in Postgres is continuouly improved in new versions. for example, Version 9.2 received a patch for skipping a table if there is lock. so autovaccum will do better job in handling bloat. This logic is further improved to skip blocks which are having locks. in latest version 9.6 (which is in development) received a patch to completely skip all the blocks where all tuples are already freezed. Robert Hass posted about this recently: http://rhaas.blogspot.in/2016/03/no-more-full-table-vacuums.html

  3. You may want to fine tune autovaccum related parameters to suit your system. if possible take the help of expert DBA. Each parameter is documented very well in Postgres documentation : http://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html

So Regarding your question : "Is there a way to make it not consume as much memory/CPU so that client queries can run more efficiently?"

Answer is yes. by fine tuning the parameters mentioned in the above link you can achieve the best results for your system

A general advice will be to always upgrade to latest stable version of PostgreSQL to take advantage of all the autovaccum improvements. Always we should keep in mind that autovaccum is very necessary for keeping the performance of the database. If there is CPU or Memory starvation in your system, consider getting better machine. otherwise tables will become bloated and performance goes down over a period of time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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