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

[Matthew Seaman]

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

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.

In the second case, it's really just about competition for resources. I
suspect that you could have achieved a pretty good speed-up simply by
adding another hard drive to your server and moving all of the database
onto it, separate from the web root and any other areas which apache
would be doing a lot of read/write operations on.

        Cheers,

        Matthew

--
Dr Matthew J Seaman MA, D.Phil.                   7 Priory Courtyard
                                                 Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey     Ramsgate
                                                 Kent, CT11 9PW

of each of those transactions gets you the best improvement in performance.

signature.asc
Description: OpenPGP digital signature