pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum full 100 mins plus?
6+ messages / 5 participants
[nested] [flat]
* vacuum full 100 mins plus?
@ 2004-07-14 21:29 Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Re: vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
0 siblings, 1 reply; 6+ messages in thread
From: Patrick Hatcher @ 2004-07-14 21:29 UTC (permalink / raw)
To: pgsql-performance
Should I be concerned that my vacuum process has taken upwards of 100 +
minutes to complete? I dropped all indexes before starting and also
increased the vacuum_mem before starting.
Looking at the output below, it appears that a vacuum full hasn't been done
on this table for quite sometime. Would I be better off exporting the data
vacuuming the table and reimporting the data? I cannot drop the table do
to views attached to the table
mdc_oz=# set vacuum_mem = 10240;
SET
mdc_oz=# vacuum full verbose cdm.cdm_ddw_Tran_item;
INFO: vacuuming "cdm.cdm_ddw_tran_item"
INFO: "cdm_ddw_tran_item": found 15322404 removable, 10950460 nonremovable
row versions in 934724 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 233 to 308 bytes long.
There were 1081 unused item pointers.
Total free space (including removable row versions) is 4474020460 bytes.
544679 pages are or will become empty, including 0 at the end of the table.
692980 pages containing 4433398408 free bytes are potential move
destinations.
CPU 29.55s/4.13u sec elapsed 107.82 sec.
TIA
Patrick Hatcher
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum full 100 mins plus?
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
@ 2004-07-14 22:36 ` Patrick Hatcher <PHatcher@macys.com>
2004-07-15 00:13 ` Re: vacuum full 100 mins plus? Josh Berkus <josh@agliodbs.com>
2004-07-15 02:21 ` Re: vacuum full 100 mins plus? Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 6+ messages in thread
From: Patrick Hatcher @ 2004-07-14 22:36 UTC (permalink / raw)
To: "Patrick Hatcher <PHatcher" <PHatcher@macys.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
Answered my own question. I gave up the vacuum full after 150 mins. I was
able to export to a file, vacuum full the empty table, and reimport in less
than 10 mins. I suspect the empty item pointers and the sheer number of
removable rows was causing an issue.
Patrick Hatcher
<PHatcher@macys.c
om> To
Sent by: pgsql-performance@postgresql.org
pgsql-performance cc
-owner@postgresql
.org Subject
[PERFORM] vacuum full 100 mins
plus?
07/14/2004 02:29
PM
Should I be concerned that my vacuum process has taken upwards of 100 +
minutes to complete? I dropped all indexes before starting and also
increased the vacuum_mem before starting.
Looking at the output below, it appears that a vacuum full hasn't been done
on this table for quite sometime. Would I be better off exporting the data
vacuuming the table and reimporting the data? I cannot drop the table do
to views attached to the table
mdc_oz=# set vacuum_mem = 10240;
SET
mdc_oz=# vacuum full verbose cdm.cdm_ddw_Tran_item;
INFO: vacuuming "cdm.cdm_ddw_tran_item"
INFO: "cdm_ddw_tran_item": found 15322404 removable, 10950460 nonremovable
row versions in 934724 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 233 to 308 bytes long.
There were 1081 unused item pointers.
Total free space (including removable row versions) is 4474020460 bytes.
544679 pages are or will become empty, including 0 at the end of the table.
692980 pages containing 4433398408 free bytes are potential move
destinations.
CPU 29.55s/4.13u sec elapsed 107.82 sec.
TIA
Patrick Hatcher
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum full 100 mins plus?
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Re: vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
@ 2004-07-15 00:13 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2004-07-15 00:13 UTC (permalink / raw)
To: Patrick Hatcher <PHatcher@macys.com>; +Cc: pgsql-performance
Patrick,
> Answered my own question. I gave up the vacuum full after 150 mins. I was
> able to export to a file, vacuum full the empty table, and reimport in less
> than 10 mins. I suspect the empty item pointers and the sheer number of
> removable rows was causing an issue.
Yeah. If you've a table that's not been vacuumed in a month, it's often
faster to clean it out and import it.
I've seen vacuums take up to 3 hours in really bad cases.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum full 100 mins plus?
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Re: vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
@ 2004-07-15 02:21 ` Christopher Browne <cbbrowne@acm.org>
2004-07-15 04:36 ` Re: vacuum full 100 mins plus? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Christopher Browne @ 2004-07-15 02:21 UTC (permalink / raw)
To: pgsql-performance
A long time ago, in a galaxy far, far away, PHatcher@macys.com (Patrick Hatcher) wrote:
> Answered my own question. I gave up the vacuum full after 150 mins. I was
> able to export to a file, vacuum full the empty table, and reimport in less
> than 10 mins. I suspect the empty item pointers and the sheer number of
> removable rows was causing an issue.
In that case, you'd be a little further better off if the steps were:
- drop indices;
- copy table to file (perhaps via pg_dump -t my_table);
- truncate the table, or drop-and-recreate, both of which make
it unnecessary to do _any_ vacuum of the result;
- recreate indices, probably with SORT_MEM set high, to minimize
paging to disk
- analyze the table (no need to vacuum if you haven't created any
dead tuples)
- cut SORT_MEM back down to "normal" sizes
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
Signs of a Klingon Programmer #6: "Debugging? Klingons do not
debug. Our software does not coddle the weak."
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum full 100 mins plus?
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Re: vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-15 02:21 ` Re: vacuum full 100 mins plus? Christopher Browne <cbbrowne@acm.org>
@ 2004-07-15 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-15 15:14 ` Re: vacuum full 100 mins plus? Mike Rylander <miker@purplefrog.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-07-15 04:36 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Christopher Browne <cbbrowne@acm.org> writes:
> A long time ago, in a galaxy far, far away, PHatcher@macys.com (Patrick Hatcher) wrote:
>> Answered my own question. I gave up the vacuum full after 150 mins. I was
>> able to export to a file, vacuum full the empty table, and reimport in less
>> than 10 mins. I suspect the empty item pointers and the sheer number of
>> removable rows was causing an issue.
> In that case, you'd be a little further better off if the steps were:
> - drop indices;
> - copy table to file (perhaps via pg_dump -t my_table);
> - truncate the table, or drop-and-recreate, both of which make
> it unnecessary to do _any_ vacuum of the result;
> - recreate indices, probably with SORT_MEM set high, to minimize
> paging to disk
> - analyze the table (no need to vacuum if you haven't created any
> dead tuples)
> - cut SORT_MEM back down to "normal" sizes
Rather than doing all this manually, you can just CLUSTER on any handy
index. In 7.5, another possibility is to issue one of the forms of
ALTER TABLE that force a table rewrite.
The range of usefulness of VACUUM FULL is really looking narrower and
narrower to me. I can foresee a day when we'll abandon it completely.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuum full 100 mins plus?
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Re: vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-15 02:21 ` Re: vacuum full 100 mins plus? Christopher Browne <cbbrowne@acm.org>
2004-07-15 04:36 ` Re: vacuum full 100 mins plus? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-07-15 15:14 ` Mike Rylander <miker@purplefrog.com>
0 siblings, 0 replies; 6+ messages in thread
From: Mike Rylander @ 2004-07-15 15:14 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
> Christopher Browne <cbbrowne@acm.org> writes:
>> A long time ago, in a galaxy far, farpliers PHatcher@macys.com (Patrick
>> Hatcher) wrote:
>>> Answered my own question. I gave up the vacuum full after 150 mins. I
>>> was able to export to a file, vacuum full the empty table, and reimport
>>> in less
>>> than 10 mins. I suspect the empty item pointers and the sheer number of
>>> removable rows was causing an issue.
>
>> In that case, you'd be a little further better off if the steps were:
>> - drop indices;
>> - copy table to file (perhaps via pg_dump -t my_table);
>> - truncate the table, or drop-and-recreate, both of which make
>> it unnecessary to do _any_ vacuum of the result;
>> - recreate indices, probably with SORT_MEM set high, to minimize
>> paging to disk
>> - analyze the table (no need to vacuum if you haven't created any
>> dead tuples)
>> - cut SORT_MEM back down to "normal" sizes
>
> Rather than doing all this manually, you can just CLUSTER on any handy
> index. In 7.5, another possibility is to issue one of the forms of
> ALTER TABLE that force a table rewrite.
>
> The range of usefulness of VACUUM FULL is really looking narrower and
> narrower to me. I can foresee a day when we'll abandon it completely.
I would love to see this 10lb sledge hammer go away when we have enough tiny
screwdrivers and needlenose pliers to make it obsolete!
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-07-15 15:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-14 21:29 vacuum full 100 mins plus? Patrick Hatcher <PHatcher@macys.com>
2004-07-14 22:36 ` Patrick Hatcher <PHatcher@macys.com>
2004-07-15 00:13 ` Josh Berkus <josh@agliodbs.com>
2004-07-15 02:21 ` Christopher Browne <cbbrowne@acm.org>
2004-07-15 04:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-15 15:14 ` Mike Rylander <miker@purplefrog.com>
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