Thread: 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@ecircle-ag.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/
How long does it take do a database dump (with gzip -1 via | and > ), drop
this database
and create the database and restore it from the backup. That is my solution,
but I dont
know how long it will take to restore your database and i dont have so large
databases.
> Secondly this sounds like a perfect time for you to consider upgrading to
8.1
I would not do this without a test (not only a dump/restore test, I would
test it with
your application too)
Greetings,
-Franz
-----Ursprüngliche Nachricht-----
Von: Sven Willenberger [mailto:sven@dmv.com]
Gesendet: Freitag, 7. Juli 2006 19:26
An: Joshua D. Drake
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] VACUUM FULL versus CLUSTER ON
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
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
> 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/
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.
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?
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.