MySQL:MySQL工具以及5.7mysqlbinlog|mysql速度極慢問題

隨便記錄點 5.7.22代碼

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

交互式
就是我們平時用的交互式命令方式
非交互式（batch mode）
主要用于解析binlog和批量導入sql低矮，每次都會讀取1行數(shù)據(jù)到buffer
如果是binlog，那么每次大約讀取的原始數(shù)據(jù)為76字節(jié)型酥，及mysqlbinlog解析出來的一行

AAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAA
AAAAAP4AAAAAAAAAAP4KAAAAAAAAAP4UAAAAAAAAAP4eAAAAAAAAAP4oAAAAAAAAAP4yAAAAAAAA
AP48AAAAAAAAAP5GAAAAAAAAAP5QAAAAAAAAAP5aAAAAAAAAAP4KAAAAAAAAAP4AAAAAAAAAAP4A
AAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4AAAAA
AAAAAP4AAAAAAAAAAP4AAAAAAAAAAP4KAAAAAAAAAP4UAAAAAAAAAP4eAAAAAAAAAP4oAAAAAAAA
AP4yAAAAAAAAAP48AAAAAAAAAP5GAAAAAAAAAP5QAAAAAAAAAP5aAAAAAAAAAP4KAAAAAAAAAP4A

類似這里就有6行备籽。如果是大事務，每次解析76字節(jié)嚣鄙，add_line 函數(shù)磷箕。然后等待大事務所有行解析完成后進行com_do函數(shù)進行執(zhí)行命令。
如果是sql語句蒿柳，那么就是一條sql執(zhí)行一次沒有什么問題饶套。

最近遇到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函數(shù)下
MAX_BATCH_BUFFER_SIZE 已經(jīng)定義好為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目錄下蛤克，如果再次遇到這種問題，可以使用修改過的進行執(zhí)行夷蚊。

最后編輯于：2021.01.22 10:35:48

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者