MySQL InnoDB File Formats

Wednesday Mar 2nd 2011 by Chris Schneider

Versions of MySQL now come equipped with the powerful and stable InnoDB plugin. We will cover file formats in this article, a very important part of the InnoDB plugin feature set.

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:

(root@localhost) [test]> select @@innodb_version; 
| @@innodb_version |
| 1.1.2            |
1 row in set (0.00 sec) 
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:

  • Include innodb_file_per_table=1 and innodb_file_format=barracuda in the [mysqld] section of the my.cnf (or mysql configuration file)
  • Add --innodb_file_per_table=1 and --innodb_file_format=barracuda to the mysqld command line.
  • Issue the statements:
    o	SET GLOBAL innodb_file_format=barracuda; SET GLOBAL innodb_file_per_table=ON; 
The new file format, as you might have noticed specified above, is the "Barracuda" format. The file format supported by older releases of InnoDB is called the "Antelope" file format. The new named file formats are the compressed and dynamic formatted tables, both of which require the use of the "Barracuda" file format and innodb file per table. It is important to note that these new data structures are not compatible with older versions of InnoDB.

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.


(root@localhost) [test]> create table compressed1 ( acol int ) ENGINE=INNODB ROW_FORMAT=COMPRESSED;

(root@localhost) [test]> show create table compressed1\G
*************************** 1. row ***************************
       Table: compressed1
Create Table: CREATE TABLE `compressed1` (
  `acol` int(11) DEFAULT NULL

With KEY_BLOCK_SIZE (set to 4kb):

(root@localhost) [test]> create table compressed2 ( acol int ) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

(root@localhost) [test]> show create table compressed2\G
*************************** 1. row ***************************
       Table: compressed2
Create Table: CREATE TABLE `compressed2` (
  `acol` int(11) DEFAULT NULL

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 COMPRESSED row format, the values of BLOB, TEXT and VARCHAR columns can be stored fully off-page. This off-page storage depends on the length of the BLOB, TEXT or VARCHAR along with the length of the entire row. A clustered index contains a 20-byte pointer per column to the overflow pages for the columns that are stored off page. The page size and total size of the row are the two determining factors on whether any columns are stored off-page. If the row is too long to fit in the page of the clustered index, InnoDB will choose the longest columns for off-page storage. More specifically, InnoDB applies the zlib compression algorithm to the entire data item and along with data written to overflow pages. Compressed overflow pages also contain an uncompressed header and trailer made up of the page checksum, a pointer to the next overflow page and other information. Basically, if your longer BLOB, TEXT and VARCHAR columns are highly compressible you will notice a large gain in disk savings.

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:

(root@localhost) [test]> create table dynamic1 ( acol int ) ENGINE=INNODB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.31 sec)

(root@localhost) [test]> show create table dynamic1\G
*************************** 1. row ***************************
       Table: dynamic1
Create Table: CREATE TABLE `dynamic1` (
  `acol` int(11) DEFAULT NULL
1 row in set (0.01 sec)

Closing thoughts

Using 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.

See all articles by Chris Schneider

Mobile Site | Full Site