Thread: 100 simultaneous connections, critical limit?

100 simultaneous connections, critical limit?

From
Jón Ragnarsson
Date:
I am writing a website that will probably have some traffic.
Right now I wrap every .php page in pg_connect() and pg_close().
Then I read somewhere that Postgres only supports 100 simultaneous
connections (default). Is that a limitation? Should I use some other
method when writing code for high-traffic website?
J.



Re: 100 simultaneous connections, critical limit?

From
Shridhar Daithankar
Date:
On Wednesday 14 January 2004 18:18, Jón Ragnarsson wrote:
> I am writing a website that will probably have some traffic.
> Right now I wrap every .php page in pg_connect() and pg_close().
> Then I read somewhere that Postgres only supports 100 simultaneous
> connections (default). Is that a limitation? Should I use some other
> method when writing code for high-traffic website?

Yes. You should rather investigate connection pooling.

I am no php expert but probably this could help you..

http://www.php.net/manual/en/function.pg-pconnect.php

 Shridhar



Re: 100 simultaneous connections, critical limit?

From
Christopher Browne
Date:
Clinging to sanity, jonr@physicallink.com (Jón Ragnarsson) mumbled into her beard:
> I am writing a website that will probably have some traffic.
> Right now I wrap every .php page in pg_connect() and pg_close().
> Then I read somewhere that Postgres only supports 100 simultaneous
> connections (default). Is that a limitation? Should I use some other
> method when writing code for high-traffic website?

I thought the out-of-the-box default was 32.

If you honestly need a LOT of connections, you can configure the
database to support more.  I "upped the limit" on one system to have
512 the other week; certainly supportable, if you have the RAM for it.

It is, however, quite likely that the connect()/close() cuts down on
the efficiency of your application.  If PHP supports some form of
"connection pooling," you should consider using that, as it will cut
down _dramatically_ on the amount of work done establishing/closing
connections, and should let your apps use somewhat fewer connections
more effectively.
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/linux.html
"It has been said  that man is a rational animal.  All  my life I have
been searching for evidence which could support this."
-- Bertrand Russell

Re: 100 simultaneous connections, critical limit?

From
Jón Ragnarsson
Date:
Ok, connection pooling was the thing that I thought of first, but I
haven't found any docs regarding pooling with PHP+Postgres.
OTOH, I designed the application to be as independent from the DB as
possible. (No stored procedures or other Postgres specific stuff)
Thanks,
J.

Christopher Browne wrote:

> Clinging to sanity, jonr@physicallink.com (Jón Ragnarsson) mumbled into her beard:
>
>>I am writing a website that will probably have some traffic.
>>Right now I wrap every .php page in pg_connect() and pg_close().
>>Then I read somewhere that Postgres only supports 100 simultaneous
>>connections (default). Is that a limitation? Should I use some other
>>method when writing code for high-traffic website?
>
>
> I thought the out-of-the-box default was 32.
>
> If you honestly need a LOT of connections, you can configure the
> database to support more.  I "upped the limit" on one system to have
> 512 the other week; certainly supportable, if you have the RAM for it.
>
> It is, however, quite likely that the connect()/close() cuts down on
> the efficiency of your application.  If PHP supports some form of
> "connection pooling," you should consider using that, as it will cut
> down _dramatically_ on the amount of work done establishing/closing
> connections, and should let your apps use somewhat fewer connections
> more effectively.


Re: 100 simultaneous connections, critical limit?

From
"Nick Barr"
Date:
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-
> owner@postgresql.org] On Behalf Of Jón Ragnarsson
> Sent: 14 January 2004 13:44
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] 100 simultaneous connections, critical limit?
>
> Ok, connection pooling was the thing that I thought of first, but I
> haven't found any docs regarding pooling with PHP+Postgres.
> OTOH, I designed the application to be as independent from the DB as
> possible. (No stored procedures or other Postgres specific stuff)
> Thanks,
> J.

As far as I know PHP supports persistent connections to a PG database.
See pg_pconnect instead of pg_connect. Each of the db connections are
tied to a particular Apache process and will stay open for the life of
that process. So basically make sure your Apache config file
(httpd.conf) and PG config file (postgresql.conf) agree on the maximum
number of connections otherwise some pages will not be able to connect
to your database.

This may not be a problem for small sites but on large sites it is, with
heavy loads and large number of concurrent users. For example, consider
a site that must support 500 concurrent connections. If persistent
connections are used at least 500 concurrent connections to PG would be
required, which I guess is probably not recommended.

The way I would like Apache/PHP to work is to have a global pool of
connections to a postgres server, which can be shared around all Apache
processes. This pool can be limited to say 50 or 100 connections.
Problems occur under peak load where all 500 concurrent connections are
in use, but all that should happen is there is a bit of a delay.

Hope that (almost) makes sense,


Kind Regards,

Nick Barr
WebBased Ltd.


> Christopher Browne wrote:
>
> > Clinging to sanity, jonr@physicallink.com (Jón Ragnarsson) mumbled
into
> her beard:
> >
> >>I am writing a website that will probably have some traffic.
> >>Right now I wrap every .php page in pg_connect() and pg_close().
> >>Then I read somewhere that Postgres only supports 100 simultaneous
> >>connections (default). Is that a limitation? Should I use some other
> >>method when writing code for high-traffic website?
> >
> >
> > I thought the out-of-the-box default was 32.
> >
> > If you honestly need a LOT of connections, you can configure the
> > database to support more.  I "upped the limit" on one system to have
> > 512 the other week; certainly supportable, if you have the RAM for
it.
> >
> > It is, however, quite likely that the connect()/close() cuts down on
> > the efficiency of your application.  If PHP supports some form of
> > "connection pooling," you should consider using that, as it will cut
> > down _dramatically_ on the amount of work done establishing/closing
> > connections, and should let your apps use somewhat fewer connections
> > more effectively.
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 8: explain analyze is your friend



Re: 100 simultaneous connections, critical limit?

From
Tom Lane
Date:
Christopher Browne <cbbrowne@acm.org> writes:
> Clinging to sanity, jonr@physicallink.com (J�n Ragnarsson) mumbled into her beard:
>> Then I read somewhere that Postgres only supports 100 simultaneous
>> connections (default).

> I thought the out-of-the-box default was 32.

Pre-7.4 it was 32; in 7.4 it's 100 (if your kernel settings will allow it).
It's important to point out that both of these are trivial-to-alter
configuration settings, not some kind of hardwired limit.  However, the
more backend processes you have, the more RAM you need on the database
server.  It's good advice to look into connection pooling instead of
trying to push max_connections up to the moon.

            regards, tom lane

Re: 100 simultaneous connections, critical limit?

From
"scott.marlowe"
Date:
On Wed, 14 Jan 2004, Jón Ragnarsson wrote:

> I am writing a website that will probably have some traffic.
> Right now I wrap every .php page in pg_connect() and pg_close().
> Then I read somewhere that Postgres only supports 100 simultaneous
> connections (default). Is that a limitation? Should I use some other
> method when writing code for high-traffic website?

A few tips from an old PHP/Apache/Postgresql developer.

1: Avoid pg_pconnect unless you are certain you have load tested the
system and it will behave properly.  pg_pconnect often creates as many
issues as it solves.

2: While php has pretty mediocre run time performance, it's startup /
shutdown / cleanup are quite fast, and it caches previously executed
pages.  Thus, if your pages are relatively small, code-wise, then the
amount of time it will take to execute them, versus the amount of time the
user will spend reading the output will be quite small.  So, you can
likely handle many hundreds of users before hitting any limit on the
database end.

3: Apache can only run so many children too.  The default for the 1.3
branch is 150.  If you decrease that to 50 or so, you are quite unlikely
to ever run out of connections to the database.

4: Postgresql can handle thousands of connections if the server and
postgresql itself are properly configured, so don't worry so much about
that.  You can always increase the max should you need to later.

5: Database connection time in a php script is generally a non-issue.
pg_connect on a fast machine, hitting a local pgsql database generally
runs in about 1/10,000th of a second.  Persistant connects get this down
to about 1/1,000,000th of a second.  Either way, a typical script takes
milliseconds to run, i.e. 1/100th of a second or longer, so the actual
difference between a pg_pconnect and a pg_connect just isn't worth
worrying about in 99% of all circumstances.

6: Profile your user's actions and the time it takes the server versus how
long it takes them to make the next click.  Even the fastest user is
usually much slower than your server, so it takes a whole bunch of them to
start bogging the system down.

7: Profile your machine under parallel load.  Note that machine simos
(i.e. the kind you get from the ab utility) generally represent about 10
to 20 real people.  I.e. if your machine runs well with 20 machine simos,
you can bet on it handling 100 or more real people with ease.


Re: 100 simultaneous connections, critical limit?

From
Adam Alkins
Date:
scott.marlowe wrote:

>A few tips from an old PHP/Apache/Postgresql developer.
>
>1: Avoid pg_pconnect unless you are certain you have load tested the
>system and it will behave properly.  pg_pconnect often creates as many
>issues as it solves.
>
>

I share the above view. I've had little success with persistent
connections. The cost of pg_connect is minimal, pg_pconnect is not a
viable solution IMHO. Connections are rarely actually reused.

--
Adam Alkins
http://www.rasadam.com

Re: 100 simultaneous connections, critical limit?

From
Evil Azrael
Date:
Hi!




AA> scott.marlowe wrote:

>>A few tips from an old PHP/Apache/Postgresql developer.
>>
>>1: Avoid pg_pconnect unless you are certain you have load tested the
>>system and it will behave properly.  pg_pconnect often creates as many
>>issues as it solves.
>>
>>


My experience with persistant connections in PHP is quite similar to
the one of Scott Marlowe. There are some nasty effects if something
is not working. The most harmless results come probably from not closed
transactions which will result in a warning as PHP seems to send
always a BEGIN; ROLLBACK; for reusing a connection.



AA> I share the above view. I've had little success with persistent
AA> connections. The cost of pg_connect is minimal, pg_pconnect is not a
AA> viable solution IMHO. Connections are rarely actually reused.


Still I think itŽs a good way to speed things up. Probably the
connection time it takes in PHP is not so the gain, but the general
saving of processor time. Spawning a new process on the backend can be
a very expensive operation. And if it happens often, it sums up.
Perhaps itŽs only a memory for CPU time deal.

My persistant connections get very evenly used, no matter if there are
2 or 10. The CPU usage for them is very equally distributed.



Christoph Nelles


--
Mit freundlichen Grüssen
Evil Azrael                            mailto:evilazrael@evilazrael.de


Re: 100 simultaneous connections, critical limit?

From
"scott.marlowe"
Date:
On Wed, 14 Jan 2004, Adam Alkins wrote:

> scott.marlowe wrote:
>
> >A few tips from an old PHP/Apache/Postgresql developer.
> >
> >1: Avoid pg_pconnect unless you are certain you have load tested the
> >system and it will behave properly.  pg_pconnect often creates as many
> >issues as it solves.
> >
> >
>
> I share the above view. I've had little success with persistent
> connections. The cost of pg_connect is minimal, pg_pconnect is not a
> viable solution IMHO. Connections are rarely actually reused.

I've found that for best performance with pg_pconnect, you need to
restrict the apache server to a small number of backends, say 40 or 50,
extend keep alive to 60 or so seconds, and use the same exact connection
string all over the place.  Also, set max.persistant.connections or
whatever it is in php.ini to 1 or 2.  Note that max.persistant.connections
is PER BACKEND, not total, in php.ini, so 1 or 2 should be enough for most
types of apps.  3 tops.  Then, setup postgresql for 200 connections, so
you'll never run out.  Tis better to waste a little shared memory and be
safe than it is to get the dreaded out of connections error from
postgresql.

If you do all of the above, pg_pconnect can work pretty well, on things
like dedicated app servers where only one thing is being done and it's
being done a lot.  On general purpose servers with 60 databases and 120
applications, it adds little, although extending the keep alive timeout
helps.

but if you just start using pg_pconnect without reconfiguring and then
testing, it's quite likely your site will topple over under load with out
of connection errors.


Re: 100 simultaneous connections, critical limit?

From
Christopher Kings-Lynne
Date:
> 7: Profile your machine under parallel load.  Note that machine simos
> (i.e. the kind you get from the ab utility) generally represent about 10
> to 20 real people.  I.e. if your machine runs well with 20 machine simos,
> you can bet on it handling 100 or more real people with ease.

8.  Use the Turck MMCache - it rocks.  Works absolutely perfectly and
caches compiled versions of all your PHP scripts - cut the load on our
server by a factor of 5.

Re: 100 simultaneous connections, critical limit?

From
Andrew McMillan
Date:
On Thu, 2004-01-15 at 01:48, Jón Ragnarsson wrote:
> I am writing a website that will probably have some traffic.
> Right now I wrap every .php page in pg_connect() and pg_close().
> Then I read somewhere that Postgres only supports 100 simultaneous
> connections (default). Is that a limitation? Should I use some other
> method when writing code for high-traffic website?

Whether the overhead of pg_connect() pg_close() has a noticeable effect
on your application depends on what you do in between them.  TBH I never
do that second one myself - PHP will close the connection when the page
is finished.

I have developed some applications which are trying to be
as-fast-as-possible and for which I either use pg_pconnect so you have
one DB connection per Apache process, or I use DBBalancer where you have
a pool of connections, and pg_connect is _actually_ connecting to
DBBalancer in a very low-overhead manner and you have a pool of
connections out the back.  I am the Debian package maintainer for
DBBalancer.

You may also want to consider differentiating based on whether the
application is writing to the database or not.  Pooling and persistent
connections can give weird side-effects if transaction scoping is
bollixed in the application - a second page view re-using an earlier
connection which was serving a different page could find itself in the
middle of an unexpected transaction.  Temp tables are one thing that can
bite you here.

There are a few database pooling solutions out there. Using pg_pconnect
is the simplest of these, DBBalancer fixes some of it's issues, and
others go further still.

Another point to consider is that database pooling will give you the
biggest performance increase if your queries are all returning small
datasets.  If you return large datasets it can potentially make things
worse (depending on implementation) through double-handling of the data.

As others have said too: 100 is just a configuration setting in
postgresql.conf - not an implemented limit.

Cheers,
                    Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
              How many things I can do without! - Socrates
-------------------------------------------------------------------------

Re: 100 simultaneous connections, critical limit?

From
Thomas Swan
Date:
scott.marlowe wrote:

>On Wed, 14 Jan 2004, Adam Alkins wrote:
>
>
>
>>scott.marlowe wrote:
>>
>>
>>
>>>A few tips from an old PHP/Apache/Postgresql developer.
>>>
>>>1: Avoid pg_pconnect unless you are certain you have load tested the
>>>system and it will behave properly.  pg_pconnect often creates as many
>>>issues as it solves.
>>>
>>>
>>>
>>>
>>I share the above view. I've had little success with persistent
>>connections. The cost of pg_connect is minimal, pg_pconnect is not a
>>viable solution IMHO. Connections are rarely actually reused.
>>
>>
>
>I've found that for best performance with pg_pconnect, you need to
>restrict the apache server to a small number of backends, say 40 or 50,
>extend keep alive to 60 or so seconds, and use the same exact connection
>string all over the place.  Also, set max.persistant.connections or
>whatever it is in php.ini to 1 or 2.  Note that max.persistant.connections
>is PER BACKEND, not total, in php.ini, so 1 or 2 should be enough for most
>types of apps.  3 tops.  Then, setup postgresql for 200 connections, so
>you'll never run out.  Tis better to waste a little shared memory and be
>safe than it is to get the dreaded out of connections error from
>postgresql.
>
>
>
I disagree.   With the server I have been running for the last two years
we found the the pconnect settings with long keep-alives in apache
consumed far more resources than you would imagine.   We found the
because some clients would not support keep-alive (older IE clients)
correctly.  They would hammer the server with 20-30 individual requests;
apache would keep those processes in keep-alive mode.   When the number
of apache processes were restricted there were DoS problems.   The
short-keep alive pattern works best to keep a single pages related
requests to be served effeciently.   In fact the best performance and
the greatest capacity in real life was with a 3 second timeout for
keep-alive requests.   A modem connection normally won't have sufficient
lag as to time-out on related loads and definitely not a broadband
connection.

Also, depending on your machine you should time the amount of time it
takes to connect to the db.   This server ran about 3-4 milliseconds on
average to connect without pconnect, and it was better to conserve
memory so that none postgresql scripts and applications didn't have the
extra memory footprint of a postgresql connection preventing memory
exhaustion and excessive swapping.

Please keep in mind that this was on a dedicated server with apache and
postgresql and a slew of other processes running on the same machine.
The results may be different for separate process oriented setups.

>If you do all of the above, pg_pconnect can work pretty well, on things
>like dedicated app servers where only one thing is being done and it's
>being done a lot.  On general purpose servers with 60 databases and 120
>applications, it adds little, although extending the keep alive timeout
>helps.
>
>but if you just start using pg_pconnect without reconfiguring and then
>testing, it's quite likely your site will topple over under load with out
>of connection errors.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>