Posts Tagged ‘innodb plugin’

Comprehensive How-To for Enabling the Standard InnoDB Plugin in MariaDB and MySQL

Monday, June 24th, 2013

I’m always switching back-and-forth between the 2 different InnoDB flavors in MariaDB – XtraDB+ and the standard InnoDB plugin, so I thought I’d simply post all of the various combinations in a single place. (And then I cover enabling the InnoDB Plugin in MySQL, since it’s an option in 5.1.) [Addition: Thanks to Andrew and Sergei for the tips on shortening plugin-load=. The changes are reflected below.]

Note: Below is for Windows. For Linux, simply change “.dll” to “.so” where appropriate.

MariaDB 10.0:

Do not add anything, as the standard InnoDB plugin is the current default (as of 10.0.3, although I do anticipate this changing in the near future, and I’ll update the post accordingly when that happens).

MariaDB 5.5:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb.dll

MariaDB 5.3:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MariaDB 5.2:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MariaDB 5.1:

# Enable the 2 below to disable XtraDB+ and Enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

Note that enabling it in 5.1, 5.2, and 5.2 are the same. As for 5.5, the only difference is that the name of the .dll has changed from “ha_innodb_plugin.dll” to “ha_innodb.dll” (so that needs changed in each place it occurs).

MySQL 5.1:

For MySQL 5.1, you would enable the InnoDB Plugin in the same way you would for MariaDB 5.1:

# Enable the 2 below to enable the standard InnoDB Plugin
ignore_builtin_innodb
plugin-load=ha_innodb_plugin.dll

MySQL 5.5+:

In MySQL 5.5+, the InnoDB Plugin is the default (and only InnoDB flavor of InnoDB in MySQL).

And finally, since I’m discussing the InnoDB Plugin, I do have some older posts on the subject for anyone who may be interested:

InnoDB Plugin Versions
http://www.chriscalender.com/?page_id=628

Ease of Switching to the InnoDB Plugin and the Numerous Benefits
http://www.chriscalender.com/?p=99

InnoDB Plugin Version Numbering in MySQL and MariaDB
http://www.chriscalender.com/?p=1205

Hope this helps. :)

 

How to Build MariaDB 10.0 on Windows from Source Code

Tuesday, March 26th, 2013

I just wanted to share my steps for building MariaDB 10.0 on Windows (10.0.1 in this case):

cd maria-10.0.1
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

That’s it! The zip file is created right there in bld/.

Hope this helps. :)

Related Build Links:

 

InnoDB Plugin Version Numbering in MySQL and MariaDB

Monday, March 18th, 2013

As some of you may or may not know, I’ve maintained a list of all InnoDB Plugin versions as they’ve historically contained a different version (entirely different numbering scheme) than the MySQL distribution they were included with.

This list was most helpful for troubleshooting various InnoDB issues when the plugin may (or may not) have been involved, and/or for benchmarking, etc. And it’s fair to say it was more useful when the InnoDB plugin was not the mainstream, which it is now.

However, with the latest releases, in MySQL and MariaDB, the “InnoDB Version” simply matches the “MySQL Version”. These “latest releases” include: MySQL 5.6.10, MySQL 5.5.30, MySQL 5.1.68, and MariaDB 5.5.30

Of course this isn’t the most newsworthy story, but having maintained this “list” the past couple/few years, I was happy to see the change, and at least wanted to mention it.

There won’t be a need for me to further update the page, but it is up-to-date now, so if you happen to need to know what version of InnoDB Plugin an older version of MySQL or MariaDB is using, then this page will still be there for you (it’s also in the right-hand column of my site under “Pages” titled “InnoDB Plugin Versions“).

Hope this helps. :)

 

How to Enable the Original InnoDB Plugin in MariaDB 5.5

Friday, May 25th, 2012

As I mentioned here, there is a slight change for enabling the [original] InnoDB Plugin in MariaDB 5.5 (as compared to how you would enable it in 5.1).

Remember, in MariaDB 5.5, if you do not “enable” (i.e., add anything to the config file to do so) the InnoDB Plugin in MariaDB 5.5, you’ll end up with XtraDB+ for your InnoDb plugin. However, if you do “enable” the InnoDB plugin, then you end up with the original InnoDB plugin provided by Oracle/InnoDB.

The change is that the plugin file (.dll for Windows, .so file for Linux) which was previously named “ha_innodb_plugin.dll” is now just “ha_innodb.dll”.

Thus, if you previously enabled the plugin with (would have been in a 5.1 instance):

[mysqld]
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;
innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;
innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;
innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

You now need to use:

