Thread: Vacuum runs in a loop?

Vacuum runs in a loop?

From

Wim

Date:

15 September 2003, 17:26:56

Hi all,

I have a question:
I deleted half of the records in my table and to free up the space I do
a VACUUM VERBOSE.
Is it normal that my VACUUM VERBOSE runs in a loop?
If not, what can I do to free up disk space?



INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
0.
        CPU 10.78s/14.52u sec elapsed 831.52 sec.
INFO:  Index i_flowdata_end_time: Pages 226712; Tuples 23875181: Deleted
0.
        CPU 13.96s/14.66u sec elapsed 496.71 sec.
INFO:  Index i_flowdata_src_as: Pages 171170; Tuples 23875181: Deleted
0.
        CPU 10.30s/14.02u sec elapsed 817.77 sec.
INFO:  Index i_flowdata_start_time: Pages 213650; Tuples 23875181:
Deleted 0.
        CPU 12.00s/14.83u sec elapsed 478.88 sec.
INFO:  Removed 1397825 tuples in 21505 pages.
        CPU 2.36s/2.65u sec elapsed 73.53 sec.
INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
0.
        CPU 10.35s/14.19u sec elapsed 834.93 sec.
INFO:  Index i_flowdata_end_time: Pages 226712; Tuples 23875181: Deleted
0.
        CPU 13.66s/14.35u sec elapsed 490.73 sec.
INFO:  Index i_flowdata_src_as: Pages 171170; Tuples 23875181: Deleted
0.
        CPU 11.05s/14.13u sec elapsed 820.02 sec.
INFO:  Index i_flowdata_start_time: Pages 213650; Tuples 23875181:
Deleted 0.
        CPU 12.58s/14.39u sec elapsed 472.55 sec.
INFO:  Removed 1397825 tuples in 21505 pages.
        CPU 2.39s/2.28u sec elapsed 63.14 sec.

INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
0.
        CPU 10.68s/14.19u sec elapsed 830.23 sec.
INFO:  Index i_flowdata_end_time: Pages 226712; Tuples 23875181: Deleted
0.
        CPU 13.89s/14.41u sec elapsed 490.14 sec.
INFO:  Index i_flowdata_src_as: Pages 171170; Tuples 23875181: Deleted
0.
        CPU 10.17s/14.42u sec elapsed 826.22 sec.
INFO:  Index i_flowdata_start_time: Pages 213650; Tuples 23875181:
Deleted 0.
        CPU 11.76s/14.61u sec elapsed 580.53 sec.
INFO:  Removed 1397825 tuples in 21505 pages.
        CPU 2.29s/2.26u sec elapsed 92.32 sec.
INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
0.
        CPU 11.00s/14.56u sec elapsed 1374.93 sec.
INFO:  Index i_flowdata_end_time: Pages 226712; Tuples 23875181: Deleted
0.
        CPU 15.37s/15.35u sec elapsed 785.42 sec.
INFO:  Index i_flowdata_src_as: Pages 171170; Tuples 23875181: Deleted
0.
        CPU 10.30s/14.37u sec elapsed 1278.69 sec.
INFO:  Index i_flowdata_start_time: Pages 213650; Tuples 23875181:
Deleted 0.
        CPU 12.78s/13.93u sec elapsed 479.11 sec.
INFO:  Removed 1397825 tuples in 21505 pages.
        CPU 2.64s/2.16u sec elapsed 72.25 sec.
INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
0.
        CPU 10.30s/13.68u sec elapsed 835.70 sec.
INFO:  Index i_flowdata_end_time: Pages 226712; Tuples 23875181: Deleted
0.
        CPU 13.63s/14.41u sec elapsed 493.88 sec.
INFO:  Index i_flowdata_src_as: Pages 171170; Tuples 23875181: Deleted
0.
        CPU 10.59s/14.19u sec elapsed 826.78 sec.
INFO:  Index i_flowdata_start_time: Pages 213650; Tuples 23875181:
Deleted 0.
        CPU 12.90s/14.34u sec elapsed 478.05 sec.
