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

…

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.

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, …

MySQL 5.6 vs MySQL 5.5 & the Star Schema Benchmark

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload.  I wanted to test a set of queries which, unlike sysbench, utilize joins.  I also wanted an easily reproducible set of data which is more rich than the simple sysbench table.  The Star Schema Benchmark (SSB) seems ideal for this.

I wasn’t going to focus on the performance of individual queries in this post, but instead intended to focus only on the overall response time for answering all of the queries in the benchmark. I got some strange results, however, which showed MySQL 5.6.10 to be much slower than MySQL 5.5.30 even with only a single connection. I felt these results warranted deeper investigation, so I did some research and …

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; …

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
-- …

The performance of flush_list flushing of InnoDB decides the basic performance for modifying workloads. So, it is important to optimize the flush behavior. In this post we’ll consider how to optimize the neighbor-flushing behavior.

Factor 1: Characteristics of storage

Depending on the characteristics of your storage’s throughput for write IO, you can term your storage as either “write amount bound” or “write times bound”. The minimum unit of the InnoDB datafile is page size (16KB or less). And InnoDB attempts to combines them in a single IO up to 1 extent (1MB) maximum, if they are contiguous.

<one HDD>:  Almost “write times bound”. Because head-seek time is the most effective factor for access time of HDD. And around 1MB size can be treated by the 1 head-seek.

…

In MySQL labs release April 2012 we have reworked the flushing heuristics in InnoDB. In this post I’ll give an overview of what we have changed and the various configuration variables we have introduced to fine tune the flushing algorithm. If you are interested in finding out how our new flushing algorithm fares in benchmarks you can get these details in Dimitri’s well-explained blog here.

Flushing means writing dirty pages to disk. I have explained in some detail about adaptive_flushing and types of flushing in my previous notes. Please go through these notes if you want to …

After introducing InnoDB persistent statistics in MySQL 5.6, in this April Labs release we have dressed it up in a nice UI and refactored the internals a bit to make the code more elegant and straight-forward.

The persistent stats are now controlled globally and can also be overridden at table level, should any table require a different behavior.

Global

The server global flag –innodb-stats-persistent (boolean) now controls whether all InnoDB tables use persistent statistics or not. Keep in mind that if a table is using persistent stats then its statistics will not be updated automatically and you are responsible for running ANALYZE TABLE periodically, whenever you think the table contents has changed too much. Thus the default for –innodb-stats-persistent is currently set to OFF.

…

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 – …