[mysqld]
ignore_builtin_innodb
plugin-load=innodb=ha_innodb.dll;innodb_trx=ha_innodb.dll;
innodb_locks=ha_innodb.dll;innodb_lock_waits=ha_innodb.dll;
innodb_cmp=ha_innodb.dll;innodb_cmp_reset=ha_innodb.dll;
innodb_cmpmem=ha_innodb.dll;innodb_cmpmem_reset=ha_innodb.dll

That is the only difference, so after making that change, just restart your instance, and you’ll be in business.

For reference, if you encounter this, you might see an error similar to the following in your error log:

120524 19:24:56 [ERROR] Can't open shared library
 'C:\Program Files\MySQL\MariaDB 5.5\lib\plugin\ha_innodb.dll'
 (errno: 0 The specified module could not be found.)
120524 19:24:56 [ERROR] Couldn't load plugin named 'innodb' with
 soname 'ha_innodb.dll'.
120524 19:24:56 [ERROR] C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld:
 unknown variable 'innodb_buffer_pool_size=10M'
120524 19:24:56 [ERROR] Aborting

Hope this helps.

 

How to Build MariaDB 5.5 on Windows from Source Code

Friday, May 25th, 2012

I built MariaDB 5.5.24 on Windows yesterday from source, so I just wanted to share my steps.

Here is the short version:

bzr branch lp:maria/5.5 maria-5.5
cd maria-5.5
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

Done! Nice, neat zip file is created right there.

Here is the longer version with outputs for those interested:

C:\mariadb-5.5>bzr branch lp:maria/5.5 maria-5.5
Connected (version 2.0, client Twisted)
Authentication (publickey) successful!
Secsh channel 1 opened.
Branched 3418 revision(s).

C:\mariadb-5.5>cd maria-5.5

C:\mariadb-5.5\maria-5.5>mkdir bld

C:\mariadb-5.5\maria-5.5>cd bld

C:\mariadb-5.5\maria-5.5\bld>cmake .. -G "Visual Studio 10"
-- Check for working C compiler using: Visual Studio 10
-- Check for working C compiler using: Visual Studio 10 -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler using: Visual Studio 10
-- Check for working CXX compiler using: Visual Studio 10 -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- MariaDB 5.5.24
-- Check size of void *
-- Check size of void * - done
-- Packaging as: mariadb-5.5.24-win32
-- Looking for include files CMAKE_HAVE_PTHREAD_H
-- Looking for include files CMAKE_HAVE_PTHREAD_H - not found.
-- Found Threads: TRUE
-- Looking for pthread_attr_getguardsize
-- Looking for pthread_attr_getguardsize - not found
-- Check size of time_t
-- Check size of time_t - done
-- Could NOT find Boost
-- Cannot find wix 3, installer project will not be generated
-- Looking for MFC
-- Looking for MFC - not found
-- Configuring done
-- Generating done
-- Build files have been written to: C:/mariadb-5.5/maria-5.5/bld

C:\mariadb-5.5\maria-5.5\bld>cmake --build . --config relwithdebinfo --target package
.......
lots of output
.......
C:\mariadb-5.5\maria-5.5\bld\...
C:\mariadb-5.5\maria-5.5\bld\...
C:\mariadb-5.5\maria-5.5\bld\...

    46 Warning(s)
    0 Error(s)

Time Elapsed 00:23:18.16

Success \o/

Package is named: mariadb-5.5.24-win32.zip

Full location:

C:\mariadb-5.5\maria-5.5\bld\mariadb-5.5.24-win32.zip

C:\Users\Chris>mysql -uroot -pmysql -P3313 -e"select version()"
+----------------+
| version()      |
+----------------+
| 5.5.24-MariaDB |
+----------------+

With XtraDB+ (i.e., InnoDB plugin is not enabled), it starts fine, and uses InnoDB version 1.1.8-24.1.

If you try to enable the original InnoDB plugin, note the library is now named ha_innodb.dll (or ha_innodb.so) instead of ha_innodb_plugin.dll (or ha_innodb_plugin.so), which is what it was named in MySQL/MariaDB 5.1. I discuss this topic in more detail here, if you’re interested.

Hope this helps.

Further resources:

Full Details on CMake here:

http://forge.mysql.com/wiki/CMake

For details on building on Linux, please see here:

http://kb.askmonty.org/en/source-building-mariadb-on-centos/
..
 

Tracking Server Variables, Documentation, Manuals, Changelogs for MySQL, InnoDB, MariaDB, and XtraDB

Monday, January 9th, 2012

I find myself constantly looking up server variables (and manuals and changelogs) for MySQL, MariaDB, and XtraDB, which versions they are in, and so forth. So I finally created a couple pages which contain the links to all of these various bits of information across the various flavors of MySQL.

I’ve been using them every day, so I thought some others might want to bookmark these as well.

I’ve created the following:

