[PHP-DEV] Database connection pooling

[Mathijs Brands]

Hi all,

This is my first post to this list, so please bear with me ;)

Anyway, I've been running/developing a PHP application (fairly simple
content management system) for some time now and it has been running
pretty well. I'm using Apache 1.3.14, PHP 3.0.16 and PostgreSQL 6.5.3.

The problem is that the increasing number of requests the application
needs to service requires me to increase the number of Apache processes,
which sometimes causes database problems. Originally I had about 10-20
running processes, but now I sometimes reach 75-100 or more. Since I'm
using persistant db connections, this means I can have 100 (or more)
open db connections; this is not something PostgreSQL really likes. 

If I use normal db connections, everything works ok, but the performance
is no longer acceptable. I suspect that I only need 2-3 db connections
for every 10 running processes.

On another server we're running several PHP/PostgreSQL and PHP/MySQL
based website, also using Apache 1.3. In this case I'm having similar
problems. In order to handle the amount of traffic this webserver needs
about 80-150 processes (I've seen up to 230 running httpd's). Some scripts
get quite a lot faster if I use persistant connections, but if I do, I
can be pretty sure that both MySQL (hangs after a while) and PostgreSQL
(dreadful performance, sometimes crashes) get into trouble. I currently
have 2 MySQL and 3 PostgreSQL databases, so if I have 150 running processes,
I could have 300 open MySQL and 450 open PostgreSQL connections. It's like
tinkering with explosives, since this is sure to cause a lot of trouble.

What I'd like is a way to have a small pool of database connections with
a fixed (configurable) size. When you request a persistant connection in
a PHP script, you would either get a connection from the pool or a new,
normal connection (if there are no more free connections in the pool).
Upon closing the connection moves back to the pool or is closed in case
of a normal connection.

I've done some searching in the mailinglist archives and on the net and I
haven't found a usable answer yet, so I'll probably look into implementing
something myself (using PHP 4.0.x and PostgreSQL 7.0/MySQL 3.23). I haven't
really looked at how complex this would be, but I am aware of the fact that
something like this is much easier to implement for a threaded webserver.

Any comments or suggestions would be really welcome,

Btw. Something I'll probably do until I come up with a solution is moving
all scripts requiring db access to another webserver (running on the same
machine) with a much lower number of Apache processes. It solves the
database problem, but introduces a whole range of new problems.

TIA,

Mathijs
-- 
ilse Technology B.V. - Postbus 542 5600 AM Eindhoven
Telefoon: 040 750 31 00 - Fax: 040 750 31 99
Web: http://www.ilse.nl/ - E-mail: [EMAIL PROTECTED]

-- 
PHP Development Mailing List 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DEV] Database connection pooling

[John Donagher]

Mathijs-

It would seem that a better model is to simply state that there are resources
that would like to be persisted outside of PHP threads of execution, possibly
outside of a webserver's process space, perhaps even outside of a physical
machine or network. Many people will agree with you.

What you're looking for would fall under the scope of an application server.
You can find past discussions on this about that, from people who know far
more about it than I do :)

John

On Sat, 10 Feb 2001, Mathijs Brands wrote:

> Hi all,
> 
> This is my first post to this list, so please bear with me ;)
> 
> Anyway, I've been running/developing a PHP application (fairly simple
> content management system) for some time now and it has been running
> pretty well. I'm using Apache 1.3.14, PHP 3.0.16 and PostgreSQL 6.5.3.
> 
> The problem is that the increasing number of requests the application
> needs to service requires me to increase the number of Apache processes,
> which sometimes causes database problems. Originally I had about 10-20
> running processes, but now I sometimes reach 75-100 or more. Since I'm
> using persistant db connections, this means I can have 100 (or more)
> open db connections; this is not something PostgreSQL really likes. 
> 
> If I use normal db connections, everything works ok, but the performance
> is no longer acceptable. I suspect that I only need 2-3 db connections
> for every 10 running processes.
> 
> On another server we're running several PHP/PostgreSQL and PHP/MySQL
> based website, also using Apache 1.3. In this case I'm having similar
> problems. In order to handle the amount of traffic this webserver needs
> about 80-150 processes (I've seen up to 230 running httpd's). Some scripts
> get quite a lot faster if I use persistant connections, but if I do, I
> can be pretty sure that both MySQL (hangs after a while) and PostgreSQL
> (dreadful performance, sometimes crashes) get into trouble. I currently
> have 2 MySQL and 3 PostgreSQL databases, so if I have 150 running processes,
> I could have 300 open MySQL and 450 open PostgreSQL connections. It's like
> tinkering with explosives, since this is sure to cause a lot of trouble.
> 
> What I'd like is a way to have a small pool of database connections with
> a fixed (configurable) size. When you request a persistant connection in
> a PHP script, you would either get a connection from the pool or a new,
> normal connection (if there are no more free connections in the pool).
> Upon closing the connection moves back to the pool or is closed in case
> of a normal connection.
> 
> I've done some searching in the mailinglist archives and on the net and I
> haven't found a usable answer yet, so I'll probably look into implementing
> something myself (using PHP 4.0.x and PostgreSQL 7.0/MySQL 3.23). I haven't
> really looked at how complex this would be, but I am aware of the fact that
> something like this is much easier to implement for a threaded webserver.
> 
> Any comments or suggestions would be really welcome,
> 
> Btw. Something I'll probably do until I come up with a solution is moving
> all scripts requiring db access to another webserver (running on the same
> machine) with a much lower number of Apache processes. It solves the
> database problem, but introduces a whole range of new problems.
> 
> TIA,
> 
> Mathijs
> 

