Re: BUG #5774: VACCUM & REINDEX kills production environement - Mailing list 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@depesz.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@a-cti.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@depesz.com / aim:depeszhdl / skype:depesz_hdl /
> gg:6749007
>