o Changelogs
o Documentation
o Server Variables
o InnoDB Plugin Versions

The Changelogs page contains links for MySQL 3.23 up through 5.6, MariaDB 5.1 – 5.3, XtraDB 5.1 – 5.5, Xtrabackup 1.3 – 1.6, Connector/ODBC, Connector/.NET, Connector/J, Connector/C++, Connector/MXJ, and MySQL Proxy.

The Documentation page contains links for the MySQL manuals 3.23-5.6, InnoDB Plugin 1.0 – 1.1, MariaDB 5.1 – 5.3, XtraDB 5.1 – 5.3, and Xtrabackup 1.6.

The Server Variables page contains the links for the MySQL Server variables for all versions, the server variable cross-reference chart, all InnoDB startup variables, and new MariaDB options.

And lastly, the InnoDB Plugin Versions (which I did mention a couple weeks ago) contains all InnoDB Plugin version information, such as which InnoDB plugin is included with which MySQL release, as well as links to changelogs, and other relevant information.

If you find yourself wishing there were some other links added, just post me a comment and I’ll try to get it added asap.

 
 

InnoDB Plugin Version History

Thursday, December 1st, 2011

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful :)

MySQL 5.6:

MySQL     Plugin     Status Date
5.6.5 1.2.5 Milestone 8 04/10/2011
5.6.4 1.2.4 Milestone 7 12/20/2011
5.6.3 1.2.3 Milestone 6 10/03/2011
5.6.2 1.2.2 Milestone 5       04/11/2011

MySQL 5.5:

MySQL     Plugin     Status Date
5.5.24 1.1.8 GA 05/07/2012
5.5.23 1.1.8 GA 04/12/2012
5.5.22 1.1.8 GA 03/21/2012
5.5.21 1.1.8 GA 02/17/2012
5.5.20 1.1.8 GA 01/10/2012
5.5.19 1.1.8 GA 12/08/2011
5.5.18 1.1.8 GA 11/16/2011
5.5.17 1.1.8 GA 10/19/2011
5.5.16 1.1.8 GA 09/15/2011
5.5.15 1.1.8 GA 07/28/2011
5.5.14 1.1.8 GA 07/05/2011
5.5.13 1.1.7 GA 05/31/2011
5.5.12 1.1.6 GA 05/05/2011
5.5.11 1.1.6 GA 04/07/2011
5.5.10 1.1.5 GA 03/15/2011
5.5.9 1.1.5 GA 02/07/2011
5.5.8 1.1.4 GA 12/03/2010
5.5.7 1.1.3 GA 10/14/2010
5.5.6 1.1.2 GA 09/13/2010
5.5.5 1.1.1 Early Adopter    06/06/2010 *3
5.5.4 1.1.0 Beta 04/09/2010 *2
5.5.3 1.0.6 RC 03/24/2010
5.5.2 1.0.6 RC 02/12/2010
5.5.1 1.0.6 RC 01/04/2010
5.5.0 1.0.5 RC 12/07/2009 *1

*1: The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
*2: InnoDB has been upgraded to version 1.1. This version is considered of Beta quality.
*3: InnoDB has been upgraded to version 1.1.1. This version is considered of “early adopter” quality.

MySQL 5.1:

MySQL     Plugin     Status     Date
5.1.62 1.0.17 GA 03/21/2012
5.1.61 1.0.17 GA 01/10/2012
5.1.60 1.0.17 GA 11/16/2011
5.1.59 1.0.17 GA 09/15/2011
5.1.58 1.0.17 GA 07/05/2011
5.1.57 1.0.16 GA 05/05/2011
5.1.56 1.0.15 GA 03/01/2011
5.1.55 1.0.15 GA 02/07/2011
5.1.54 1.0.14 GA 11/26/2010
5.1.53 1.0.13 GA 11/03/2010
5.1.52 1.0.13 GA 10/11/2010
5.1.51 1.0.12 GA 09/10/2010
5.1.50 1.0.11 GA 08/03/2010
5.1.49 1.0.10 GA 07/09/2010
5.1.48 1.0.9 GA 06/02/2010
5.1.47 1.0.8 GA 05/06/2010
5.1.46 1.0.7 GA 04/06/2010
5.1.45 1.0.6 RC 03/01/2010
5.1.44 1.0.6 RC 02/04/2010
5.1.43 1.0.6 RC 01/15/2010
5.1.42 1.0.6 RC 12/15/2009
5.1.41 1.0.5 RC 11/05/2009 *4
5.1.40 1.0.4 Beta 10/06/2009
5.1.39 1.0.4 Beta 09/04/2009
5.1.38 1.0.4 Beta 09/01/2009 *3
x.x.xx 1.0.3 Alpha 03/11/2009
x.x.xx 1.0.2 Alpha 12/01/2008
x.x.xx 1.0.1 Alpha 05/08/2008 *2
x.x.xx 1.0.0 Alpha 04/15/2008 *1

