MySQL Conf 2008 – InnoDB (Day 2)

Author Michael Tougeron on April 15, 2008

Posted under Events, mysql and tagged with , ,

A new version for InnoDB was announced today. During the InnoDB: Fast, Reliable, Proven Transactional Storage for MySQL session Heikki Tuuri and Ken Jacobs (Heikki’s handler) went over the changes and improvements. InnoDB is now using MySQL 5.1’s plugin API and has some very cool/interesting new features, including:
Fast index creation – Add/drop indexes without copying the data.
Data compression – Significantly reduces storage size & I/O by shrinking the tables.
New row format – Off-page storage of large data elements like BLOB, TEXT, etc.

The new format of InnoDB can be dynamically installed w/o re-linking MySQL, which should be pretty handy for people who cannot easily re-build MySQL. The InnoDB upgrade is fully compatible with existing InnoDB tables and can even be downgraded back to the old version if you need to.

Installation is a breeze and can be done by downloading, extracting the into the MySQL Server plugin directory, and then restarting the mysqld. Be sure to enable “skip_innodb” in the my.cnf so that it doesn’t conflict with the engine already in MySQL. The innodb_file_format is now Barracuda. Finally log into the cli and “INSTALL PLUGIN INNODB SONAME ‘'” and “INSTALL PLUGIN INNODB_LOCKS SONAME ‘ha_innodb.s'”

MySQL/InnoDB 5.1 rebuilds the entire table row by row when creating a new index. The InnoDB Plugin builds just the new index. DROP INDEX for secondary indexes has improved performance as well because it only changes the data dictionary. When changing the PRIMARY KEY or FOREIGN KEYs it still needs to rebuild the table; it’s faster then 5.1.

Any C/U/D style queries will wait on the index creation, but SELECTs will still go through in shared mode. Newly created indexes lack the historical version info for row (for only that index). This can affect consistent read SELECTs of older transactions.

According the benchmarks that Peter Zaitsev performed, creating a new index on a 3GB table it takes 88 minutes with version 5.1 and an amazing 8 minutes with the new InnoDB plugin.

With the new table compression you can choose the compressed page size on a per table basis. In order to use this functionality, you need to have innodb_file_per_table = 1 and innodb_file_format = barracuda. The patterns in the data will determine your compression rate. According to Heikki, it can often be >50%. Both the tables and the indexes are compressed using zlib. It now trys to do updates and inserted in small sets so that it does not need to recompress when the data is updated. Deletions do not require page recreation.If the page is frequently accessed, it will keep it uncompressed. It is important to monitoring the compress vs uncompressed rates in the INFORMATION_SCHEMA. As few as 1% unsuccessful compression operations wastes your cpu time.

The best time to use compression is when you are I/O bound. If you are cpu-bound, then it is not a good idea to use compression as it will spend even more cpu time compressing and uncompressing. As a test, you can gzip your .ibd data file and see what level of compression you get. If it does not compress to significantly less than 50% of the original size, it probably won’t be worth doing.

The new ROW_FORMAT = DYNAMIC also requires the Baricuda file format. It is meant for situations when you have a table with a lot of large data elements like BLOB or TEXT. Now InnoDB can store the data off-page.

With the INFORMATION_SCHEMA updates, you can now find out much more information about the status of InnoDB and the individual tables. You can even now easily find the blocking queries and which process id is the owner. This should be a great help to DBAs who need to manage large sets of InnoDB databases.

Posted under Events, mysql

This post was written by Michael Tougeron on April 15, 2008

Tags: , ,

Leave a Comment

You must be logged in to post a comment.

More Blog Posts