Re: tuning our database by increasing shared buffer - Mailing list pgsql-admin
From | Glyn Astill |
---|---|
Subject | Re: tuning our database by increasing shared buffer |
Date | |
Msg-id | 877073.94795.qm@web23604.mail.ird.yahoo.com Whole thread Raw |
In response to | tuning our database by increasing shared buffer (Barbara Stephenson <barbara@turbocorp.com>) |
List | 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@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. > > > > >
pgsql-admin by date: