From: Gnanakumar <gnanam@zoniac.com>
To: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org
Subject: Re: Postgresql shared_buffer and SHMMAX configuration
Date: Sat, 10 Jul 2010 18:12:21 +0530
Message-ID: <003f01cb202d$5905f190$0b11d4b0$@com> (raw)
In-Reply-To: <4C36EA8602000025000332EB@gw.wicourts.gov>
References: <01e201cb1f5a$2e3f01f0$8abd05d0$@com>
<4C36EA8602000025000332EB@gw.wicourts.gov>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gnanam@zoniac.com, Kevin.Grittner@wicourts.gov
Subject: Re: Postgresql shared_buffer and SHMMAX configuration
In-Reply-To: <003f01cb202d$5905f190$0b11d4b0$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox