Conversion of table engine from MyISAM to Innodb

When we have seen conversion of table engine from MyISAM to Innodb as being most beneficial?

  1. If you need ACID compliance and need your db to be transactional then choosing Innodb is an obvious choice and you ought to make the necessary conversion including adding any FK constraints, etc if you need them.
  2. If you are not disproportionately read-only heavy and are doing a mix of reads (not requiring full text indexing) and writes then we do recommend that you go with Innodb.
  3. Most commonly we have observed that MyISAM tables would rather be converted to Innodb when you face frequent table lock escalations for long periods of time.
  4. If a read is slow or hasn’t completed and a read/write is waiting on the first read to finish then the MyISAM table referenced in the read is held in a locked state till the resultset is made available to the query. This also causes a rise in the load average on the server and slows your site down. During this time no reads or writes can complete ofcourse as MyISAM only has table-level locking.

So to summarize, the queries that are victims of lock escalations under heavy but slow reads would do much better as a table converted to Innodb.

 

How to change your table Engine type from MyISAM to Innodb?

You do so by simply issuing the “ALTER TABLE” DDL statement:

ALTER TABLE <table-name> ENGINE=INNODB;

Below is a step by step process for altering a table in PHPMyAdmin:

DB_article.png

  1. Log into the PHPMyAdmin utility through your control panel. If you are unsure how, please see Working with a MySQL database for instruction on how to login to PHPMyAdmin.
  2. Select the database which contains the Table-Name
  3. Click on the SQL tab
  4. Paste in the query provided above. Be sure to replace table-name with the correct name of your table
  5. Click the GO button.

Note: A MyISAM table that is using FULL TEXT Indexing can not be converted to an Innodb Table Engine type.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s