Date: Sun, 06 Jul 2008 09:58:54 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: John Almberg <jalmberg@identry.com> Cc: freebsd-questions@freebsd.org Subject: Re: Why would it make such a difference to move mysqld to another machine? Message-ID: <4870894E.7090708@infracaninophile.co.uk> In-Reply-To: <E46D5ADF-51F4-4393-BDEC-79FE02E4A574@identry.com> References: <E46D5ADF-51F4-4393-BDEC-79FE02E4A574@identry.com>
next in thread | previous in thread | raw e-mail | index | archive | help
This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigE32958F99AD96DA651039451
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
John Almberg wrote:
=20
> Luckily, I have a pretty powerful machine sitting right next to my main=
=20
> webserver that I mainly use for backup. The two servers are directly=20
> 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.
>=20
> Desperate, I moved mysqld to this other machine, so basically this=20
> second machine became a dedicated database server.
>=20
> The improvement this change made seems out of proportion. Both machines=
=20
> are now cruising with extremely low load averages and the WCPU for the =
> mysqld instance on the new machine is practically zero.
>=20
> I'm not complaining. Problem solved. But I am scratching my head over=20
> how mysql could be getting crushed on the first, 8 core/8G machine, but=
=20
> running cool as a cucumber on the second, 2-core/2G machine???
Database performance is critically dependant on the underlying Disk IO su=
b-
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 SAT=
A
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 performanc=
e.=20
RAID levels are important: single disks are not bad if dedicated to the D=
B.
RAID1 mirrors are fine. RAID10 striped mirrors are excellent. RAID5 not=
so good. If you're willing to spend money, hardware RAID controllers hel=
p.
Hardware RAID controllers with Battery Backup Units (so you can safely tu=
rn
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
--=20
Dr Matthew J Seaman MA, D.Phil. 7 Priory Courtyard
Flat 3
PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate
Kent, CT11 9PW
--------------enigE32958F99AD96DA651039451
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEAREIAAYFAkhwiVYACgkQ8Mjk52CukIwt7ACeN/gyGhW3aO8br5xRauFrnIGu
A+EAn2H07z7wZ9shE+XaMoJ7iDQODp/X
=RUat
-----END PGP SIGNATURE-----
--------------enigE32958F99AD96DA651039451--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?4870894E.7090708>