Re: Index Problem?

Tom Lane wrote:

>Josh Berkus <josh@agliodbs.com> writes:
>
>
>>A better way to set this would be to run VACUUM VERBOSE ANALYZE right after
>>doing one of your update batches, and see how many dead pages are being
>>reclaimed, and then set max_fsm_pages to that # + 50% (or more).
>>
>>
>
>Actually, since he's running 7.4, there's an even better way.  Do a
>"VACUUM VERBOSE" (full-database vacuum --- doesn't matter whether you
>ANALYZE or not).  At the end of the very voluminous output, you'll see
>something like
>
>INFO:  free space map: 240 relations, 490 pages stored; 4080 total pages needed
>DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.
>
>Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to
>exactly cover the present freespace needs of my system.  I concur with
>the suggestion to bump that up a good deal, of course, but that gives
>you a real number to start from.
>
>The DETAIL part of the message shows my current settings (which are the
>defaults) and what the FSM is costing me in shared memory space.
>
>
>
Okay, after running the function VACUUM VERBOSE is telling me:
 INFO:  free space map: 136 relations, 25014 pages stored; 22608 total
pages needed
 DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 178 kB
shared memory.

My max_fsm_pages was set to 20,000 and I reset it to 40,000 on the dev
server and the function ran about 20-30% faster, so I'll try the same on
the production server. Thanks for the analysis of the VACUUM info.

Ron