The websites convert from MyISAM tables to InnoDB to improve their site speed.

Here at Bobcares, we help website owners convert their MyISAM databases to InnoDB with zero data loss and minimal downtime as part of our Server Management Services.

Read to know why InnoDB is faster and how to convert MyISAM to InnoDB.

Why is InnoDB better than MyISAM?

MySQL uses the MyISAM engine by default, which is an older storage engine when compared to InnoDB.

InnoDB resolves many of MyISAM’s performance bottlenecks and has the following advantages over MyISAM.

• Automatic crash recovery
• Supports transactions by tables which makes dealing with larger databases easier.
• InnoDB provides row-level locking which makes inserting and updating easier than the table-level locking in MyISAM.
• It uses transactional logs for auto-recovery.

But before converting the storage engine, we take a few factors into consideration. Let’s have a look at them.

Factors to consider before changing MyISAM to InnoDB

For better reliability and scalability we consider the following factors before converting to InnoDB.

1. Memory Usage

The MyISAM uses key cache whereas InnoDB uses InnoDB Buffer Pool. So, we no longer need to lower the value of key_buffer_size to free memory for caching results.

But we have to increase the value of the innodb_buffer_pool_size. Because this allocates cache memory for InnoDB tables in my.cnf.

2. Handling transactions

MyISAM tables do not support transactions. But the same doesn’t go with InnoDB as it provides transactions.

So it is necessary to be aware of autocommit configuration option and the COMMIT and ROLLBACK statements. These keywords allow multiple sessions to read and write InnoDB tables.

How to convert MyISAM to InnoDB?

Here is how we convert to InnoDB using phpMyAdmin and MySQL CLI. First, let’s see how we do it using phpMyAdmin.

• First, we login to phpMyAdmin.
• Then, check the type column and see the storage engines.
• Later, click on the MyISAM table and click the Operations tab and change the storage engine.

Alternatively, we run the below command to change the storage engine of a particular table to InnoDB.

ALTER TABLE table_name ENGINE=InnoDB;

This allows conversion via MySQL CLI.

Common errors while converting MyISAM to InnoDB

Let’s take a look at some of the common errors and how our Support Engineers fix it.

1. Error with memory usage

The innodb_buffer_pool_size configuration provides the value of buffer pool size. Typically, a recommended value is 50 to 75% system memory.

However, if a proper value is not set in the account then it will end up with an error. Normally, the error shows up as,

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page!

The best fix for this error is to increase the value of innodb_buffer_pool_size in my.cnf.

2. Database crash

A database crash is one of the common errors. The error message users normally report appears as,

[ERROR] [FATAL] InnoDB: Page [page id: space=45, page number=4551] still fixed or dirty

Here, the binary or one of the libraries linked is corrupt. That is, it is improperly built or misconfigured. This error can also be caused due to hardware trouble.

We normally fix this error by running the below commands

myisamchk <database> <server>
mysqlcheck -r <database> <postname>

Later we ensure that the database is working.

3. Database ownership

This is a general error. If the ownership of the database is changed then it throws an error. And we fix this error by correcting the ownership using the command,

chown mysql:mysql database_name

[Need assistance to convert MySQL storage engines? – We’ll help you.]

Conclusion

In short, we convert MyISAM to InnoDB because of its better features. This includes automatic crash recovery, transaction support, row-level locking, and many more. Today, we saw how our Support Engineers did this conversion.