Horton

Thread

  1. RE: [HACKERS] PHP and PostgreSQL

    Matthew <matt@ctlno.com> — 2000-12-27T13:54:59Z

    	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

  2. Re: RE: [HACKERS] PHP and PostgreSQL

    Adam Lang <aalang@rutgersinsurance.com> — 2000-12-27T14:14:40Z

    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

  3. Re: PHP and PostgreSQL

    Rod Taylor <rbt@zort.on.ca> — 2000-12-27T15:09:59Z

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

The *real* problem with persistent connections is:

Script1:  BEGIN;
Script1:  UPDATE table set row = 'things';
Script2:  Insert into table (id) values ('bad data');
Script1: COMMIT;

Since script2 managed to do a BAD insert in the middle of script1's
transaction, the transaction in script1 fails.  Obvious solution?  Don't do
connection sharing when a transaction is enabled.  The whole persistent
connection thing is only valid for mysql as it's the only thing that doesn't
really support transactions (and even thats partially changed).

They need to look for stuff going through (keywords like BEGIN) and 'lock'
that connection to the single entity that opened it.

It's much easier to write your own.  I wrote a few functions like:

get_connection('DB PARMS');
begin_transaction();

commit_transaction();
close_connection();

All of this is done in a class which has knowledge of all connections that a
script is currently using.  Beginning a transaction locks down the
connection from use by any other handler, they're all bumped to another one.
Problem?  It requires atleast 1 connection per page, but since they're
actually dropped at close it's not so bad.

  4. Re: PHP and PostgreSQL

    mlw <markw@mohawksoft.com> — 2000-12-27T15:15:42Z

    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

  5. Re: [HACKERS] PHP and PostgreSQL

    Karl DeBisschop <karl@debisschop.net> — 2000-12-27T16:45:04Z

    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

  6. Re: PHP and PostgreSQL

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-02T03:57:38Z

    This is interesting.  I always wondered how the persistent connection
stuff handled this, and not I see that it doesn't.

[ Charset ISO-8859-1 unsupported, converting... ]
> > 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?
> 
> The *real* problem with persistent connections is:
> 
> Script1:  BEGIN;
> Script1:  UPDATE table set row = 'things';
> Script2:  Insert into table (id) values ('bad data');
> Script1: COMMIT;
> 
> Since script2 managed to do a BAD insert in the middle of script1's
> transaction, the transaction in script1 fails.  Obvious solution?  Don't do
> connection sharing when a transaction is enabled.  The whole persistent
> connection thing is only valid for mysql as it's the only thing that doesn't
> really support transactions (and even thats partially changed).
> 
> They need to look for stuff going through (keywords like BEGIN) and 'lock'
> that connection to the single entity that opened it.
> 
> It's much easier to write your own.  I wrote a few functions like:
> 
> get_connection('DB PARMS');
> begin_transaction();
> 
> commit_transaction();
> close_connection();
> 
> All of this is done in a class which has knowledge of all connections that a
> script is currently using.  Beginning a transaction locks down the
> connection from use by any other handler, they're all bumped to another one.
> Problem?  It requires atleast 1 connection per page, but since they're
> actually dropped at close it's not so bad.
> 
> 


-- 
  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, Pennsylvania 19026

  7. RE: PHP and PostgreSQL

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-01-02T03:59:45Z

    At my work we were happily cruising along using PHP/Postgres persistent
connections.  This was all happy until we installed Phorum.  Phorum has its
own database abstraction layer, quite separate to all our own database
classes.

Now what happens is if someone browses the phorums for a little while, and
then switches to pages that use our own db classes, you get random slowdowns
(ie. a select occurs, and then times out after 2 mins or so).

If you use psql, and perform the select you get a 'shared memory corruption'
and 'timed out waiting for lock' errors.

Our basic theory is that somehow locks aren't being properly released by
phorum, or the connection pooling is stuffed - although Phorum doesn't use
the LOCK or BEGIN/COMMIT commands.  Actually, we did notice that Phorum
issues a COMMIT whenever it closes a connection, even tho it never issues a
BEGIN.  If we avoid running any Phorum scripts - no problems occur.

In an attempt to fix the problem, we configured phplib and Phorum to use
non-persistent postgresql connections.  This worked fine, until now we have
a particular script, no different to the rest, that cannot acquire a
connection - it just gets an invalid resource handle.  We switched phplib
back to persistent connections, and it works fine again - nothing else was
changed!

