Thread: RE: [HACKERS] PHP and PostgreSQL

RE: [HACKERS] PHP and PostgreSQL

From
Matthew
Date:
I have been asked by the major PHP developer Rasmus Lerdorf to see
ifthe PostgreSQL/PHP interface needs any improvements.
Is the current PostgreSQL interface module in PHP adequate?  Does itsupport all the current libpq features?


The only problem we have run into (and I have heard of others having this
problem also) is with persistent connections.  I have seen discussion on
persistent connection problems but I'm not sure the problem was ever
resolved.  The problem we have seen is that when using persistent
connections the web server doesn't seen to reuse the connections or somthing
to that effect.  The result being that we eventually use up our postgres
limit of 48 connections and nothing can connect to postgre anymore.  It is
possible that this is a configuration problem that we haven't sufficiently
investigated, but I meniton it because I have heard other talk of this.
Anyone have more information?

Matthew



Re: RE: [HACKERS] PHP and PostgreSQL

From
"Adam Lang"
Date:
Yeah, this is not a "bug", but a feature. :)

In a nutshell, the persistent connection number is not how many persistent
connections there are available to the webserver.  It is the number
available per webserver PROCESS.

So, if you have 5 persistent connections, but 10 webserver processes open,
you have 50 persistent connections open.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Matthew" <matt@ctlno.com>
To: "'Bruce Momjian'" <pgman@candle.pha.pa.us>; "PostgreSQL-development"
<pgsql-hackers@postgresql.org>; "PostgreSQL-interfaces"
<pgsql-interfaces@postgresql.org>
Sent: Wednesday, December 27, 2000 8:54 AM
Subject: [INTERFACES] RE: [HACKERS] PHP and PostgreSQL


>
> I have been asked by the major PHP developer Rasmus Lerdorf to see
> if
> the PostgreSQL/PHP interface needs any improvements.
>
> Is the current PostgreSQL interface module in PHP adequate?  Does it
> support all the current libpq features?
>
>
> The only problem we have run into (and I have heard of others having this
> problem also) is with persistent connections.  I have seen discussion on
> persistent connection problems but I'm not sure the problem was ever
> resolved.  The problem we have seen is that when using persistent
> connections the web server doesn't seen to reuse the connections or
somthing
> to that effect.  The result being that we eventually use up our postgres
> limit of 48 connections and nothing can connect to postgre anymore.  It is
> possible that this is a configuration problem that we haven't sufficiently
> investigated, but I meniton it because I have heard other talk of this.
> Anyone have more information?
>
> Matthew



Re: [HACKERS] PHP and PostgreSQL

From
Karl DeBisschop
Date:
Matthew wrote:
> 
>         I have been asked by the major PHP developer Rasmus Lerdorf to see
> if
>         the PostgreSQL/PHP interface needs any improvements.
> 
>         Is the current PostgreSQL interface module in PHP adequate?  Does it
>         support all the current libpq features?
> 
> The only problem we have run into (and I have heard of others having this
> problem also) is with persistent connections.  I have seen discussion on
> persistent connection problems but I'm not sure the problem was ever
> resolved.  The problem we have seen is that when using persistent
> connections the web server doesn't seen to reuse the connections or somthing
> to that effect.  The result being that we eventually use up our postgres
> limit of 48 connections and nothing can connect to postgre anymore.  It is
> possible that this is a configuration problem that we haven't sufficiently
> investigated, but I meniton it because I have heard other talk of this.
> Anyone have more information?

Persistent connections behave exactly as advertised. Each apache process
sets up and maintains persistent connections as needed. The problem is
that for a typical web server, there are so many subprocesses that
persistent connections are probably consume more resources than they
save, unless they are combined with connection pooling across ALL the
apache processes.

Implementation of connection pooling is by far the most serious
shortcoming of the current implementation, IMHO. 

I would dearly love to see this addressed as our postgresql database
sees connections from about 300 servers for 6 databases. Since our
postgresql server cannot support 1800 simultaneous active backends,
persistent connections are useless without pooling. So instead we
initiate 10 or more backends every second for generally very simple
queries. Most of the queries are pretty simple, so I would not be at all
surprised if we sent more system resources opening connections than we
do actually answering queries

-- 
Karl DeBisschop                      kdebisschop@alert.infoplease.com
Learning Network/Information Please  http://www.infoplease.com
Netsaint Plugin Developer            kdebisschop@users.sourceforge.net


Re: PHP and PostgreSQL

From
mlw
Date:
Matthew wrote:
> 
>         I have been asked by the major PHP developer Rasmus Lerdorf to see
> if
>         the PostgreSQL/PHP interface needs any improvements.
> 
>         Is the current PostgreSQL interface module in PHP adequate?  Does it
>         support all the current libpq features?
> 
> The only problem we have run into (and I have heard of others having this
> problem also) is with persistent connections.  I have seen discussion on
> persistent connection problems but I'm not sure the problem was ever
> resolved.  The problem we have seen is that when using persistent
> connections the web server doesn't seen to reuse the connections or somthing
> to that effect.  The result being that we eventually use up our postgres
> limit of 48 connections and nothing can connect to postgre anymore.  It is
> possible that this is a configuration problem that we haven't sufficiently
> investigated, but I meniton it because I have heard other talk of this.
> Anyone have more information?
> 
I have not seen this, though now that you mention it, I will be on the
lookout.

We have a load balenced system with [n] apache servers, each with a
local Postgres database that receives updates from a master.

