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 <cr...@postnewspapers.com.au>wrote: > On 29/05/2011 10:44 AM, Edison So wrote: > >> Can anyone tell me that if the max_connections is above 100, the server >> will use pooling instead? >> > > 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. > > 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"). > > > 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. >> > > 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. > > 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
