Date: Mon, 07 Jul 2008 14:50:28 -0400 From: Michael Powell <nightrecon@verizon.net> To: freebsd-questions@freebsd.org Subject: Re: Why would it make such a difference to move mysqld to another machine? Message-ID: <g4toee$17i$1@ger.gmane.org> References: <E46D5ADF-51F4-4393-BDEC-79FE02E4A574@identry.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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... [snip] > 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??? > [snip] 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. -Mike
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?g4toee$17i$1>