pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-performance@postgresql.org
Subject: Re: Index Problem?
Date: Fri, 16 Apr 2004 14:54:55 -0700
Message-ID: <4080562F.6050006@syscor.com> (raw)
In-Reply-To: <2408.1082145927@sss.pgh.pa.us>
References: <40800CC2.3050707@syscor.com>
	<200404161001.52989.josh@agliodbs.com>
	<40801E1B.4020401@syscor.com>
	<200404161210.47995.josh@agliodbs.com>
	<2408.1082145927@sss.pgh.pa.us>

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




view thread (13+ messages)  latest in thread

Message-ID: <4080562F.6050006@syscor.com>
Permalink:  ../4080562F.6050006@syscor.com/
Also on:    postgresql.org/message-id/4080562F.6050006@syscor.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rstpierre@syscor.com
  Subject: Re: Index Problem?
  In-Reply-To: <4080562F.6050006@syscor.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox