Thread: tuning our database by increasing shared buffer

Hello,
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                    125
Shared_buffers = 16MB                    48MB

My concern is that max_locks_per_transaction and max_prepared_transaction are
still set as default??

Shouldn't we increase the max_locks_per_transaction from 64 to 100 or 128
since we have more than doubled the # of connections?

max_prepared_transaction is set at default of 5 which is says if we use it to
set it to max_connection.

Or leaving those 2 max parameters as default would be ok?
--
Regards,

Barbara Stephenson
EDI Specialist/Programmer
Turbo, division of OHL
2251 Jesse Jewell Pkwy
Gainesville, GA  30507
tel: (678)989-3020 fax: (404)935-6171
barbara@turbocorp.com
www.ohl.com

Barbara Stephenson <barbara@turbocorp.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                    125
> Shared_buffers = 16MB                    48MB

You will need to make sure that the FSM size parameters are correct for
the 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 with
max_connections.  (Probably max_locks_per_transaction should have been
called max_locks_per_connection ...)

> max_prepared_transaction is set at default of 5 which is says if we use it to
> set it to max_connection.

Are you using prepared transactions at all?  If not, I'd actually
recommend setting that to zero to make sure nobody creates a prepared
transaction accidentally.  You do *not* want anyone doing PREPARE
TRANSACTION unless there's an XA manager or something in place to make
sure the prepared xact gets committed or rolled back reasonably soon.

            regards, tom lane

Thank ypu!


Tom Lane wrote:

Barbara Stephenson <barbara@turbocorp.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					125
Shared_buffers = 16MB					48MB   

You will need to make sure that the FSM size parameters are correct for
the 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 with
max_connections.  (Probably max_locks_per_transaction should have been
called max_locks_per_connection ...)
 

max_prepared_transaction is set at default of 5 which is says if we use it to
set it to max_connection.   

Are you using prepared transactions at all?  If not, I'd actually
recommend setting that to zero to make sure nobody creates a prepared
transaction accidentally.  You do *not* want anyone doing PREPARE
TRANSACTION unless there's an XA manager or something in place to make
sure the prepared xact gets committed or rolled back reasonably soon.
		regards, tom lane
 

Regards,

Barbara Stephenson
EDI Specialist/Programmer
Turbo, division of OHL
2251 Jesse Jewell Pkwy
Gainesville, GA  30507
tel: (678)989-3020 fax: (404)935-6171
barbara@turbocorp.com
www.ohl.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.

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@ohl.com> wrote:

> From: Allgood, John <jallgood@ohl.com>
> Subject: Re: [ADMIN] tuning our database by increasing shared buffer
> To: "Barbara Stephenson" <barbara@turbocorp.com>, "Tom Lane" <tgl@sss.pgh.pa.us>
> Cc: pgsql-admin@postgresql.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@ohl.com
>
>
> www.ohl.com
>
>
>
>
>   
>
>
>
>
>
> From:
> pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of
> Barbara
> Stephenson
>
> Sent: Tuesday, June 23, 2009 3:43 PM
>
> To: Tom Lane
>
> Cc: pgsql-admin@postgresql.org
>
> Subject: Re: [ADMIN] tuning our database by
> increasing shared buffer
>
>
>
>
>
>   
>
> Thank ypu!
>
>
>
>
>
> Tom Lane wrote:
>
> Barbara Stephenson <barbara@turbocorp.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@turbocorp.comwww.ohl.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.
>
>
>
>
>

Hello Again

Here is the end output from a vacuum verbose on one of the databases. What exactly am I looking for. If I am reading
thecorrectly then I am ok with the current settings. 

INFO:  free space map contains 2667 pages in 22 relations
DETAIL:  A total of 3008 page slots are in use (including overhead).
3008 page slots are required to track all free space.
Current limits are:  153600 page slots, 1000 relations, using 965 kB.

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@ohl.com
www.ohl.com


-----Original Message-----
From: Glyn Astill [mailto:glynastill@yahoo.co.uk]
Sent: Wednesday, June 24, 2009 9:46 AM
To: Barbara Stephenson; Tom Lane; Allgood, John
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 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@ohl.com> wrote:

> From: Allgood, John <jallgood@ohl.com>
> Subject: Re: [ADMIN] tuning our database by increasing shared buffer
> To: "Barbara Stephenson" <barbara@turbocorp.com>, "Tom Lane" <tgl@sss.pgh.pa.us>
> Cc: pgsql-admin@postgresql.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@ohl.com
>
>
> www.ohl.com
>
>
>
>
>   
>
>
>
>
>
> From:
> pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of
> Barbara
> Stephenson
>
> Sent: Tuesday, June 23, 2009 3:43 PM
>
> To: Tom Lane
>
> Cc: pgsql-admin@postgresql.org
>
> Subject: Re: [ADMIN] tuning our database by
> increasing shared buffer
>
>
>
>
>
>   
>
> Thank ypu!
>
>
>
>
>
> Tom Lane wrote:
>
> Barbara Stephenson <barbara@turbocorp.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@turbocorp.comwww.ohl.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.
>
>
>
>
>




______________________________________________________

This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended
exclusivelyfor the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other
thanthe intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended
recipientor their designee, please notify the sender immediately by return e-mail and delete all copies. 

"Allgood, John" <jallgood@ohl.com> wrote:

> What exactly am I looking for

> INFO:  free space map contains 2667 pages in 22 relations
> DETAIL:  A total of 3008 page slots are in use (including overhead).
> 3008 page slots are required to track all free space.
> Current limits are:  153600 page slots, 1000 relations, using 965
kB.

This is saying that limits of 3008 and 22 would have (barely) been
sufficient as of the vacuum run.  You want to leave a little room
above that, but if you're consistently getting numbers on this order
of magnitude, you could reduce the fsm settings from 153600 and 1000
and get back a little RAM for additional cache space.

-Kevin