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

Find out the background of the culprit who delayed MySQL for thousands of seconds.

Part1: write at the front

The delay alarm of MySQL must be familiar to all of you. There are many reasons for slave database delay caused by MySQL. In terms of hardware, it may be due to the bottleneck of network card, disk and memory. From the database level, it may be caused by inefficient SQL or mass writes. The case of this article will analyze a delay problem caused by binlog format. After reading this article, when you encounter this kind of alarm, I believe you can instantly locate the problem!

Part2: analysis of key parameters

Binlog_format

PropertyValueCommand-Line Format--binlog-format=formatSystem Variablebinlog_formatScopeGlobal, SessionDynamicYesType (> = 5.5.31-ndb-7.2.13) enumerationType (> = 5.5.15-ndb-7.2.1, = 5.5.31-ndb-7.2.13) MIXEDDefault (> = 5.5.15-ndb-7.2.1, = 5.5.31-ndb-7.2.13) ROW

STATEMENT

MIXED

Valid Values (> = 5.5.15-ndb-7.2.1, show global variables like 'binlog_format';+-+-+ | Variable_name | Value | +-+ | binlog_format | MIXED | +-+-+ 1 row in set (0.08 sec)

Part3: knowledge Reserve

For MIXED format, binlog is automatically converted to ROW format when

1.NDB engine

The UUID () function is included in the 2.SQL statement.

3. The self-growth field has been updated.

4. Contains the insert delayed statement.

5. User-defined functions (UDF) are used.

6. A temporary table is used.

7.? There is also a situation that causes the mixed format to be converted to ROW, which is repeated in this article.

Actual combat

Part1: monitorin

We can see that at 2: 00 in the morning, the delay from the library increased sharply, while the machine load and network card from the library did not reach the bottleneck.

Part2: delay cause analysis

We can see that from 2: 06 onwards, binlog refresh is very fast, basically dozens of seconds can be filled with a 1.1GB binlog file. In this way, it is basically certain that it is caused by the excessive amount of writing.

There are two other situations when the amount of writing is too large:

The simple surge in business volume is caused by the growth of QPS

The conversion of binlog to ROW format led to a surge in stored content.

Using the pt tool pt-query-digest or the command line, we can analyze what binlog has done. Using pt-query-digest, you can combine the mysqlbinlog command to analyze the log.

Part3:rootcase

Delete from tablename where xxxx limit 100

This grammar will convert the binlog in MIXED format to ROW format, and the table in the author's case contains large TEXT fields. Each delete will bring the entire large TEXT field into binlog, resulting in a surge in binlog and a delay in catching up with the main database.

Part4: the solution

Once the root cause is found, it is easy to solve it. By finding the relevant developers and removing the use of delete from table where xxx limit, records in row format can be avoided.

Warning: warning: in fact, the use of delete/update limit, insert. Select limit is dangerous and prone to problems. If you really want to use this method, you also need to combine order by statements to ensure the effectiveness of limit.

When you encounter such a statement:

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

When STATEMENT mode is used, warnings are issued for DML statements that contain LIMIT, even if they also have an ORDER BY clause (and therefore deterministic). This is a known problem. (BUG#42851)

When using MIXED mode, statements are copied using row's mode.

Part5: official documentation

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)

As you can see, in the official document, when the MIXED format will be converted to ROW format, there is no mention that the sentence will convert MIXED format to ROW, and it is not mentioned in many domestic books and blogs. This case is recorded in this article. I hope that readers who encounter this problem and may encounter this problem in the future can save processing time and locate the root cause as soon as possible.

The official document for the conversion from MIXED format to ROW format when using the ROW syntax is described as follows:

Statement-based replication of LIMIT clauses in DELETE, UPDATE, 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.

-- Summary.

Through this case, we can learn when binlog_format will change from MIXED format to ROW format, as well as common reasons for delays and solutions. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them. Like the author's article, click a wave of attention in the upper right corner, thank you!

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.