揪出MySQL推迟上千秒的首恶51CTO博客 - 亚美娱乐

揪出MySQL推迟上千秒的首恶51CTO博客

2019年03月05日14时10分33秒 | 作者: 语海 | 标签: 格局,运用,句子 | 浏览: 339

binlog_format

PropertyValue
Command-Line Formatbinlog-format=format
System Variablebinlog_format
ScopeGlobal, Session
DynamicYes
Type (>= 5.5.31-ndb-7.2.13)enumeration
Type (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)enumeration
Typeenumeration
Default (>= 5.5.31-ndb-7.2.13)MIXED
Default (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)STATEMENT
DefaultSTATEMENT
Valid Values (>= 5.5.31-ndb-7.2.13)

ROW

STATEMENT

MIXED

Valid Values (>= 5.5.15-ndb-7.2.1, <= 5.5.30-ndb-7.2.12)

ROW

STATEMENT

MIXED

Valid Values

ROW

STATEMENT

MIXED

众所周知,binlog_format是设置binlog格局的参数,咱们可以装备为STATEMENT、MIXED、ROW三种格局,可以动态调理。三种格局各有有缺。咱们的线上出产库一致装备的是MIXED格局。MIXED格局会在STATEMENT格局和ROW格局中依据场景不同来运用不同的格局进行切换。

mysql> show global variables like 'binlog_format';
+-+-+
| Variable_name | Value |
+-+-+
| binlog_format | MIXED |
+-+-+
1 row in set (0.08 sec)


Part3:常识储藏

关于MIXED格局来说,在如下状况的时分,binlog会主动转为ROW格局记载

1.NDB引擎

2.SQL句子里包括了UUID()函数。

3.自增加字段被更新了。

4.包括了insert delayed句子。

5.运用了用户界说函数(UDF)。

6.运用了暂时表。

7.?还有一种状况会导致mixed格局转换为ROW,本文会加以复现。


实战


Part1:监控

  • 单纯的业务量激增,QPS增加引起;

  • binlog转为了ROW格局导致存储内容激增引起。


  • 咱们运用pt东西pt-query-digest或许指令行,都可以分分出binlog做了哪些操作。运用pt-query-digest的话可以结合mysqlbinlog指令,对日志进行剖析。


    Part3:rootcase

    delete from tablename where xxxx limit 100;

    这种语法会将MIXED格局的binlog,转为ROW格局记载,而笔者事例中的这张表包括TEXT大字段,每次delete都会把整个TEXT大字段带入binlog,进而导致binlog激增,从库追不上主库发生推迟的状况。


    Part4:处理办法

    根本原因找到后,处理起来就称心如意了,找到相关开发,去掉delete from table where xxx limit 这种用法,就可以防止row格局的记载。


    Warning:警告其实,delete/update limit、insert .....select limit这种用法是风险的,很简单发生问题。真的要运用这种这种办法的话,也需求结合order by句子来确保limit的有效性。

    遇到此类句子时:

    当运用STATEMENT形式时,会宣布一个正告,阐明句子关于根据句子的仿制是不安全的。

    当运用STATEMENT形式时,即便它们也有一个ORDER BY子句(因此是确定性的),也会为包括LIMIT的DML句子宣布正告。 这是一个已知的问题。 (BUG#42851)

    当运用MIXED形式时,句子运用row的形式仿制。


    Part5:官方文档

    When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
    When a DML statement updates an NDBCLUSTER table.
    When a function contains UUID().
    When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
    When any INSERT DELAYED is executed.
    When a call to a UDF is involved.
    If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
    This is true whether or not any temporary tables are actually logged.
    Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
    When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
    When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)
    When a statement refers to one or more system variables. (Bug #31168)

    可以看出,在官方文档中,何时MIXED格局会转换为ROW格局中,并未说到limit句子会将MIXED格局转换为ROW,国内不少书本和博客上也未有提及,本文记载这个事例,期望对遇到这个问题和未来或许遇到这个问题的读者可以节约处理时刻,赶快定位到本源。


    官方文档关于MIXED格局在运用limit语法时转换为ROW格局记载在其他章节,是如下描绘的:

    Statement-based replication of LIMIT clauses in DELETEUPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered:

    • When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued.

      When using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851)

    • When using MIXED mode, the statement is now automatically replicated using row-based mode.



    ——总结——

    经过这个事例,咱们可以了解到什么状况下binlog_format会由MIXED格局转为ROW格局,以及常见的推迟原因和处理办法。因为笔者的水平有限,编写时刻也很匆促,文中难免会呈现一些过错或许不精确的当地,不当之处恳请读者批评指正。喜爱笔者的文章,右上角点一波重视,谢谢!


    33.png

    版权声明
    本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

    猜您喜欢的文章