Re: Database connection pooling

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 shouldn't see any horrible differences unless you drop it too low (say, 10 connections before dying. :-) )... HTH, -Ronabop -- Personal: ron@opus1.com, 520-326-6109, http://www.opus1.com/ron/ Work: rchmara@pnsinc.com, 520-546-8993, http://www.pnsinc.com/ The opinions expressed in this email are not necessarily those of myself, my employers, or any of the other little voices in my head.