Re: degenerate performance on one server of 3

On 6/4/09 6:16 AM, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Jun 4, 2009 at 7:31 AM, Erik Aronesty <erik(at)q32(dot)com> wrote:
>> Seems like "VACUUM FULL" could figure out to do that too depending on
>> the bloat-to-table-size ratio ...
>>
>>   - copy all rows to new table
>>   - lock for a millisecond while renaming tables
>>   - drop old table.
>
> You'd have to lock the table at least against write operations during
> the copy; otherwise concurrent changes might be lost.
>
> AIUI, this is pretty much what CLUSTER does, and I've heard that it
> works as well or better as VACUUM FULL for bloat reclamation.
> However, it's apparently still pessimal:
> http://archives.postgresql.org/pgsql-hackers/2008-08/msg01371.php (I
> had never heard this word before Greg Stark used it in this email, but
> it's a great turn of phrase, so I'm reusing it.)
>

Interesting, I suppose a race between VACUUM FULL and CLUSTER will depend a
lot on the index and how much of the table already exists in RAM.

If the index is in RAM, and most of the table is, CLUSTER will be rather
fast.

>> Locking a whole table for a very long time is scary for admins.
>
> Agreed. It would be nice if we had some kind of "incremental full"
> vacuum that would run for long enough to reclaim a certain number of
> pages and then exit. Then you could clean up this kind of problem
> incrementally instead of in one shot. It would be even nicer if the
> lock strength could be reduced, but I'm guessing that's not easy to do
> or someone would have already done it by now. I haven't read the code
> myself.
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>