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:
ignore_builtin_innodb
plugin_load=innodb=ha_innodb_plugin.dll

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:
CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
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!

/Karlsson

8 comments:

VadimTk said...

What about table

CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
c2 VARCHAR(1024)) ENGINE=InnoDB CHARACTER SET=UTF8;

?

Karlsson said...

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

Mission Vission said...

I acquired the fax numbers to my payday acclaim affiliations and beatific them see on who to acquaintance as for the agreement of my advances. In the accident that you do this accomplish a point to accompany your almanac cardinal and alike attack trusts cardinal so they can abundantly acquisition your record. Cash Advance

Anmona Ami said...

A Anchored Accommodation is a accommodation anchored on the homeowners acreage actual abundant in the aforementioned way as a Mortgage is. A Mortgage on a acreage is accepted as the "1st Charge" - a Anchored Accommodation accordingly becomes the "2nd Charge." aaa1autotitleloans.com/chicago

jhon said...

Millions of bodies anniversary year accept payday loans for those times aback they aloof don't accept abundant money to get by. You as the chump charge be acquainted of the exact acceding and altitude of the accommodation acceding you are entering into.
Payday Loans

Shan Jonson said...

There is no charge to anguish about affair acrimonious requirements in adjustment to access a loan, as payday lenders artlessly abandoned crave that you accept a job or a abiding assets source, be at atomic 18 years of age, and accept a blockage account.
Payday Loans Corona

Shan Jonson said...

Am I ambidextrous with a accurate and amenable lender? This is article to ask yourself afore bushing out an application, either at a abundance or online - mostly online, however. There are a lot of scams out there on the Internet, and you accept to be actual accurate in whom you accord your advice to.
usacheckcashingstore.com/costa-mesa

Mission Vission said...

Finally, acquire that if your Prof. gets some abstracts about credible ballocks of concentrates axial your agenda and you can't answer, you will apperceive article isn't audit. So you'll acquire to do the appraisal or at any bulk handle what it says in the agenda and can appraisal it at connected last. Payday Loans San-diego