So my advice to the PHP people is to just fix Postgres connection handling!

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian
> Sent: Tuesday, January 02, 2001 11:58 AM
> To: Rod Taylor
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] PHP and PostgreSQL
>
>
> This is interesting.  I always wondered how the persistent connection
> stuff handled this, and not I see that it doesn't.
>
> [ Charset ISO-8859-1 unsupported, converting... ]
> > > 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?
> >
> > The *real* problem with persistent connections is:
> >
> > Script1:  BEGIN;
> > Script1:  UPDATE table set row = 'things';
> > Script2:  Insert into table (id) values ('bad data');
> > Script1: COMMIT;
> >
> > Since script2 managed to do a BAD insert in the middle of script1's
> > transaction, the transaction in script1 fails.  Obvious
> solution?  Don't do
> > connection sharing when a transaction is enabled.  The whole persistent
> > connection thing is only valid for mysql as it's the only thing
> that doesn't
> > really support transactions (and even thats partially changed).
> >
> > They need to look for stuff going through (keywords like BEGIN)
> and 'lock'
> > that connection to the single entity that opened it.
> >
> > It's much easier to write your own.  I wrote a few functions like:
> >
> > get_connection('DB PARMS');
> > begin_transaction();
> >
> > commit_transaction();
> > close_connection();
> >
> > All of this is done in a class which has knowledge of all
> connections that a
> > script is currently using.  Beginning a transaction locks down the
> > connection from use by any other handler, they're all bumped to
> another one.
> > Problem?  It requires atleast 1 connection per page, but since they're
> > actually dropped at close it's not so bad.
> >
> >
>
>
> --
>   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, Pennsylvania 19026
>

  8. Re: PHP and PostgreSQL

    Rod Taylor <rbt@zort.on.ca> — 2001-01-02T04:08:16Z

    It was obviously designed with MySQL's "Nobody needs transactions for
webwork" type of situation in mind.

> This is interesting.  I always wondered how the persistent connection
> stuff handled this, and not I see that it doesn't.
>
> [ Charset ISO-8859-1 unsupported, converting... ]
> > > 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?
> >
> > The *real* problem with persistent connections is:
> >
> > Script1:  BEGIN;
> > Script1:  UPDATE table set row = 'things';
> > Script2:  Insert into table (id) values ('bad data');
> > Script1: COMMIT;
> >
> > Since script2 managed to do a BAD insert in the middle of script1's
> > transaction, the transaction in script1 fails.  Obvious solution?  Don't
do
> > connection sharing when a transaction is enabled.  The whole persistent
> > connection thing is only valid for mysql as it's the only thing that
doesn't
> > really support transactions (and even thats partially changed).
> >
> > They need to look for stuff going through (keywords like BEGIN) and
'lock'
> > that connection to the single entity that opened it.
> >
> > It's much easier to write your own.  I wrote a few functions like:
> >
> > get_connection('DB PARMS');
> > begin_transaction();
> >
> > commit_transaction();
> > close_connection();
> >
> > All of this is done in a class which has knowledge of all connections
that a
> > script is currently using.  Beginning a transaction locks down the
> > connection from use by any other handler, they're all bumped to another
one.
> > Problem?  It requires atleast 1 connection per page, but since they're
> > actually dropped at close it's not so bad.
> >
> >
>
>
> --
>   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, Pennsylvania 19026
>

  9. Re: PHP and PostgreSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-02T04:59:56Z

    "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Now what happens is if someone browses the phorums for a little while, and
> then switches to pages that use our own db classes, you get random slowdowns
> (ie. a select occurs, and then times out after 2 mins or so).
> If you use psql, and perform the select you get a 'shared memory corruption'
> and 'timed out waiting for lock' errors.

Ugh.  We would like to think that client misbehavior couldn't cause such
things.  Can you dig into this some more, and provide further details on
what is happening and what the sequence of client requests is?

			regards, tom lane

  10. Re: [INTERFACES] Re: PHP and PostgreSQL

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-02T07:16:53Z

    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, Pennsylvania 19026

  11. Re: [INTERFACES] Re: PHP and PostgreSQL

    mlw <markw@mohawksoft.com> — 2001-01-02T13:31:46Z

    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

  12. Re: [INTERFACES] Re: PHP and PostgreSQL

    Tom Samplonius <tom@sdf.com> — 2001-01-03T04:32:03Z

    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

  13. Re: [INTERFACES] Re: PHP and PostgreSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-03T06:07:16Z

    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

  14. Re: [INTERFACES] Re: PHP and PostgreSQL

    Frank Joerdens <frank@joerdens.de> — 2001-01-03T13:44:32Z

    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 reliability enable the Global Lock
      by removing #define NO_GLOBAL_LOCK in main/main.c. Also don't use
      php_value as it 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