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

On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
> 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.

It was difficult to describe because I was not recieving consistent
results in experiments due to a number of factors. It makes sense now.

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

Well, see, the thing is, we do webhosting for a number of different
domains from the same server so the number of MaxClients needs to be
high. I think that 300 is obscene, as the server is not powerful enough
to handle 300 apache processes without dumping a large number of them
into swap space, not to mention the processing, but no matter what, we
would have several extra postgres backends just hanging around wasting
ram.
Only a few unique persistent connections would be in use at any given
time as only a few domains use the database.

This has made me realize just how completely braindead our server setup
is. ;-) It seems that we would to bring up a seperate database
server, very soon.

>
> 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).

Just what I was thinking. Connection pooling would avoid that, correct?

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

True, but in this case probably moving the database to a different server
would make more sense because most of the backends would be serving
content that is completely unrelated to the database.

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

Your explanation makes perfect sense. A Zen sort of understanding has
come to me through experimenting with different settings.

How would persistent connections fit into a dual-server setup where one
server is handling all of the webserving and the other simply handles the
database data-serving?
The number of backends on the database server would be independent
of the number of Apache processes on the webserver inasmuch as there
could be 75 Apache processes but only 25 are connected to backends on the
database server, correct?
There would not necessarily be any Apache stuff on the database server?

>
> Oh, and if you are using pg_close() I don't think it works
> in any currently released PHP4 versions. See:

This seems to be true. I ran into some fun link errors while
connecting and disconnecting more than once in a script.

Thanks again, and again.

gh

> 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