Change your hardware

MySQL performance is very hardware dependent, and you can make a big difference in performance by either upgrading, or changing your setup - for example, spreading your database data across more than one disk can provide a drastic speed improvement by increasing the number of hard disk seeks a second MySQL can perform.

This might sound like an obvious way to make your code faster, and indeed it is: upgrading your system is likely to make the biggest immediate difference. Attitudes on what you should upgrade are mixed, with much of the conventional wisdom not really holding true any more. For example, MySQL recommend upgrading your RAM, then your hard drives, then your CPU. That may well be true in some circumstances, however the majority of us are likely to find the biggest speed difference by upgrading in the opposite order: CPU, hard drives, RAM.

The reason for this is because the "RAM first" recommendation is based upon the concept that you are likely to be filling up your RAM with your MySQL data. If you are indeed using up all your RAM with your databases, then upgrading your RAM will be a huge help. However, on the smaller of my two servers I have 512MB of RAM, and my databases (large as they are) only take up 200 - upgrading RAM will not change things a whit for me.

Upgrading your hard disk helps when committing data to disk and also reading data into RAM. If you can upgrade to a SCSI drive, or, better if you can stripe your data across several disks, you will see a noticeable speed up for your server. At the very least, consider using hdparm to tweak your settings. Try something like this:

hdparm -m16 -d1 -m1 -a1 /dev/hda
Author's Note: Using hdparm can damage or destroy your hardware - read the manual before use.

If your RAM isn't full and your hard disk aren't a problem - because they are fast enough or because they are not hit often - then it is CPU power that will count the most, and in my experience I have found sheer CPU horsepower is the key bottleneck. MySQL uses a lot of 64-bit integer code for its calculations, so if you can afford an Opteron or even an Athlon 64 you will find it makes a substantial difference.

 

Next chapter: Choose your data types carefully >>

Previous chapter: Use the EXPLAIN statement

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar