Why would it make such a difference to move mysqld to another machine?

[John Almberg]

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