Performance gain of MySQL 5.1 InnoDB plugin

You know already that InnoDB in MySQL 5.5 has great improvements in performance and scalability. You will have to wait a few months for that, though, because MySQL 5.5 is not GA yet. But if you need some extra performance in MySQL 5.1, you may want to use the Innodb Plugin instead of the built-in one. As of version 5.1.47, the Innodb plugin is of GA quality, and it comes with a good out-of-the-box improvement compared to the built-in engine.

To test my assumptions, I used one of my test Linux servers to perform a sysbench on 5.0.91, 5.1.47 built-in and plugin, and 5.5.4. The MySQL servers were all configured with

innodb_buffer_pool_size=5G

MySQL 4.1.47 was tested both as out-of-the-box, and with the plugin enabled.

ignore_builtin_innodb
# note: the following statements must go all in one line
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=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

default-storage-engine=InnoDBinnodb_file_per_table=1
innodb_file_format=barracudainnodb_strict_mode=1

The test was the same for all the servers. A simple sysbench both read-only and read/write on a 1M records table.

sysbench \
    --test=oltp \
    --oltp-table-size=1000000 \
    --mysql-db=test \
    --mysql-user=$USER \
    --mysql-password=$PASSWD \
    --mysql-host=$HOST \
    --mysql-port=$PORT \
    --max-time=60 \
    --oltp-read-only=$ON_OFF \
    --max-requests=0 \
    --num-threads=8 run

What came out is that, by using the innodb plugin instead of the built-in engine, you get roughly 15% more in read-only, and close to 8% in read/write.


Note that 5.5. enhancements are more impressive in scalability tests with more than 8 cores. In this server, I have just tested a simple scenario.

I did some more testing using "ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=X" in the InnoDB table, where X changed from 4 to 16. But sysbench didn't seem to play well with compression. For low values of KEY_BLOCK_SIZE, you actually get a much worse result than the built-in engine. I have yet to figure out how I would use this compressed InnoDB in practice.