Thread: RE: [HACKERS] PHP and PostgreSQL
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
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
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
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
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
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
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
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
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