这篇文章介绍了 MySQL 5.1.47 里使用内建的 InnoDB 与 InnoDB 的插件之间的性
能差别。两者性能可谓天翻地覆。我一直以为 built-in 的话,应该会有更好的优
化,而且内部通信之间的延时也会降低,从而在性能上有点优势。但是恰恰相反的
是在这个测试。是不是这个插件从 MySQL 5.5 中把 InnoDB 的特性 backport 出
来的呢?
刚好 Fedora 12/13 的源里都是 MySQL 5.1.47 这个版本,大家可以留意一下。
原
文:http://datacharmer.blogspot.com/2010/06/performance-gain-of-mysql-51-innodb.html
Sent to you by liangsuilong via Google Reader: Performance gain of
MySQL 5.1 InnoDB plugin via Planet MySQL by Giuseppe Maxia on 6/6/10
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.
PlanetMySQL Voting: Vote UP / Vote DOWN
Things you can do from here:
- Subscribe to Planet MySQL using Google Reader
- Get started using Google Reader to easily keep up with all your
favorite sites
Show replies by date