Why do I recommend switching over from MyISAM to Innodb!

MyISAM has been the default storage engine for MySQL for years now. But that is soon going to change. With MySQL 5.5, Innodb is going to be the default storage engine (and I am pretty pleased about that, no more new users complaining about MySQL not being ACID compliant).

But why do I recommend Innodb over MyISAM. Following are the reasons:

I will start of with reasons not so obviously discussed during comparisons of Innodb and MyISAM storage engines. But these reasons are so compelling that I bet you would want to switch over to Innodb after reading the reasons.

Innodb is being constantly improved and worked upon

There hasn’t been much work put forth into improving the MyISAM storage engine, and with Oracle acquiring MySQL and owning Innodb, it has put its weight on the Innodb storage engine. While there hasn’t been much work done on MyISAM side, Innodb has seen improvements and work is being done aggressively. MySQL 5.5 will ship with a new version of Innodb – version 1.1, which has major performance improvements. Even if you are using MySQL 5.1, you can replace the built-in Innodb with the newer Innodb plugin – version 1.0.8, which has introduced major performance improvements.

Innodb supports row-level locking

Innodb not only supports table-level locking, but also supports row-level locking, making it the storage engine of choice for high concurrency applications.

Innodb supports hot backup

With MyISAM, backing up a live database, is pretty much an issue for many DBAs because of the way how MyISAM supports locking. MyISAM only supports table-level locks which pretty much means that the server must go down during the backup, for sometime depending upon the amount of data in the database. With Innodb this is no issue at all, Innodb supports row level locking which allows you to do hot backups.

Innodb is ideal for high-concurrency applications

Yet another big reason for using Innodb is that with Innodb you can achieve high-user-throughput and high-database-concurrency. That is again thanks to the row-level mechanism of Innodb, which makes it a big winner (compared to MyISAM) in cases where you need highly concurrent read-write operations.

Innodb is crash proof

What I essentially mean by being crash proof is the database, recovering back into a consistent state in case of a crash (be it an OS crash or a MySQL server crash). While in the case of MyISAM, a crash would mean that you would have to deal with recovering tables on the crash or partially executed statements, in the case of Innodb there would be essentially no data inconsistency and all the more its more of an automated recovery, where by Innodb would recover itself by replaying its logs.

Read more: Ovaistariq

Posted via email from .NET Info