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
logsabout the free space map (fsm)? If your install didn't have a big enough fsm to keep track of deleted tuples, you'd
facemassive 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
Ineed 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
needvacuuming. 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
bloatanyway, 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
stillin 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-conversionrestrictions. 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!