Re: Persistent connections in PHP

Hi,
Does the connection pooling feature of PHP cause the persistent
connections to keep the properties between accesses? E.g., if a user
takes a connection, sets a timezone to it using SET TIMEZONE, will the
next user who happens to take this connection get it in that same state,
or will it be reset to a blank or starting state as though it had been
opened? Also, what about temp tables? Will they be present to the second
user if the first user set some up?
- Naz.

2007/8/13, Naz Gassiep <naz(at)mira(dot)net>:
> Hi,
> Does the connection pooling feature of PHP cause the persistent
> connections to keep the properties between accesses? E.g., if a user
> takes a connection, sets a timezone to it using SET TIMEZONE, will the
> next user who happens to take this connection get it in that same state,
> or will it be reset to a blank or starting state as though it had been
> opened? Also, what about temp tables? Will they be present to the second
> user if the first user set some up?
> - Naz.
>

Yes, it's works like you write. But connection assigning is little bit
random and you cannot count with it.

Regards
Pavel Stehule

On 8/13/07, Naz Gassiep <naz(at)mira(dot)net> wrote:
> Hi,
> Does the connection pooling feature of PHP cause the persistent
> connections to keep the properties between accesses? E.g., if a user
> takes a connection, sets a timezone to it using SET TIMEZONE, will the
> next user who happens to take this connection get it in that same state,
> or will it be reset to a blank or starting state as though it had been
> opened? Also, what about temp tables? Will they be present to the second
> user if the first user set some up?

Except for a few special uses, persistent connections in php are more
of a misfeature. They are NOT pooling, in the standard sense. Most
of the time they cause more problems than they solve.

On Aug 13, 2007, at 9:35 AM, Scott Marlowe wrote:

> On 8/13/07, Naz Gassiep <naz(at)mira(dot)net> wrote:
>> Hi,
>> Does the connection pooling feature of PHP cause the persistent
>> connections to keep the properties between accesses? E.g., if a user
>> takes a connection, sets a timezone to it using SET TIMEZONE, will
>> the
>> next user who happens to take this connection get it in that same
>> state,
>> or will it be reset to a blank or starting state as though it had
>> been
>> opened? Also, what about temp tables? Will they be present to the
>> second
>> user if the first user set some up?
>
> Except for a few special uses, persistent connections in php are more
> of a misfeature. They are NOT pooling, in the standard sense. Most
> of the time they cause more problems than they solve.

I'll agree with Scott on this one. (Not that I can recall
specifically ever disagreeing with him before...). Unless you know
all of the potential caveats associated with php's persisent postgres
connections and have a use case that fits them, don't use them. If
you need something to pool connections, look at pgpool.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Mon, 13 Aug 2007 09:44:26 -0500
Erik Jones <erik(at)myemma(dot)com> wrote:

> I'll agree with Scott on this one. (Not that I can recall
> specifically ever disagreeing with him before...). Unless you
> know all of the potential caveats associated with php's persisent
> postgres connections and have a use case that fits them, don't use
> them. If you need something to pool connections, look at pgpool.

Could elaborate a little on the problems with using php's persistent
connections?

Personally I use ADODB php abstraction library (adodb.sf.net) for my
database stuff and I think there's a way to enable persistent
connections though I just use the default connection.

I've heard before that php's persistent connections are to be
avoided, was just curious as to why though?

Thanks!

Josh

Le lundi 13 août 2007, Erik Jones a écrit :
> If you need something to pool connections, look at pgpool.

Or better yet, pgbouncer. At least for my values of better :)
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer
http://pgfoundry.org/projects/pgbouncer/

Hope this helps,
--
dim

On 8/13/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> On Mon, 13 Aug 2007 09:44:26 -0500
> Erik Jones <erik(at)myemma(dot)com> wrote:
>
> > I'll agree with Scott on this one. (Not that I can recall
> > specifically ever disagreeing with him before...). Unless you
> > know all of the potential caveats associated with php's persisent
> > postgres connections and have a use case that fits them, don't use
> > them. If you need something to pool connections, look at pgpool.
>
> Could elaborate a little on the problems with using php's persistent
> connections?
>
> Personally I use ADODB php abstraction library (adodb.sf.net) for my
> database stuff and I think there's a way to enable persistent
> connections though I just use the default connection.
>
> I've heard before that php's persistent connections are to be
> avoided, was just curious as to why though?

OK, there are a few things that gather together to make php's
persistant connections a problem.

1: Each apache / php process maintains its own connections, not
sharing with others. So it's NOT connection pooling, but people tend
to think it is.
2: Each unique connection creates another persistent connection for
an apache/php child process. If you routinely connect to multiple
servers / databases or as > 1 user, then each one of those
combinations that is unique makes another persistent connection.
3: There's no facility in PHP to clean an old connection out and make
sure it's in some kind of consistent state when you get it. It's in
exactly the same state it was when the previous php script finished
with it. Half completed transactions, partial sql statements,
sequence functions like currval() may have values that don't apply to
you.
4: pg_close can't close a persistent connection. Once it's open, it
stays open until the child process is harvested.
5: Apache, by default, is configured for 150 child processes.
Postgresql, and many other databases for that matter, are configured
for 100 or less. Even if apache only opens one connection to one
database with one user account, it will eventually try to open the
101st connection to postgresql and fail. So, the default
configuration of apache / postgresql for number of connections is
unsafe for pconnect.
6: The reason for connection pooling is primarily to twofold. One is
to allow very fast connections to your database when doing lots of
small things where connection time will cost too much. The other is
to prevent your database from having lots of stale / idle connections
that cause it to waste memory and to be slower since each backend
needs to communicate with every other backend some amount of data some
times. pconnect takes care of the first problem, but exacerbates the
second.

