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

[John Almberg]

On Jul 6, 2008, at 4:58 AM, Matthew Seaman wrote:

> John Almberg wrote:
>> 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???
>
> Database performance is critically dependant on the underlying Disk  
> IO sub-
> systems.
>
> There's two things to consider here:
>
>  * What's the difference in disk hardware between the two machines?
>
>  * How well does the database compete with apache for disk IO when
>    they are sharing the same drives?
>
> In the first case, you'll see very big improvements in MySQL  
> performance
> if you use disks with plenty of IO cache memory on them, plus disks  
> that
> work with controllers that support tagged queuing (SAS and higher  
> end SATA
> generally.)  DB disk IO consists primarily of a large number of small
> transactions randomly distributed across the drive.  Reducing the  
> latency
> of each of those transactions gets you the best improvement in  
> performance.
> RAID levels are important: single disks are not bad if dedicated to  
> the DB.
> RAID1 mirrors are fine.  RAID10 striped mirrors are excellent.   
> RAID5 not
> so good.  If you're willing to spend money, hardware RAID  
> controllers help.
> Hardware RAID controllers with Battery Backup Units (so you can  
> safely turn
> on write caching) help a very great deal.

The first box has 2 independent raid arrays, with an Intel hardware  
raid controller with battery backup. But apache and mysql were  
probably on the same array. I have to confirm this.

The second box has 2 drives with raid 1, from an integrated  
controller (i.e, software raid).

So the second box is much less powerful, but running mysqld is all  
it's doing. I guess that is the primary difference.

My main worry about the second box is that it only has 1G of RAM (not  
2 as I mis-remembered before checking specs.) Easily expanded, but  
it's something to watch.

Bottom line, I might do better by moving mysqld back to the first  
machine, but putting the data on the second, raid 10 array (which is  
more or less empty at the moment.)

I appreciate your insight. This is starting to make sense to me.

Brgds: JOhn


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Websites for On-line Collectible Dealers
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Identry, LLC
John Almberg
(631) 546-5079
jalmberg at identry.com
www.identry.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~