Why would it make such a difference to move mysqld to another
nightrecon at verizon.net
Mon Jul 7 18:48:55 UTC 2008
John Almberg wrote:
> I asked a question the other day about using top on a multi-processor
> machine. As a side note, I asked how mysqld could be consuming more
> than 100% of CPU power...
> Well, that mysqld reading should have been a warning to me. This
> weekend, my webserver with about 20 database-driven websites got
> clobbered by Slurp and Googlebot. Between the two of them, they had
> over 50 robot instances crawling my machine at the same time
> (visible, thanks to pftop).
> Apache handled them fine, and the load average was still pretty low
> (2-3 on an 8 core machine) but mysqld turned into a major bottleneck.
> It was showing 180-220% WCPU on 'top', and just couldn't keep up with
> all the requests. Page loads crawled to a stop. Big problem!
> Luckily, I have a pretty powerful machine sitting right next to my
> main webserver that I mainly use for backup. The two servers are
> directly connected to each other with a twisted ethernet cable, using
> extra NIC cards in the machines, so they have a fast, dedicated 'LAN'
> to share.
> Desperate, I moved mysqld to this other machine, so basically this
> second machine became a dedicated database server.
> The improvement this change made seems out of proportion. Both
> machines are now cruising with extremely low load averages and the
> WCPU for the mysqld instance on the new machine is practically zero.
> I'm not complaining. Problem solved. But I am scratching my head over
> how mysql could be getting crushed on the first, 8 core/8G machine,
> but running cool as a cucumber on the second, 2-core/2G machine???
One data point to consider is that libthr uses process scope as its default.
MySQL uses system scope unless the Makefile knob WITH_PROC_SCOPE_PTH=yes is
used to change it. When this is done MySQL will tend to hog resources and
Apache would probably have been the process sufferring. I believe most of
the MySQL tuning info I've read wrt multi-core was centered around the use
of libthr in conjunction with the ULE scheduler. However, that being said,
most of this would be more potentially beneficial to a MySQL server running
on its own machine, as opposed to sharing a machine with Apache.
As the other eagle eyes spotted, the difference in IO caching between the
two is probably the important factor, and well covered. Your Apache also
seemed a little on the "fat" side - keep in mind that all the modules
loading in your httpd.conf are initialized in each instance Apache forks a
child to handle a request. You may consider an audit to determine which
modules you actually require and comment out those you don't. You'll be
able to fork more children in the memory you have available.
This also brings to the forefront the process fork() vs event driven
threading. I've been using lighttpd for about a year now, running PHP and
Python as FastCGI. The lighttpd instance only contains one process but
spawns additional threads to service requests. The main difference here is
that all the threads in the process have access to the same resource pool,
(ie the database connection layer) while in the fork() model the resources
each child process initializes are only usable by that child alone. There
is a well known bottleneck in the Apache process forking model that
eventually starves new children from getting access to the backend database
in a timely manner. Some will turn off http_keepalives in order to get
children to release db connections sooner but this is really only a stopgap
solution. I don't necessarily advocate any of this for "right now", but
more as subject material to be examined for the future. Lighttpd is
certainly not a replacement for Apache in all situations at all. And the
Apache threaded worker-mpm was really designed for Windows and can hit a
PHP problem if it hits any non thread-safe code in PHP that barfs.
Should you have excess memory available because Apache isn't using it all
memcached may be a consideration. My thoughts here are only carve out a
memcached chunk in RAM if Apache is leaving some available. With MySQL
running on a second machine you may choose to turn MySQL's query_caching on
in lieu of memcached. It probably isn't as good as memcached as it's on the
wrong end of the TCP connection, but it can help.
Also, just like turning off Apache modules you don't need also turn off
INNODB if you are not using it. What you seem to have experienced here is
the "Slashdot" effect - your setup was running fine as long as it only
received a certain level of load traffic. The jump you got hit with has
served to illuminate the bottlenecks that get hit when traffic ramps up.
Make use of the opportunity.
More information about the freebsd-questions