Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the common reasons for slave delay in mysql, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

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:

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.

Third, expand reading.

[1] how to solve the problem of master-slave replication delay in MySQL database

[2] Analysis of three MySQL parallel replication schemes

[3] A master-slave synchronization acceleration scheme for MySQL-improvement

[4] introduction to MySQL multithreaded synchronous MySQL-Transfer

6. How to confirm the real delay?

The parameter seconds_bebind_master is not accurate, and sometimes it is displayed as 0, but in the case of data delay, the data will be synchronized after stop slave and start slave. Because slave sometimes fails to detect the normal network, you can use scripts to monitor and deploy on salve and master nodes respectively.

First create a monitoring table on both nodes

# filename: run_mysql_replication_heartbeat.py

# encoding=gbk

Import datetime,time

Import os,sys

From public import db

Import db_conf

Source_folder = db_conf.SOURCE_FOLDER

Def init_eviroment_path ():

Print sys.path

Python_path = (source_folder)

For i in python_path:

If i not in sys.path:

Sys.path.append (I)

Print sys.path

Def main ():

Conn, cursor = db.GetMysqlCursor ('update')

Cursor.execute ("insert into heartbeat (master_datetime,slave_datetime) values (now (), sysdate ()")

Cursor.close ()

Conn.close ()

If _ _ name__ = ='_ _ main__':

Init_eviroment_path ()

Os.system ("title MySQL Replication heartbeat")

Count = 1

While True:

Main ()

Print "(d) s" (count,datetime.datetime.now ())

Count+=1

Time.sleep (60)

Change the binlog mode of mastery to statement (default is mix)

So the reason for this modification is that it must be the SQL statement synchronization mode of statement, otherwise the result data synchronization mode of ROW may not work under mix. I also found this reason through master > show binlog events.

If you want to see the results immediately, just change the time of the master side, for example, an hour in advance, and execute:

Insert into heartbeat (master_datetime,slave_datetime) values (now (), sysdate ()) can see results similar to the following on slave:

Thank you for reading this article carefully. I hope the article "what are the common reasons for slave delays in mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.