*1: The initial release of the InnoDB Plugin is based on the built-in InnoDB in MySQL version 5.1.
*2: These 4 versions (1.0.0 – 1.0.3) were only available as separate downloads.
*3: 1st Release of Plugin (see *1* below for more details)
*4: InnoDB Plugin has been upgraded to version 1.0.5. This version is considered of Release Candidate (RC) quality.

MariaDB 5.5:

MariaDB     Plugin     Status    Date            Comments
5.5.24 1.1.8-24.1 GA 05/24/12 XtraDB+
5.5.24 1.1.8 GA 05/24/12 InnoDB
5.5.23 1.1.8-24.1 GA 04/11/12 XtraDB+
5.5.23 1.1.8 GA 04/11/12 InnoDB
5.5.22 1.1.8-24.1 RC 03/29/12 XtraDB+
5.5.22 1.1.8 RC 03/29/12 InnoDB
5.5.21 1.1.8-24.1 Beta 03/16/12 XtraDB+
5.5.21 1.1.8 Beta 03/16/12 InnoDB
5.5.20 1.1.8-24.1     Alpha 02/26/12 XtraDB+
5.5.20 1.1.8 Alpha 02/26/12 InnoDB

MariaDB 5.3:

MariaDB     Plugin     Status    Date            Comments
5.3.7 1.0.17-13.0 GA 05/04/12 XtraDB+
5.3.7 1.0.17 GA 05/04/12 InnoDB
5.3.6 1.0.17-13.0 GA 04/09/12 XtraDB+
5.3.6 1.0.17 GA 04/09/12 InnoDB
5.3.5 1.0.17-13.0 GA 02/29/12 XtraDB+
5.3.5 1.0.17 GA 02/29/12 InnoDB
5.3.4 1.0.17-13.0 GA 02/15/12 XtraDB+
5.3.4 1.0.17 GA 02/15/12 InnoDB
5.3.3 1.0.17-13.0 GA 12/21/11 XtraDB+
5.3.3 1.0.17 GA 12/21/11 InnoDB
5.3.2 1.0.15-12.7     GA 10/14/11 XtraDB+
5.3.2 1.0.16 GA 10/14/11 InnoDB

MariaDB 5.2:

MariaDB     Plugin     Status    Date            Comments
5.2.12 1.0.17-13.0 GA 04/06/12 XtraDB+
5.2.12 1.0.17 GA 04/06/12 InnoDB
5.2.11 1.0.17-13.0 GA 04/02/12 XtraDB+
5.2.11 1.0.17 GA 04/02/12 InnoDB
5.2.10 1.0.17-13.0 GA 12/05/11 XtraDB+
5.2.10 1.0.17 GA 12/05/11 InnoDB
5.2.9 1.0.15-12.7     GA 09/22/11 XtraDB+
5.2.9 1.0.16 GA 09/22/11 InnoDB

MariaDB 5.1:

MariaDB     Plugin     Status    Date            Comments
5.1.62 1.0.17-13.0 GA 04/06/12 XtraDB+
5.1.62 1.0.17 GA 04/06/12 InnoDB
5.1.61 1.0.17-13.0 GA 04/02/12 XtraDB+
5.1.61 1.0.17 GA 04/02/12 InnoDB
5.1.60 1.0.17-13.0 GA 12/05/11 XtraDB+
5.1.60 1.0.17 GA 12/05/11 InnoDB
5.1.55 1.0.15-12.5     GA 03/01/11 XtraDB+ *1
5.1.55 1.0.15 GA 03/01/11 InnoDB

*1: XtraDB+ differs some from XtraDB as it contains even further enhancements on top of XtraDB. If you use the default (i.e., do not enable the InnoDB plugin in the my.cnf file), then you will be using XtraDB+. If you enable the InnoDB Plugin in the my.cnf file, then you are enabling the standard InnoDB Plugin. Please see the following 2 links for more details on these differences:

http://kb.askmonty.org/en/mariadb-53-asynchronous-io-on-windows-with-innodb (work by Wlad)
http://www.facebook.com/note.php?note_id=10150211546215933 (work by KNielsen)

There are even some more optimizer additions, but no graphs yet to report.

 
Changelogs:

Note: The plugin versions had their own changelogs initially, but eventually the changes were just mixed into the general changelogs for the MySQL Server.

Note: Also, some plugin versions span multiple MySQL versions, so those have multiple links below.

Plugin 1.1:

