UPDATED: explaining the role of innodb_strict_mode and correcting introduction of innodb_file_format
Compressed tables is an example of an InnoDB feature that became available with the Barracuda file format, introduced in the InnoDB plugin. They can bring significant gains in raw performance and scalability: given the data is stored in a compressed format the amount of memory and disk space necessary to hold it and move it around (disk/memory) is lower, thus making them attractive for servers equipped with SSD drives of smaller capacity.
The notion of “file formats” (defined by the variable innodb_file_format) was first introduced when InnoDB was still a plugin. The evolution of InnoDB has lead to the development of new features and some of them required the support of new on-disk data structures. That means those particular features (like compressed tables) will only work with the newer file format. To make things clear and help manage compatibility issues when upgrading and (specially) downgrading MySQL the original file format started being referred to as Antelope.
The default file format in MySQL 5.6 and the latest 5.5 releases is Antelope. Note this can be a bit confusing as the first releases of 5.5 (until 5.5.7) introduced the new file format as being the default one, a decision that was later reversed to assure maximum compatibility in replication configurations comprised of servers running different versions of MySQL. To be sure about which file format is the one set as default in your server you can issue:
mysql> SHOW VARIABLES LIKE 'innodb_file_format';
The important lesson here that motivated me to write this post is that the file format can only be defined for tablespaces – not tables, in general. This is documented in the manual but maybe not entirely clear:
innodb_file_format: The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.
Even if you configure your server with innodb_file_format=Barracuda and recreate the datadir and basic tables with the script mysql_install_db, the common tablespace will always use Antelope. So, to create tables under the new file format it is imperative you use innodb_file_per_table. Although this requirements is documented what might be misleading here is the fact there’s no error being issued if you set the file format to Barracuda and create a new compressed table without having innodb_one_file_per_table set – only a couple of warnings, if you pay close attention. Here’s an example:
mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test.testA (id int) row_format=Compressed;
Query OK, 0 rows affected, 2 warnings (0.96 sec)
If you do choose to check the warnings, you’ll find:
mysql> show WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+---------------------------------------------------------------+
2 rows in set (0.00 sec)
This happens when innodb_strict_mode is turned OFF, as it usually is. If it was turned ON the table creation would fail with the following error:
mysql> create table test.testA (id int) row_format=Compressed;
ERROR 1031 (HY000): Table storage engine for 'testA' doesn't have this option
Now, let’s take a look at what the INFORMATION_SCHEMA tell us about this table:
mysql> SELECT * FROM information_schema.tables WHERE table_schema='test' and table_name='testA'G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: testA
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compact
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2014-01-07 14:21:05
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=COMPRESSED
TABLE_COMMENT:
1 row in set (0.00 sec)
There’s two at-first-look “contradictory” fields here:
- “ROW_FORMAT” says the table is using the Compact format while
- “CREATE_OPTIONS” indicates “row_format=COMPRESSED” has been used when creating the table
The one to consider is ROW_FORMAT: CREATE_OPTION is used to store the options that were used at the moment the table was created and is evoked by the SHOW CREATE TABLE statement to “reconstruct” it:
mysql> show create table test.testA;
*************************** 1. row ***************************
Table: testA
Create Table: CREATE TABLE testA (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
Conclusion
A customer contacted us asking how he could get a list of the tables using the compression format, which we can obtain by interrogating INFORMATION_SCHEMA:
mysql> SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ROW_FORMAT=Compressed’;
To their surprise this statement returned an empty set. We verified that the tables created by them specified ROW_FORMAT=Compressed but as shown in this article this method is not to be trusted – ask the INFORMATION_SCHEMA instead.
‘SHOW TABLE STATUS’ does also show the correct Row_format. The table uses the Barracuda format if the Row_format reported as Compressed or Dynamic. Antelope: Compact or Redundant. But also displays Create_options, which might contain the intention of a compressed table, like Create_options: row_format=COMPRESSED, but should be ignored when it comes to table format.
show table status where name like ‘table_name%’\G
So, when should I bother with creating compressed tables? Should I use that everywhere just for the performance gain or only in certain scenarios?
Cheers!
Hi,
Could you please comment on Query performance if I moved to
innodb_file_format=Barracuda
row_format=Dynamic
from
innodb_file_format=Antelope
row_format=Compact
What would the consequences of Barracuda have on replication with different mysql versions?
You can use the table bellow to see witch tables are using Barracuda or Antelope format.
select * from information_schema.INNODB_SYS_TABLESPACES;
I got those exact same warnings for simply inserting a new column on an existing table. Is this something I should be concerned about?