The real issue is this, we have THE SAME queries taking anywhere from
.001 -
90.0 seconds... the server is using 98% of the available RAM at all
times
(because of the persistant connections via php), and I don't know
what to
do.
Another possible line of attack is to use persistent (pooled)
connections to cut down the number of live backend processes you need.
However, depending on what your application software is, that might
take more time/effort (= money) than dropping in some more RAM.
This particular feature is pure evilness. Using all of my fingers and
toes, I can't count the number of times I've had a client do this and
get themselves into a world of hurt. Somewhere in the PHP
documentation, there should be a big warning wrapped in the blink tag
that steers people away from setting this. The extra time necessary to
setup a TCP connection is less than the performance drag induced on the
backend when persistent connections are enabled. Reread that last
sentence until it sinks in. On a local network, this is premature
optimization that's hurting you.
max_files_per_process = 3052 # min 25
You really have your kernel set to support 3052 * 75 simultaneously
open
files? Back this off. I doubt values beyond a couple hundred buy
anything except headaches.
This, on the other hand, has made a large difference for me. Time
necessary to complete open(2) calls can be expensive, especially when
the database is poorly designed and is touching many different parts of
the database spread across multiple files on the backend. 3000 is
high, but I've found 500 to be vastly too low in some cases... in
others, it's just fine. My rule of thumb has become, if you're doing
lots of aggregate functions (ex, SUM(), COUNT()) more than once in the
lifetime of a backend, increasing this value helps.. otherwise it buys
you little (if so, 1500 is generally sufficient). Faster IO, however,
is going to save you here. If you can, increase your disk caching in
the OS. On FreeBSD, increase your KVA_PAGES and NBUFs. Since you've
freed up more ram by disabling persistent connections, this shouldn't
be a problem. -sc
--
Sean Chittenden