1.1.0	http://dev.mysql.com/doc/innodb/1.1/en/innodb-changes-11.html
1.1.1	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-5.html
1.1.2	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-6.html
1.1.3	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html
1.1.4	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-8.html
1.1.5	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-9.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-10.html
1.1.6	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-11.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-12.html
1.1.7	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-13.html
1.1.8	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-14.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-15.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-16.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-17.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html
	http://dev.mysql.com/doc/refman/5.5/en/news-5-5-19.html

Plugin 1.0:

1.0.0	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-100.html
1.0.1	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-101.html
1.0.2	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-102.html
1.0.3	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-103.html
1.0.4	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-104.html
1.0.5	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-105.html
1.0.6	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-106.html
1.0.7	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-107.html
1.0.8	http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes-108.html
1.0.9	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-48.html
1.0.10	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-49.html
1.0.11	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-50.html
1.0.12	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html
1.0.13	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-52.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-53.html
1.0.14	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-54.html
1.0.15	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-55.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-56.html
1.0.16	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-57.html
1.0.17	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-58.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-59.html
	http://dev.mysql.com/doc/refman/5.1/en/news-5-1-60.html

Misc. Notes:

5.1.38 is first release of the plugin

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html

And for reference, here is the text from that announcement regarding the InnoDB Plugin:

C.1.1. Changes in MySQL 5.1.38

  As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL
  releases, in addition to the built-in version of InnoDB that
  has been included in previous releases. This version of the
  InnoDB Plugin is 1.0.4 and is considered of Beta quality.

  The InnoDB Plugin offers new features, improved performance
  and scalability, enhanced reliability and new capabilities
  for flexibility and ease of use. Among the features of the
  InnoDB Plugin are "Fast index creation," table and index
  compression, file format management, new INFORMATION_SCHEMA
  tables, capacity tuning, multiple background I/O threads, and
  group commit.

  For information about these features, see the InnoDB Plugin
  Manual at

http://www.innodb.com/products/innodb_plugin/plugin-documentation

  For general information about using InnoDB in MySQL,
  see Section 13.6, "The InnoDB Storage Engine."

  The InnoDB Plugin is included in source and binary
  distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
  ia64), and generic Linux RPM packages.

  To use the InnoDB Plugin, you must disable the built-in
  version of InnoDB that is also included and instruct the
  server to use InnoDB Plugin instead. To accomplish this, use
  the following lines in your my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so

  For the plugin-load option, innodb is the name to associate
  with the plugin and ha_innodb_plugin.so is the name of the
  shared object library that contains the plugin code. The
  extension of .so applies for Unix (and similar) systems. For
  HP-UX on HPPA (11.11) or Windows, the extension should be .sl
  or .dll, respectively, rather than .so.

  If the server has problems finding the plugin when it starts
  up, specify the pathname to the plugin directory. For
  example, if plugins are located in the lib/mysql/plugin
  directory under the MySQL installation directory and you have
  installed MySQL at /usr/local/mysql, use these lines in your
  my.cnf file:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
    plugin_dir=/usr/local/mysql/lib/mysql/plugin

  The previous examples show how to activate the storage engine
  part of InnoDB Plugin, but the plugin also implements several
  InnoDB-related INFORMATION_SCHEMA tables. (For information
  about these tables, see

http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html)

  To enable these tables, include additional name=library
  pairs to the plugin-load option:

    [mysqld]
    ignore-builtin-innodb
    plugin-load=innodb=ha_innodb_plugin.so
      ;innodb_trx=ha_innodb_plugin.so
      ;innodb_locks=ha_innodb_plugin.so
      ;innodb_cmp=ha_innodb_plugin.so
      ;innodb_cmp_reset=ha_innodb_plugin.so
      ;innodb_cmpmem=ha_innodb_plugin.so
      ;innodb_cmpmem_reset=ha_innodb_plugin.so

  The plugin-load option here is formatted on multiple lines
  for display purposes but should be written in my.cnf using a
  single line without spaces in the option value. On Windows,
  substitute .dll for each instance of the .so extension.

  After the server starts up, verify that InnoDB Plugin has
  been loaded by using the SHOW PLUGINS statement. For example,
  if you have loaded the storage engine and the
  INFORMATION_SCHEMA tables, the output should include lines
  similar to these:

    mysql> SHOW PLUGINS;
    +---------------------+--------+--------------------+---------------------...
    | Name                | Status | Type               | Library             ...
    +---------------------+--------+--------------------+---------------------...
    | InnoDB              | ACTIVE | STORAGE ENGINE     | ha_innodb_plugin.so ...
    | INNODB_TRX          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_LOCKS        | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP          | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMP_RESET    | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM       | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
    +---------------------+--------+--------------------+---------------------...

  If you build MySQL from a source distribution, InnoDB Plugin
  is one of the storage engines that is built by default. Build
  MySQL the way you normally do; for example, by using the
  instructions at Section 2.10, "MySQL Installation Using a
  Source Distribution." After the build completes, you should
  find the plugin shared object file under the
  storage/innodb_plugin directory, and make install should
  install it in the plugin directory. Configure MySQL to use
  InnoDB Plugin as described earlier for binary distributions.

