Sunday, June 06, 2010

Performance gain of MySQL 5.1 InnoDB plugin

plugin performanceYou 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.

6 comments:

Mark Robson said...

Was this on Linux, and did you have native async IO turned on in the Innodb 1.1 ?

Giuseppe Maxia said...

@Mark
It was on Linux.
I did not modify anything in the server except the innodb_buffer_pool_size.

Gerry Narvaja said...

On 8 core servers w/ 16G memory and InnoDB buffer pool set to 80% of available memory, we clocked double the performance with the plugin: http://mmatemate.blogspot.com/2010/05/mysql-5146-with-innodb-plugin-kicks.html. A couple of our development teams tested with their application specific benchmarks and got the similar results.

My $.02
G

Ronald Bradford said...

You have these jumbled.

--mysql-user=$PASSWD \
--mysql-password=$USER \

Giuseppe Maxia said...

@Ronald,
Thanks. Fixed.

Giuseppe Maxia said...

@Gerry,
Very interesting. As everyone knows, sysbench is a very simple test, and more elaborate usage can achieve substantially bigger gain.