On the local web servers, we have done a good deal of load testing. Our
apache instances are pretty persistent, i.e. we do not have a time out
or maximum number of transactions. I never see more more postgres
instances than I have apache instances. We are using pg_pConnect to
connect to the postgres system.

The behavior may differ if the database is on a different system.

-- 
http://www.mohawksoft.com


Re: Re: [HACKERS] PHP and PostgreSQL

From
Bruce Momjian
Date:
Does this requested chagne have to do with Apache or PostgreSQL?

w wrote:
> > 
> >         I have been asked by the major PHP developer Rasmus Lerdorf to see
> > if
> >         the PostgreSQL/PHP interface needs any improvements.
> > 
> >         Is the current PostgreSQL interface module in PHP adequate?  Does it
> >         support all the current libpq features?
> > 
> > The only problem we have run into (and I have heard of others having this
> > problem also) is with persistent connections.  I have seen discussion on
> > persistent connection problems but I'm not sure the problem was ever
> > resolved.  The problem we have seen is that when using persistent
> > connections the web server doesn't seen to reuse the connections or somthing
> > to that effect.  The result being that we eventually use up our postgres
> > limit of 48 connections and nothing can connect to postgre anymore.  It is
> > possible that this is a configuration problem that we haven't sufficiently
> > investigated, but I meniton it because I have heard other talk of this.
> > Anyone have more information?
> 
> Persistent connections behave exactly as advertised. Each apache process
> sets up and maintains persistent connections as needed. The problem is
> that for a typical web server, there are so many subprocesses that
> persistent connections are probably consume more resources than they
> save, unless they are combined with connection pooling across ALL the
> apache processes.
> 
> Implementation of connection pooling is by far the most serious
> shortcoming of the current implementation, IMHO. 
> 
> I would dearly love to see this addressed as our postgresql database
> sees connections from about 300 servers for 6 databases. Since our
> postgresql server cannot support 1800 simultaneous active backends,
> persistent connections are useless without pooling. So instead we
> initiate 10 or more backends every second for generally very simple
> queries. Most of the queries are pretty simple, so I would not be at all
> surprised if we sent more system resources opening connections than we
> do actually answering queries
> 
> -- 
> Karl DeBisschop                      kdebisschop@alert.infoplease.com
> Learning Network/Information Please  http://www.infoplease.com
> Netsaint Plugin Developer            kdebisschop@users.sourceforge.net
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Re: PHP and PostgreSQL

From
mlw
Date:
Bruce Momjian wrote:
> 
> Does this requested chagne have to do with Apache or PostgreSQL?
> 
I suspect it is a request that live postgresql processes can linger
around after a connection is completed and be re-assigned to a new
connection as soon as one comes along. This will save the startup cost
of a new postgresql process. This is what apache does.


-- 
http://www.mohawksoft.com


Re: Re: PHP and PostgreSQL

From
Tom Samplonius
Date:
On Tue, 2 Jan 2001, mlw wrote:

> Bruce Momjian wrote:
> > 
> > Does this requested chagne have to do with Apache or PostgreSQL?
> > 
> I suspect it is a request that live postgresql processes can linger
> around after a connection is completed and be re-assigned to a new
> connection as soon as one comes along. This will save the startup cost
> of a new postgresql process. This is what apache does.
 I don't think is really going to provide much of an impact.  Postgres
has to do a lot more initialization per session than Apache.  Mainly
because Postgres has to deal with a stateful protocol, not a stateless one
like Apache.  Besides, as already has been tested, session startup time is
minimal.

> -- 
> http://www.mohawksoft.com

Tom



Re: Re: PHP and PostgreSQL

From
Tom Lane
Date:
Tom Samplonius <tom@sdf.com> writes:
> ... Besides, as already has been tested, session startup time is
> minimal.

Well, mumble ...

I think the startup time is negligible if you are issuing a reasonable
number of queries per session (say a few dozen).  But if you connect,
issue one query, and disconnect, then undoubtedly you will find that
performance sucks.

We could probably do more to improve this situation on the server side,
but IMHO it makes most sense to address the issue on the client side
via connection reuse.  The main reason for this is that a significant
amount of the startup time for a standard connection consists of
authentication overhead and context setup overhead (such as setting the
timezone and character set encoding that the client wants to use).
A general-purpose connection-reuse facility on the server end cannot
eliminate these overheads, whereas it's trivial to avoid them within
the context of a multi-threaded client.

Bottom line: better to solve it by fixing Apache or PHP.
        regards, tom lane


Re: Re: PHP and PostgreSQL

From
Frank Joerdens
Date:
Tom Lane wrote:
[ . . . ]
> A general-purpose connection-reuse facility on the server end cannot
> eliminate these overheads, whereas it's trivial to avoid them within
> the context of a multi-threaded client.

PHP 4.04 does provide support for AOLServer now (which is multithreaded). I haven't had
time to play with it, and it moreover isn't certified for production use yet:

---------------------------------- snip ----------------------------------
NOTE: You should not use this module in production. PHP is not 100% stable     yet in threaded mode. To increase
reliabilityenable the Global Lock     by removing #define NO_GLOBAL_LOCK in main/main.c. Also don't use     php_value
asit will lead to races in a sub-system (use an ini file     instead).
 
---------------------------------- snap ----------------------------------
(from the php/sapi/aolserver/README)

But it might be the way to go forward with the connection pooling issue. Maybe the PHP
people could use some help from the Postgres developers there? I think it has been pointed
out in the context of this thread that Apache, as a multi-process server, could not
properly handle connection pooling because it's not feasible to pool connections across
several Apache children (or was that the problem?).

Regards, Frank