P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the
time, and it's really great for simple small scripts that need to be
done NOW and need to be lightweight. I even use pconnect a bit. But
my machine is set for 50 or fewer apache children and 150 postgresql
connects, and I only use pconnect on small, lightweight things that
need to zoom. Everything else gets regular old connect.

On 8/13/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On 8/13/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> > On Mon, 13 Aug 2007 09:44:26 -0500
> > Erik Jones <erik(at)myemma(dot)com> wrote:
> >
> > > I'll agree with Scott on this one. (Not that I can recall
> > > specifically ever disagreeing with him before...). Unless you
> > > know all of the potential caveats associated with php's persisent
> > > postgres connections and have a use case that fits them, don't use
> > > them. If you need something to pool connections, look at pgpool.
> >
> > Could elaborate a little on the problems with using php's persistent
> > connections?
> >
> > Personally I use ADODB php abstraction library (adodb.sf.net) for my
> > database stuff and I think there's a way to enable persistent
> > connections though I just use the default connection.
> >
> > I've heard before that php's persistent connections are to be
> > avoided, was just curious as to why though?
>
> OK, there are a few things that gather together to make php's
> persistant connections a problem.
>
> 1: Each apache / php process maintains its own connections, not
> sharing with others. So it's NOT connection pooling, but people tend
> to think it is.
> 2: Each unique connection creates another persistent connection for
> an apache/php child process. If you routinely connect to multiple
> servers / databases or as > 1 user, then each one of those
> combinations that is unique makes another persistent connection.
> 3: There's no facility in PHP to clean an old connection out and make
> sure it's in some kind of consistent state when you get it. It's in
> exactly the same state it was when the previous php script finished
> with it. Half completed transactions, partial sql statements,
> sequence functions like currval() may have values that don't apply to
> you.
> 4: pg_close can't close a persistent connection. Once it's open, it
> stays open until the child process is harvested.
> 5: Apache, by default, is configured for 150 child processes.
> Postgresql, and many other databases for that matter, are configured
> for 100 or less. Even if apache only opens one connection to one
> database with one user account, it will eventually try to open the
> 101st connection to postgresql and fail. So, the default
> configuration of apache / postgresql for number of connections is
> unsafe for pconnect.
> 6: The reason for connection pooling is primarily to twofold. One is
> to allow very fast connections to your database when doing lots of
> small things where connection time will cost too much. The other is
> to prevent your database from having lots of stale / idle connections
> that cause it to waste memory and to be slower since each backend
> needs to communicate with every other backend some amount of data some
> times. pconnect takes care of the first problem, but exacerbates the
> second.
>
> P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the
> time, and it's really great for simple small scripts that need to be
> done NOW and need to be lightweight. I even use pconnect a bit. But
> my machine is set for 50 or fewer apache children and 150 postgresql
> connects, and I only use pconnect on small, lightweight things that
> need to zoom. Everything else gets regular old connect.

Oh, one other thing that contributes to the problem is that the
php.ini file has an entry for max persistent connections. Sadly, most
people think this is max persistent connections for apache / php as a
whole. it's not. It's for each apache / php child process. This
number should generally be set to 1, 2 at the absolute most when using
persistent connections.

> 6: The reason for connection pooling is primarily to twofold. One is
> to allow very fast connections to your database when doing lots of
> small things where connection time will cost too much. The other is
> to prevent your database from having lots of stale / idle connections
> that cause it to waste memory and to be slower since each backend
> needs to communicate with every other backend some amount of data some
> times. pconnect takes care of the first problem, but exacerbates the
> second.
>

next advance ... longer life of cached executive plans in PL/pgSQL procedures.

Regards
Pavel Stehule

On Mon, 13 Aug 2007 11:30:37 -0500
"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> Oh, one other thing that contributes to the problem ....

Thanks for the replies - all of this was very useful info.

Josh

Pconnects are absolutely necessary if we use tsearch2, because it
initializes its dictionaries on a first query in a session. It's a very
heavy process (500 ms and more). So, if we do not use pconnect, we waste
about 500 ms on each DB connection. Too much pain.

Or, of course, pconnect may be replaced with pgbouncer. It's even better.

