Skip site navigation (1) Skip section navigation (2)

Re: BUG #5774: VACCUM & REINDEX kills production environement

From: Balamurugan Mahendran <balamurugan(at)adaptavant(dot)com>
To: depesz(at)depesz(dot)com
Cc: Bala Murugan <b2m(at)a-cti(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5774: VACCUM & REINDEX kills production environement
Date: 2010-11-29 15:16:08
Message-ID: AANLkTi=Ju1s945p+XNYKE2OtAhOPWJ-xX+1CcQRHReZ8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
This is how I used to do maintenance through cron job. Please advise if i
need to change my method, Also I used to run re-index all the time while
doing Vacuum (cron job updated twice a week).

queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.brands
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.
contactmethodtype
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contacttype
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.skillset
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.skillsettype
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.location
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contactmethod
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contact2
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contact
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.people
Return code for VaccumDb: 0
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.brands
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contactmethodtype
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contacttype
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.skillset
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.skillsettype
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.location
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contactmethod
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contact2
queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contact

Thanks,
Bala


On Sun, Nov 28, 2010 at 9:53 PM, hubert depesz lubaczewski <
depesz(at)depesz(dot)com> wrote:

> On Sun, Nov 28, 2010 at 07:25:52AM +0000, Bala Murugan wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference:      5774
> > Logged by:          Bala Murugan
> > Email address:      b2m(at)a-cti(dot)com
> > PostgreSQL version: 8.3.7
> > Operating system:   openSUSE 10.3 (X86-64) - Kernel \r (\l).
> > Description:        VACCUM & REINDEX kills production environement
> > Details:
> >
> > Iam running postgres 8.3 version for more than 2 yrs on Amazon EC2
> Instance,
> > in recent days Vaccum and reindex make the application down for more than
> > 2hrs. I am not sure this because of my configuration or postgres.
>
> is it normal vacuum?
>
> or are you using "vacuum full"?
>
> generally - voth vacuum full and reindex do lock tables for exclusive
> access.
>
> that's why you generally don't use them!
>
> vacuum full is especially frowned upon.
>
> as for reindex - if you *really* need it (are you sure? what makes you
> think you need it), then there are ways to do reindex without actually
> using "reindex" command, which are mostly transparent for users, but you
> should check if you really need to run reindex at all.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz  /  blog:
> http://www.depesz.com/
> jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl /
> gg:6749007
>

In response to

Responses

pgsql-bugs by date

Next:From: Mohammed RashadDate: 2010-11-29 15:20:17
Subject: Re: postgresql-8.4 error
Previous:From: Kevin GrittnerDate: 2010-11-29 15:13:24
Subject: Re: postgresql-8.4 error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group