Re: re : PHP and persistent connections - Mailing list pgsql-novice

Howdy,

> It turns out that the number of max_persistent
> is linked to the httpd processes in some
> difficult-to-describe way.

It's not that hard to describe. The max_persistent/max_links values are
per Apache process.

Thus if you have:

pgsql.max_persistent = 2

and

MaxClients 300

You could potentially reach a state where you are maintaining 600
persistant connections to the database (if your PHP scripts actually
pg_pconnect() with two different connect strings).

I think that if you are using persistant connections you may as well set
MaxClients to the same number of database backends you are allowing (or
possibly a bit less if you need other connections to the database).
There's no real point in allowing more Maxclients as they'll just start
hitting connect errors anyway.

Obviously this isn't the most efficient use of backends because a fair
amount of the time the Apache processes won't be using them at all
(they'll be sitting there doing nothing, or serving images or other
static content).

If your application is big enough you may benefit from serving static
content (images etc) from a different server, so the Apache processes
with persistant connection to backends are being used more heavily for
database work.

Ie if you normally have 100 Apache processes running on your webserver
and you are using one persistant connection per process you will need
100 backends. However if at any one time those 60%
of those processes are serving images then you could have an Apache
server on one machine serving those images and only need 40 Apache
processes and therefore 40 backends on the Apache server that serves the
PHP script. You'll be tuning each machine for a more specific task,
rather than having one machine doing all sorts of different stuff.

> I do not know what will happen with PHP when there are more than one
> different (i.e. different username, database) persistent connections.
> I suppose they would be affected by the max_persistent. (?).

If you want persistant connections to two different database/username
pairs then you need to have max_persistant=2, one for each different
connection string.

If you have one database that is used a lot and one that isn't, you may
wish to set max_persistant to 1 and max_clients to 2. Use pg_pconnect()
for the one accessed a lot and pg_connect() for the other. Set Apache
MaxClients to X and the max number of PG backends to X + Y, where Y
allows for the load required by the short lived pg_connect()s.

As you've probably noticed, balancing all this is a rather manual
process.
Perhaps Apache 2.0 will make way for some connection pooling.

I hope that wasn't too confusing.

Oh, and if you are using pg_close() I don't think it works
in any currently released PHP4 versions. See:
http://bugs.php.net/bugs.php?id=7007
From the changelog:
http://cvs.php.net/viewcvs.cgi/~checkout~/php4/ChangeLog?rev=1.541&conte
nt-type=text/plain
it seems a fix went in to CVS on 2000-11-03.


--
Paul McGarry            mailto:paulm@opentec.com.au
Systems Integrator      http://www.opentec.com.au
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9878 1744
North Ryde NSW 2113     Fax:   (02) 9878 1755755