Re: tuning our database by increasing shared buffer - Mailing list pgsql-admin

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

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