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
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.
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Choose your data types carefully >>
Previous chapter: Use the EXPLAIN statement
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.