|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
MySQL 4.1.47 was tested both as out-of-the-box, and with the plugin enabled.
# note: the following statements must go all in one line
The test was the same for all the servers. A simple sysbench both read-only and read/write on a 1M records table.
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.