Tuesday, August 18, 2009

Testing the InnoDB plugin with MySQL snapshots


MySQL plugins

The cat is out of the bag.
MySQL 5.1 will include the InnoDB plugin, and thanks to
labs.mysql.com
you can try the new version right away.
Here is a step-by-step guide to testing the InnoDB plugin with MySQL snapshot 5.1.39 and MySQL Sandbox.

1. Install MySQL::Sandbox
This is a straightforward part. Please refer to the manual for the details.

2. get the binaries
Check the list of available binaries and download the one that matches your architecture and operating system.

3. Install the sandbox
Since we want to use the InnoDB plugin, we need to start the Sandbox with the builtin innodb engine disabled.
make_sandbox \
/path/to/mysql-5.1.39-snapshot20090812-osx10.5-i386.tar.gz \
-c ignore-builtin-innodb
The option passed with "-c" will be written to the options file.
Make sure that the sandbox is installed and the server starts. If it doesn't, check the error log at $HOME/sandboxes/msb_5_1_39/data/msandbox.err and try to figure out what happened.

4. Check the available engines
~/sandboxes/msb_5_1_39/use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39-snapshot20090812 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

select engine, support from information_schema.engines;
+------------+---------+
| engine | support |
+------------+---------+
| MyISAM | DEFAULT |
| MRG_MYISAM | YES |
| BLACKHOLE | YES |
| CSV | YES |
| MEMORY | YES |
| FEDERATED | NO |
| ARCHIVE | YES |
+------------+---------+
As you can see, InnoDB is not in the list.

5. Install the innodb plugin
install plugin innodb soname 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.85 sec)

select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.4 |
+------------------+

6. Install the additional INFORMATION SCHEMA tables
INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

7. Finally, check the results
select plugin_name, plugin_type, plugin_status from information_schema.plugins;
+---------------------+--------------------+---------------+
| plugin_name | plugin_type | plugin_status |
+---------------------+--------------------+---------------+
| binlog | STORAGE ENGINE | ACTIVE |
| partition | STORAGE ENGINE | ACTIVE |
| ARCHIVE | STORAGE ENGINE | ACTIVE |
| BLACKHOLE | STORAGE ENGINE | ACTIVE |
| CSV | STORAGE ENGINE | ACTIVE |
| FEDERATED | STORAGE ENGINE | DISABLED |
| MEMORY | STORAGE ENGINE | ACTIVE |
| MyISAM | STORAGE ENGINE | ACTIVE |
| MRG_MYISAM | STORAGE ENGINE | ACTIVE |
| InnoDB | STORAGE ENGINE | ACTIVE |
| INNODB_TRX | INFORMATION SCHEMA | ACTIVE |
| INNODB_LOCKS | INFORMATION SCHEMA | ACTIVE |
| INNODB_LOCK_WAITS | INFORMATION SCHEMA | ACTIVE |
| INNODB_CMP | INFORMATION SCHEMA | ACTIVE |
| INNODB_CMP_RESET | INFORMATION SCHEMA | ACTIVE |
| INNODB_CMPMEM | INFORMATION SCHEMA | ACTIVE |
| INNODB_CMPMEM_RESET | INFORMATION SCHEMA | ACTIVE |
+---------------------+--------------------+---------------+
Now you can read the InnoDB plugin manual and have as much fun as you can.

7 comments:

Mark Daems said...

Giuseppe,

Do I understand correctly all these plugin goodies aren't yet available for us, old fashioned Windows users?

I just downloaded the latest snapshot, but looks like no plugins are included. Are there any plans to port the plug-in feature to windows soon?

Mark

Giuseppe Maxia said...

Mark,
It should work for Windows as well. If it doesn't, it's a bug. And Sheeri has filed Bug#46794 for this reason.

Mark Leith said...

Actually I wouldn't necessarily class this as a bug - but rather a phenomenon of working with the snapshot releases.

Note the build team was already working on it:

http://lists.mysql.com/commits/80996

This is always the risk when dealing with a snapshot release.. It's safe to assume that we would never have released a full release in that state.

Giuseppe Maxia said...

Mleith,
As you know, I trust MySQL QA process quite a lot and I am sure we would never ship a product with this defect.
However, in this case, I'd like to think of it as a bug that was caught early in the development process, and as such is less expensive to fix than one we may catch after a GA release.
Basically, the rationale of having snapshot builds is all here!

Giuseppe

Mark Daems said...

Giuseppe,

This is really good news. I never saw some Windows demonstrations of the plugin stuff, so I wrongly assumed it was *nix only at the moment.
The more : the plugin directory seemed to be absent in 5.1, 5.4 and 6.0 releases so I already gave up long time ago.
Maybe next time a plugin demo for Windows users?

Thanks for the quick response.
And of course, thanks to Sheeri for the bug report.

seattlegaucho said...

It doesn't look like we'll have Windows plugins anytime soon:

http://forge.mysql.com/worklog/task.php?id=3653

The first comment was 2 years ago.

Wagner Bianchi said...

Hi Mr. Maxia,

Was great the explaination about the InnoDB Plugin instalation. I found so many others "tutos", but, yours was the better!

Happy MySQL'ing! See you @ MySQL Conference.