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

12 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.

devon broad said...

Anytime disorders get difficult consequently you would like enclosed cash, having obtaining some style of Strategy loan is that the foremost style and magnificence of home mortgage out there for you. The user gets what proportion cash need; someone hold on to your current automobile additionally to the bulk people typically program the actual issue in direction payday loan of automobile before the disposal choices will possible be settled entirely, specifically wherever event this type of issue can possible be come back to fit your wants no price additionally to noticeable. it's typically that simple.

herbal medicine said...

tanda tanda wasir atau ambeien wasir pada wanita obat kelamin keluar nanah obat menyembuhkan kemaluan keluar nanah

herbal medicine said...

apa obat kencing nanah atau gonore cara mengobati penis keluar cairan nanah bagaimana mengobati kencing nanah atau gonore cara mengobati kelamin keluar nanah

First Aly said...

obat kutil di sekitar peniskutil di sekitar kelamin obat kutil kelamin jakarta bisakah kutil kelamin disembuhkan cara mengatasi kutil di kemaluan bintik kutil di ujung penis pria obat ampuh menghilangkan kutil kelamin cara menghilangkan kutil di kelamin

obat herbal said...

obat kutil di sekitar peniskutil di sekitar kelamin obat kutil kelamin jakarta bisakah kutil kelamin disembuhkan cara mengatasi kutil di kemaluan bintik kutil di ujung penis pria obat ampuh menghilangkan kutil kelamin cara menghilangkan kutil di kelamin

herbal medicine said...

kelamin mengeluarkan nanah dan alat kelamin keluar nanah dan keluar nanah dari kemaluan dan alat kelamin keluar nanah dan kemaluan keluar nanah serta obat kemaluan mengeluarkan nanah obat kemaluan mengeluarkan nanah dan obat alat kelamin keluar nanah dan kemaluan mengeluarkan nanah dan alat kelamin keluar nanah dan obat kencing nanah merupakan solusi pengobatan herbal dari denature indonesia

wasirdenature said...

kenapa keputihan tidak normal dan mengeluarkan nanah obat kutil herbal di kemaluan cara menghilangkan kutil kemaluan secara alami tips menghindari kutil kelamin tips menghilangkan kencing nanah cara mengobati kelamin keluar nanah obat alam tradisional kencing bernanah obat kutil di kemaluan pria obat kemaluan keluar nanah Obat Ambeien Ampuh Tradisional Resep Obat Wasir obat alami untuk gejala wasir Mengobati Wasir Secara Alami Obat ampuh atasi wasir Obat herbal efektif menyembuhkan wasir Obat untuk kelamin laki laki keluar cairan nanah Obat kemaluan keluar nanah atau gonore Obat cairan nanah keluar dari alat kelamin Mengapa kencing sakit dan keluar cairan nanah Mengapa kencing perih dan keluar nanah Mengapa nanah keluar dari kelamin Obat untuk kutil di sekitar bibir vagina Obat kencing nanah manjur menghilangkan benjolan kutil di vagina wanita

wasirdenature said...

obat kemaluan keluar nanah herpes kelamin penyakit kencing nanah atau gonore obat kencing nanah diapotek obat penyakit gonore di apotik obat alat kelamin keluar nanah atau kencing nanah kencing terasa perih dan keluar cairan nanah obat cairan nanah dari kemaluan mengapa kencing perih dan keluar nanah mengapa ujung kelamin keluar cairan nanah cara mengobati kutil di area vagina penis keluar cairan nanah dan kencing perih penyebab penis keluar nanah obat wasir herbal tanpa operasi penis keluar nanah kenapa kemaluan lelaki keluar nanah obat wasir herbal semarang obat ambeien alami obat ambeien tanpa operasi obat ambeien tanpa operasi dan efek samping obat wasir alami obat manjur kemaluan keluar nanah di apotik kenapa kecing perih dan bernanah obat kemaluan keluar nanah atau gonore

lionelmessi10 said...

kenapa keputihan tidak normal dan mengeluarkan nanah obat kutil herbal di kemaluan cara menghilangkan kutil kemaluan secara alami tips menghindari kutil kelamin tips menghilangkan kencing nanah cara mengobati kelamin keluar nanah obat alam tradisional kencing bernanah obat kutil di kemaluan pria obat kemaluan keluar nanah Obat Ambeien Ampuh Tradisional Resep Obat Wasir obat alami untuk gejala wasir Mengobati Wasir Secara Alami Obat ampuh atasi wasir Obat herbal efektif menyembuhkan wasir Obat untuk kelamin laki laki keluar cairan nanah Obat kemaluan keluar nanah atau gonore Obat cairan nanah keluar dari alat kelamin Mengapa kencing sakit dan keluar cairan nanah Mengapa kencing perih dan keluar nanah Mengapa nanah keluar dari kelamin Obat untuk kutil di sekitar bibir vagina Obat kencing nanah manjur menghilangkan benjolan kutil di vagina wanita

marko said...

Secured individual auto advances are for individuals who need lower interest rates and need higher reimbursement periods. These are solely the components of the secured advances. As the bank has some kind of security with him, he is prepared to surrender you to ten years of time to pay off the advance. payday loans chicago