InnoDB is red hot, MyISAM not

October 11, 2011

Everyone who started using MySQL before 5.5 started off with MyIsam. It was the default storage engine and you had to go out of your way to use anything else. It was a good looking database, rugged, simple and fast in many respects. But crash recovery was not a MyIsam forte. Neither was locking or transactions. Speed was pretty good on reads. But many database snobs pointed to the short comings of MyIsam and claimed it was proof that MySQL was a ‘toy database’.

But InnoDB arrived with transactions, row level locking, and better crash recovery than MyIsam. Many detractors of MySQL now started talking positively about it. In some cases the performance for some bench marks was behind MyIsam. Notice the use of ‘was‘. Now the InnoDB and server teams report to the same management and are going through all the code carefully to achieve better performance. Now the REDO log in its own table space, buffer pools can be pre-loaded at start up, and page sizes can be set to 4k, 7k or 16K to take advantage of new disks. Now the performance for most applications is much better with InnoDB than MyIsam. And as of MySQl 5.5, InnoDB is the default storage engine.

Now if the time for those who primarily use MyIsam to switch over to InnoDB. Oracle is poring manpower and money into MySQL to make it an better product. Much of that is focused on InnoDB.

‘But Dave! I do not need transactions, never need row locking, and do not worry about crashing’ I can hear some of you say. The majority of the new features are designed around InnoDB. Thread pooling, for an example, is going to greatly increase the transaction per seconds and transactions are InnoDB territory. Data compression allows InnoDB to read more off disk peer disk seek. The way of the future is with InnoDB.

MyIsam is not going away any time soon. All the account tables are MyIsam, for now. It is still a good database. But InnoDB is better.

But now is the time for you to move to InnoDB.


New InnoDB features

August 25, 2011

Yesterday I wrote about the new Full-text search for InnoDB. Some of you may have not noticed there is a package on Labs.MySQL.Com named mysql-5.6-labs-innodb-features that needs attention. Some of the features are already in the main MySQL source trunk and others will soon be there. Code to support solid state drives with 4K and 8K pages is there. Along with REDO logs up to 2TB in size. There is better thread scheduling and an option to pre-load the buffer pool for better performance after a restart. And UNDO logs can be in their own table space. And please let us know what you find in your testing so we can improve the product!

MySQL 5.6 — InnoDB and Memcached

May 11, 2011

One of the more exciting new features in MySQL 5.6 is the InnoDB to Memcached interface. Basically memcached runs as a daemon plugin and can bypass the SQL optimizer and parser for NoSQL access.

The first step is to download the new MySQL 5.6 with the InnoDB-Memcache preview. Sorry, Linux only at this time. And install memcached.

Second, run the provided configuration script mysql> < scripts/innodb_memcached_config.sql. This will do a lot of the work to get things running out of the box and one of the links below details what is happening behind the scenes when you run the script. Third, load the plugin mysql> install plugin daemon_memcached soname “”;. Forth, to make sure we can see recently inserted data, you will need to set the transaction level mysql> set session TRANSACTION ISOLATION LEVEL read uncommitted;

Fifth step, feed some data into the provided InnoDB demo table mysql> INSERT INTO demo_test VALUES ('dave','it works', 10, 200, NULL).

Now, can we get memcached to get the data from InnoDB?

MySQl 5.6 Memached-InnoDB

Using memached to get the data from InnoDB

Yes! This is a very simple example using the ‘canned’ configuration. Your mileage may vary and this server is a preview and not for production. But there is a lot more stuff in there that I will be looking into soon.