Re: Impact of vacuum full... - Mailing list pgsql-general

Scott Marlowe wrote:
> On Fri, 2006-07-21 at 11:40, Erik Jones wrote:
>
>> Scott Marlowe wrote:
>>
>>> On Fri, 2006-07-21 at 10:13, Erik Jones wrote:
>>>
>>>
>>>> Hello, I was wondering if someone could enlighten me as to the impact to
>>>> the entire database of running VACUUM FULL against a single table.  The
>>>> reason I ask is that at company we work for we have a very large number
>>>> of queue type tables that fill up and empty out on a regular basis
>>>>
>>>>
>>> HOLD ON! Do you empty them by doing something like
>>>
>>> delete from table
>>>
>>> with no where clause?
>>>
>>> If so, then try truncating the table.  That will clean it completely and
>>> reclaim all the dead space, plus it's faster than delete anyway.
>>>
>>> If that doesn't help, look at scheduling more aggressive plain vacuums
>>> (no just autovacuum, but cron job vacuum on specific tables that you
>>> know have a high turnover).
>>>
>>> Vacuum full is basically admitting your regular vacuum schedule isn't /
>>> can't be aggressive enough.
>>>
>>>
>> No!!!  The table is filled and entries are deleted one at a time, or in
>> groups, but definitely not all at once.    So, then what is the
>> difference between scheduling regular vacuum on specific tables v.
>> scheduling vacuum full on specific tables?  Basically, what I want to do
>> is to ensure that when I clean out a table row or rows at a time, the
>> space is immediately freed up.
>>
>
> Oh, ok.  Misunderstood based on your description there.
>
> With regular vacuum, postgresql marks the freed tuples as available, and
> the next time someone writes to the table it uses the freed up space.
> Eventually, the table should stop growing and reach a kind of "stable
> state" where it averages some percentage free (20 to 50% is generally
> considered optimal).
>
> If the space used by your table continues to grow, this points to a
> possible problem with not having a large enough free space map.
>
> Since regular vacuums are MUCH cheaper in terms of locking and such, it
> might be practical to schedule a plain vacuum at the end of any large
> deletes that you currently run.
>
> I'd use regular cronned vacuums on the tables that you know grown a lot
> (or just hit the whole db and not worry about it) and run occasional
> vacuum verbose / vacuum full verbose by hand to see if you have problems
> with your Free Space Map being too small.
>
Awesome!  Thanks, guys, for all of your input/advice.  That's pretty
much how I thought stuff worked after reading the docs but was
confused/misled by other inputs.  With regards to the Free Space Map and
max_fsm_relations: is using the value of "SELECT COUNT(*) FROM
pg_class;" plus some room for growth a good way to set that?

--
erik jones <erik@myemma.com>
software development
emma(r)