Antelope and barracude MYSQL file format

Source: Internet
Author: User
Tags compact

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

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.