Re: tuning our database by increasing shared buffer

Run a vacuum verbose and look at the output at the end.

Word is that as of 8.4 these parameters will autotune themselvs.

--- On Wed, 24/6/09, Allgood, John <jallgood(at)ohl(dot)com> wrote:

> From: Allgood, John <jallgood(at)ohl(dot)com>
> Subject: Re: [ADMIN] tuning our database by increasing shared buffer
> To: "Barbara Stephenson" <barbara(at)turbocorp(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: pgsql-admin(at)postgresql(dot)org
> Date: Wednesday, 24 June, 2009, 2:34 PM
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Hello All
>
>  
>
> I am working with  Barbara
> on this project and I am curios about
> what would be a good starting place for setting the
> max_fsm_relations and
> max_fsm_pages. Here are the current values max_fsm_pages =
> 153600 and the
> max_fsm_relations is set to the default of 1000. I have
> have read that the
> output from vacuum can help determine the values. We are
> using the autovacuum
> daemon. Is there some logging from that process that could
> help.
>
>  
>
> Thanks
>
>  
>
>  
>
>
>
> John
> Allgood
>
> Senior
> Systems Administrator
>
> Turbo,
> division of OHL
>
> 2251
> Jesse Jewell Pky. NE
>
> Gainesville,
> GA 30507
>
> tel:
> (678) 989-3051  fax: (770) 531-7878
>
>
>  
>
> jallgood(at)ohl(dot)com
>
>
> www.ohl.com
>
>
>
>
>  
>
>
>
>
>
> From:
> pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of
> Barbara
> Stephenson
>
> Sent: Tuesday, June 23, 2009 3:43 PM
>
> To: Tom Lane
>
> Cc: pgsql-admin(at)postgresql(dot)org
>
> Subject: Re: [ADMIN] tuning our database by
> increasing shared buffer
>
>
>
>
>
>  
>
> Thank ypu!
>
>
>
>
>
> Tom Lane wrote:
>
> Barbara Stephenson <barbara(at)turbocorp(dot)com>
> writes: 
>
> We will
> be consolidating from 4 databases to 2 and want to make sure
> that  these parameters are the only ones
> that need changing.   Please
> advise.   
>
>   
>
> Current                                                       
> Future=====                                                         
> =====Max_connection =
> 50                                   
> 125Shared_buffers =
> 16MB                                 
> 48MB   
>
>  You will need to make sure that the
> FSM size parameters are correct forthe combined
> databases, too.   
>
> Shouldn't
> we increase the max_locks_per_transaction from 64 to 100 or
> 128 since we have more than doubled the # of
> connections?   
>
>
>  No, because the lock table size
> automatically scales withmax_connections. 
> (Probably max_locks_per_transaction should have
> beencalled max_locks_per_connection
> ...)   
>
> max_prepared_transaction
> is set at default of 5 which is says if we use it
> toset it to
> max_connection.   
>
>
>  Are you using prepared transactions
> at all?  If not, I'd actuallyrecommend
> setting that to zero to make sure nobody creates a
> preparedtransaction accidentally.  You do
> *not* want anyone doing PREPARETRANSACTION unless
> there's an XA manager or something in place to
> makesure the prepared xact gets committed or
> rolled back reasonably soon.
>                        
> regards, tom lane   
>
>  
>
>
>
> --
>
>
>
>
>
> Regards,
>  Barbara
> StephensonEDI
> Specialist/ProgrammerTurbo, division of
> OHL2251 Jesse Jewell
> PkwyGainesville, GA 
> 30507tel: (678)989-3020 fax:
> (404)935-6171barbara(at)turbocorp(dot)comwww(dot)ohl(dot)com
>
>
>
>
>
>
>
> ______________________________________________________
>
>
>
> This e-mail transmission may contain information that is
> proprietary, privileged and/or confidential and is intended
> exclusively for the person(s) to whom it is addressed. Any
> use, copying, retention or disclosure by any person other
> than the intended recipient or the intended recipient's
> designees is strictly prohibited. If you are not the
> intended recipient or their designee, please notify the
> sender immediately by return e-mail and delete all copies.
>
>
>
>
>