Re: Adding more space, and a vacuum question.

On 01/31/2011 12:14 AM, Herouth Maoz wrote:
>
> On 30/01/2011, at 12:27, Craig Ringer wrote:
>>
>> OK, so you're pre-8.4 , which means you have the max_fsm settings to
>> play with. Have you seen any messages in the logs about the free space
>> map (fsm)? If your install didn't have a big enough fsm to keep track
>> of deleted tuples, you'd face massive table bloat that a regular
>> vacuum couldn't fix.
>
> Ouch. You're absolutely right. There are messages about max_fsm_pages in
> the postgres log. It's currently set to 153600. According to the
> documentation, I can increase it up to 200000. Will that even help? How
> do I find out how many I need to set it to?

I think the logs suggest what to set. I haven't used 8.3 in ages and
don't remember well.

Increasing it won't help after the fact. You almost certainly have badly
bloated tables. Fixing that will be interesting in your current
low-disk-space situation. VACUUM FULL would work - but will exclusively
lock the table being vacuumed for *ages*, so nothing else can do any
work, not even reads. CLUSTER will do the same, and while it's much
faster, to work it requires enough free disk space to store a complete
copy of the still-valid parts of the table while the bloated original is
still on disk. You may have to look into some of the lockless fake
vacuum full approaches.

I think table bloat identification and management is one of the worst
problems PostgreSQL has remaining. It's too hard, out of the box, to
discover bloat developing, and it's too disruptive to fix it if and when
it does happen. The automatic free space map management in 8.4, and the
ongoing autovacuum improvements, help reduce the chances of bloat
happening, but it's still a pain to monitor for and a pain to fix when
it does happen.

For approaches to possibly fixing your problem, see:

http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/

http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html

--
Craig Ringer