So I tried MySQL PS, and for a smaller blog (a few hundred visitors a day based on Google Analytics/WordPress.com stats) and it didn’t solve my problems with the admin site slowness. I don’t think the DB is the root of my problems, so I’m canceling the MySQL PS within the free one week trial.
@mpclark, if you’re already using W3 Total Cache, then the SQL server is no longer the bottle neck. Especially with Xcache, it’s all being served out of memory. Make sure you’re not even using the enhanced disk cache mode. You should also bump up the memory on Xcache (copy your PHP INI file from your regular PS if you’re on one to ~/.php-ini and look for the xcache settings at the end, then do a ‘sudo /etc/init.d/nginx -s reload’). The W3 Total Cache FAQ recommends at least over 50 meg for the cache and on a DH PS, the default for me was 16. Even for my small blog, only after I went up to 128 megs did I see Xcache not max out immediately.
Oddly enough, those 128 megs didn’t reflect in the Dreamhost PS resource usage panel, but until I went that high, I always saw the memory usage on Xcache max out within minutes. Now it stays at around 70% usually. If you look at the source for Xcache, there’s a folder called ‘admin’, which you can set up on your server to monitor the status of it. You don’t actually need to compile your own copy, you just need the PHP scripts from that folder and to enable it and a password in your php.ini.
One thing to keep in mind is that if you’re looking at your own pages, you’re not going to get the cached copies. WP sees you are logged in and serves you dynamic results again.