Re: Postgresql shared_buffer and SHMMAX configuration

"Gnanakumar" <gnanam@zoniac.com> wrote:

> max_connection
>     From - 500
>     To - 500
>
> PGPOOL configuration
> ---------------------
> num_init_children
>     From - 150
>     To - 420
> child_max_connections
>     From - 20
>     To - 30

> We have more than 300 Users accessing our server concurrently so
> we need around 300 concurrent connections.

Not to the database itself, you don't; and it's probably killing
performance for you to try to do that.  You should use your
connection pooler to funnel that many client-side connections down
to a much smaller number of database connections.  Your pgpool
configuration doesn't seem to be doing that.

> Recently we upgraded our production server RAM from 7.5GB to 15GB

> Kernal.SHMMAX
>    From - 2147483648
>    To   - 19818063053

> 1) Is my tuning of PostgreSQL parameters to 15 GB RAM
> configuration correct?

No.  For starters, you should not be configuring a shared memory
maximum of over 18GB for your 15GB machine.

More like (assuming your "given" settings):

(250 kB) + (2304 MB * 1.025) + (14.2 kB * 500)
 = 2.48384348 * 10^9 bytes

plus whatever shared memory you need for other processes and a
little "slush".  I might just go to something in the 4GB range,
unless I know something else needs a lot.

> 2) Will the above configuration improve the performance of the
> database overall?

I would expect these changes in the pgpool and PostgreSQL settings
to actually make things worse, although the extra caching from the
additional RAM may counteract that to some degree.  Before I could
venture a suggestion on what good settings might be, I would need to
know more about the database server hardware.  How many CPU cores?
How many disks in what arrangement?

-Kevin