Or... How I got my server to run OK without wasting too much time.
Database performance, whether in Drupal or any other platform is serious business. There's a lot of benchmarking, analyzing queries, forming hypotheses, making changes, benchmarking again... I've done it and it can take a team weeks to hit performance targets on a major enterprise application. Everyone wants a shortcut. There really are no shortcuts, but sometimes a few tweaks can get you to "good enough".
I have a Drupal website running under Plesk on my own dedicated server. It's moderately busy, outpacing all the other sites on the server, but it's not really all that busy. The problem was that MySQL was really slogging on the server. Load averages were getting really high. Occasionally, it became so unresponsive that I'd have to restart MySQL (if I could even log in through ssh). The server itself has dual Xeon processors, 2GB RAM and SCSI drives.
Running the "top" command I saw that the CPU wait states would sometime be almost 50%. So, the CPU was sitting around waiting for disk operations. But... it wasn't really all that busy. I suspected locking in the database.
To maintain consistency, a database usually uses the concept of transactions and locking. It's simplifying things somewhat, but if you are writing to a row in a table, MySQL will lock that table (or at least that row), so that no other operations happen at the same time. Two processes writing to the same resource could cause corrupt data.
MySQL supports different types of storage. The two main choices are: MyISAM and InnoDB. MyISAM is probably what most DBs have as a default. It uses table-level locking--if you write to a row, the entire table will be locked and all other operations on the table will have to wait. InnoDB offers row-level locking--if you write to a row, only that row is locked. Every other row in the table is still unlocked, so you probably aren't delaying any other requests.
Why not just convert every table to InnoDB? A lot of people advocate that. But locking aside, MyISAM can actually be faster. Maybe it is better to be selective and only convert some tables to InnoDB.
My hypothesis was that MySQL locking was creating the slow database performance and the CPU wait states. My proposed action was to switch tables with regular writes to InnoDB to take advantage of row-level locking, while leaving everything else as MyISAM.
The tables I changed:
- cache and cache_* - these experience occasional updates as requests are made.
- search_* - these will update on cron runs... probably not a big one for me, but it could be on some sites.
- sessions - this gets updated regularly... whenever a new visitor hits the site.
- users - there isn't really a lot of user logins, so I could probably have skipped this one.
- watchdog - if I was really serious about performance, I would log to syslog instead of the DB, but hey... I'm lazy.
Tables I did not change (but you might want to):
- node and node_revisions - my site doesn't have a lot of regular content updates. Also, node_counter, but you probably shouldn't be using that feature on a "real" site.
- content_* - same as above.
- comments - comments are disabled on my site, so no need for this.
How to change the table type:
ALTER TABLE `watchdog` ENGINE = INNODB;
After this monumental effort of about 10-15 minutes, what was the result? I captured the CPU states every few minutes for a day. The wait state dropped down to almost nothing. CPU idle percentage stayed over 95% almost all the time. The load average was almost always under 0.20 and usually less than 0.05. The overall "feel" from ssh or a browser went from sluggish to snappy.
Doing a "real" database performance project would be much more involved. I planned doing a more comprehensive set of articles on DB performance, but really there's not much need. Everything is running great for now.
If you have tried anything like this, please share your experiences in the comments.