Postgresql shared_buffer and SHMMAX configuration

Hi,

Recently we upgraded our production server RAM from 7.5GB to 15GB for the
following reasons:
1. to solve performance issues
2. the number of concurrent users increased from 150 to 300

Our production server is not a dedicated database server and is being shared
with our other application softwares like:
1. Tomcat
2. Apache
3. Cron based scheduled programs
4. Also few Java-based thread programs
all running in the same server.

We are trying to configure postgresql parameters with 15GB RAM.

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

250kB + 8.2kB * shared_buffers + 14.2kB * max_connections
(250kB + 8.2kB * 2359296kB(2304*1024) + 14.2kB * 500) = 19353577.2 * 1024 =
19818063052.8

postgresql.conf
--------------------
shared_buffers
From - 1536MB
To - 2304MB

Since we have other application also running we have taken 15% of the RAM
value for shared_buffers.

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.

My questions are:
1) Is my tuning of PostgreSQL parameters to 15 GB RAM configuration correct?
Experts advice/recommendation on this are highly appreciated.
2) Will the above configuration improve the performance of the database
overall?

Thanks in advance

Regards
Gnanam


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Postgresql shared_buffer and SHMMAX configuration

"Gnanakumar" wrote:
=20
> max_connection=20
> From - 500
> To - 500
>=20
> PGPOOL configuration
> ---------------------
> num_init_children=20
> From - 150
> To - 420
> child_max_connections=20
> From - 20
> To - 30
=20
> We have more than 300 Users accessing our server concurrently so
> we need around 300 concurrent connections.
=20
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.
=20
> Recently we upgraded our production server RAM from 7.5GB to 15GB
=20
> Kernal.SHMMAX=20
> From - 2147483648
> To - 19818063053
=20
> 1) Is my tuning of PostgreSQL parameters to 15 GB RAM
> configuration correct?
=20
No. For starters, you should not be configuring a shared memory
maximum of over 18GB for your 15GB machine.
=20
More like (assuming your "given" settings):
=20
(250 kB) + (2304 MB * 1.025) + (14.2 kB * 500)
=3D 2.48384348 * 10^9 bytes
=20
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.
=20
> 2) Will the above configuration improve the performance of the
> database overall?
=20
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?=20
How many disks in what arrangement?
=20
-Kevin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Postgresql shared_buffer and SHMMAX configuration

> 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.

We have our java servlet code running in Tomcat and the connection is
managed by pgpool.

When we did a Load Test in this environment with 150 connections in
num_init_children we got error in java side saying "DBACCESS CONNECTION
exception no connection could be got"
While there was no error in PGPOOL and POSTGRESQL logs

When we increased this to 420, it was working fine.

Also we don't have connection persistence in java code.

> 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.
Is there any documentation link for the above formula?
250kB + 8.2kB * shared_buffer + 14.2kB * max_Connections Was our formula

and yours is (250 kB) + (2304 MB * 1.025) + (14.2 kB * 500)

> 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?

We have our database server running in Amazon EC2.
It is not dedicated database server our java application is also running in
the same instance.

Regarding server hardware, CPU core, etc. It is 4 virtual cores with 2 EC2
Compute Units each.
For further details on this please refer
http://aws.amazon.com/ec2/faqs/#What_is_an_EC2_Compute_Unit_ and_why_did_you_
introduce_it



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Postgresql shared_buffer and SHMMAX configuration

"Gnanakumar" wrote:

> We have our java servlet code running in Tomcat and the connection
> is managed by pgpool.

If you're running under Tomcat, you might not even need pgpool; see
this recent post:

http://archives.postgresql.org/pgsql-performance/2010-07/msg 00154.php

> When we did a Load Test in this environment with 150 connections
> in num_init_children we got error in java side saying "DBACCESS
> CONNECTION exception no connection could be got"
> While there was no error in PGPOOL and POSTGRESQL logs
>
> When we increased this to 420, it was working fine.

You're not going to get errors with your current configuration, but
you're also not going to see optimal performance. If performance is
"good enough" for your needs, maybe you don't need to care.

>> 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.
> Is there any documentation link for the above formula?
> 250kB + 8.2kB * shared_buffer + 14.2kB * max_Connections Was our
> formula

Actually, I was just using your formula, correcting for an obvious
error -- you took "8.2kB * shared_buffer" to mean that you multiply
the kB of buffer space by 8.2kB. This is obviously silly, since
there's no such thing as square kB. The thing to multiply by 8.2kB
would appear to be the *number of 8kB buffers*, which is allowing
for some overhead involved in tracking the buffers. 8.2 / 8 =
1.025.

>> 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?
>
> We have our database server running in Amazon EC2.
> It is not dedicated database server our java application is also
> running in the same instance.
>
> Regarding server hardware, CPU core, etc. It is 4 virtual cores
> with 2 EC2 Compute Units each.

You said nothing about the effective spindle count. Benchmarks by
myself and others have suggested that PostgreSQL gets best
performance (both response time and throughput) when a connection
pool limits the number of active queries to somewhere around:

((2 * CPU core count) + (effective spindle count))

Beyond that, resource contention and context switching causes
performance to degrade. I always try to use a pooling technique
which will limit active queries on the database to some hard limit,
queuing excess queries to run as other queries finish. You're using
what by all accounts is a good pooling product, but you're not
configuring it to do this for you. And since Tomcat has a built-in
pool, it would probably be better to use that and not even set up an
intermediate pool; although since you already have one I would
recommend benchmarking both ways to check.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin