Horton

Thread

  1. 100 simultaneous connections, critical limit?

    Jón Ragnarsson <jonr@physicallink.com> — 2004-01-14T12:48:17Z

    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.

  2. Re: 100 simultaneous connections, critical limit?

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2004-01-14T13:01:00Z

    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

  3. Re: 100 simultaneous connections, critical limit?

    Chris Browne <cbbrowne@acm.org> — 2004-01-14T13:27:03Z

    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

  4. Re: 100 simultaneous connections, critical limit?

    Jón Ragnarsson <jonr@physicallink.com> — 2004-01-14T13:44:17Z

    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.

  5. Re: 100 simultaneous connections, critical limit?

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-01-14T15:04:53Z

    Christopher Browne <cbbrowne@acm.org> writes:
> Clinging to sanity, jonr@physicallink.com (Jn 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

  6. Re: 100 simultaneous connections, critical limit?

    scott.marlowe <scott.marlowe@ihs.com> — 2004-01-14T17:09:23Z

    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.

  7. Re: 100 simultaneous connections, critical limit?

    Adam Alkins <postgresql@rasadam.com> — 2004-01-14T18:10:14Z

    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

  8. Re: 100 simultaneous connections, critical limit?

    Christoph Nelles <evilazrael@evilazrael.de> — 2004-01-14T18:36:25Z

    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

  9. Re: 100 simultaneous connections, critical limit?

    scott.marlowe <scott.marlowe@ihs.com> — 2004-01-14T21:35:52Z

    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.

  10. Re: 100 simultaneous connections, critical limit?

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2004-01-15T01:28:07Z

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

  11. Re: 100 simultaneous connections, critical limit?

    Andrew McMillan <andrew@catalyst.net.nz> — 2004-01-15T02:57:08Z

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

  12. Re: 100 simultaneous connections, critical limit?

    Thomas Swan <tswan@idigx.com> — 2004-01-16T06:24:45Z

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