I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda.

I was looking to play around with some compresses innodb tables. My understanding is this is only available under the Barracuda format.

  1. I see innodb_file_format is dynamic so I can just switch over with out a bounce. Are there any implications of doing this I should be aware of. All I can tell is that means new tables or subsequently altered will be created with that format. Is this all correct?
  2. I was hoping to have to not go through and convert all my tables. Is is kosher to have antelope and barracude tables coexisting in the same tablespace? Even if it works are there any gotcha's to look out for?

From what I've read and gathered from my tests the answers are: Yes. Yes. I'm not sure.


I've been running w/ some Dynamic and some Compressed tables in various instances since this post with out issue. Further I neglected to read http://dev.mysql.com/doc/refman/5.5/en/innodb-file-format-identifying.html at the time.

After you enable a given innodb_file_format, this change applies only to newly created tables rather than existing ones. If you do create a new table, the tablespace containing the table is tagged with the “earliest” or “simplest” file format that is required for the table's features. For example, if you enable file format Barracuda, and create a new table that is not compressed and does not use ROW_FORMAT=DYNAMIC, the new tablespace that contains the table is tagged as using file format Antelope.

So tables will be created as Antelope even if you allow Barracuda. The mixing is unavoidable unless you specify every table as row_format dynamic or a compressed table.

There is no indication you should do a complete dump and reload when introducing your first Barracuda table (such as is recommended when upgrading major versions of mysql)


So I'm answering this question almost 4 years late:

  • InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB).

  • The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed upgrade, you want to move back to a version that can not read a newer format). i.e. there should be no technical reasons why Antelope is better.

  • In MySQL 5.7 the innodb_file_format option was deprecated. Since MySQL and InnoDB are no longer independent, and thus InnoDB can use the MySQL rules of upgrades and what backwards compatibility is required. No confusing setting required!

  • In MySQL 5.7, the default switched to Barracuda/DYNAMIC. Since all currently supported releases of MySQL can read this format, it is safe to move away from Antelope and still offer downgrade.

  • On a MySQL 5.7 server, Antelope tables will be upgraded to Barracuda/DYNAMIC on the next table rebuild (OPTIMIZE TABLE etc). That is unless they were specifically created with ROW_FORMAT=oldrowformat.

  • In MySQL 8.0, the option innodb_file_format is removed.

MySQL 5.7 also introduces the option innodb_default_row_format, which defaults to DYNAMIC. This addresses the point in your update.

Just give a try!!!

mysql> select version();
| version()  |
| 5.5.21-log |
1 row in set (0.00 sec)

mysql> show variables like "%innodb_file%";
| Variable_name            | Value    |
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format = barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%innodb_file%";
| Variable_name            | Value     |
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
| innodb_file_per_table    | ON        |
4 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_file_format_max = barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%innodb_file%";
| Variable_name            | Value     |
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
4 rows in set (0.00 sec)

I had observed a single line logged in Error Log file :

[root@dhcppc0 Desktop]# tail -1 /usr/local/mysql/data/dhcppc0.err
120402 11:26:52 [Info] InnoDB: the file format in the system tablespace is
now set to Barracuda.

After switching to barracuda file format, I could also access my Database
and tables without any error :

mysql> show databases;
| Database           |
| information_schema |
| mysql              |
| opentaps1          |
| performance_schema |
| test               |
5 rows in set (0.00 sec)

mysql> use opentaps1;
Database changed
mysql> select count(*) from product;
| count(*) |
|     3244 |
1 row in set (0.42 sec)

mysql> show engines;
9 rows in set (0.00 sec)

mysql> show engine innodb status\G
Type: InnoDB
Per second averages calculated from the last 18446744073709534037 seconds
srv_master_thread loops: 12 1_second, 12 sleeps, 1 10_second, 2 background,
2 flush
srv_master_thread log flush and writes: 12
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
Trx id counter F01
Purge done for trx's n:o < 0 undo n:o < 0
History list length 0
---TRANSACTION F00, not started
MySQL thread id 1, OS thread handle 0x7f38309f9710, query id 28 localhost
show engine innodb status
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
554 OS file reads, 7 OS file writes, 7 OS fsyncs
-0.01 reads/s, 16384 avg bytes/read, -0.00 writes/s, -0.00 fsyncs/s
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 15 buffer(s)
-0.15 hash searches/s, -0.12 non-hash searches/s
Log sequence number 221536390
Log flushed up to   221536390
Last checkpoint at  221536390
0 pending log writes, 0 pending chkp writes
10 log i/o's done, -0.00 log i/o's/second
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 3476070
Buffer pool size   8192
Free buffers       7635
Database pages     542
Old database pages 220
Modified db pages  0
Pending reads 0
Pages made young 0, not young 0
-0.00 youngs/s, -0.00 non-youngs/s
Pages read 542, created 0, written 1
-0.01 reads/s, -0.00 creates/s, -0.00 writes/s
Buffer pool hit rate 980 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead -0.00/s, evicted without access -0.00/s, Random read ahead
LRU len: 542, unzip_LRU len: 0
I/O sum[0]:cur[238], unzip sum[0]:cur[0]
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2937, id 139879303665424, state: waiting for server
Number of rows inserted 0, updated 0, deleted 0, read 3244
-0.00 inserts/s, -0.00 updates/s, -0.00 deletes/s, -0.18 reads/s
1 row in set (0.00 sec)

