Antelope and barracude MYSQL file format

Wu Bingxi Source: http://www.mysqlsupport.cn/Contact: wubingxi#163.com Reprint Please specify AS/translator and source, and can not be used for commercial purposes, offenders must investigate.

Antelope is the innodb-base file format, Barracude is the file format introduced after Innodb-plugin, and Barracude file format is also supported. The difference between the two is:

File format	Support for row formats	Characteristics
Antelope (innodb-base)	Row_format=compact Row_format=redundant	The difference between the compact and the redumdant is the difference between the first and the remaining content. Compact storage Format The first part is a non-null variable-length list of field lengths The redundant storage format is the first part of a field-length offset list (each field occupies a byte length and its corresponding displacement). In Antelope for variable-length fields, less than 768 bytes are not overflow page storage, and in some cases result set IO is reduced.
Barracuda (Innodb-plugin)	Row_format=dynamic Row_format=compressed	The two are mainly functional differences. In addition, the difference between the variable-length field in the line and the antelope is that only 20 bytes are saved, and the other overflow page is stored. Besides, both of them need to be open innodb_file_per_table=1. (This feature is useful for some optimizations.)

Note:

One thing to note here is that if you want to use compression, you must first use the Innodb_file_format =barracuda format, otherwise it will not work.

Let's look at the difference:

(testing) [Email protected] [(none)]> use WUBX;

Database changed

(testing) [Email protected] [wubx]> CREATE TABLE T1

--(C1 INT PRIMARY KEY)

Row_format=compressed

key_block_size=8;

Query OK, 0 rows affected, 4 warnings (0.01 sec)

Report 4 Warnings Check the error:

(testing) [Email protected] [wubx]> Show warnings;

+ ——— +--+ ——————————————————————— –+

| Level | Code | Message |

+ ——— +--+ ——————————————————————— –+

| Warning | 1478 |        Innodb:key_block_size requires Innodb_file_format > antelope. |

| Warning | 1478 |                                    Innodb:ignoring key_block_size=8. |

| Warning | 1478 | innodb:row_format=compressed requires Innodb_file_format > antelope. |

| Warning | 1478 |                                  Innodb:assuming row_format=compact. |

+ ——— +--+ ——————————————————————— –+

4 rows in Set (0.00 sec)

From the above error can be seen to not support compression. But look at the table structure as follows:

(testing) [Email protected] [wubx]> Show CREATE table T1;

+ ——-+ ——————————————————————————————————————————————— –+

| Table |                                                                                                                                  Create Table |

+ ——-+ ——————————————————————————————————————————————— –+

| T1 | CREATE TABLE t1 (

c1Int (one) is not NULL,

PRIMARY KEY ( c1 )

) Engine=innodb DEFAULT Charset=utf8 row_format=compressed key_block_size=8 |

+ ——-+ ——————————————————————————————————————————————— –+

1 row in Set (0.00 sec)

This is the place to compare pits, so be careful when using compression.

(testing) [Email protected] [Wubx]>create table T2 (c1 int (one) not NULL, primary key (C1));

(testing) [Email protected] [wubx]> INSERT INTO T2 select * from T1;

Query OK, 5417760 rows Affected (37.12 sec)

records:5417760 duplicates:0 warnings:0

To create a table that supports compression:

(testing) [Email protected] [Wubx]>set GLOBAL innodb_file_per_table=1

(testing) [Email protected] [Wubx]>set GLOBAL Innodb_file_format=barracuda;

(testing) [Email protected] [Wubx]>create TABLE T3

(C1 INT PRIMARY KEY)

Row_format=compressed

key_block_size=8;

(testing) [Email protected] [wubx]> INSERT INTO T3 select * from T1;

Query OK, 5417760 rows affected (1 min 10.98 sec)

records:5417760 duplicates:0 warnings:0

Look at the physical size of the table as follows:

-rw-rw--1 mysql mysql 8.4K Jul 5 16:58 t1.frm

-rw-rw--1 mysql mysql 136M Jul 5 19:40 t1.ibd

-rw-rw--1 mysql mysql 8.4K Jul 5 19:43 t2.frm

-rw-rw--1 mysql mysql 136M Jul 5 19:44 t2.ibd

-rw-rw--1 mysql mysql 8.4K Jul 5 19:46 t3.frm

-rw-rw--1 mysql mysql 96M Jul 5 19:47 t3.ibd

Visible T1, T2 are not compressed, T3 is supported compression.

Antelope and barracude MYSQL file format