Home > database > RDS最佳实践(五)—Mysql大字段的频繁更新导致binlog暴增

RDS最佳实践(五)—Mysql大字段的频繁更新导致binlog暴增

August 4th, 2014

背景:RDS Mysql采用的binlog 格式默认为ROW,在Mysql 5.6的版本之前,Mysql每次列的修改(update)都需要记录表中所有列的值。这样就存在一个问题,如果表中包含很多的大字段,表的单行长度就会非常长,这样每次update就会导致大量的 binlog空间生成。针对这个问题,在mysql 5.6中进行了改进,复制支持”row image control” ,只记录修改的列而不是行中所有的列,这对一些包含 BLOGs 字段的数据来说可以节省很大的处理能力,因此此项改进不仅节省了磁盘空间,同时也提升了性能:

binlog_row_image Before image After image
minimal All columns where a value was specified, and the autoincrement column if there is one
noblob All columns where a value was specified, and the autoincrement column if there is one, and all non-blob columns
full All columns

测试如下:

mysql> show global variables like ‘%binlog_row_image%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| binlog_row_image | FULL |
+——————+——-+

CREATE TABLE `t_text_56` (
`id` int(11) NOT NULL DEFAULT ‘0’,
`c1` text,
`c2` text,
`c3` text,
`c4` text,
`c5` text,
`gmt_modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into t_text_56 values (3,repeat(‘test_text’,500),repeat(‘test_text’,500),repeat(‘test_text’,500),repeat(‘test_text’,500),repeat(‘test_text’,500),now());

表的单行记录是16K:

mysql> show table status like ‘%tex%’\G;
*************************** 1. row ***************************
Name: t_text_56
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
进行一次update操作:

update t_text_56  set gmt_modified=now() where id=3;

### UPDATE `test`.`t_text_56`

### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=’test_texttest……………..’/* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @3=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @4=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @5=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @6=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @7=’2014-08-04 22:32:54′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2=’test_texttest……………..’/* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @3=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @4=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @5=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @6=’test_texttest……………..’ /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @7=’2014-08-04 22:32:58′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

5.6新增的binlog_row_image参数:minimal

mysql> set global binlog_row_image=minimal;
Query OK, 0 rows affected (0.00 sec)

mysql> update t_text_56 set gmt_modified=now() where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

### UPDATE `test`.`t_text_56`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @7=’2014-08-04 22:33:32′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

binlog_row_image参数:NOLOB

mysql> set global binlog_row_image=noblob;

mysql> alter table t_text_56 add column gmt_create datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update t_text_56 set gmt_create=now() where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

### UPDATE `test`.`t_text_56`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @7=’2014-08-04 22:41:22′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @8=NULL /* DATETIME(0) meta=0 nullable=1 is_null=1 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @7=’2014-08-04 22:41:22′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @8=’2014-08-04 22:43:44′ /* DATETIME(0) meta=0 nullable=1 is_null=0 */

可以看到,mysql 5.6中binlog_row_image:

当设置为minimal时候,binlog只记录了要修改的列的记录;

当设置为nolob的时候,在minimal的基础上binlog中加上非lob字段;

当binlog_row_image默认设置为了full,与5.5,5.1的日志格式保持一致,binlog记录上有的行记录信息;

所以在5.6中binlog_row_image设置为minimal,这样就可以大大减小了binlog的长度,进而减少了空间的使用。

敬请大家期待RDS 5.6的上线。

Categories: database Tags:
Comments are closed.