-- 

John Donagher
Application Engineer
Intacct Corp. - Powerful Accounting on the Web
408-395-0989
720 University Ave.
Los Gatos CA 95032
www.intacct.com

Public key available off http://www.keyserver.net
Key fingerprint = 4024 DF50 56EE 19A3 258A  D628 22DE AD56 EEBE 8DDD


-- 
PHP Development Mailing List 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DEV] Database connection pooling

[Ron Chmara]

Mathijs Brands wrote:
> The problem is that the increasing number of requests the application
> needs to service requires me to increase the number of Apache processes,
> which sometimes causes database problems. Originally I had about 10-20
> running processes, but now I sometimes reach 75-100 or more. Since I'm
> using persistant db connections, this means I can have 100 (or more)
> open db connections; this is not something PostgreSQL really likes.

Only so much shared memory (been there, done that..)

> If I use normal db connections, everything works ok, but the performance
> is no longer acceptable. I suspect that I only need 2-3 db connections
> for every 10 running processes.

Sounds like you need to either "isolate those servers", or find a way
of just 'turning the connections off" for most of the traffic.. (It's
just another way to look at the problem, rather than sharing the
connection, turing off the unused ones.)... I chose to turn off the
old, unused, pgsql connections by eliminating them more frequently.

> I've done some searching in the mailinglist archives and on the net and I
> haven't found a usable answer yet, so I'll probably look into implementing
> something myself (using PHP 4.0.x and PostgreSQL 7.0/MySQL 3.23). I haven't
> really looked at how complex this would be, but I am aware of the fact that
> something like this is much easier to implement for a threaded webserver.
> Btw. Something I'll probably do until I come up with a solution is moving
> all scripts requiring db access to another webserver (running on the same
> machine) with a much lower number of Apache processes. It solves the
> database problem, but introduces a whole range of new problems.

Well, that's certainly a solution. Another is to mix your connection
types, i.e., use persistant connections where you have many fast and
furious connections in a page, and use non-persistant connections for
where you only have a single lookup for the page. The speed hit should be
*completely negligible* (less than .01 sec.) per view on a page with 1 or 2
select or update statements (less time than adding a single image to
the page). Another appoach is to set the apache processes to exit more
often, so your "pooling" is really in the apache process pool churning
over faster than usual. It's not as fast as *always* reusing an open
connection, but it keeps the unused-but-open-PHP-to-pgsql connections
down, and allows re-use of an open pgsql connection for much longer
than a non-persistant conncetion does...In your apache conf file
(usually httpd.conf):

# Tune up or down as needed for throwing away open Apache/PHP/pgsql threads
MaxRequestsPerChild 100


At one hundred requests, that keeps our open connection threads to pgsql
hovering at only 18-20 (about half our pages use pgsql, about 160K hits
an day.) By working with this parameter and setting:


MaxClients 32
MinSpareServers 8
MaxSpareServers 20


We finally got to where we only needed *32* pgsql (and 32 apache) backends
for 160K hits a day (for a pgsql-heavy set of websites). If you can already take
64 apache processes and 64 Postgres processes, you could easily double this
set of numbers to 320K hits a day without breaking a sweat. IOW: Since
you're hitting 75-100 threads, something you might do is killing off those
75-100 earlier, so you aren't wasting so much on unused peristant connection
resources
Especially if you're only using them on 20-30% of your page hits.

