Thread: vacuum process taking more than 33 hours
Hi all,
I've executed a VACUUM FULL on a database 33GB in size. The process was going fine until it reached a index (I guess it's an index) and there it stopped for more than 30 hours...........the whole weekend......
I've canceled it but I desperately need to free some space at the server's disk. I was thinking about using the TRUNCATE statement at the table I know to be the largest one. I have some questions though:
Also, what else can I do to avoid this kind of problem? Would a REINDEX fix the problem and let the VACUUM command finish?
Please advise.
Thank you.
Best regards,
Mario Behring
I've executed a VACUUM FULL on a database 33GB in size. The process was going fine until it reached a index (I guess it's an index) and there it stopped for more than 30 hours...........the whole weekend......
I've canceled it but I desperately need to free some space at the server's disk. I was thinking about using the TRUNCATE statement at the table I know to be the largest one. I have some questions though:
- Will the TRUNCATE free disk space?
- Can I run this command and let the openNMS software running at the same time? The ONMS product will certainly try to execute INCLUDES and such.
- If the TRUNCATE indeed free disk space, what should I do next, reindex, vacuum ??
Also, what else can I do to avoid this kind of problem? Would a REINDEX fix the problem and let the VACUUM command finish?
Please advise.
Thank you.
Best regards,
Mario Behring
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote: > Hi all, > > I've executed a VACUUM FULL on a database 33GB in size. The process > was going fine until it reached a index (I guess it's an index) and > there it stopped for more than 30 hours...........the whole > weekend...... It may not have been doing anything. VACUUM FULL needs to take an exclusive lock on each table it is processing. It may have been waiting for that lock. > I've canceled it but I desperately need to free some space at the > server's disk. I was thinking about using the TRUNCATE statement at > the table I know to be the largest one. I have some questions > though: TRUNCATE and VACUUM are different beasts. VACUUM recovers space from deleted or updated rows. If you have done neither of those things, then it won't recover any space. TRUNCATE is like DELETE on steriods: it simply removes all the data from your table. TRUNCATE will indeed recover disk, although I can't remember whether it actually returns that disk space to the operating system, or whether it remains allocated for the table in question by postgres. If the latter, a VACUUM FULL on the table in question ought to be enough to get you the space back. A -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun
Hi Andrew,
Why don't you try droping all indexes exept but the constraints ones for each table.
then you'll free aprox half of DB size. then re-create indexes, one by one. and finally you can perform a full vacuum.
that worked for me. :)
cheers
pelle.-
--
Saludos cordiales. Ezequiel L. Pellettieri
Why don't you try droping all indexes exept but the constraints ones for each table.
then you'll free aprox half of DB size. then re-create indexes, one by one. and finally you can perform a full vacuum.
that worked for me. :)
cheers
pelle.-
2007/1/15, Andrew Sullivan <ajs@crankycanuck.ca>:
On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote:
> Hi all,
>
> I've executed a VACUUM FULL on a database 33GB in size. The process
> was going fine until it reached a index (I guess it's an index) and
> there it stopped for more than 30 hours...........the whole
> weekend......
It may not have been doing anything. VACUUM FULL needs to take an
exclusive lock on each table it is processing. It may have been
waiting for that lock.
> I've canceled it but I desperately need to free some space at the
> server's disk. I was thinking about using the TRUNCATE statement at
> the table I know to be the largest one. I have some questions
> though:
TRUNCATE and VACUUM are different beasts. VACUUM recovers space from
deleted or updated rows. If you have done neither of those things,
then it won't recover any space. TRUNCATE is like DELETE on
steriods: it simply removes all the data from your table.
TRUNCATE will indeed recover disk, although I can't remember whether
it actually returns that disk space to the operating system, or
whether it remains allocated for the table in question by postgres.
If the latter, a VACUUM FULL on the table in question ought to be
enough to get you the space back.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Saludos cordiales. Ezequiel L. Pellettieri
On Mon, Jan 15, 2007 at 02:44:15PM +0000, Ezequiel Luis Pellettieri wrote: > Hi Andrew, > > Why don't you try droping all indexes exept but the constraints ones for > each table. I don't have the problem, so I don't know whether that will help. > then you'll free aprox half of DB size. then re-create indexes, one by one. > and finally you can perform a full vacuum. > > that worked for me. :) On some versions of Postgres, it will, but AFAIK the latest versions don't have the same index bloat problems. Moreover, if your vacuum regime is right, you should _never_ have to VACUUM FULL. A -- Andrew Sullivan | ajs@crankycanuck.ca When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes