Re: tuning our database by increasing shared buffer

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