My branch that updates the innobase plugin in Drizzle to be based on innodb_plugin 1.0.9 has been merged. For the next milestone, we’ll probably have 1.0.11 as well.
How’s the progress getting 1.1 and 1.2 in? Pretty good actually. We’ll have it for either this milestone or the next one.
and merging newer innodb into HailDB? It’s going well too, expect more news “soon”.
While setting up MySQL Enterprise Monitor 2.2 (Merlin) on a system which had been running version 2.1 I thought I’d try and see what difference the change from using normal innodb tables to using the compressed table format available in the innodb plugin.
I’ve been using a separate db backend for merlin because for me it’s easier to manage and also the database backend has been put on a dedicated server. I’ve also been trying the innodb_plugin on another busier server as I had performance problems with the normal 5.1.42 built-in innodb engine which the plugin managed to solve.
So given that I was using a separate db server I upgraded it to 5.1.47, configured the server to use the plugin (1.0.8) rather than to use the built-in innodb engine and then decided to alter the data tables (dc_p_long, dc_p_string and dc_p_double) to use the new innodb compressed table format. These tables are designed for storing a large number of rows of a specific type but there was no harm in trying.
Here are the results by doing the following:
SET GLOBAL innodb_file_format = "Barracuda";
ALTER TABLE dc_p_xxxx ROW_FORMAT=compressed;
Using the older Antelope storage format:
dc_p_string 178 MB
dc_p_double 514 MB
dc_p_long 15.3 GB
Using Barracuda COMPRESSED:
dc_p_string 35 MB
dc_p_double 223 MB
dc_p_long 6.8 GB
The compressed format is using the standard block size. I need to do further tests to see how much difference using the smaller 1 kb, 2 kb or 4 kb blocks will make.
So from the point of view of Merlin only it does seem to make sense to use this format, assuming performance is not significantly affected. After all it means I can store twice the amount of data on disk, and one of the problems I have had in the past is that I could only keep a week’s worth of data because of storage limitations. Note: the 2.1 to 2.2 update will save a lot of space as the string table will drop in size significantly. However gaining an extra 50% by using innodb compression seems worth doing if it comes for free.
That said I have been told that there are still a few issues with this new table format so for anyone looking to use it in production it may be best to wait for 5.1.48 which should remove a few of these edge cases. If you only want to see how much difference the storage usage is then 5.1.47 should be ok. YMMV.
In the meantime I’m going to leave this server running for a while. Merlin does hammer the db quite heavily, so I’ll be able to see if it survives in a few days.
I also have a few servers which currently use MyISAM tables because of the smaller disk footprint compared to innodb. These servers do suffer from some of MyISAM’s weaknesses such as concurrent reading and writing on the same table is not possible, so now it looks like we might have a good reason to try this compressed table format out and with that gain a lot using innodb. Recovery after a crash has always been a problem on this type of server and innodb recovery should be both quicker and less intrusive.
I’m looking forward to further experimentation but so far this new compressed format does look promising. So thanks to the innodb folk who have made this possible.
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.