Thread: Vacuum VS Vacuum Analyze
Been reading a number of the posts regarding vacuum and vacuum analyze. I currently running vacuum nighly using cron and once in a while I run vacuum analyze (as postgres). Any reason why I wouldn't just simply run vacuum analyze each night? Matt Friedman Spry New Media http://www.sprynewmedia.com Lead Programmer/Partner email: matt@sprynewmedia.com phone: 250 744 3655 fax: 250 370 0436
"Matt Friedman" <matt@daart.ca> writes:
> I currently running vacuum nighly using cron and once in a while I run
> vacuum analyze (as postgres).
> Any reason why I wouldn't just simply run vacuum analyze each night?
If you can spare the cycles, you might as well make every vacuum a
vacuum analyze.
regards, tom lane
On Friday, March, 2001-03-23 at 17:42:37, Tom Lane wrote: > "Matt Friedman" <matt@daart.ca> writes: > > I currently running vacuum nighly using cron and once in a while I run > > vacuum analyze (as postgres). > > Any reason why I wouldn't just simply run vacuum analyze each night? > > If you can spare the cycles, you might as well make every vacuum a > vacuum analyze. I have experienced that vacuum, especially vacuum analyze on heavily used database sometimes seems to last forever. A very quick_and_dirty hack is to run it twice: first time I run simple vacuum, but before that I drop all the indices. After recreating of indices I run vacuum analyze. The whole process runs lightning fast (the longest process is to recreate the indices). The only problem is not to allow users to add anything to the database, because it may end up in broken unique-key indices. My solution to that is... temporary shutdown of services using the database (those are helper services for my WWW application) which simply makes my application refuse to work. The whole process is scheduled for a deep night (about 4:00 AM) so hardly anybody can notice ;-) (it takes approx. 5 minutes) The other solution would be not to drop the unique indices (but I don't know the speed penalty in this case). Question is: have I misspotted something? Is this routine of any danger that I fail to notice? regards -- Marek Pętlicki <marpet@buy.pl>
[ Charset ISO-8859-2 unsupported, converting... ] > On Friday, March, 2001-03-23 at 17:42:37, Tom Lane wrote: > > "Matt Friedman" <matt@daart.ca> writes: > > > I currently running vacuum nighly using cron and once in a while I run > > > vacuum analyze (as postgres). > > > Any reason why I wouldn't just simply run vacuum analyze each night? > > > > If you can spare the cycles, you might as well make every vacuum a > > vacuum analyze. > > I have experienced that vacuum, especially vacuum analyze on heavily > used database sometimes seems to last forever. A very quick_and_dirty > hack is to run it twice: first time I run simple vacuum, but before that > I drop all the indices. After recreating of indices I run vacuum analyze. > > The whole process runs lightning fast (the longest process is to > recreate the indices). The only problem is not to allow users to add > anything to the database, because it may end up in broken unique-key > indices. My solution to that is... temporary shutdown of services using > the database (those are helper services for my WWW application) which > simply makes my application refuse to work. The whole process is > scheduled for a deep night (about 4:00 AM) so hardly anybody can notice > ;-) (it takes approx. 5 minutes) You are seeing two things here. First, pre-7.1 vacuum analyze locked the table for the analyze portion as well as the vacuum, which was not needed. 7.1 will lock table only for vacuum, analyze will run with very light lock. The second part is that index updating is very slow in vacuum. We how to address this in 7.2. We ran out of time in 7.1. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026