RDS最佳實踐(五)—Mysql大欄位的頻繁更新導致binlog暴增

背景：RDS Mysql採用的binlog 格式預設為ROW，在Mysql 5.6的版本之前，Mysql每次列的修改（update）都需要記錄表中所有列的值。這樣就存在一個問題，如果表中包含很多的大欄位，表的單行長度就會非常長，這樣每次update就會導致大量的 binlog空間生成。針對這個問題，在mysql 5.6中進行了改進，複製支援”row image control” ，只記錄修改的列而不是行中所有的列，這對一些包含 BLOGs 欄位的資料來說可以節省很大的處理能力，因此此項改進不僅節省了磁碟空間，同時也提升了效能：

• write event:

測試如下：

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;

### 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的上線。