Misc Links:

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-introduction.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-changes.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-5-5.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-installation.html

 
 

Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At

Thursday, November 10th, 2011

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65839 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 65838, ACTIVE 26 sec, OS thread id 768 starting index read,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 2
MySQL thread id 4, query id 75 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

Now, we do see a lot about what caused the deadlock above, but we are only seeing *half* of the picture.

Allow me to explain ..

First of all, note transaction #1 has been running for 19 seconds, while transaction #2 for 26 seconds. So, the output is referring to the newer transaction as #1 and the older as #2 (also somewhat counter-intuitive, but good to be aware of).

Now, what we can see clearly is this:

Transaction #1 (“UPDATE parent .. WHERE id=1″) is waiting on a lock from Transaction #2 (“UPDATE parent .. WHERE id=2″).

Thus TX #2 holds a lock (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 2), but is waiting on (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 3 – held by TX #1).

Clearly the 2 updates should not cause a conflict in and of themselves. Thus we know something must have happened earlier in the transaction(s).

For reference, here is how to reproduce it:

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

Then, open 2 connections (T1 and T2 – note order is opposite compared to what is shown in SHOW INNODB STATUS):

T1:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;

<-- T2 Hangs

T1:

UPDATE parent SET age=age+1 WHERE id=2;

<-- Deadlock (T1 completes, T2 is rolled back)

But why does this deadlock? Well, it is due to the foreign key. In fact, this example would not deadlock at all if no foreign key was defined on `parent`.`id`.

So then what exactly is preventing T2 from completing?

Here is what is happening behind the scenes, so-to-speak:

T1#1:

Obtains the following 2 locks:

X lock on `child`  where id=1 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK on parent.id

(Note this S lock means other S locks can be obtained on this row, but not X locks -- which is the crux of this issue).

T1#2:

Obtains the following lock:

X lock on `parent` where id=1 <-- due to the actual "UPDATE parent" statement itself

T2#1:

Obtains the following 2 locks:

X lock on `child`  where id=2 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK (again, this is okay since it is also a S-lock)

T2#2:

Tries to obtain the following, but hangs due to the existing X-lock from T1#2:

X lock on parent where id=1

T1#3:

Tries to obtain the following lock:

X lock on parent where id=2

However, since there are 2 S-locks on this row already (one from T1 and T2), and T1 now wants an X-lock on the same row, then there is a conflict.

Now this would normally just wait for the S-locks to be released, but since T2 is already "hung" waiting on the other lock to be released from T1, we now have the deadlock.

T1 wins the dispute, T2 rolls back thus releasing its locks, and T1 completes.

So all in all, quite a bit is going on there, but you only see about half of this information from the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS output. And had I not posted the SHOW CREATE TABLE status (and prior tx statements), it'd be unclear as to what happened exactly.

Well, so how do you find out exactly what happened when locking problems happen to you?

SHOW INNODB STATUS only tells you so much. Furthermore, once the deadlock occurs, the winner moves on, and the loser is rolled back. *Meaning*, there is no longer any information about these "transactions" in the output as they are in the "past" now.

Therefore, in general, if you're having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits, and so forth), do yourself a favor and capture all of the following outputs at the time, if possible, to give you the best likelihood in tracking down the issue:

  1. SHOW ENGINE INNODB STATUS
    • This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
  2. Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)
    • This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.
  3. Run "mysqladmin debug"
    • Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the InnoDB storage engine. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
  4. SHOW FULL PROCESSLIST
    • This will show all connected threads. Specifically, when it comes to 'hidden' locks, it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
  5. Error log
    • Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
  6. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
  7. SHOW CREATE TABLE outputs for each table involved

 
 
 

IOPS, innodb_io_capacity, and the InnoDB Plugin

Tuesday, May 17th, 2011

In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the bottleneck for IOPS) out there that are not quite at 200 IOPS. Here is a rough estimate based on some external resources which have already documented IOPS for various disk drives (*see links at end for further reference):

15k rpm: 180-210 IOPS
10k rpm: 130-150 IOPS
7200 rpm: 80-100 IOPS
5400 rpm: 50-80 IOPS

So if you are running with a single disk, no raid, and it’s a slower disk (say 5,400 rpm – 10,000 rpm range), then this default value of 200 might be too high for you.

In which case, reverting back to 100 should help (note you cannot reduce this lower than 100). The manual mentions reverting back to 100 to achieve the “old behavior”. However, it neglects to explicitly mention why this might be necessary.

Also, since this option controls the insert buffer batch size, the number of merges per background I/O loop also doubles (5% of innodb_io_capacity, or 5% of 100 in non-plugin).

What this means is that double the writes could be occurring, leaving you with a 1:1 insert to merge ratio, hence much more pages could be written to the secondary indexes, thus hurting performance (in this specific case obvious due to slave lag) and undermining the purpose of the insert buffer in the first place. See bug #48341 for more details on this specific case, as well as this following post from the Facebook Team.

And for those interested in even more details concerning innodb_io_capacity, James and Dimitri both have some excellent comments at the following 2 links, respectively:

http://bugs.mysql.com/bug.php?id=52393
http://dimitrik.free.fr/blog/archives/07-01-2010_07-31-2010.html

*Basis for IOPS general measurements (only to be used as general guidelines):

http://wiki.horde.org/HardwareRequirements
http://en.wikipedia.org/wiki/IOPS#Examples
http://storagearchitect.blogspot.com/2008/09/how-many-iops.html

Ease of Switching to the InnoDB Plugin and the Numerous Benefits

Thursday, July 1st, 2010

In my last post, I discussed how to troubleshoot InnoDB locks using the new InnoDB Plugin’s new Information Schema tables.

However, that got me to thinking about how many MySQL 5.1 users who have still not switched to use the plugin as opposed to the built-in version of InnoDB.

There are many advantages to using the plugin as opposed to the built-in version (aside from just the new I_S tables, and more importantly, numerous performance enhancements), and it’s breeze to set up, so I wanted to provide a quick start guide to using the new InnoDB plugin.

Note that the InnoDB plugin is now GA (as of plugin version 1.0.7) and as of 5.1.38 it is included with the MySQL downloads (it just needs to be enabled).

If you’re running pre-5.1.38, then you may want to consider upgrading to a newer MySQL anyways (5.1.48 is the latest as of this posting). But if not, then you’ll need to download the plugin and extract it.

But if you’re using 5.1.38 or newer, then just take the following steps:

1. Update your my.cnf (or my.ini) by adding the following 2 lines (note the dll should already exist in BASEDIRlibplugin):

ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;
                innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;
                innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;
                innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

2. Restart mysqld

3. Now you can check some various commands to verify it’s enabled.

I’ll post the BEFORE and AFTER, so you can see the differences:

SHOW GLOBAL VARIABLES LIKE ‘%innodb%’ Output:

BEFORE Plugin:

mysql> show global variables like '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 2097152                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 49283072               |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 25165824               |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | .                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.00 sec)

AFTER Plugin:

mysql> show global variables like '%innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | ON                     |
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 2097152                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 49283072               |
| innodb_change_buffering         | inserts                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 25165824               |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | .                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 1.0.9                  |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
51 rows in set (0.00 sec)

In the above, with the plugin, you can see the value of ignore_builtin_innodb is now “ON”.

Also, the innodb_file_format and innodb_file_format_check is “Antelope” for this particular version of the plugin.

There are also a number of new variables, such as innodb_adaptive_flushing, innodb_change_buffering, innodb_file_format, innodb_file_format_check, innodb_io_capacity, innodb_old_blocks_pct, innodb_old_blocks_time, innodb_read_ahead_threshold, innodb_read_io_threads, innodb_replication_delay, innodb_spin_wait_delay, innodb_stats_sample_pages, innodb_stats_sample_pages, innodb_strict_mode, innodb_use_sys_malloc, innodb_version, and innodb_write_io_threads.

The only two missing from the new plugin are innodb_file_io_threads and innodb_use_legacy_cardinality_algorithm.

SELECT * FROM PLUGINS Output:

BEFORE Plugin:

mysql> use information_schema;
Database changed
mysql> select * from plugins;
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE    | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| ARCHIVE     | 3.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                   | GPL            |
| BLACKHOLE   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)           | GPL            |
| CSV         | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                       | GPL            |
| FEDERATED   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                           | GPL            |
| MEMORY      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                | GPL            |
| InnoDB      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Innobase OY                                | Supports transactions, row-level locking, and foreign keys               | GPL            |
| MyISAM      | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Default engine as of MySQL 3.23 with great performance                   | GPL            |
| MRG_MYISAM  | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                    | GPL            |
| partition   | 1.0            | ACTIVE        | STORAGE ENGINE | 50148.0             | NULL           | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                          | GPL            |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
10 rows in set (0.01 sec)

AFTER Plugin:

