Difference Analysis Between MySQL Antelope and Barracuda, antelopebarracuda

Source: Internet
Author: User

Difference Analysis Between MySQL Antelope and Barracuda, antelopebarracuda

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:
Copy codeThe Code is as follows:
(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:
Copy codeThe Code is as follows:
(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:
Copy codeThe Code is as follows:
(Testing) root @ localhost [wubx]> show create table t1;

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

| Table | Create Table |

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

| T1 | create table t1 (

C1 int (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.
Copy codeThe Code is as follows:
(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:
Copy codeThe Code is as follows:
(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:
Copy codeThe Code 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.




Related Article

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.