Skip site navigation (1) Skip section navigation (2)

Re: tuning our database by increasing shared buffer

From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
To: Barbara Stephenson <barbara(at)turbocorp(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, JohnAllgood <jallgood(at)ohl(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: tuning our database by increasing shared buffer
Date: 2009-06-24 13:46:01
Message-ID: 877073.94795.qm@web23604.mail.ird.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.
> 
>  
> 
> 
> 


      

Responses

pgsql-admin by date

Next:From: Allgood, JohnDate: 2009-06-24 14:03:41
Subject: Re: tuning our database by increasing shared buffer
Previous:From: Allgood, JohnDate: 2009-06-24 13:34:08
Subject: Re: tuning our database by increasing shared buffer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group