Something else to look at is simplifying your connection code, i.e.
removing layers of abstraction (which are slowing you down) or
excessive db lookups on a page just the normal code optimization
things when you start pushing your hardware capabilities beyond
what you can afford. Can you move some pgsql auth code out to LDAP?
Can you write a page for pgsql joins, so you remove the 10-15
lookups a MySQL page might require (foreign keys are nice. :-) )?
That way you can take the speed hits of non-persistant connections
easier.

I already asked the pgsql folks about throwing away unused connections
after a certain amount of time, and the code to do such a thing wasn't
pretty (and Apache/PHP _really_ didn't like PG shutting down those
connections). The Apache folks should be closer to this capability
in 2.0, but one of the main problems is that *Apache* isn't pooling
all the resources, so PHP (inside the apache processes) can't use a
pool to talk to other PHP threads, to borrow/take/reuse the db
connection from another process.

There are some people out there working on different multiplexors
and db pool mechanisms for PHP, look in the archives for "application
server", "connection pooling", etc. But you may find that simply killing
off your processes sooner may do just what you need. Tune it down,
and balance your apache-process-creation-speed against your hardware-
resource-requirements. Since apache's spare process spawning happens
in the background (not related to a page load), you shou

Re: [PHP-DEV] Database connection pooling

[Manuel Lemos]

Hello Ron,

On 11-Feb-01 20:48:54, you wrote:

>There are some people out there working on different multiplexors
>and db pool mechanisms for PHP, look in the archives for "application
>server", "connection pooling", etc. But you may find that simply killing
>off your processes sooner may do just what you need. Tune it down,
>and balance your apache-process-creation-speed against your hardware-
>resource-requirements. Since apache's spare process spawning happens
>in the background (not related to a page load), you shouldn't
>see any horrible differences unless you drop it too low (say, 10
>connections before dying. :-) )... 

Once I have though of developing a connection pooling proxy server for
Metabase but then I gave up because since PHP does not support
multi-threading it would be too slow to be useful.

I also thought of making a backend Apache server a sort connection pool
manager with a limited number of opened server processes to make sure there
will never be too many database server connections opened at once, as you
also suggested.

I wanted to get back to this problem because Tuxedo like solutions are too
expensive to be considered.  However I no longer have the time to work on
that.

So, I am about to open the source of Metabase so more qualified developers
can actively work on problems like this.  Since you exhibited such
experience in complex matters like this, I wonder if you would be
interested to join what will become the Metabase core developers team.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED]
--
E-mail: [EMAIL PROTECTED]
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--


-- 
PHP Development Mailing List 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Re: [PHP-DEV] Database connection pooling

[Shaun Batterton]

I am also looking into pooling options for the same
reasons.  Since we also will be using it for content management, we have 
a caching mechanism in place.  One of the options we thought of using was
having an second apache instance running that accepts all of the dynamic
requests and only has one process.  So the 75-100 processes would be
serving cached pages, but when a cache hit occured, it would pass it off
to the other apache instance running only one process.  

We probably won't do this, but it seems to be a viable option.  We are
looking into pooling applications as alternatives.

Shaun

On Fri, 9 Feb 2001, John Donagher wrote:

> 
> Mathijs-
> 
> It would seem that a better model is to simply state that there are resources
> that would like to be persisted outside of PHP threads of execution, possibly
> outside of a webserver's process space, perhaps even outside of a physical
> machine or network. Many people will agree with you.
> 
> What you're looking for would fall under the scope of an application server.
> You can find past discussions on this about that, from people who know far
> more about it than I do :)
> 
> John
> 
> On Sat, 10 Feb 2001, Mathijs Brands wrote:
> 
> > Hi all,
> > 
> > This is my first post to this list, so please bear with me ;)
> > 
> > Anyway, I've been running/developing a PHP application (fairly simple
> > content management system) for some time now and it has been running
> > pretty well. I'm using Apache 1.3.14, PHP 3.0.16 and PostgreSQL 6.5.3.
> > 
> > The problem is that the increasing number of requests the application
> > needs to service requires me to increase the number of Apache processes,
> > which sometimes causes database problems. Originally I had about 10-20
> > running processes, but now I sometimes reach 75-100 or more. Since I'm
> > using persistant db connections, this means I can have 100 (or more)
> > open db connections; this is not something PostgreSQL really likes. 
> > 
> > If I use normal db connections, everything works ok, but the performance
> > is no longer acceptable. I suspect that I only need 2-3 db connections
> > for every 10 running processes.


-- 
PHP Development Mailing List 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]