mysql innodb_file_format_Innodb表压缩过程中遇到的坑(innodb_file_format)

对于越来越多的数据,数据库的容量越来越大,压缩也就越来越常见了。在我的实际工作中进行过多次压缩工作,也遇到多次问题,在此和大家分享一下。

首先,我们先说说怎么使用innodb的压缩.

第一,mysql的版本需要大于5.5第二,设置innodb_file_format=barracuda

第三,create table或者alter talble 增加 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;(默认的key_block_size=16)

其实很简单,根据经验,一般压缩比例可以达到30%-40%

然后,我们说说我在压缩过程中遇到的坑和发现的关联,当然有些比较二。

No1:

问题:使用脚本批量alter操作,只动态修改了实例的innodb_file_format=barracuda,然后alter所有数据库中的表。并没有修改配置文件中的设置。

结果:表中已有数据被压缩,但是在重启之后,由于innodb_file_format参数被重新修改成antelope,导致后续写入的数据没有被压缩(虽然表结构中有row_format=compressed,但是不会起作用),最终表体积仍然很大。

教训:实例和配置文件要同步修改。(这个错误最二,太低级 T_T,不解释了。)

No2:

问题:在innodb_file_format=antelope的情况下,建立压缩表(表结构中带有row_format=compressed),然后在设置innodb_file_format=barracuda。

结果:表结构中的row_format=compressed被忽略,后续写入表的数据并没有被压缩,最终导致表体积大。

教训:先修改innodb_file_format(session和global都需要修改),在create table或者alter table。

但是以上这点有个坑人的地方,在错误的顺序下,表是可以被成功建立了,只是会有warning,但是表结构中会有row_format=compressed,在后期排查的时候非常误导人!

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

| Variable_name | Value |

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

| innodb_file_format | Antelope |

| innodb_file_format_check | ON |

| innodb_file_format_max | Antelope |

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

3 rows in set (0.00 sec)

test> create table test_1 (x int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

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

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

我们可以从warnings中看见,压缩设置被忽略了。但是最坑爹的一点是,如果我们show create table会有如下结果:

test_1 | CREATE TABLE`test_1` (

`x`int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

在这种情况下,我们吸取教训,不能使用show create table看压缩状态,而是应该用show table status;

show table status like 'test_1'\G;*************************** 1. row ***************************Name: test_1

Engine: InnoDB

Version:10Row_format: Compact

Rows:0Avg_row_length:0Data_length:16384Max_data_length:0Index_length:0Data_free:0Auto_increment:NULLCreate_time:2013-09-27 15:59:13Update_time:NULLCheck_time:NULLCollation: utf8_general_ci

Checksum:NULLCreate_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8Comment:1 row in set (0.00 sec)

坑爹啊,不说了。正常应该这个样子

show table status like 'test_2'\G;*************************** 1. row ***************************Name: test_2

Engine: InnoDB

Version:10Row_format: Compressed

Rows:0Avg_row_length:0Data_length:8192Max_data_length:0Index_length:0Data_free:0Auto_increment:NULLCreate_time:2013-09-27 16:09:51Update_time:NULLCheck_time:NULLCollation: utf8_general_ci

Checksum:NULLCreate_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8Comment:1 row in set (0.00 sec)

No3:

发现和innodb_file_format相关的2个参数:

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

| Variable_name | Value |

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

| innodb_file_format | Barracuda |

| innodb_file_format_check | ON |

| innodb_file_format_max | Barracuda |

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

3 rows in set (0.00 sec)

官方的解释可以参考如下的链接:http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format

测试过程中发现,如果是innodb_file_format=barracuda而innodb_file_format_max=antelop,那么在建立压缩表的时候,max会自动变成barracuda。

localhost.test>show global variables like 'innodb_file_format%';+--------------------------+-----------+

| Variable_name | Value |

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

| innodb_file_format | Barracuda |

| innodb_file_format_check | ON |

| innodb_file_format_max | Antelope |

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

3 rows in set (0.00sec)

localhost.test>create table test_4(x int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;Query OK,0 rows affected (0.01sec)

localhost.test>show global variables like 'innodb_file_format%';+--------------------------+-----------+

| Variable_name | Value |

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

| innodb_file_format | Barracuda |

| innodb_file_format_check | ON |

| innodb_file_format_max | Barracuda |

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

3 rows in set (0.00 sec)

如果innodb_file_format_check这参数解释的,决定innodb是否会检查共享表空间中的表格式的tag,如果检查开启,那么当标记的表格式的tag高于innodb可以支撑的表格式,那么innodb会报错,并停止启动。如果支持,那么会将innodb_file_format_max的值改为这个tag的值。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值