I’ve heard about the innodb_plugin but not had time to put it to the test.
Recently though due to some problems I’ve been having with the MySQL Enterprise Monitor (Merlin) I’ve had to try a few changes and had the opportunity to try out the innodb plugin.
I have been using Merlin for some time and like it a lot. It is not perfect but does a good job for me. However, since upgrading to version 2.1 I have been having some database load problems. I long ago split the merlin server into a front- and back-end server with the backend running a standard MySQL 5.1 Advanced package. That has been working fine.
I have been monitoring more and more mysqld servers and recently the database backend could not cope. Basically the writes of data collected from the agents and the deletes of old date (purging) caused too much I/O and that is on a box with 6 disks in RAID-10 with a battery backed write-cache.
So I upgraded the db server to a new box with lots of memory and a 300 GB Fusion IO card. I expected all problems to go away. Well not quite. In spite of the solid state drive which was not I/O bound, and the CPU which was not CPU bound, mysqld could not keep up with the load. This was running the MySQL 5.1.44 Advanced rpm. Looking more deeply it seems that mysqld itself was the bottleneck and there was too much contention on the PK by the different INSERTing and DELETing threads.
The Merlin team suggested trying the innodb_plugin (1.0.6) and all of a sudden the bottleneck seems to have gone away.
This is the iostat output taken before switching to the innodb plugin:
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util fioa 0.00 0.00 4.21 4049.50 33.67 32337.07 16.83 16168.54 7.99 192.42 6.31 0.25 100.22 fioa 0.00 0.00 9.78 4379.44 153.29 34942.12 76.65 17471.06 8.00 0.00 6.17 0.00 0.00 fioa 0.00 0.00 20.80 4455.40 408.00 35552.60 204.00 17776.30 8.03 0.00 5.96 0.00 0.00 fioa 0.00 0.00 21.80 4685.00 267.20 37391.20 133.60 18695.60 8.00 24.00 5.87 0.21 100.02 fioa 0.00 0.00 23.60 5200.40 320.00 41490.40 160.00 20745.20 8.00 0.00 6.04 0.00 0.00 fioa 0.00 0.00 15.17 5020.36 202.79 40055.09 101.40 20027.54 7.99 0.00 5.65 0.00 0.00
This is the iostat output taken after switching to the innodb plugin:
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util fioa 0.00 0.00 4.40 2344.80 160.00 201520.00 85.85 0.00 0.41 0.00 0.00 fioa 0.00 0.00 2.00 2188.20 64.00 193500.80 88.38 0.00 0.41 0.00 0.00 fioa 0.00 0.00 1.80 2113.60 57.60 200889.20 94.99 0.00 0.44 0.00 0.00 fioa 0.00 0.00 0.40 1961.60 12.80 194291.20 99.03 0.00 0.43 0.00 0.00 fioa 0.00 0.00 0.00 2118.00 0.00 202496.40 95.61 0.00 0.47 0.00 0.00 fioa 0.00 0.00 0.00 2030.00 0.00 191482.40 94.33 0.00 0.46 0.00 0.00 fioa 0.00 0.00 0.00 2152.60 0.00 208485.20 96.85 0.00 0.44 0.00 0.00 fioa 0.00 0.00 0.00 1936.20 0.00 178732.40 92.31 0.00 0.42 0.00 0.00 fioa 0.00 0.00 4.79 1249.70 153.29 115475.45 92.17 0.00 0.40 0.00 0.00
Note: the first set of figures was taken using CentOS 4, and the second using CentOS 5. The IO statistics aren’t exactly identical and on CentOS 5 for some reason the driver appears not to be providing all the stats. However the wsec/s value clearly shows a significant performance improvement and the original problem mysqld was having of not being able to purge as fast as it was inserting data seems to have been solved. At least initial signs seem to indicate this. The only configuration change made to the server was the following:
ignore_builtin_innodb 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 innodb_adaptive_flushing = 1 innodb_io_capacity = 1000
Conclusion, if you are having performance problems on your MySQL server and perhaps the hardware is not the bottleneck then try using the plugin. It may make a big difference.
Also a big thanks to the Merlin team for helping me out with this problem and getting things up and running.
PlanetMySQL Voting: Vote UP / Vote DOWN