mysql> use information_schema;
Database changed
mysql> select * from plugins;
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY       | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR                              | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| ARCHIVE             | 3.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Brian Aker, MySQL AB                       | Archive storage engine                                                   | GPL            |
| BLACKHOLE           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | /dev/null storage engine (anything you write to it disappears)           | GPL            |
| CSV                 | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Brian Aker, MySQL AB                       | CSV storage engine                                                       | GPL            |
| FEDERATED           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Patrick Galbraith and Brian Aker, MySQL AB | Federated MySQL storage engine                                           | GPL            |
| MEMORY              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Hash based, stored in memory, useful for temporary tables                | GPL            |
| MyISAM              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Default engine as of MySQL 3.23 with great performance                   | GPL            |
| MRG_MYISAM          | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | MySQL AB                                   | Collection of identical MyISAM tables                                    | GPL            |
| partition           | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | NULL                 | NULL                   | Mikael Ronstrom, MySQL AB                  | Partition Storage Engine Helper                                          | GPL            |
| InnoDB              | 1.0            | ACTIVE        | STORAGE ENGINE     | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Supports transactions, row-level locking, and foreign keys               | GPL            |
| INNODB_TRX          | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB transactions                                                      | GPL            |
| INNODB_LOCKS        | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB conflicting locks                                                 | GPL            |
| INNODB_LOCK_WAITS   | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | InnoDB which lock is blocking which                                      | GPL            |
| INNODB_CMP          | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compression                                    | GPL            |
| INNODB_CMP_RESET    | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compression; reset cumulated counts            | GPL            |
| INNODB_CMPMEM       | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compressed buffer pool                         | GPL            |
| INNODB_CMPMEM_RESET | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50148.0             | ha_innodb_plugin.dll | 1.0                    | Innobase Oy                                | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | GPL            |
+---------------------+----------------+---------------+--------------------+---------------------+----------------------+------------------------+--------------------------------------------+--------------------------------------------------------------------------+----------------+
17 rows in set (0.01 sec)

In the above output, you can see the values for the InnoDB Plugin have changed. For instance, the PLUGIN_LIBRARY now shows ha_innodb_plugin.dll. Additionally, you can see the new INFORMATION SCHEMA plugins listed, which are INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS, INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM, INNODB_CMPMEM_RESET.

Lastly, I’ll just post the output from SHOW ENGINE INNODB STATUS from the new Plugin only. There’s new information provided here as well (see “Pages made young 0, not young 0″ amongst numerous others).

mysql> show engine innodb statusG
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
100622 19:22:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 2 background, 2 flush
srv_master_thread log flush and writes: 1
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 3, OS waits 3; RW-excl spins 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 20E00
Purge done for trx's n:o < 20C18 undo n:o < 0
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, OS thread id 8812
MySQL thread id 1, query id 7 localhost 127.0.0.1 root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
27 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2174, seg size 2176,
0 inserts, 0 merged recs, 0 merges
Hash table size 195193, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 745473833
Log flushed up to   745473833
Last checkpoint at  745473833
0 pending log writes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 50184192; in additional pool allocated 0
Dictionary memory allocated 20888
Buffer pool size   3008
Free buffers       2992
Database pages     16
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 16, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 16, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9800, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

So in conclusion, it's very easy to enable the InnoDB Plugin (add 2 lines to your my.cnf file and restart). So if you're running 5.1, you should definitely consider enablig the InnoDB plugin if you haven't already, and you will gain the following numerous benefits (posted from the manual):

  • Fast index creation: add or drop indexes without copying the data
  • Data compression: shrink tables, to significantly reduce storage and i/o
  • New row format: fully off-page storage of long BLOB, TEXT, and VARCHAR columns
  • File format management: protects upward and downward compatibility
  • INFORMATION_SCHEMA tables: information about compression and locking
  • Performance and scalability enhancements:
    • Faster locking for improved scalability
    • Using operating system memory allocators
    • Controlling InnoDB insert buffering
    • Controlling adaptive hash indexing
    • Changes regarding thread concurrency
    • Changes in read ahead algorithm
    • Multiple background I/O threads
    • Group Commit
    • Controlling master thread I/O rate
    • Controlling flushing rate of dirty pages
    • Using a portable PAUSE to InnoDB spin loop
    • Control Over Spin Lock Polling
    • Changing defaults of parameters
    • Making Buffer Cache Scan Resistant
    • Improvements to Crash Recovery Performance
  • Other changes for flexibility, ease of use and reliability:
    • Dynamic control of system configuration parameters
    • TRUNCATE TABLE reclaims space
    • InnoDB “strict mode”
    • Control over statistics estimation
    • Better error handling when dropping indexes
    • More compact output of SHOW ENGINE INNODB MUTEX
    • More Read Ahead Statistics

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-plugin-introduction-features.html


Click here to give this article a "Thumbs Up" on Planet MySQL !

  


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty