Fixing hangs in Drupal Admin on Media Temple Hosting
So a while back, I identified that Drupal can run incredibly slow on MediaTemple's Grid Service. It's otherwise a great hosting plan that I've been very happy with. SSH access, easy to configure with additional domains, scales well. Of course I forgot about this as I did my Drupal conversion, but it looks like there's a fix.
Apparently the issue stems from the MySQL servers. Anything that is database-heavy can have serious lag problems on their shared database servers. Supposedly under heavy MySQL load, they'll automatically move you to an isolated MySQL instance, and notify you by email. Perhaps I'm on a very old plan, and don't have the auto-scaling enabled, but I never receive this message, and instead anything admin-related on Drupal completely hangs.
The problem can be traced to the fact that Drupal uses INNODB tables, and MediaTemple prefers MyISAM. If you install your site via MediaTemple's 1-click-Drupal-install, you won't experience the issue, but if like me, you built on a local dev server and then uploaded, or migrated from another host, you'll be scratching your head. The fix is to use PHP Admin, or you MySQL interface app of choice (I really like Sequel Pro on OSX) and convert at least any tables who's name starts with cache or variable. If you don't have a MySQL interface app, you can create a backup of your database, open the file in a text editor, and replace "INNODB" with "MyISAM" and then import your new file.
I'll be giving this a try and hope it improves things. I'd hate to leave MediaTemple as their customer service has been great and I've referred many clients to them over the years.
I'm only halfway done changing the table formats, and already the site is screaming fast.
Update June 30, 2014:
Here's a quick SQL script to batch change all your tables from InnoDB to MyISAM:
select concat('alter table ',table_schema,'.',table_name,' engine=MyISAM;') from information_schema.tables where engine = 'InnoDB'