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

This article introduces the knowledge of "common causes of delay and solutions to MySQL". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A preface

In the MySQL database of MmurM architecture on the operation and maintenance line, more alarms are received about the delay between the master and the slave:

Click (here) to collapse or open

Check_ins_slave_lag (err_cnt:1) critical-slavelag on ins:3306=39438

It is believed that slave latency is an old growth problem encountered by MySQL dba. Let's first analyze the risks caused by slave latency.

1. Under abnormal circumstances, the master-slave HA cannot be switched. The HA software needs to check the consistency of the data, and when there is a delay, the master and standby are inconsistent.

two。 Replication of hang in standby database will cause backup failure (flush tables with read lock will time out at 900s)

3. For backups based on slave, the data is not up to date, but delayed.

How to solve the problem

In the face of such problems, how can we solve and avoid them? This paper analyzes several reasons for the delay of database preparation.

1. ROW mode has no primary key, no index or low index discrimination. It has the following characteristics

A. Show slave status shows that position has not changed.

B. show open tables shows that a table has always been in_use 1

C. show create table looks at the table structure and sees that there is no primary key, or there is no index, or the index differentiation is poor.

Solution:

a. Find a few fields with high discrimination in the table, and you can use this method to determine:

Select count (*) from xx

Select count (*) from (select distinct xx from xxx) t

If the results of two queries count (*) are similar, these fields can be indexed.

b. Reserve library stop slave

It may take a long time to execute because the transaction needs to be rolled back.

c. Prepare the library

Set sql_log_bin=0

Alter table xx add key xx (xx)

The old version of slave will only choose the first index when applying binlog. You need to put the newly added index first. You can delete the old index, build a new index, and then build the old index. It can be executed in a sql.

d. Reserve library start slave

If it is innodb, you can judge by looking at the indicators of rows_inserted,updated,deleted,selected through show innodb status.

If a large number of records are modified per second, replication is being performed at a faster rate.

2 MIXED mode has no index or SQL is slow

Show full processlist to see the executing SQL on the slave library.

Solution:

A. SQL is relatively simple, check to see if the index is missing and add the index.

b. The other is the insert into select from statement, if the select contains group by, multi-table association, may be less efficient.

In this category, you can change binlog_format to row in the main library.

(3) there are large transactions on the master database, resulting in slave database delay

Phenomenon analysis binlog found a situation similar to the following figure.

Solution:

Communicate with the developer, increase the cache, write to the database asynchronously, and reduce the large number of writes directly to db.

4. The main library is written frequently, and the pressure from the slave library can not keep up with the delay.

The main phenomenon of this kind of reason is that there are a lot of IUD operations in the database, and slave can not catch up with the main library because of sql_thread single thread.

Solution:

A upgrade the hardware configuration of the slave library, such as ssd,fio.

B use @ Dinky's preheating tool-relay fetch

Loading the corresponding data into memory before the SQL thread performs the update does not improve the ability of the sql_ thread to execute sql, nor can it speed up the log reading speed of the io_thread thread.

C uses multithreading to replicate the scheme implemented by Ali MySQL's team-row-based parallel replication.

This scheme allows two transactions that modify the same table to execute in parallel, as long as the two transactions modify different rows in the table. This solution can achieve a higher degree of concurrency between transactions, but the limitation is that binlog in Row format must be used. Because only binlog in Row format can know the range of rows modified by a transaction, while binlog in Statement format only knows the modified table object.

5. There are a large number of myisam tables in the database, causing slave latency during backup

Because the xtrabackup tool backup finally executes flash tables with read lock, the database is locked for consistent backup, and then the myisam table lock will hinder the stagnation of salve_sql_thread and lead to hang

At present, a better solution to this problem is to modify the table whose structure is innodb storage engine.

This is the end of the content of "Common causes of delay and Solutions in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for 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.