Differences between Antelope and Barracuda _ MySQL-mysql tutorial

Source: Internet
Author: User
Tags mysql tutorial
Antelope and Barracuda difference author: Wu Bingxi Source: http://www.mysqlsupport.cn/Contact Information: wubingxi # 163.com reprint please indicate/translator and source, and cannot be used for commercial purposes, offenders must investigate.

Antelope is the file format of innodb-base, Barracude is the file format introduced after innodb-plugin, and Barracude also supports Antelope file format. The difference is:

File format Supports row format Features

Antelope

(Innodb-base)

ROW_FORMAT = COMPACT

ROW_FORMAT = REDUNDANT

The difference between Compact and redumdant lies in the difference between the first storage content.

The storage format of compact is a list of non-NULL variable length fields.

The redundant storage format is the header of a field length offset list (each field occupies the byte length and its corresponding displacement ).

In Antelope, if the variable length field is smaller than 768 bytes, the overflow page is not stored. in some cases, the result set IO is reduced.

Barracuda

(Innodb-plugin)

ROW_FORMAT = DYNAMIC

ROW_FORMAT = COMPRESSED

The two are mainly functional differences. In addition, the variable-length field in a row differs from Antelope in 20 bytes, while other overflow pages store data.

In addition, innodb_file_per_table = 1 must be enabled.

(This feature is useful for some optimizations)

Note:

Note that to use compression, innodb_file_format = Barracuda must be used first. Otherwise, it will not work.

Let's take a look at the differences below:

(Testing) root @ localhost [(none)]> use wubx;

Database changed

(Testing) root @ localhost [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)

Four warnings are reported. check whether the error is returned:

(Testing) root @ localhost [wubx]> show warnings;

+ --- + -- + ------------------------ +

| Level | Code | Message |

+ --- + -- + ------------------------ +

| Waring | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format> Antelope. |

| Waring | 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)

We can see from the above error that compression is not supported. But let's take a look at the table structure as follows:

(Testing) root @ localhost [wubx]> show create table t1;

+ --- + ------------------------------------------------ +

| Table | Create Table |

+ --- + ------------------------------------------------ +

| T1 | CREATE TABLEt1(

c1Int (11) 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 a pitfall, so pay attention to the use of compression.

(Testing) root @ localhost [wubx]> create table t2 (c1 int (11) not null, primary key (c1 ));

(Testing) root @ localhost [wubx]> insert into t2 select * from t1;

Query OK, 5417760 rows affected (37.12 sec)

Records: 5417760 Duplicates: 0 Warnings: 0

Create tables that support compression:

(Testing) root @ localhost [wubx]> set global innodb_file_per_table = 1

(Testing) root @ localhost [wubx]> set global innodb_file_format = Barracuda;

(Testing) root @ localhost [wubx]> create table t3

(C1 int primary key)

ROW_FORMAT = COMPRESSED

KEY_BLOCK_SIZE = 8;

(Testing) root @ localhost [wubx]> insert into t3 select * from t1;

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

Records: 5417760 Duplicates: 0 Warnings: 0

The physical size of the table is as follows:

-Rw -- 1 mysql 8.4 K Jul 5 t1.frm

-Rw -- 1 mysql 136 M Jul 5 t1.ibd

-Rw -- 1 mysql 8.4 K Jul 5 t2.frm

-Rw -- 1 mysql 136 M Jul 5 t2.ibd

-Rw -- 1 mysql 8.4 K Jul 5 t3.frm

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

It can be seen that t1 and t2 are not compressed, and t3 supports compression.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.