pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Number of connections
9+ messages / 6 participants
[nested] [flat]

* Number of connections
@ 2003-05-17 18:53  David Busby <busby@pnts.com>
  0 siblings, 3 replies; 9+ messages in thread

From: David Busby @ 2003-05-17 18:53 UTC (permalink / raw)
  To: pgsql-php

List,
    I cannot tell from the documentation if pg_pconnect() or pg_connect()
are really different in how the connection pool is managed.  Does anyone
know if that is the case?  Seems that using pg_pconnect would dictate "use a
pooled connection" and pg_connect is "use a pooled connection, or make a new
one".  On "live" apps which is better to use, seems pg_pconnect.  Thoughts?

David Busby
Systems Engineer
busby@pnts.com




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-18 12:28  Raphael Bauduin <raphael.bauduin@be.easynet.net>
  parent: David Busby <busby@pnts.com>
  2 siblings, 3 replies; 9+ messages in thread

From: Raphael Bauduin @ 2003-05-18 12:28 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

A related mail I tried to post on the list yesterday but that I got
back....

when inserting a record in a PHP script, I sometimes use the currval
function on the corresponding sequence to get the id of the row
inserted.

Maybe a stupid question, but I wondered if when using persisten
connection, I could be sure there would be no problem. From the doc,
currval  "Returns the value most recently obtained by nextval  for this
sequence in the current server process."

Can you confirm me several script using the same persistent connection
in parallel are in separate server processes?

Thanks.

Raph



On Sat, May 17, 2003 at 11:53:44AM -0700, David Busby wrote:
> List,
>     I cannot tell from the documentation if pg_pconnect() or pg_connect()
> are really different in how the connection pool is managed.  Does anyone
> know if that is the case?  Seems that using pg_pconnect would dictate "use a
> pooled connection" and pg_connect is "use a pooled connection, or make a new
> one".  On "live" apps which is better to use, seems pg_pconnect.  Thoughts?
> 
> David Busby
> Systems Engineer
> busby@pnts.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-18 13:25  Philipp Ottlinger <ottlinger@computer-systeme.de>
  parent: David Busby <busby@pnts.com>
  2 siblings, 0 replies; 9+ messages in thread

From: Philipp Ottlinger @ 2003-05-18 13:25 UTC (permalink / raw)
  To: pgsql-php

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

David Busby wrote:
<snip>

> one".  On "live" apps which is better to use, seems pg_pconnect.
Thoughts?
>

