Shulou(Shulou.com)06/01 Report--

Due to too many Blob fields in the database, the processing method of binlog from the database can not be carried out normally.

When binlog_format is in row format, the record is not a simple sql, but the actual changed lines. Some large DML operations will lead to a great increase in the amount of binlog and consumption of additional IO and network resources.

It can be solved by setting binlog_row_p_w_picpath=minimal.

Test:

The default for binlog_row_p_w_picpath is full

Update the user table

Go to binlog to view the update record, and the binlog log records all affected lines.

Now binlog_row_p_w_picpath=minimal

Perform the same update operation on the rows in the table and then take a look at the binlog record

Conclusion: it can be found that binlog only records the row of influence when binlog_row_p_w_picpath=minimal, which effectively reduces the amount of binlog log.

Database version: 5.6.*

1.row log p _ w_picpath type

The parameter binlog_row_p_w_picpath controls this p_w_picpath type, which defaults to FULL (log all columns), that is, record before&after p_w_picpaths.

There are two other parameters. Minimal and noblob,minimal indicate that only the changed values of after are recorded, and if there is a primary key or a non-empty unique index, only this field is used as a where condition; noblob is the same as full, but the blob and text columns are not recorded.

2.binlog log

There is nothing to say about insert, but let's focus on update and delete operations.

In the case of binlog_row_p_w_picpath=full, the binlog generated by all tables of update and delete (including non-null unique index with primary key, unique index, no index) is the same, as in the case of binlog:

-- build a table statement

CREATE TABLE `pk_ test` (

`id` bigint (20) NOT NULL

`username` varchar (30) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into pk_test values (1 dint 2)

Insert into pk_test values (2Jing 2)

Commit

Show master statusG;-- records binlog files and pos

Deletefrom pk_test where id = 1

Update pk_test set username='3'

Commit

Mysqlbinlog-- no-defaults-v-- start-position=637945822/mysqllog/3307/binlog/mysql-bin.000001 | more

# DELETE FROM `baofeng`.`pk _ test`

# WHERE

# @ 1room1

# @ 2thanks 2'

.

# UPDATE `baofeng`.`pk _ test`

# WHERE

# @ 1room2

# @ 2thanks 2'

# SET

# @ 1room2

# @ 2percent 3'

From the above, we can see that under the binlog_row_p_w_picpath with the default of FULL, no matter whether the table has a primary key or a unique index, all the fields of the table are taken as conditions, and update updates all fields.

In case of binlog_row_p_w_picpath=minimal:

-- build a table statement

CREATE TABLE `ui_ test` (

`id` bigint (20) NOT NULL

`username` varchar (30) NOT NULL

UNIQUE (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `ui_test_ null` (

`id`bigint (20)

`username` varchar (30) NOT NULL

UNIQUE key (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `null_ test` (

`id`bigint (20)

`username` varchar (30) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into pk_test values (1 dint 2)

Insert into ui_test values (1 dint 2)

Insert into ui_test_null values (1 dint 2)

Insert into null_test values (1 dint 2)

Commit

Update pk_test set username='4'

Deletefrom pk_test

Deletefrom ui_test

Deletefrom ui_test_null

Update null_test set username='4'

Deletefrom null_test

# UPDATE `baofeng`.`pk _ test`

# WHERE

# @ 1room1

# SET

# @ 2room4'

....

# DELETE FROM `baofeng`.`pk _ test`

# WHERE

# @ 1room1

.

# DELETE FROM `baofeng`.`ui _ test`

# WHERE

# @ 1room1

.

# DELETE FROM `baofeng`.`ui _ test_ null`

# WHERE

# @ 1room1

# @ 2thanks 2'

.

# UPDATE `baofeng`.`null _ test`

# WHERE

# @ 1room1

# @ 2thanks 2'

# SET

# @ 2room4'

.

# DELETE FROM `baofeng`.`null _ test`

# WHERE

# @ 1room1

# @ 2thanks 2'

As you can see from the above example, in the case of binlog_row_p_w_picpath=minimal, the where condition has only a primary key or a unique index that is not empty, and only the fields that have been changed are updated.

3. Summary:

In the above test, we can see that if the minimal format is used, the beforevalue of the primary key and non-empty unique index table will be reduced, as well as the unchanged value of the after of all tables update.

In terms of efficiency, it reduces the network transmission and speeds up the efficiency of update.

Reference:

Https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_p_w_picpath

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.