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

Let's talk about the reasons for the master-slave delay in MySQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the reasons for the delay between the master and slave of MySQL.

Step1: iostat to check IO status

Iostat-x 1 check the IO, which disk has the highest IO load, and then we will locate the specific load source.

Step2: iotop locates the load source process

The essence of iotop is a python script that gets the IO information of thread from proc and aggregates it.

You can see from the figure below that most of the IO sources come from the mysqld process. Therefore, it can be determined that the load source of dfa is the database.

Step3 pt-ioprofile locates the load source file

The principle of pt-ioprofile is to attach a strace process to a pid for IO analysis.

Here is a warning from the official website:

However, it works by attaching strace to the process using ptrace (), which will make it run very slowly until strace detaches. In addition to freezing the server, there is also some risk of the process crashing or performing badly after strace detaches from it, or indeed of strace not detaching cleanly and leaving the process in a sleeping state. As a result, this should be considered an intrusive tool, and should not be used on production servers unless you are comfortable with that.

Find the process number corresponding to the mysqld process through ps aux | grep mysqld, and check which file takes up the most IO time through pt-ioprofile.

By default, the tool shows the time taken by IO.

Pt-ioprofile-profile-pid 3082

What is more useful for positioning problems is through the throughput of IO. Use the parameter-- cell=sizes, which shows the result in the way that it has been shown in Bhand s.

Pt-ioprofile-profile-pid 3082-cell

The first: the table does not have a primary key and a secondary index (troubleshooting the most easily ignored situations)

As shown in the figure below, when sql_thread replays the relay log, it determines whether the table has a full table scan based on whether the table has a primary key and a secondary index.

A new parameter, slave_rows_search_algorithms, is provided in MySQL5.6, which can partially solve the problem of replication delay caused by no primary key table. The basic idea is to collect all the previous images in a ROWS EVENT, and then judge each record in the HASH to update when scanning the whole table at once.

Slave_rows_search_algorithms consists of a combination of three values: TABLE_SCAN,INDEX_SCAN, HASH_SCANTABLE_SCAN,INDEX_SCAN (default configuration, which means index is used if there is an index, otherwise a full table scan is used)

Use combinations include:

INDEX_SCAN,HASH_SCAN

TABLE_SCAN,HASH_SCAN

TABLE_SCAN,INDEX_SCAN,HASH_SCAN (equivalent to INDEX_SCAN,HASH_SCAN)

Is there anything you don't understand about the reasons for the delay of MySQL master and slave above? Or if you want to know more about it, you can continue to follow our industry information section.

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.