InnoDB Plugin Has Some Very Cool features

Reducing Administration Costs and Down Time
DBAs are always going to be challenged with growth, scalability and performance as long as databases get bigger and more users access them. Reducing administration and down time are always key issues in our 24 x 7 environments. The new InnoDB Plugin has some very important features that DBAs are going to want to take a serious look at. So I thought I would point out some of the highlights.

The InnoDB Plugin

InnoDB is the primary transaction storage engine through the 5.x releases of MySQL. The InnoDB Plugin allows MySQL DBAs to replace (SKIP_INNODB) the default InnoDB storage engine with an enhanced version of InnoDB (plugin) that offers some important enhanced features and performance. This InnoDB plugin is in the final beta (release candidate) releases.

Key Features
The InnoDB Plugin offers some of these important key features:

• Fast Index Creation - The ability to add and drop secondary indexes without copying table data. Index maintenance is always an issue. The performance benchmarks on this feature are significant.
• Out of Line Data - BLOB, Text and VARCHAR columns can be stored out of line. By default only 768 bytes are stored inline for large columns. However even this 768 bytes is not good for your table storage. If the INNODB_FILE_FORMAT is set to Barracuda (default is Antelope) and the ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED is set, the only a 20 byte pointer is stored in line and the rest of the data is stored out of line. New InnoDB file formats are likely to use additional animal names in alphabetical order.
• Enhanced Metadata - New metadata in INFORMATION_SCHEMA on locking and compression. Enhanced metadata definitely helps with dealing with locking issues and getting better information on compression. The new INFORMATION_SCHEMA metadata tables include: INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM_RESET, INNODB_CMPMEM, INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS. DBAs have been waiting a long time to get detailed locking and transaction information. These new metatables are awesome!
• Data Compression - Data compression support for InnoDB. There is a new attribute called ROW_FORMAT that can be set to compressed. The KEY_BLOCK_SIZE is another new parameter that can compress in 1K, 2K, 4K, 8K or 16K bytes. The LZ77 compression algorithm compresses data and indexes. Compressed data is uncompressed in the InnoDB buffer cach for reading. Compression requires the INNODB_FILE_TABLE option to be set. Example:

CREATE TABLE mycooltable (col1 INT PRIMARY KEY) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

• Truncate Table - TRUNCATE TABLE command reclaims space in .ibd file.
• Optimization Statistics - More control over statistics generation. New features allow better statistics generation to determine the key distributions on indexes. This is a very important feature for the cost based optimizer to make good decisions on indexed columns that have uneven distributions of key values.
• Dynamic Parameters - Dynamic parameter eliminates need to bounce database server when modification is required. It is very important that DBAs minimize downtime so these making the following parameters dynamic is pretty important:

• innodb_file_per_table
• innodb_stats_on_metadata
• innodb_lock_wait_timeout
• innodb_adaptive_hash_index

InnoDB Plugin Installation

The installation of the InnoDB Plugin can pretty straight forward and only takes a few minutes once the InnoDB Plugin is downloaded into the proper location. Of course every version and platform has its own unique idiosyncrasies (or the devil is in the details).