Re: Adding more space, and a vacuum question.

On 30 Jan 2011, at 17:14, 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?
>
>>
>> You also don't have the visibility map, which means that (auto)vacuum can't skip bits of the tables it knows don't need vacuuming. Your vacuums will be slower.
>>
>> Autovacuum improved significantly in both 8.4 and 9.0; consider an upgrade.
>
> I will consider it. Thank you.

I'm not trying to push an upgrade on you, but if restoring a dump is one of your better options to get rid of table bloat anyway, now seems a good time.

What I'd do is:
- Find a test system with enough disk space
- Install PG 8.4 on it
- Create a dump of your database, using this 8.4 version of pg_dump
- Restore it to the test version and check everything's alright
- If it is, pull the plug on that 8.3 database - you'll probably want to create a fresh dump here if the system is still in production
- Install an 8.4 and use the previously made dump to restore it
- Don't forget to run analyse right after if it's going to be used right away

You will want to check the release notes. One of the things that sometimes bites people is that 8.4 has tighter type-conversion restrictions. Some implicit type-casts that used to work don't anymore, unless made explicit.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d466a8511732033268635!