I wish to present some compelling reasons to use the InnoDB plugin. The plugin is a drop-in replacement for “normal” InnoDB tables; enabling many new features. It is the outcome of a long termed silence from InnoBase (Oracle), which were thought to be neglecting the InnoDB engine.
I’m going to leave out “performance” for the reason that grander forces have benchmarked and written about it.
Compression
Using the new Barracuda table format, table data can be compressed. Compression depends on the type of data you have in your table, and in KEY_BLOCK_SIZE. I have found tables with lots of textual data to compress well, to about 25% volume (that is, reduction of 75%), and strictly integer-typed tables (like an a-2-b connecting table) to compress poorly.
I have seen an InnoDB 50GB database shrink into some 12GB only. Wow! That meant a server which only had RAID 1 two 72GB disks, and which was dangerously filled up with disk space, could now accommodate the database, a backup, and then some!
Compression does not only occur on disk: when pages are loaded to memory, they are loaded compressed. This means the same innodb_buffer_pool_size you had, now holds a lot more data.
There is no compression for the undo buffer.
INFORMATION_SCHEMA
There’s lot’s of useful data in the new INFORMATION_SCHEMA tables. Very interesting questions can be answered:
- How many transactions are open right now?
- What’s the state of each transaction?
- What queries are being run right now?
- Are any transaction waiting on locks? Which locks? Held by which transactions?
- What kind of locks are currently held? On which tables?
- How much time has been spent on compression/uncompress?
- How many compression operations have occurred?
- How many pages are allocated? How many are free?
Fast index creation
Just to be clear, this is not non-blocking index creation. It’s just fast. What’s fast? Well, slow index creation is what we’re used to: to add an index we ALTER TABLE, thereby creating a new, temporary tablespace, into which the entire table’s data is copied. With fast index creation, table data is left untouched. The new index is created by scanning the primary key and to-be indexed values. Thus, it requires less I/O operations.
Dropping an index is an instantaneous operation.
Yes, compression is the main one; I think fast index creation (and faster index dropping) might be useful in some cases, for example, if you have data partitioned by day and want to create more indexes on old days’ data for efficient searching, but only after the day is over (and the data aren’t as “hot” any more so you don’t really want to do table scans)
How does Barracuda handle fragmentation? same as InnoDB?
@Sheeri,
As far as I understand, indexes on text columns / BLOBs will be less fragmented internally, as long text data is stored off page (instead of allocating up to 768 bytes per value). This means more data per page, which, apart from less total number of pages, also leads to less splitting, and hence less fragmentation.
I’m not sure how the off-page texts/BLOBs are fragmented.
Otherwise I have no actual information about this. No doubt Vadim (Percona) can tell us all about it.
Regards