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: [EMAIL PROTECTED], 520-326-6109, http://www.opus1.com/ron/
Work: [EMAIL PROTECTED], 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.
--
PHP Development Mailing List <http://www.php.net/>
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