On 8/13/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> On 8/13/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> > On Mon, 13 Aug 2007 09:44:26 -0500
> > Erik Jones <erik(at)myemma(dot)com> wrote:
> >
> > > I'll agree with Scott on this one. (Not that I can recall
> > > specifically ever disagreeing with him before...). Unless you
> > > know all of the potential caveats associated with php's persisent
> > > postgres connections and have a use case that fits them, don't use
> > > them. If you need something to pool connections, look at pgpool.
> >
> > Could elaborate a little on the problems with using php's persistent
> > connections?
> >
> > Personally I use ADODB php abstraction library (adodb.sf.net) for my
> > database stuff and I think there's a way to enable persistent
> > connections though I just use the default connection.
> >
> > I've heard before that php's persistent connections are to be
> > avoided, was just curious as to why though?
>
> OK, there are a few things that gather together to make php's
> persistant connections a problem.
>
> 1: Each apache / php process maintains its own connections, not
> sharing with others. So it's NOT connection pooling, but people tend
> to think it is.
> 2: Each unique connection creates another persistent connection for
> an apache/php child process. If you routinely connect to multiple
> servers / databases or as > 1 user, then each one of those
> combinations that is unique makes another persistent connection.
> 3: There's no facility in PHP to clean an old connection out and make
> sure it's in some kind of consistent state when you get it. It's in
> exactly the same state it was when the previous php script finished
> with it. Half completed transactions, partial sql statements,
> sequence functions like currval() may have values that don't apply to
> you.
> 4: pg_close can't close a persistent connection. Once it's open, it
> stays open until the child process is harvested.
> 5: Apache, by default, is configured for 150 child processes.
> Postgresql, and many other databases for that matter, are configured
> for 100 or less. Even if apache only opens one connection to one
> database with one user account, it will eventually try to open the
> 101st connection to postgresql and fail. So, the default
> configuration of apache / postgresql for number of connections is
> unsafe for pconnect.
> 6: The reason for connection pooling is primarily to twofold. One is
> to allow very fast connections to your database when doing lots of
> small things where connection time will cost too much. The other is
> to prevent your database from having lots of stale / idle connections
> that cause it to waste memory and to be slower since each backend
> needs to communicate with every other backend some amount of data some
> times. pconnect takes care of the first problem, but exacerbates the
> second.
>
> P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the
> time, and it's really great for simple small scripts that need to be
> done NOW and need to be lightweight. I even use pconnect a bit. But
> my machine is set for 50 or fewer apache children and 150 postgresql
> connects, and I only use pconnect on small, lightweight things that
> need to zoom. Everything else gets regular old connect.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On 14.08.2007 23:13, Dmitry Koterov wrote:
> Pconnects are absolutely necessary if we use tsearch2, because it
> initializes its dictionaries on a first query in a session. It's a very
> heavy process (500 ms and more). So, if we do not use pconnect, we waste
> about 500 ms on each DB connection. Too much pain.

We've been using pconnect for exactly the same reason. Though startup
time for our dictionary is even higher (around 2 seconds). The problem
is that persistent connections in PHP are not clean implemented, they
can get randomly garbage collected. The problem seems well known, though
I'm unaware of any fix. I think it's best to use pgbouncer and plain
connect ATM. Additionally, as mentioned earlier, using pconnect under
the Apache webserver is not a good idea at all, at least not with it's
current architecture.

--
Regards,
Hannes Dorbath

Hannes Dorbath schrieb:
> On 14.08.2007 23:13, Dmitry Koterov wrote:
>> Pconnects are absolutely necessary if we use tsearch2, because it
>> initializes its dictionaries on a first query in a session. It's a very
>> heavy process (500 ms and more). So, if we do not use pconnect, we waste
>> about 500 ms on each DB connection. Too much pain.
>
> We've been using pconnect for exactly the same reason. Though startup
> time for our dictionary is even higher (around 2 seconds). The problem
> is that persistent connections in PHP are not clean implemented, they
> can get randomly garbage collected. The problem seems well known, though
> I'm unaware of any fix. I think it's best to use pgbouncer and plain
> connect ATM. Additionally, as mentioned earlier, using pconnect under
> the Apache webserver is not a good idea at all, at least not with it's
> current architecture.

If the dictionary is not too large, you should store it directly in the
memory of the server. Therefore you can use Shared Memory
(http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php).

Another advantage of the solution is, that you have one dictionary for
all php-childs - so you do not waste memory by loading the dictionary
each request.

Greetings,
Torsten

On 15.08.2007 10:53, Torsten Zühlsdorff wrote:
> If the dictionary is not too large, you should store it directly in the
> memory of the server. Therefore you can use Shared Memory
> (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php).

Uhm, but how does TSearch get it from there? And even if it does, it
will still held its own copy?

--
Regards,
Hannes Dorbath

Hannes Dorbath schrieb:
> On 15.08.2007 10:53, Torsten Zühlsdorff wrote:
>> If the dictionary is not too large, you should store it directly in
>> the memory of the server. Therefore you can use Shared Memory
>> (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php).
>
> Uhm, but how does TSearch get it from there? And even if it does, it
> will still held its own copy?

In this case I misunderstood you. I believed, that you get a dictionary
from pg and use it. -.-
Because often people load a dictionary from db to get the translation of
$content, shared memory can make this very fast. My advise based on this
assumption.

Greetings,
Torsten