I prefer pg_pconnect due to its faster reconnect times -
you may use a profiler to validate the effects for your application
(e.g. http://www.adepteo.net/profiler/)

HTH
Phil

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: Weitere Infos: siehe http://www.gnupg.org

iD8DBQE+x4m0QogH4WkR2CwRAtdeAJ9MfXP0zoh5GE8JB7tMzFVt3Kq/NQCdFHZV
Z05VXv/qYlwHzzHl4NDRZ6o=
=fb5o
-----END PGP SIGNATURE-----




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-19 15:37  scott.marlowe <scott.marlowe@ihs.com>
  parent: David Busby <busby@pnts.com>
  2 siblings, 0 replies; 9+ messages in thread

From: scott.marlowe @ 2003-05-19 15:37 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

On Sat, 17 May 2003, David Busby wrote:

> List,
>     I cannot tell from the documentation if pg_pconnect() or pg_connect()
> are really different in how the connection pool is managed.  Does anyone
> know if that is the case?  Seems that using pg_pconnect would dictate "use a
> pooled connection" and pg_connect is "use a pooled connection, or make a new
> one".  On "live" apps which is better to use, seems pg_pconnect.  Thoughts?

This is a far more complex subject than it may at first appear.

The first and most important point is that PHP doesn't "pool" connections 
when using persistant connections.  what happens is that each apache child 
preocess holds a connection open after a pconnect has been used.

this means that using the default apache configuration option of 150 max 
children, and the postgresql default of 32, that after a little bit of 
load is applied, your whole web site comes crashing with errors about no 
more backend connections available.  

I.e. using pconnect without understand the implications, and configuring 
your web site accordingly, is dangerous.  pconnects in PHP are like tiny 
ints in mysql, I consider them to almost be a misfeature, in that they are 
so often used by folks who don't know what they're getting themselves 
into.

pconnects don't save much time.  My testing showed them to be 1,000 times 
faster than regular connects.  Unfortunately, since regular connects run 
in about 1 mS, having persistant connects run at 1 uS doesn't really help.  
I.e. the average PHP page takes 10 to 100 mS to run, so chopping off <1 mS 
from the connect speed is pretty much noise for most folks.

Another issue is that if you use pconnect to two different databases in 
the same cluster in a PHP script, you now have TWO persistant connections 
open at the same.  Open to three databases, three persistant connections.

IF you want to use pconnects, then you need to do some system planning and 
configuration.

If you are running multiple front end apache servers, they need to be 
configured so that you have fewer connects open than the max that 
postgresql can handle at any given time.  So, set max children lower in 
apache (20 to 50 or so) and multiply the max number of different connects 
in each script, times the number of apache servers, add a 10 or 20 for 
overhead, and set the postgresql max connects to that.

Otherwise, your site will have issues with connecting.  And that would 
make it WAY slower than just using plain old connect.




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-23 02:13  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Raphael Bauduin <raphael.bauduin@be.easynet.net>
  2 siblings, 1 reply; 9+ messages in thread

From: Christopher Kings-Lynne @ 2003-05-23 02:13 UTC (permalink / raw)
  To: Raphael Bauduin <raphael.bauduin@be.easynet.net>; David Busby <busby@pnts.com>; +Cc: pgsql-php

> Maybe a stupid question, but I wondered if when using persisten
> connection, I could be sure there would be no problem. From the doc,
> currval  "Returns the value most recently obtained by nextval  for this
> sequence in the current server process."
>
> Can you confirm me several script using the same persistent connection
> in parallel are in separate server processes?

PHP will execute 'begin; rollback;' to your connection before passing it to
another PHP process, guaranteeing that unclosed transactions won't leak.

Chris




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-23 15:45  scott.marlowe <scott.marlowe@ihs.com>
  parent: Raphael Bauduin <raphael.bauduin@be.easynet.net>
  2 siblings, 0 replies; 9+ messages in thread

From: scott.marlowe @ 2003-05-23 15:45 UTC (permalink / raw)
  To: Raphael Bauduin <raphael.bauduin@be.easynet.net>; +Cc: David Busby <busby@pnts.com>; pgsql-php

Actually, you can't be sure.  It is quite possible to build a system in 
PHP that will behave in unwanted ways if you leave transactions open 
across accesses.

Apache is stateless, with a thing layer of semi-statefullness layered on 
top like butter.  This is the keep alive system, which is a stock part of 
the http 1.1 spec.

What happens is that when a user accesses a web page, a certain apache 
backend gets associated to it for a short period of time.

The problem with keep alive is that apache has a short attention span, 
since the default timeout for keep alives is 15 seconds.  Let's say user A 
opens a web page with a form, and edits it for 3 minutes.  His keep alive 
connection is gone.  While it is still very likely that his next request 
will be serviced by the same child as the last time, there is NOT 
guarantee.

Even if the user does make the changes before the timeout, or you crank up 
the timeout to something huge like 30 minutes, they still aren't 
guaranteed to get their own child process back, as if someone requested 
access and all the other children are now tied up in keep alives or 
active requests, the apache server throws the pid of all the kept alive 
and waiting requests and randomly grabs one to service the request.  Poof, 
keep alive gone, not the same connection.

So, you can't count on always getting your old transaction back.  What you 
can do is rollback at the beginning of each script to make sure you're in 
"clean space" transactionally.

For a read only type setup, where you're tossing a cursor around, you 
might be able to check for the existence of one, but I don't know how.

On Sun, 18 May 2003, Raphael Bauduin wrote:

> A related mail I tried to post on the list yesterday but that I got
> back....
> 
> when inserting a record in a PHP script, I sometimes use the currval
> function on the corresponding sequence to get the id of the row
> inserted.
> 
> Maybe a stupid question, but I wondered if when using persisten
> connection, I could be sure there would be no problem. From the doc,
> currval  "Returns the value most recently obtained by nextval  for this
> sequence in the current server process."
> 
> Can you confirm me several script using the same persistent connection
> in parallel are in separate server processes?
> 
> Thanks.
> 
> Raph
> 
> 
> 
> On Sat, May 17, 2003 at 11:53:44AM -0700, David Busby wrote:
> > List,
> >     I cannot tell from the documentation if pg_pconnect() or pg_connect()
> > are really different in how the connection pool is managed.  Does anyone
> > know if that is the case?  Seems that using pg_pconnect would dictate "use a
> > pooled connection" and pg_connect is "use a pooled connection, or make a new
> > one".  On "live" apps which is better to use, seems pg_pconnect.  Thoughts?
> > 
> > David Busby
> > Systems Engineer
> > busby@pnts.com
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-23 15:58  scott.marlowe <scott.marlowe@ihs.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 9+ messages in thread

From: scott.marlowe @ 2003-05-23 15:58 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Raphael Bauduin <raphael.bauduin@be.easynet.net>; David Busby <busby@pnts.com>; pgsql-php

On Fri, 23 May 2003, Christopher Kings-Lynne wrote:

> > Maybe a stupid question, but I wondered if when using persisten
> > connection, I could be sure there would be no problem. From the doc,
> > currval  "Returns the value most recently obtained by nextval  for this
> > sequence in the current server process."
> >
> > Can you confirm me several script using the same persistent connection
> > in parallel are in separate server processes?
> 
> PHP will execute 'begin; rollback;' to your connection before passing it to
> another PHP process, guaranteeing that unclosed transactions won't leak.

Didn't it used to not do that?  I remember posts about people having 
issues with transaction bleedover back in the day.




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-23 17:05  Ernest E Vogelsinger <ernest@vogelsinger.at>
  parent: Raphael Bauduin <raphael.bauduin@be.easynet.net>
  2 siblings, 0 replies; 9+ messages in thread

From: Ernest E Vogelsinger @ 2003-05-23 17:05 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Raphael Bauduin <raphael.bauduin@be.easynet.net>; David Busby <busby@pnts.com>; pgsql-php

At 17:45 23.05.2003, scott.marlowe said:
--------------------[snip]--------------------
>...
>The problem with keep alive is that apache has a short attention span, 
>since the default timeout for keep alives is 15 seconds.  Let's say user A 
>opens a web page with a form, and edits it for 3 minutes.  His keep alive 
>connection is gone.  While it is still very likely that his next request 
>will be serviced by the same child as the last time, there is NOT 
>guarantee.
--------------------[snip]-------------------- 

I've read in another thread (either here, or in php-general) that PHP
clears out any transactions that might be open when you pg_connect() or
ph_pconnect(), by issuing "begin;rollback;".

If this is the case this would mean you simply CANNOT have transactions
open across multiple requests.

Which is a good thing IMHO. Just think of the mess that would be created if
client A opens a transaction, client B (using the same child process,
pg_pconnect()ing to the same database) runs a totally different set of
instructions and commits the transaction. A's "transaction" would be
partially committed by B...

Transactions are designed to be atomic operations, and I strongly believe
they should be treated exactly this way - begin, do your stuff, commit or
rollback. In one request. Anyway, pending transactions may lock rows or
tables, and may keep other parts of an application from running at all.

Just my 2c worth...

-- 
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Number of connections
@ 2003-05-24 09:03  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Christopher Kings-Lynne @ 2003-05-24 09:03 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Raphael Bauduin <raphael.bauduin@be.easynet.net>; David Busby <busby@pnts.com>; pgsql-php

> Didn't it used to not do that?  I remember posts about people having
> issues with transaction bleedover back in the day.

Yeah, but it's been doing it for a while now.  It think in 7.3 they could
probably even trim it down to just ROLLBACK?

Chris





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2003-05-24 09:03 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-17 18:53 Number of connections David Busby <busby@pnts.com>
2003-05-18 12:28 ` Raphael Bauduin <raphael.bauduin@be.easynet.net>
2003-05-23 02:13   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-05-23 15:58     ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-24 09:03       ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-05-23 15:45   ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-23 17:05   ` Ernest E Vogelsinger <ernest@vogelsinger.at>
2003-05-18 13:25 ` Philipp Ottlinger <ottlinger@computer-systeme.de>
2003-05-19 15:37 ` scott.marlowe <scott.marlowe@ihs.com>

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