Anthony McLin

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.

Update:
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'

Categories: 

Comments

This really helped me, thanks a lot. The site I have been working on was taking around 4-5 minutes to load pages. The admin side would not load at all and would just hang. I've not found this anywhere else online, so for other users of Media Temple, using Drupal 7 this turned out to be a great work around. I just opened the .sql file in TextEdit.

After hours of searching for a solution, yours really helped. I'm just wondering whether I will have to change it all back to INNODB when I move the database to the client's server?

You shouldn't have to switch back to InnoDB on the client's server, and probably shouldn't if they are on a MediaTemple Grid Server account as well. You can have a mix of both InnoDB and MyISAM tables in the same database.

The Drupal cache and variable tables are safe to run as MyISAM, they don't have any functionality that requires InnoDB. InnoDB usually is faster, but uses more memory than MyISAM. Since these tables are very large, and accessed very frequently, that causes a memory bottleneck on the MediaTemple MySQL cluster.

If your client is on a different hosting environment, then you should test switching the tables back to InnoDB.

- Anthony McLin

Will do, thanks once again.

Thanks a lot, it solved it! just in case someone else needs it, this is what i did:

I am running drupal 7.22 in Media Temple Grid Service, after installing (1-click installer) and adding modules, style and content, site started to get slower and totally unresponsive. Then got only gateway timeouts.

I opened phpMyAdmin
1) Backup the whole databse. Backup of every drupal_cache* and drupal_variables tables independently.
2) On every drupal_cache* and drupal_variables table, opened "Operations" tab and changed the "Storage Engine" from InnoDB to MyISAM.
- Some of them were MyISAM already, some were not, i changed only the InnoDB ones.
- Some of them retrieved timeout after few minutes of loading. For these ones:
a) I opened the respective backup/dump (creation and insertion statements) with a text editor (Sublime Text 2),
b) Replaced "InnoDB" with "MyISAM" in the creation statement.
c) Dropped the respective table in the database
d) Copied and pasted the new sql code to create a new table and insert all the original values in the DB, but this time with MyISAM engine.

After all of this, the site is working normally again, a bit slow, but at least works.

I think doing this in all tables would improve the general performance, but by now I only changed these ones.

Thanks a lot, great article!

- Guille (not verified)

Thanks for the added info Guille. You should see some improvements if you change more tables to myISAM, especially the cache and registry tables which get accessed a lot.

Also, you can write an SQL query to just alter the table format instead of a complete dump/import.

- Anthony McLin

I ended up changing all the tables and it is actually working faster! Especially the admin pages , they still were pretty slow, I could really see improvements. Thanks again!

Anyway, every time I install a new module or create a field in a content type (or create a new content type) Drupal adds some new tables with InnoDB as engine, degrading performance again. So as an advice I would take especial attention to this, checking the new tables after structure changes.

- Guille (not verified)

this really bothers me a long time. change cache tables to MYISAM seems work. thx again

- James (not verified)

My site started crawling last night taking several minutes to load some pages and Media Temple was no help at all. They kept telling me to optimize the website. We just spent the last 12 hours going back and forth about this with them moving me to a container that made no difference at all. The switch to MyISAM worked like a charm and the site is finally back and running . I'm going to move off of Media Temple after this.

Add new comment