preface
In the previous section, we have explained the mysql cluster setup with one master, one slave, and two master and two slave, which seems to be fine in standalone application, but in enterprise production environment, there will be replication delay in many cases. So HERE I am!!
The principle of master-slave replication we here no longer here, as has been said before, this is a nagging question of principle of nature and almost in the interview to ask rotten, the principle of the nature of things is not very difficult, but you need to pay attention to, master-slave replication latency issue will become a difficulty, can very comprehensive test students’ technical strength. [Obtaining resources]
1. How do I view the synchronization delay status?
Run the show slave status command on the slave server to view specific parameters. The following parameters are important:
Master_log_file: specifies the name of the binary log file of the master server being read by the I/O thread in the slave
Read_master_log_pos: Position in the current master binary log that the IO thread in the slave has read
Relay_log_file: Name of the relay log file that the SQL thread is currently reading and executing
Relay_log_pos: Position in the current relay log where the SQL thread has read and executed
Relay_master_log_file: Name of the primary server binary log file that contains most recent events executed by the SQL thread
Slave_io_running: Whether the IO thread is started and successfully connected to the primary server
Slave_sql_running: indicates whether the SQL thread is started
Seconds_behind_master: The event difference between the SLAVE SQL thread and the slave I/O thread, in seconds
Seconds_behind_master = seconds_behind_master = seconds_behind_master = seconds_behind_master [Obtaining resources]
Note the following key points during master/slave replication:
1. Primary database A completes A transaction and writes to binlog, which is denoted as T1.
2. Then it is passed to standby database B, and the time when standby database B receives the binlog is denoted as T2;
3. Standby database B performs and completes the transaction, which is denoted as T3;
The so-called primary/secondary delay is the difference between the completion time of the secondary database and the completion time of the primary database for the same transaction, namely T3-T1. SBM also performs calculation in this way. There is a time field in the binlog of each transaction, which is used to record the writing time of the master database. The standby database takes out the value of the time field of the currently executing transaction, calculates its difference with the current system time, and obtains SBM. [Obtaining resources]
If you understand the process, then we will start to analyze the reasons for the time difference, so that we can better solve the problems in the production environment. [Obtaining resources]
2. What are the causes of the master-slave replication delay?
1. In some deployment environments, the performance of the machine where the standby library is located is worse than that of the machine where the primary library is located. At this time, if the machine resources are insufficient, it will affect the efficiency of standby database synchronization.
2. The standby database acts as the read database. Under normal circumstances, the main write pressure is on the primary database, so the standby database will provide part of the read pressure
If a transaction in the primary database takes 10 minutes and writes to the binlog must wait for the transaction to complete before being sent to the standby database, then the execution is already 10 minutes late
4. Write operations to the master library are sequential. Single thread from the master library reads the binlog sequentially. The master/slave replication of mysql is a single-thread operation, but since the master library is sequential, the efficiency is high, and the slave library also reads the log of the master library sequentially, but when the data is pulled back, the operation becomes random, not sequential, so the cost will increase.
5. While synchronizing data from the slave library, lock preemption may occur with other query threads, and delay will also occur at this time.
Delays can also occur when the TPS concurrency of the main library is very high, resulting in more DDLS than a thread can handle
7. If the network bandwidth is not good during the transmission of binlog logs, the network delay may also cause the data synchronization delay
These are some of the possible reasons for back-up delays.
3. How to solve the problem of replication delay
Let’s start with something empty. What is empty? It sounds reasonable at first, but it may be very difficult or difficult to implement in implementation or actual business scenarios. We will describe it from the following aspects:
1. Architecture
1. The implementation of the business persistence layer adopts the library architecture, so that different business requests are dispersed to different database services, and the pressure of a single machine is dispersed
2, service infrastructure add buffer layer between the business and mysql, reduce the pressure mysql to read, but it is important to note that if the data is to change often, so this design is not reasonable, because this constant need to update the data in the cache, maintain the consistency of the data, lead to the cache hit ratio is very low, so this time will be careful with the cache
3, using better hardware devices, such as CPU, SSD, and so on, but the plan for the company can not accept, the reason is simple, would increase the cost of the company, and the general company actually is very stingy, so the meaning is not big, but you should know that this is also a way to solve the problem, but you need to evaluate the input-output ratio. [Obtaining resources]
2, from the library configuration
1. Change the sync_binlog parameter value
To set this parameter properly, you must know the disk write process of binlog:
As you can see, each thread has its own binlog cache, but shares the same binlog.
In the figure, write refers to write logs to the page cache of the file system, and does not persist data to disk, so it is fast
Fsync in the figure is the operation to persist data to disk. In general, we think of fsync as the disk IOPS
The timing of write and fsync is controlled by the sync_binlog parameter.
1. When sync_binlog=0, it indicates that only write, not fsync, is committed for each transaction
2. When sync_binlog=1, fsync is executed for each committed transaction
3. When sync_binlog=N, it means that every transaction is committed and fsync is performed after N transactions are accumulated.
In most application scenarios, you are advised to set this parameter to 1 to ensure data security. However, if there is a delay in master/slave replication, you are advised to set this parameter to a value between 100 and 1000 rather than 0. There is no way to control the amount of data that is lost, but this parameter is not very meaningful for business systems with high security requirements.
2, Disable the binlog function on salve. When synchronizing data from the slave database, the slave database may also record data from the slave database, which will consume I/O resources. Therefore, you can consider disabling it. In this case, the binlog will be sent to another slave database for data synchronization. In this case, this configuration item will not be of much use. [Obtaining resources]
Set the innodb_flush_log_at_trX_COMMIT property. This property specifies whether every transaction commit needs to be written to disk, which is a waste of time. There are three values in the property: 0. 1 (flush disk for every transaction committed), 2 (flush disk for every second written to OS cache), generally we recommend setting it to 2, so that data in OS cache will persist even if mysql service is down.
4. Fundamentally solve the problem of master/slave replication delay
Many students use the master-slave replication of mysql in their online business systems. However, it should be noted that not all scenarios are suitable for master-slave replication. In general, the scenarios require far more reads than writes, and the timeliness of reads is not so high. If a real scenario really requires that the updated data be read immediately, then only the master library data is forced to be read, so consider the actual application scenario when implementing, not technology for technology’s sake, which is a serious matter.
One concept introduced after mysql5.6 is what we call parallel replication, as shown below:
The previous sql_thread is now a coordinator component. When a log arrives, the coordinator reads the log information and distributes the transaction. The real log execution process is placed on the worker thread and executed by multiple threads in parallel.
Show global variables like 'slave_parallel_workers'; Set global slave_parallel_workers = 4; Database show global variables like '%slave_parallel_type%'; database show global variables like '%slave_parallel_type%'; Stop slave stop slave; Set global slave_parallel_type='logical_check'; Start slave start slave show full processList;Copy the codeThis configuration allows you to do what we call parallel replication, but there are a few things you need to think about
1. In parallel operation, there may be concurrent transaction problems. Can our standby database be sent to each worker in the way of rotation training during execution?
The answer is no, because after the transaction is distributed to the worker, different workers will start to execute independently. However, due to the different scheduling strategies of CPU, it is likely that the second transaction will be executed before the first transaction. If they just modify the same row of data, then due to the execution order, Data on the active and standby nodes may be inconsistent. [Obtaining resources]
2. Can multiple update statements of the same transaction be distributed to different workers for execution?
The answer is also not line, for example, a transaction to update the each row in the table table t1 and t2, if the two update statement is assigned to the different worker, although the end result is the main equipment consistent, but if the table t1 completes, a query for library, you will see this transaction half results are updated. Breaks the isolation of transaction logic.
The main purpose of the above two issues is to explain one thing: what are the strategies that coordinators need to follow when distributing them?
1. Update coverage cannot be caused. This requires that two transactions updating the same row must be distributed to the same worker.
2. The same transaction cannot be separated and must be placed in the same worker.
After listening to the above description, let’s talk about the principle and process of concrete implementation. [Obtaining resources]
If let us design, how should we operate? This is a question worth thinking about. In fact, if according to the actual operation, we can be classified by granularity, divided into distribution by library, distribution by table, distribution by row.
In fact, no matter what method is used for distribution, what we need to pay attention to is that the distribution must meet the two rules mentioned above. Therefore, when distributing, we need to define a hash table on each worker to save the table involved in the transaction currently being executed by the work. Hash table keys store different values for different granularity:
Distribution by library: The key value is the name of the database, which is simpler
Distribution by table: The key value is the library name + table name
Row by row distribution: the key value is the library name + table name + unique key
1. Parallel replication strategy for MySQL5.6
Parallel replication has been supported since version 5.6 of mysql, but the granularity of parallel replication by library is supported. This is why the current version of mysql supports parallel replication by library.
But as those of you who have used it know, the parallelism of this strategy depends on the pressure model. If there are multiple DB on the main library, and each DB pressure equilibrium, the effect of using this strategy will be very good, but if the main repository of all the tables are on the same DB, so all the operation will be distributed to a worker, into a single thread operation, then this policy effect is not good, so in the actual production environment, Not very much. [Obtaining resources]
2. MariaDB’s parallel replication strategy
In mysql5.7, parallel replication based on group commit was used. In other words, the slave server played back the same way as the host, that is, the slave played back the same way as the master library did in parallel. This is actually a reference to mariaDB’s parallel replication.
MariaDB’s parallel replication strategy takes advantage of this feature:
1. Transactions that can be committed in the same group must not change the same row;
2. Transactions that can be executed in parallel on the primary must also be executed in parallel on the secondary.
In implementation, mariaDB does this:
1. For transactions committed together in one set, have the same commit_id, and the next set is commit_id+1.
Commit_id writes directly to binlog.
3. When transmitted to the standby database application, transactions of the same commit_id will be distributed to multiple workers for execution;
4. After the group is executed, the coordinator can fetch another batch.
This is mariaDB’s parallel replication strategy, and in general it looks fine, but if you look closely it doesn’t achieve the goal of “truly simulating the concurrency of the master library”, where one set of transactions is committed and the next set of transactions is simultaneously in the “executing” state.
What we really want to achieve in parallel replication is a state where the first set of transactions commit, the next set of transactions run, and the next set of transactions commit as soon as the first set of transactions commit. [Obtaining resources]
However, with mariaDB’s parallel replication strategy, the execution state on the standby library would look like this:
As you can see, the biggest difference between this diagram and the above diagram is that the secondary database must wait for the first set of transactions to complete before the second set of transactions can be executed, so the system throughput is insufficient. If TRx2 is a large transaction, when the standby library is applied, after the execution of TRX1 and TRx3 is completed, the next group can only start execution. During this period, only one worker thread is working, which is a waste of resources.
Mysql5.7 parallel replication strategy
In mysql5.7, the mariaDB parallel replication policy is optimized to provide its own parallel replication policy, and the slave-parallel type parameter can be used to control the parallel replication policy:
1. When the value of DATABASE is set to DATABASE, it indicates that version 5.6 is used.
2. If the value is LOGICAL_CLOCK, it indicates the same policy as mariaDB.
At this point, you need to consider a question: can all the transactions that are executing at the same time be parallel?
The answer is no, because it is possible for multiple executing transactions to have lock conflicts, which can lead to lock wait problems.
In mariaDB, all transactions that are in the commit state can be done in parallel. If the commit state is set to commit, the lock is not broken.
Thus, the idea behind mysql5.7’s parallel replication strategy is:
A prepared transaction can be executed in parallel on the standby database.
A prepared transaction can be executed in parallel with a COMMIT transaction on the standby database.
Based on this processing mechanism, we can set most of the logs in the prepare state, so it can be set
1. The binlog_group_COMMIT_SYNc_delay parameter, which indicates the number of microseconds before fsync is called;
The binlog_group_COMMIT_SYNC_NO_delay_count parameter indicates how many times fsync will be called.
5. Master/slave replication based on GTID
In the master-slave replication practice we explained earlier, each time you want to replicate, you must execute the corresponding command on the standby machine, as follows:
change master to Master_host = '192.168.85.11, master_user =' root ', master_password = '123456', master_port = 3306, master_log_file = 'master - bin. 000 001',master_log_pos=154;Copy the codeIn this configuration, we have to know what is specific binlog file, at the beginning of the file where copy at the same time, under normal circumstances there is no problem, but if it is a main case of master-slave cluster, and then if the host goes down, when the machine starts to work, then it is the standby synchronization from the position of machine, the position may be different with the location of the host, In this case, finding the location was a bit of a hassle, so a GTID-based master-slave copy was made after 5.6.
The GLOBAL Transaction ID(GTID) is the number of a committed transaction and is a globally unique number. GTID is actually composed of UUID+TID, where UUID is the unique identifier of the mysql instance, and TID represents the number of committed transactions on the instance, and increases monotonically with transaction commits. In this way, transactions have unique ids in the cluster, enhancing the active/standby consistency and fault recovery capability. [Obtaining resources]
1. Construction based on GTID
1. Modify the mysql configuration file and add the following configuration
gtid_mode=on
enforce-gtid-consistency=true
Copy the code2. Restart the primary and secondary services
3. Run the following command from the library
Change master to master_host='192.168.85.111',master_user='root',master_password='123456',master_auto_position=1;Copy the code4, master library from library insert data test.
2. Parallel replication based on GTID
The principle of master-slave replication is not very different in any way, but the key is to store the information submitted by the group in the GTId.
show binlog events in 'lian-bin.000001';
Copy the code
Previous_gtids: Used to represent the position of the last gtid of the previous binlog. Each binlog has only one gTID.
Gtid: When GTID is enabled, a GTID event is added before each operation statement is executed to record the global transaction ID. The group commit information is stored in the GTID event, including last_COMMITTED and sequence_number, which identifies the group commit information. [Obtaining resources]
The above log may seem cumbersome, you can execute it using the following command:
Last_committed Indicates the number of the last committed transaction. If the value of last_COMMITTED is the same, the transaction is in the same group and can be executed in parallel in the standby database. Note also that each last_COMMITTED value is the sequence_number value of the previous group transaction.
If gTID is not enabled, how can the grouping information be saved?
Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid: Anonymous_Gtid [Obtaining resources]
If you want to see parallelism, you can execute the following code:
package com.mashibing;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class ConCurrentInsert extends Thread{
public void run() {
String url = "jdbc:mysql://192.168.85.111/lian2";
String name = "com.mysql.jdbc.Driver";
String user = "root";
String password = "123456";
Connection conn = null;
try {
Class.forName(name);
conn = DriverManager.getConnection(url, user, password);//获取连接
conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错
} catch (Exception e1) {
e1.printStackTrace();
}
// 开始时间
Long begin = new Date().getTime();
// sql前缀
String prefix = "INSERT INTO t1 (id,age) VALUES ";
try {
// 保存sql后缀
StringBuffer suffix = new StringBuffer();
// 设置事务为非自动提交
conn.setAutoCommit(false);
// 比起st，pst会更好些
PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");//准备执行语句
// 外层循环，总提交事务次数
for (int i = 1; i <= 10; i++) {
suffix = new StringBuffer();
// 第j次提交步长
for (int j = 1; j <= 10; j++) {
// 构建SQL后缀
suffix.append("(" +i*j+","+i*j+"),");
}
// 构建完整SQL
String sql = prefix + suffix.substring(0, suffix.length() - 1);
// 添加执行SQL
pst.addBatch(sql);
// 执行操作
pst.executeBatch();
// 提交事务
conn.commit();
// 清空上一次添加的数据
suffix = new StringBuffer();
}
// 头等连接
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 结束时间
Long end = new Date().getTime();
// 耗时
System.out.println("100万条数据插入花费时间 : " + (end - begin) / 1000 + " s"+" 插入完成");
}
public static void main(String[] args) {
for (int i = 1; i <=10; i++) {
new ConCurrentInsert().start();
}
}
}
Copy the codePrivate message me, free Java full set of resources.
In the end, I wish you all success as soon as possible, get satisfactory offer, fast promotion and salary increase, and walk on the peak of life.
If you can, please give me a three support me?????? [Obtain information]