If you really want to play with InnoDB using the Barracuda format, you should mysqldump everything to something like /root/MySQLData.sql. That makes the data file format independent.

Use another MySQL instance with a fresh ibdata1 and innodb_file_per_table (optional, my personal preference). Change the file format with ibdata1 empty. Then, reload /root/MySQLData.sql and play with the data.

I have heard slight horror stories about PostgreSQL having to tweek settings to get a 8.2.4 database to work with 9.0.1 binaries. The same story could apply if we tried make both file formats reside in the same system tablespace (ibdata1) and/or .ibd file if we are aware of such settings.

As far as being kosher...

  • Nobody should store meat and dairy in the same refrigerator
  • Nobody should put a bull and an ass under the same yoke (Deuteronomy 22:10)
  • Nobody should store Antelope and Barracuda inside the same ibdata1

UPDATE 2013-07-05 14:26 EDT

I just answered this question in ServerFault : Setting MySQL INNODB Compression KEY_BLOCK_SIZE. This made me look back for any questions I answered in the DBA StackExchange had discussed the Barracuda format and I found this old post of mine. I will place the same information here...

According to the MySQL Documentation on InnoDB Compression for Barracuda

Compression and the InnoDB Buffer Pool

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes. To access the data in a page, InnoDB reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original 16K byte form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.

To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, InnoDB may “evict” from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page may be written to disk, to free space for other data. Thus, at any given time, the buffer pool may contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

InnoDB keeps track of which pages to keep in memory and which to evict using a least-recently-used (LRU) list, so that “hot” or frequently accessed data tends to stay in memory. When compressed tables are accessed, InnoDB uses an adaptive LRU algorithm to achieve an appropriate balance of compressed and uncompressed pages in memory. This adaptive algorithm is sensitive to whether the system is running in an I/O-bound or CPU-bound manner. The goal is to avoid spending too much processing time uncompressing pages when the CPU is busy, and to avoid doing excess I/O when the CPU has spare cycles that can be used for uncompressing compressed pages (that may already be in memory). When the system is I/O-bound, the algorithm prefers to evict the uncompressed copy of a page rather than both copies, to make more room for other disk pages to become memory resident. When the system is CPU-bound, InnoDB prefers to evict both the compressed and uncompressed page, so that more memory can be used for “hot” pages and reducing the need to uncompress data in memory only in compressed form.

Notice that the InnoDB Buffer Pool has to load data pages and index pages read to fulfill queries. When reading a table and its indexes for the first time, the compressed page must be uncompressed to 16K. That means you will have twice as much data content in the buffer pool, the compressed and uncompressed data page.

If this duplication of data content is going on in the Buffer Pool, you need to increase innodb_buffer_pool_size by a small linear factor of the new compression rate. Here is how:


  • You have a DB Server with a 8G Buffer Pool
  • You ran compression with key_block_size=8
    • 8 is 50.00% of 16
    • 50.00% of 8G is 4G
    • raise innodb_buffer_pool_size to 12G (8G + 4G)
  • You ran compression with key_block_size=4
    • 4 is 25.00% of 16
    • 25.00% of 8G is 2G
    • raise innodb_buffer_pool_size to 10G (8G + 2G)
  • You ran compression with key_block_size=2
    • 2 is 12.50% of 16
    • 12.50% of 8G is 1G
    • raise innodb_buffer_pool_size to 9G (8G + 1G)
  • You ran compression with key_block_size=1
    • 1 is 06.25% of 16
    • 06.25% of 8G is 0.5G (512M)
    • raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

MORAL OF THE STORY : The InnoDB Buffer Pool just needs additional breathing room when handling compressed data and index pages.

Your Answer

