Alvaro, since you are the autovacuum guru :) What setting do you recommend I make to the autovacuum to see if I can get it to work better, and assuming I cannot get it to complete in a reasonable time, I will run vacuum manually on the 3 tables so I can kill it if I see a deadlock issue.
Obviously I prefer to use autovacuum, but I have to vacuum the files because when they bloat the performance gets affected significantly, resulting in a snowball effect. > -----Original Message----- > From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] > Sent: Monday, January 04, 2010 8:40 PM > To: Scott Marlowe > Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Finetuning Autovacuum > > Scott Marlowe escribió: > > On Mon, Jan 4, 2010 at 6:38 PM, Benjamin Krajmalnik > <k...@illumen.com> wrote: > > > > Initially, I had scheduled tasks through pgagent running a vacuum > analyze > > > every 15 minutes, but other posts I have read here have stated this > could > > > cause deadlocks, and mentioned running autovacuum is preferable > > > > Autovacuum is just a daemon that calls vacuum (regular) for you, so > if > > regular vacuum could cause deadlocks then so could autovacuum. > > FWIW there are some smarts in the deadlock detection code that prefer > to > kill autovacuum if it is blocking some other process (even if it > doesn't > cause a deadlock -- just blocking a user process is enough). This does > not occur with user invoked vacuum, so there indeed can be a difference > here. Note that this means that if you have an operation somewhere > that > wants to get a lock that conflicts with vacuum all the time, it could > be > causing autovacuum to get killed and thus never completing, leading to > catastrophic bloat. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
