pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum process taking more than 33 hours
4+ messages / 3 participants
[nested] [flat]
* vacuum process taking more than 33 hours
@ 2007-01-15 14:23 Mario Behring <mariobehring@yahoo.com>
2007-01-15 14:32 ` Re: vacuum process taking more than 33 hours Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 4+ messages in thread
From: Mario Behring @ 2007-01-15 14:23 UTC (permalink / raw)
To: pgsql-sql
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:
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
____________________________________________________________________________________
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.
http://tv.yahoo.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum process taking more than 33 hours
2007-01-15 14:23 vacuum process taking more than 33 hours Mario Behring <mariobehring@yahoo.com>
@ 2007-01-15 14:32 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-15 14:44 ` Re: vacuum process taking more than 33 hours Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrew Sullivan @ 2007-01-15 14:32 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum process taking more than 33 hours
2007-01-15 14:23 vacuum process taking more than 33 hours Mario Behring <mariobehring@yahoo.com>
2007-01-15 14:32 ` Re: vacuum process taking more than 33 hours Andrew Sullivan <ajs@crankycanuck.ca>
@ 2007-01-15 14:44 ` Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
2007-01-15 14:53 ` Re: vacuum process taking more than 33 hours Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 4+ messages in thread
From: Ezequiel Luis Pellettieri @ 2007-01-15 14:44 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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.-
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum process taking more than 33 hours
2007-01-15 14:23 vacuum process taking more than 33 hours Mario Behring <mariobehring@yahoo.com>
2007-01-15 14:32 ` Re: vacuum process taking more than 33 hours Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-15 14:44 ` Re: vacuum process taking more than 33 hours Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
@ 2007-01-15 14:53 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2007-01-15 14:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-01-15 14:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-15 14:23 vacuum process taking more than 33 hours Mario Behring <mariobehring@yahoo.com>
2007-01-15 14:32 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-15 14:44 ` Ezequiel Luis Pellettieri <ezequiel.pellettieri@gmail.com>
2007-01-15 14:53 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox