I just installed Pressflow and simultaneously updated Drupal late last week. Pressflow is a modified version of Drupal that allows it to scale more easily. For sites with huge traffic and databases, it's essential. It supports database replication, Squid and Varnish reverse-proxy caching, and is optimized for MySQL and PHP 5.

TABLE LOCKS
I plan to use some of those features in the future, but my main reason for installing it is to get rid of the table locks from Drupal. During heavy load, the database locks don't crash the site, but it does cause the site to hang for a minute or two. This happens several times a day, if not more. One of the co-founders and developers of Pressflow told me this distribution of Drupal would get rid of the table locks, so I went ahead and installed it. Because it's a just modified version of Drupal, the installation/upgrade process is the same. I could have just converted the problematic MyISAM tables into InnoDB, but Pressflow offers more upside. Since installing it, the site hasn't had any of those one-to-two minute hangs. Although, I do occasionally see locks on the cache tables, but it's not enough to cause the site grind to a halt.

WRITING TO DISK
One command that I issue all the time in MySQL is "show processlist." I saw a couple queries that were writing to disk (instead of memory), which slowed down the site and caused it to hang. When it's writing to disk, it means that there's not enough RAM to store the results, so it goes to the hard drive. You can solve this either by brute force or by simplifying the query. I chose brute force because I recently added more RAM to my VPS at Linode. I noticed that my system was only configured to max out at 60%, so I increased the "key_buffer_size" from 16MB to 200MB in the MySQL config file. Now, my RAM usage maxes out at around 80%, and I'm seeing less writing to disk and less hang time.

I still have to optimize some more queries in the slow query log to make the site load faster for logged-on users. Anonymous users get cached pages, which loads up pretty fast. Right now, I don't have any registered users, but I plan to open up a community section for the site soon, and that's why I'm doing all these performance optimization stuff now. I've been holding off on community related features like blogs, forums, and social networking because they're resource intensive. I want to make sure my server and code are in tip-top condition before setting them up.