Skip site navigation (1) Skip section navigation (2)

Re: Adding more space, and a vacuum question.

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Adding more space, and a vacuum question.
Date: 2011-01-31 01:49:16
Message-ID: 4D46151C.1010001@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Matt WarnerDate: 2011-01-31 02:45:32
Subject: Re: Full Text Index Scanning
Previous:From: Jerry LeVanDate: 2011-01-30 23:54:10
Subject: Re: iPad and Pg revisited...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group