From owner-freebsd-questions@FreeBSD.ORG Sat Jul 5 20:17:15 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 287621065691 for ; Sat, 5 Jul 2008 20:17:15 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: from mx1.identry.com (on.identry.com [66.111.0.194]) by mx1.freebsd.org (Postfix) with ESMTP id CADAD8FC19 for ; Sat, 5 Jul 2008 20:17:14 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: (qmail 33580 invoked by uid 89); 5 Jul 2008 20:17:13 -0000 Received: from unknown (HELO ?192.168.1.110?) (jalmberg@75.127.142.66) by mx1.identry.com with ESMTPA; 5 Jul 2008 20:17:13 -0000 Mime-Version: 1.0 (Apple Message framework v752.3) Content-Transfer-Encoding: 7bit Message-Id: Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed To: freebsd-questions@freebsd.org From: John Almberg Date: Sat, 5 Jul 2008 16:17:12 -0400 X-Mailer: Apple Mail (2.752.3) Subject: Why would it make such a difference to move mysqld to another machine? X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 05 Jul 2008 20:17:15 -0000 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... last pid: 43730; load averages: 1.93, 2.64, 2.22 up 92+19:45:54 09:26:27 238 processes: 3 running, 235 sleeping CPU states: 8.1% user, 0.0% nice, 17.3% system, 0.2% interrupt, 74.4% idle Mem: 1384M Active, 3753M Inact, 373M Wired, 884K Cache, 214M Buf, 2150M Free Swap: 16G Total, 88K Used, 16G Free PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND 1393 mysql 63 20 0 400M 221M kserel 0 191.5H 157.13% mysqld 43698 www 1 4 0 169M 29888K sbwait 5 0:00 2.63% httpd 43697 www 1 20 0 169M 29804K lockf 1 0:00 1.18% httpd 23376 vpopmail 1 4 0 81468K 55772K select 7 0:28 1.17% perl5.8.8 43729 root 1 96 0 7228K 2676K select 5 0:00 1.00% couriertls 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??? First Machine: last pid: 32971; load averages: 0.04, 0.14, 0.15 up 95 +02:31:20 16:11:53 190 processes: 1 running, 189 sleeping CPU states: 4.1% user, 0.0% nice, 3.1% system, 0.0% interrupt, 92.8% idle Mem: 905M Active, 3075M Inact, 390M Wired, 292M Cache, 214M Buf, 3008M Free Swap: 16G Total, 88K Used, 16G Free PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND 72523 mysql 31 20 0 337M 121M kserel 6 268:13 9.13% mysqld 32883 www 1 96 0 160M 20732K select 6 0:00 0.35% httpd 29261 www 1 96 0 161M 20924K select 5 0:06 0.34% httpd 32878 www 1 20 0 168M 28548K lockf 5 0:01 0.15% httpd 32568 www 1 96 0 170M 30064K select 2 0:01 0.15% httpd SECOND MACHINE: last pid: 9841; load averages: 0.11, 0.15, 0.16 up 92+06:54:37 16:12:54 59 processes: 2 running, 56 sleeping, 1 stopped CPU states: 15.6% user, 0.0% nice, 3.2% system, 0.0% interrupt, 81.2% idle Mem: 62M Active, 640M Inact, 131M Wired, 49M Cache, 111M Buf, 19M Free Swap: 2048M Total, 1568K Used, 2046M Free PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND 8672 mysql 7 100 0 58412K 41004K CPU1 0 27:07 1.17% mysqld It doesn't seem to make sense! I expected the heavy mysql load to be moved to the second machine, not eliminated. Any thoughts, much appreciated. -- John