http://oldboy.blog.51cto.com/2561410/1682147----What are the reasons for synchronization delays in MySQL? How to solve?
Original: http://www.zjian.me/mysql/mysql%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5%E5%BB%B6%E8%BF%9F%E5%8E%9F%E5%9B%A0%E4% bb%a5%e5%8f%8a%e8%a7%a3%e5%86%b3%e6%96%b9%e6%a1%88/
1.MySQL Database Master-Slave synchronization delay principle.
A: When it comes to MySQL database master-Slave synchronization delay principle, from the MySQL database master-slave replication principle, MySQL master-slave replication is a single-threaded operation, the main library for all DDL and DML production Binlog,binlog is sequential write, so the efficiency is very high; slave slave The _io_running thread will fetch logs from the main library, and the slave slave_sql_running thread will implement the DDL and DML operations of the main library in slave. The IO operations of DML and DDL are random, not sequential, so the cost can be high, and perhaps other queries on slave generate lock contention, because Slave_sql_running is also single-threaded, so a DDL card master, need to execute 10 minutes, Then all subsequent DDL will wait for the DDL to execute before continuing, which results in a delay. A friend will ask: "The same DDL on the main library also needs to perform 10 points, why slave delay?" "The answer is that master can be concurrent and slave_sql_running threads are not."
How the 2.MySQL database master-Slave synchronization delay is generated.
A: When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with a large query statement from slave.
3.MySQL Database Master-Slave synchronous delay solution
A: The simplest solution to reduce the slave synchronization delay is to optimize the architecture and try to get the DDL of the main library to execute quickly. There is the main library is written, the data security is high, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 and other settings, and slave do not need this high data security, fully can speak sync_ Binlog is set to 0 or off Binlog,innodb_flushlog can also be set to zero to increase the efficiency of SQL execution. The other is to use a better hardware device than the main library as a slave.
The factors of the 4.MySQL database master-Slave synchronization delay.
1. Network Latency
2. Master Load
3. Slave load
It is common practice to use multiple slave to apportion read requests, and then take a dedicated server from these slave, only as a backup, without any other action, to achieve the ' real time ' requirement in relative maximum.
In addition, we introduce 2 parameters which can reduce the delay.
–slave-net-timeout=seconds
Parameter meaning: How long to wait to reestablish the connection and get the data when slave reads log data from the primary database
Slave_net_timeout units are set to seconds by default to 3,600 seconds
| Slave_net_timeout | 3600
–master-connect-retry=seconds
Parameter meaning: When the master-slave connection is re-established, if the connection establishment fails, how long after the interval is retried.
Master-connect-retry units are set to seconds by default to 60 seconds
Usually configuring the above 2 parameters can reduce the master-slave data synchronization delay caused by network problems
Related optimization:mysql> CREATE database Fafa; Query OK, 1 row affected (0.01 sec) mysql> use fafadatabase changed mysql> CREATE TABLE test (JJ Int,kk varchar (10)); Query OK, 0 rows affected (0.02 sec) Next we loop through the data experiment in the main library, and then we look at the process state, and the synchronization situation is created Proceduredelimiter//create procedure Fafa () Begindeclare num int;set num=1;while num < 8000000 doinsert into test (JJ,KK) VALUES (num, ' Fafa '); set num=num+1;end while ; end//we are going to call this procedure before we insert the data called FAFA (); Because I did not change the delimiter ' back, so input '; ' After not executed, also need//note here there is a phenomenon, there has been a large delay, here we conduct one by one. Summary troubleshooting Scenario 1: Read-only instance specification too small delay scenarios such as latency are often the case where the master node has a larger configuration, while the read-only node buys a minimal configuration (such as 2G memory/200 IOPS). Principle Analysis: Read-only node data in order to maintain synchronization with the master node, using the MySQL binlog replication technology, by an IO thread and a SQL thread to complete, the IO thread is responsible for the main library Binlog pull to the read-only node, the SQL thread is responsible for consuming these binlog logs, These two threads consume the IO resources of a read-only node, so when the read-only node IOPS configuration is insufficient, the data for the read-only node is delayed
Workaround:Upgrade the specification of a read-only instance (you can refer to the consumption of IOPS at this time in the main library) to prevent data latency due to the small size of the read-only instance. Only the configuration of a read-only instance node is greater than or equal to the configuration of the master node. Scenario 2: The high TPs in the main library causes the read-only node delay principle to be resolved: Because the synchronization of the read-only node and the main library is single-threaded, the pressure of the main library is concurrent multi-threaded writing, which inevitably results in the data delay of the read-only node
Workaround:Enabling parallel replication of read-only nodes is the fundamental way to solve this problem, and it is necessary to troubleshoot whether the business write pressure is normal, to optimize or split the business appropriately, and to ensure that the TPS in the main library does not cause slave delays.
Expansion:In MySQL5.6, concurrent replication is introduced, which is at the database level, which means that one SQL thread can handle successive transactions of a database without waiting for other databases to complete. This version of concurrent replication can be understood as a database of one SQL thread. The parameters associated with concurrency are as follows: Slave_parallel_workers //number of worker threads Slave-checkpoint-group //How many transactions do once checkpointslave-checkpoint-period //how often do checkpoi Ntslave-pending-jobs-size-max //The maximum size of the event that is sent to the worker, in the waiting state MySQL5.6 based on database level concurrent replication can solve the problem of synchronization delay of the business table under different database, but most of the tables in the actual production are still in the same library, even if the setting slave_parallel_workers is greater than 0 , and concurrency is not possible. In the case of high concurrency, there is still a master-slave replication delay, so the MySQL 5.7 version really supports the "real" parallel replication feature in MySQL5.7, introduces a new concurrent replication method, based on Logical_clock concurrent replication, Can support the concurrent execution of transactions in Relaylog in a database. The same binary log group commits the parallel application on master to the slave node, there is no cross-database limit, and no data needs to be split into multiple databases. To implement this function, you need to commit the transactions committed in the master node tag Binlog which can be executed concurrently, although the MySQL5.6 has introduced Binarylog group commit, but no concurrent transactions are marked. This command can be used to view: MYSQLBINLOG-VVV mysql-bin.000106 | Grep-i Last_commit in MySQL5.7, has solved the master-slave replication delay problem, the specific configuration parameters are as follows: slave-parallel-type=logical_clockslave-parallel-workers=16Master_info_repository=tablerelay_log_info_repository=tablerelay_log_recovery=on Scenario 3: DDL of the Main library (Alter, DROP, repair, Create) causes read-only node delay
Possible 1: The DDL synchronization of the read-only node and the main repository is serial, and if the DDL operation takes a long time to execute in the main library, the same time will be spent in the repository, for example, when the main library is adding a field to a 500W table for 10 minutes, it will also take 10 minutes on the read-only node. So read-only nodes delay 600S, other common operations such as:mysql> ALTER TABLE Test add column NN varchar;mysql> ALTER TABLE Test add index (JJ);
Possible 2: A query with a very long execution time on a read-only node is executing, and the query blocks the DDL of the Autonomous repository, and the Read node table is locked until the query ends, resulting in the data latency of the read-only node. On a read-only node, you can view the status of the connection by executing the show processlist command in: Waiting for table metadata lock
Workaround:For possible 1, can only say before the operation of the possible impact of the consideration, in case 2, you can kill the read-only node on the large query, you can recover the read-only node and the master node data Synchronization 4: The main library to perform large transactions caused the delay of the main library to perform an insert ... Select Very large insert operation, which produces nearly hundreds of g of binlog file transfer to a read-only node, resulting in a read-only node application Binlog delay.
Workaround:Splitting a large transaction into a small transaction for displacement commits so that the read-only node can quickly complete the execution of the transaction without causing the data to be delayed. Scenario 5: A DML operation with a table without a primary key causes a delay of mysql> update test set kk= ' fafa01′; because there is no primary key in the table, the update for each transaction entry is a full table scan, and if the table has a lot of data, When the repository executes the updated transaction entry, there will be a lot of full table scan updates, further explained that because there is no primary key in the table, in row mode, every deletion of data will do a full table sweep, that is, a delete, if the deletion of 10, will do 10 full table sweep, So slave will always be stuck;
Expansion:Primary key for InnoDB, is very important, each table design, should be the primary key by default, whether you need him, and the primary key design is best to choose the self-increment of the primary key, Here you can also briefly mention the advantages of the self-increment primary key: A. Self-increasing primary key to facilitate the improvement of insert performance; B. Self-increasing primary key design (Int,bigint) reduces the space of two-level indexes and increases the memory hit rate of level two indexes C. A self-increasing primary key can reduce page fragmentation and increase the use of space and memory.
http://blog.csdn.net/u010522235/article/details/51865978http://blog.csdn.net/cym_summer/article/details/ 52781135
Go What are the causes of synchronization delays in MySQL? How to solve?
