Thanks Graig for your comprehensive explanation although I do not understanding everything you said such as pgbouncer and pg_connect. I have just started to use Postgres 9.0 with no prior training.
I live in Canada and where I live has no instructor-led training on Postgres 9.0 with replication. Can you tell where I can get one. I just want a Postgres 9.x administration course which also talks about built-in replication and Slony. I do not mind traveling to USA for it.
Thanks,
On Sun, May 29, 2011 at 4:39 AM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote:
On 29/05/2011 10:44 AM, Edison So wrote:No. PostgreSQL does not have any built-in connection pooling, that was the point of the suggestion, to advise people that they might want to consider it.
Can anyone tell me that if the max_connections is above 100, the server
will use pooling instead?
You should _consider_ using connection pooling instead of high numbers of connections if your application is suitable. You will usually get better throughput and often get better overall query latency if you configure lower max_connections and then use a connection pool like pgbouncer or PgPool-II.
Many people using high max_connections are using PHP and pg_pconnect. Those people should particularly consider using a connection pool instead of increasing max_connections . Most people who have performance issues due to overload seem to have this setup.
A few features aren't suitable for pooling, including LISTEN/NOTIFY, advisory locking, and named server-side prepared statements (explicit SQL "PREPARE").It's dependent on your workload, the capacity of your server, whether requests come in batches or continuously, and all sorts of other things. That's why Tom (wisely) pointed out that naming a number was a really bad idea, even if it was intended only as a vague hint.
For all participants in this particular dsicuss, what is the reasonable
value for max_connections without causing any harm to the Postgres 9.0
server.
Some people on this list clearly run production servers with max_connections in the several-hundreds without any problems. Others have posted asking for help with server load, stalls and memory exhaustion when using only 250 connections.
There's a big difference between an Amazon EC2 node and a real server with a local, big, fast RAID10 array. The former might practically melt down with a configuration that would not be enough to push the latter even close to its limits.
I'm beginning to suspect that the comment I suggested is a bad idea as currently constructed. Maybe the problem cannot be even hinted at in a single short paragraph without creating more confusion than it solves. Something is needed, but perhaps it should just a be a pointer to the documentation:
max_connections = 50
# Thinking of increasing this? Read http://some-documentation-url first!
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
--
Edison
