Re: VACUUM FULL versus CLUSTER ON

Postgresql 8.0.4 on FreeBSD 5.4

I have a table consisting of some 300million rows that, every couple of
months, has 100 million rows deleted from it (an immediately vacuumed
afterward). Even though it gets routinely vacuumed (the only
deletions/updates are just the quarterly ones), the freespace map was
not increased in size to keep up with the growing size of the other
tables in the database which do experience many updates,etc.

I suspect that the table is suffering from bloat (not the indexes though
as I drop them prior to the huge delete, then create them anew). What
would be the recommended method for reclaiming the disk space lost due
to bloat? Dropping the 5 indexes on the table and doing a VACUUM FULL,
keeping the indexes and doing a VACUUM FULL (does FULL perform the same
disk moving operations on the indexes as it does on the actual table?),
dropping the indexes except the primary key and CLUSTER ON primary key,
keeping the indexes and doing a CLUSTER ON primary key (again, does
CLUSTER ON just operation on the table proper?)

What are the caveats on using one over the other? I imagine any of the
options I listed above will involve a full table lock. Are there any
differences in the amount of free disk space required for each method?

Thanks,

Sven

[snip]
> as I drop them prior to the huge delete, then create them anew). What
> would be the recommended method for reclaiming the disk space lost due
> to bloat? Dropping the 5 indexes on the table and doing a VACUUM FULL,
> keeping the indexes and doing a VACUUM FULL (does FULL perform the same
> disk moving operations on the indexes as it does on the actual table?),
> dropping the indexes except the primary key and CLUSTER ON primary key,
> keeping the indexes and doing a CLUSTER ON primary key (again, does
> CLUSTER ON just operation on the table proper?)

I won't know for sure, but I guess the least downtime you would get by
not dropping the indexes before the delete, but do a reindex after it.
Then cluster on the primary key...

My reasoning (correct me if I'm wrong): the deletion speed won't be
affected by the indexes, I think deletions don't touch the indexes at
all. The REINDEX command recreates all indexes at once, I think it needs
only one full table scan. That needs the indexes in place, so you
shouldn't drop them. The CLUSTER is a lot faster than VACUUM FULL. The
only problem could be that I think all these operations might take more
disk space than the individual indexing + VACUUM FULL.

Are my assumptions correct ?

Cheers,
Csaba.

On Friday 07 July 2006 08:19, Sven Willenberger wrote:
> Postgresql 8.0.4 on FreeBSD 5.4
>
> I have a table consisting of some 300million rows that, every couple of
> months, has 100 million rows deleted from it (an immediately vacuumed
> afterward). Even though it gets routinely vacuumed (the only
> deletions/updates are just the quarterly ones), the freespace map was
> not increased in size to keep up with the growing size of the other
> tables in the database which do experience many updates,etc.

Based on the size of the table, you may want to:

Backup the table
Drop the table
Restore the table

Is is possible that this will be faster in this instance.

Secondly this sounds like a perfect time for you to consider upgrading to 8.1
and making use of table partitioning. That way you can just truncate the child
table containing the old data.

Sincerely,

Joshua D. Drake

Csaba Nagy <nagy(at)ecircle-ag(dot)com> writes:

> I won't know for sure, but I guess the least downtime you would get by
> not dropping the indexes before the delete, but do a reindex after it.
> Then cluster on the primary key...
>
> My reasoning (correct me if I'm wrong): the deletion speed won't be
> affected by the indexes, I think deletions don't touch the indexes at
> all.

That's true, more or less. I think there's a small hit actually as queries set
the hint bit and the pages have to be flushed.

As long as you're just deleting and not inserting or updating

> The REINDEX command recreates all indexes at once, I think it needs
> only one full table scan.

No, each index build has to do its own full scan. It wouldn't save much
anyways not to, where would you store the tuples in the meantime? And why
would this temporary storage place be any faster than scanning the original
table?

--
greg

On Fri, 2006-07-07 at 09:55 -0700, Joshua D. Drake wrote:
> On Friday 07 July 2006 08:19, Sven Willenberger wrote:
> > Postgresql 8.0.4 on FreeBSD 5.4
> >
> > I have a table consisting of some 300million rows that, every couple of
> > months, has 100 million rows deleted from it (an immediately vacuumed
> > afterward). Even though it gets routinely vacuumed (the only
> > deletions/updates are just the quarterly ones), the freespace map was
> > not increased in size to keep up with the growing size of the other
> > tables in the database which do experience many updates,etc.
>
> Based on the size of the table, you may want to:
>
> Backup the table
> Drop the table
> Restore the table
>
> Is is possible that this will be faster in this instance.
>
> Secondly this sounds like a perfect time for you to consider upgrading to 8.1
> and making use of table partitioning. That way you can just truncate the child
> table containing the old data.
>
> Sincerely,
>
> Joshua D. Drake

Doing a quick check reveals that the relation in question currently
consumes 186GB of space (which I highly suspect is largely bloat). The
delete was just run this past weekend as was the recreation of the
indexes. I have 50GB of disk space left; If I vacuum full, it does not
need to create a temporary copy of the relation and indexes like cluster
does, does it? At this point, I think CLUSTER ON is out of the question
due to the need to create the temporary table and indexes (I will run
out of space during the operation).

I do plan on migrating the whole mess to a new server which will run 8.1
(I had looked at inheritance for partitioning, I am glad to see that 8.1
took the concept and ran with it further :) ) This new server will use
an external SAS array so I should simply be able to add another array as
the need arises and partition to it via tablespace.

Thanks to all who offered suggestions; it would appear that at this
stage my only option to buy some time is try a vacuum full. My final
question: can I leave the indexes in place when I vacuum full? I assume
this will only operate on the table itself?

Sven

> > Sincerely,
> >
> > Joshua D. Drake
>
> Doing a quick check reveals that the relation in question currently
> consumes 186GB of space (which I highly suspect is largely bloat).

Good lord.. .186 gig for a 300 million row table? Unless those are seriously
large rows, you have a TON of bloat.

Joshua D. Drake

--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Fri, 2006-07-07 at 10:41 -0700, Joshua D. Drake wrote:
> > > Sincerely,
> > >
> > > Joshua D. Drake
> >
> > Doing a quick check reveals that the relation in question currently
> > consumes 186GB of space (which I highly suspect is largely bloat).
>
> Good lord.. .186 gig for a 300 million row table? Unless those are seriously
> large rows, you have a TON of bloat.
>
> Joshua D. Drake
>

Yes, that number came from the dbsize functions (in contrib) so I don't
know if that includes the associated indexes as well. The rows are
fairly large, yes, but not enough (IMO) to account for that size. It
will be interesting to see the final size after the vacuum full (which
is the method I have settled on to reclaim space this go round).

Sven

Sven Willenberger presumably uttered the following on 07/07/06 13:52:
> On Fri, 2006-07-07 at 10:41 -0700, Joshua D. Drake wrote:
>>>> Sincerely,
>>>>
>>>> Joshua D. Drake
>>> Doing a quick check reveals that the relation in question currently
>>> consumes 186GB of space (which I highly suspect is largely bloat).
>> Good lord.. .186 gig for a 300 million row table? Unless those are seriously
>> large rows, you have a TON of bloat.
>>
>> Joshua D. Drake
>>
>
> Yes, that number came from the dbsize functions (in contrib) so I don't
> know if that includes the associated indexes as well. The rows are
> fairly large, yes, but not enough (IMO) to account for that size. It
> will be interesting to see the final size after the vacuum full (which
> is the method I have settled on to reclaim space this go round).
>
> Sven
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

Unfortunately it would appear that I cannot vacuum full either as I get an out of
memory error:

# - Memory -

shared_buffers = 5000 # min 16, at least max_connections*2, 8KB each
work_mem = 131072 # min 64, size in KB
maintenance_work_mem = 524288 # min 1024, size in KB
max_stack_depth = 4096 # min 100, size in KB

/boot/loader.conf
kern.maxdsiz="1610612736"
kern.dfldsiz="891289600"

I have disabled other connections to the db except for slony (which will not access
the table in question). I begin a Vacuum full <tablename> and start watching memory
use constantly increase (top). It pushes to:

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
61048 pgsql -4 0 1640M 1472M getblk 1 6:58 16.75% 16.75% postgres

and then it bails:
ERROR: out of memory
DETAIL: Failed on request of size 78.

Server version is 8.03

Is this a known issue?

Sven

>
> Unfortunately it would appear that I cannot vacuum full either as I get an
> out of memory error:
>
>
> # - Memory -
>
> shared_buffers = 5000 # min 16, at least max_connections*2, 8KB
> each work_mem = 131072 # min 64, size in KB
> maintenance_work_mem = 524288 # min 1024, size in KB
> max_stack_depth = 4096 # min 100, size in KB

You could decrease your maintenance_work_mem,

But honestly, at this point I would do the backup restore method.

Joshua D. Drake

--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote:
> >
> > Unfortunately it would appear that I cannot vacuum full either as I get an
> > out of memory error:
> >
> >
> > # - Memory -
> >
> > shared_buffers = 5000 # min 16, at least max_connections*2, 8KB
> > each work_mem = 131072 # min 64, size in KB
> > maintenance_work_mem = 524288 # min 1024, size in KB
> > max_stack_depth = 4096 # min 100, size in KB
>
> You could decrease your maintenance_work_mem,
>
> But honestly, at this point I would do the backup restore method.

Also, this kind of points out that you might not have enough swap
space. On most database servers there's enough hard drive space laying
about to have as large a swap space as you'd like, and I can't count the
number of times a large swap has given me enough to time to catch
runaway processes and keep an ailing server up and running, albeit
hobbling along, rather than having to worry about running out of virtual
memory.

Unless the memory being allocated here just has to be real memory. But
I'm guessing not. Sure, swapping is slow, but at least it will let some
memory hungry processes finish.

On Mon, 2006-07-10 at 10:50 -0500, Scott Marlowe wrote:
> On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote:
> > >
> > > Unfortunately it would appear that I cannot vacuum full either as I get an
> > > out of memory error:
> > >
> > >
> > > # - Memory -
> > >
> > > shared_buffers = 5000 # min 16, at least max_connections*2, 8KB
> > > each work_mem = 131072 # min 64, size in KB
> > > maintenance_work_mem = 524288 # min 1024, size in KB
> > > max_stack_depth = 4096 # min 100, size in KB
> >
> > You could decrease your maintenance_work_mem,
> >
> > But honestly, at this point I would do the backup restore method.
>
> Also, this kind of points out that you might not have enough swap
> space. On most database servers there's enough hard drive space laying
> about to have as large a swap space as you'd like, and I can't count the
> number of times a large swap has given me enough to time to catch
> runaway processes and keep an ailing server up and running, albeit
> hobbling along, rather than having to worry about running out of virtual
> memory.
>
> Unless the memory being allocated here just has to be real memory. But
> I'm guessing not. Sure, swapping is slow, but at least it will let some
> memory hungry processes finish.

The box has 8G of RAM and 10G swap space available to it (almost none of
which touched). The problem was that the VACUUM FULL process never
released any memory. With maintenance work mem set to 512MB, I would
think that it would be enforced such that any given connection would
only be allowed 512MB for maintenance. Why it it growing beyond the 1.5G
I allow system-wide for any given process eludes me right now (and why I
suspect a bad memory leak).

As per the other suggestions, I will end up doing a pg_dump/restore to
reclaim the lost space.

Sven

At 10:50 AM 7/10/2006 -0500, Scott Marlowe wrote:

>On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote:
> > >
> > > Unfortunately it would appear that I cannot vacuum full either as I
> get an
> > > out of memory error:
>
>Also, this kind of points out that you might not have enough swap
>space. On most database servers there's enough hard drive space laying
>about to have as large a swap space as you'd like, and I can't count the
>number of times a large swap has given me enough to time to catch
>runaway processes and keep an ailing server up and running, albeit
>hobbling along, rather than having to worry about running out of virtual
>memory.

In my opinion, there is not enough real memory, or postgresql (or something
else) is using more memory than it should.

Because I prefer the reverse - processes die rather than the entire server
hobble along while your ssh connection attempts (or other arguably
important stuff) keep timing out. Of course some O/Ses appear to randomly
kill processes when out of memory. BTW, I regard any O/S that kills
critical processes such as the disk syncing processes or swap, or "initd"
in _typical_ out-of-memory scenarios as a product of shoddy workmanship.

I'd prefer just enough swap[1] that when maxed out the server is just
slowed enough to be noticeable, rather than effectively dead and
continuously "running" like a "drum memory" computer.

If a normal userland program cannot handle being killed because there is
not enough memory, then I think something is wrong somewhere (e.g. the O/S
is doing stuff like SIGKILLing postgresql, or postgresql is not handling
SIGTERM properly).

Link.

[1] How much is enough? My guess is that the suitable size would be related
to the random read/write throughput from/to the swap, and the largest worst
case amount of memory that would have to be continuously read and written,
and how long you would be willing to wait. If you have programs that
allocate tons of memory but don't ever actually use the full amount, you
can adjust your swap accordingly.

> The box has 8G of RAM and 10G swap space available to it (almost none of
> which touched). The problem was that the VACUUM FULL process never
> released any memory. With maintenance work mem set to 512MB, I would
> think that it would be enforced such that any given connection would
> only be allowed 512MB for maintenance. Why it it growing beyond the 1.5G
> I allow system-wide for any given process eludes me right now (and why I
> suspect a bad memory leak).

My understanding is that the maintenance_work_mem is the amount of RAM
that can be used before PostgreSQL swaps out to disk. This is not the amount
of memory that vacuum full will use. Vacuum full is going to use whatever it
needs to get the job done, and on the table your dealing with, its going to
be alot.

> As per the other suggestions, I will end up doing a pg_dump/restore to
> reclaim the lost space.

You already know my opinion on that ;)

Sincerely,

Joshua D. Drake

>
> Sven

--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:

> Secondly this sounds like a perfect time for you to consider upgrading to 8.1
> and making use of table partitioning.

How does that work, exactly?

NM I found the documentation.

Joseph Shraibman wrote:
> Joshua D. Drake wrote:
>
>> Secondly this sounds like a perfect time for you to consider upgrading
>> to 8.1
>> and making use of table partitioning.
>
>
> How does that work, exactly?

Sven Willenberger writes:

> I do plan on migrating the whole mess to a new server which will run 8.1
> (I had looked at inheritance for partitioning, I am glad to see that 8.1
> took the concept and ran with it further

Coming late to the thread..
If you do consider inheritance be aware that some replication methods will
not work with it.