Friday, October 30, 2009

InnoDB Plugin (With some Windows focus) - Part 1

So InnoDB is now, from 5.1.38, distributed with MySQL. It is still not enabled by default though. What this means is that there are 2 InnoDB implementations that comes with your MySQL installation. The old-school standard one, which is compiled in with the MySQL binary and is enabled by default, and one plugin.

The plugin comes as a DLL, and to load it, you have to do two things:
- Disable the builtin InnoDB Engine.
- Load the plugin.
To do this, on windows with a standard configuration, you edit the my.ini file and add the following two lines:

And that's about it, if you restart MySQL now
, the plugin version of InnoDB will be used. So, what is new in the plugin then? Many things, actually, some rather cool features, a bunch of performance and scalability improvements and some enhancements to the operational aspect.

I will write a few blogposts on the subject on the InnoDB plugin, and I will use Windows for what I am doing, mainly as I have noticed there is little in the way of documentation, blogs etc on this same subject with a Windows focus.

And let me tell you that although the performance and scalability improvements are what is most talked about, this by far is not the only changes. Even if you have OK performance today, use Windows and have little need for more scalability, there is stuff here for you. But before I go into today's subject, let me tell you one important thing: The InnoDB plugin, as it stands here, is not considered GA yet. This is planned for December this year, but right now, it's considered Beta, despite the fact that MySQL 5.1.38 is GA. So be a bit careful here. And that said, the plugin in and of itself actually is GA, so I don't think we'll see many issues here.

Now, today's lesson: Fast index creation
This is a feature that is waaay overdue, if you ask me, but it's available now. What it means, is that in most cases, creating or dropping an indexes does not mean that the table needs to be rewritten anymore! This is a major advantage when it comes to managing a MySQL installation. To me, this is reason alone to leave MyISAM / Maria and whathaveyou, and go InnoDB instead!

The interesting thing right now, in 5.1.38 and up, is that there are 2 InnoDB engines, and we can compare them, old and new, by just flipping a few parameters and restarting MySQL! So lets do that.

I create an InnoDB table:
c2 VARCHAR(1024)) ENGINE=InnoDB;

The next step is to insert some data. I insert some semi random data in the c2 column, 300 bytes long. It's not terribly random actually, but I wanted a simple test and go on and insert some 311296 rows (just some arbitrary number).

I then want to create an index on the 10 first bytes of the c2 column. This is what happened with the InnoDB plugin being used:
mysql> create index ix1 on t1(c2(10));
Query OK, 0 rows affected (21.75 sec)
Records: 0 Duplicates: 0 Warnings: 0

And then I drop the index:
mysql> drop index ix1 on t1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

At this point, I should say one thing. Before I created the index, I restarted MySQL, to make sure that I could "benchmark" this properly, without data in the cache.

OK, now I flip the configuration back to using the built-in InnoDB Engine, restart MySQL and run the same simple test again, and this is what happened:
mysql> create index ix1 on t1(c2(10));
Query OK, 311296 rows affected (1 min 6.28 sec)
Records: 311296 Duplicates: 0 Warnings: 0

mysql> drop index ix1 on t1;
Query OK, 311296 rows affected (43.27 sec)
Records: 311296 Duplicates: 0 Warnings: 0

Quite a difference, right? Using the InnoDB plugin, index creation is 3 times as fast! And this is not that a big index! And dropping the index is more than 300 times faster! That's the kind of performance improvement I like!



VadimTk said...

What about table



Karlsson said...

Oh, it is charset UTF8. But that's dthe default I use.

Paulo said...

Resources are ordered into classes, and every class has an alternate permissible deterioration rate. In the primary year of the advance, just half of the deterioration can be composed off and in addition the interest segment of the advance. In ensuing years, devaluation can be composed off on a declining equalization premise. check cashing

Paulo said...

Most mortgage holders that choose to proceed with redesign ventures come up short on cash most of the way into the venture. This happens because of the way that numerous individuals don't have the appropriate measure of financing from the begin of the venture. Payday Loans Chicago

Paulo said...

Not all the oil organizations are occupied with the versatile oil change design because of its little impression and subsequently unless you had some enormous armada accounts you may discover their help to some degree non-existent. Without a doubt, you appear to likewise be searching for capital. Auto Title Loans Chicago