INFO:  Removed 1397825 tuples in 21505 pages.
        CPU 2.31s/2.31u sec elapsed 63.51 sec.



Cheers!
Wim De Hul
------------------------------------------------------------------------------
IP SERVICES SPECIALIST
Belgacom Carrier and Wholesale Business Unit (CBU)
------------------------------------------------------------------------------
AS6774
Ripe : WDH25-RIPE
reply-to : NOC{at}belbone{dot}be
peering : peering{at}belbone{dot}be
NOC Tel : +32 2/547.51.00
www.belgacom.be/carrier
------------------------------------------------------------------------------
------------------------------------------------------------------------------
7:23pm up 6 days, 7:09, 1 user, load average: 1.30, 1.57, 1.56

Re: Vacuum runs in a loop?

From

Tom Lane

Date:

15 September 2003, 17:47:52

Wim <wdh@belbone.be> writes:
> I deleted half of the records in my table and to free up the space I do
> a VACUUM VERBOSE.
> Is it normal that my VACUUM VERBOSE runs in a loop?
> If not, what can I do to free up disk space?

It's normal for VACUUM to make multiple passes over the indexes when
it's got to clean up lots of tuples.  You can reduce the number of
passes by increasing vacuum_mem, which basically limits the number of
dead tuples that VACUUM can remember at one time.

It seems odd though that the indexes report "Deleted 0" each time.

> INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
> 0.
>         CPU 10.78s/14.52u sec elapsed 831.52 sec.

Normally I'd expect the index deleted count to agree with the number of
tuples removed from the underlying table in each pass:

> INFO:  Removed 1397825 tuples in 21505 pages.
>         CPU 2.36s/2.65u sec elapsed 73.53 sec.

These wouldn't happen to be partial indexes would they?  If they're
partial, and would not have had entries for the rows you are cleaning
out, then the behavior makes sense to me.  Otherwise there's something
weird going on.

The indexes seem a tad bloated compared to the underlying file size
anyway.  Maybe you should REINDEX them.

            regards, tom lane

Re: Vacuum runs in a loop?

From

Wim

Date:

15 September 2003, 18:25:43

On Mon, 2003-09-15 at 19:47, Tom Lane wrote:

> It seems odd though that the indexes report "Deleted 0" each time.
>
> > INFO:  Index i_flowdata_dst_as: Pages 171123; Tuples 23875181: Deleted
> > 0.
> >         CPU 10.78s/14.52u sec elapsed 831.52 sec.
>
> Normally I'd expect the index deleted count to agree with the number of
> tuples removed from the underlying table in each pass:
>
> > INFO:  Removed 1397825 tuples in 21505 pages.
> >         CPU 2.36s/2.65u sec elapsed 73.53 sec.
>
> These wouldn't happen to be partial indexes would they?  If they're
> partial, and would not have had entries for the rows you are cleaning
> out, then the behavior makes sense to me.  Otherwise there's something
> weird going on.
>
> The indexes seem a tad bloated compared to the underlying file size
> anyway.  Maybe you should REINDEX them.
>
>             regards, tom lane
>
Weird! After dropping the indexes, a lot of disk space became free!
I vacuum the table now and re-create the indexes.

Thanx Tom!

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Cheers!
Wim De Hul
------------------------------------------------------------------------------
IP SERVICES SPECIALIST
Belgacom Carrier and Wholesale Business Unit (CBU)
------------------------------------------------------------------------------
AS6774
Ripe : WDH25-RIPE
reply-to : NOC{at}belbone{dot}be
peering : peering{at}belbone{dot}be
NOC Tel : +32 2/547.51.00
www.belgacom.be/carrier
------------------------------------------------------------------------------
I want to die quietly in my sleep, like my grandfather.
Not screaming in terror, like his passengers.
------------------------------------------------------------------------------
8:21pm up 6 days, 8:08, 1 user, load average: 1.15, 1.08, 1.14