MySQL InnoDB File Formats
March 2, 2011
Versions of MySQL now come equipped with the powerful and stable InnoDB plugin. The InnoDB plugin comes with a lot of new features, bug fixes and performance gains. We will be covering file formats today, which is a very important part of the InnoDB plugin feature set.
For this article I will be working with the beta MySQL version 5.5.6-rc-log, which contains the 1.1.2 InnoDB plugin. Although I am working with beta in this article please note that MySQL 5.5 is now GA and you can download it here. On a side note, if you would like to see what version of the InnoDB plugin you are using, please use the following command:
To take advantage of the new file formats, there are two server configuration parameters that you will need to enable, innodb_file_format and innodb_file_per_table. There are a few ways you can enable these changes:
If a version of InnoDB supports a particular file format, enabled or not, you can access and even update any table that requires the format for an earlier table format (i.e., you can access a COMPACT (Antelope) table when the Barracuda format is enabled on the server). On the other hand, the creations of new tables using the new formats are limited based on the particular file format enabled at the time of table creation. Furthermore, if a tablespace (defined in this case as a part of the "ib-file set," the .ibd file(s)) contains a table or index that uses a file format that is not supported by your MySQL/InnoDB plugin version, it cannot be accessed at all, not even for reads. For more information about InnoDB file format compatibility please check out the "File Format Compatibility" documentation. Now that we have a bit of background on the InnoDB plugin and Barracuda, we need to go over the specifics of the two new table formats, compressed and dynamic.
The compressed table format does what it says, compresses data in the table. To create a compressed table, you can either specify ROW_FORMAT=COMPRESSED, with the optional [KEY_BLOCK_SIZE] in a create table DDL or use ALTER TABLE. Below are two examples, one without specifying KEY_BLOCK_SIZE and that other with.
With KEY_BLOCK_SIZE (set to 4kb):
Keep in mind that the default compression page size is 8KB. If you do not specify the compression with KEY_BLOCK_SIZE, your page size will be, by default, 8KB. It is also important to note that the compression is the size of the page, which means that the table will be compressed and not individual rows.
For tables created in the
Like the COMPRESSED row format, the DYNAMIC row has the same characteristics of storing values of BLOB, TEXT and VARCHAR columns off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. When using the DYNAMIC row format, InnoDB will not fill B-tree nodes with a large number of data bytes for longer column types like, BLOB, TEXT and VARCHAR. Shorter columns are more likely to remain in the B-tree node, which minimizes the numbers of off-page storage needed for a given row. An example of creating an InnoDB table with the DYNAMIC row format is listed below:
Closing thoughtsUsing the DYNAMIC or COMPRESSED row formats will vary from system to system. If you have a lot of long column types with an extremely demoralized table or data set, you may run into errors. That said, it is always advised to test as much as you can before production implementation.