mysql 5.7 binlog 压缩_MySQL:MySQL工具以及5.7mysqlbinlog|mysql速度极慢问题

---

随便记录点 5.7.22代码

MySQL工具主要分为 交互式和非交互式

- 交互式

就是我们平时用的交互式命令方式

- 非交互式(batch mode)

主要用于解析binlog和批量导入sql，每次都会读取1行数据到buffer

如果是binlog，那么每次大约读取的原始数据为76字节，及mysqlbinlog解析出来的一行

```

AAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAA

AAAAAP4AAAAAAAAAAP4KAAAAAAAAAP4UAAAAAAAAAP4eAAAAAAAAAP4oAAAAAAAAAP4yAAAAAAAA

AP48AAAAAAAAAP5GAAAAAAAAAP5QAAAAAAAAAP5aAAAAAAAAAP4KAAAAAAAAAP4AAAAAAAAAAP4A

AAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAA

AAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4KAAAAAAAAAP4UAAAAAAAAAP4eAAAAAAAAAP4oAAAAAAAA

AP4yAAAAAAAAAP48AAAAAAAAAP5GAAAAAAAAAP5QAAAAAAAAAP5aAAAAAAAAAP4KAAAAAAAAAP4A

```

类似这里就有6行。如果是大事务，每次解析76字节，add_line 函数。然后等待大事务所有行解析完成后进行com_do函数进行执行命令。

如果是sql语句，那么就是一条sql执行一次没有什么问题。

最近遇到mysqlbinlog|mysql大事务特别慢的情况，因此提交了如下问题：

https://bugs.mysql.com/bug.php?id=102278

8.0.13修复了，5.7.33最新版本依旧存在这个问题。

```

Hi:

we use mysqlbinlog|mysql to parse binlog and recover binlog,when have large trasaction,mysql client tool

is very slow at add_line function.

20M trasaction event mysql add line use 1 hour!! cpu is 100% use in sy% and pstack mysql client stack like:

__memmove_sse3

my_realloc

String::mem_realloc

add_line

read_and_execute

main

when batch mode,mysql every time add 76 bytes event line to buffer,buffer init 520 bytes,when 20M event load in

buffer then call com_do funcation to execute command。

Breakpoint 9, add_line (buffer=..., line=0xa603e0 "/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;", line_length=45, in_string=0x7fffffffe287 "", ml_comment=0x7fffffffe286, truncated=false)

at /opt/percona-server-locks-detail-5.7.22/client/mysql.cc:2533

2533 bool need_space= 0;

(gdb) p buffer.m_length

$20 = 0

(gdb) p buffer.m_alloced_length

$21 = 520

when buffer is smaller, every time allocate 4K mem:

if (buffer.length() + length >= buffer.alloced_length())

buffer.mem_realloc(buffer.length()+length+IO_SIZE);

this step rise frequently mem allocate.and cpu sy% is very high!!

when mysql use batch mode can we use large init buffer or give our a parameter to control this.

when i search bug i find Bug #85155 is like this,is this fix at mysql 8.0.13,what 5.7 version fix this?

thanks!!

```

我进行了一下修改，如下：

```

mysql.cc main函数下

MAX_BATCH_BUFFER_SIZE 已经定义好为1G

glob_buffer.mem_realloc(MAX_BATCH_BUFFER_SIZE);

[root@mgr3 client]# /opt/my_mysql/bin/mysqlbinlog /opt/bin/log_bin.000002 |/opt/my_mysql/bin/mysql

速度极快了

[root@mgr3 client]# /opt/my_mysql/bin/mysqlbinlog /opt/bin/log_bin.000002 |/opt/my_mysql/bin/mysqlbak

CPU 99%

位于192.168.1.63 上的/opt/my_mysql/bin/mysqlbak目录下，如果再次遇到这种问题，可以使用修改过的进行执行。

```

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2757322/，如需转载，请注明出处，否则将追究法律责任。