Kafka,mysql, Redis primary/secondary replication scheme comparison of mysql

According to my understanding, the leader copy and follower copy in Kafka are discussed in the last article. The follower copy keeps sending fetch requests to the leader copy to obtain records and write them into the local log to ensure that there are multiple backups of messages in the leader copy. When the leader replica is not working, a new leader replica can be elected from brokers whose ISR is in sync with the Leader replica.

Summary:

1) The kafka scenario is a backup of the messages in the Leader replica in order to work when the Leader replica fails

2) All reads and writes are performed on the leader copy, while the follower copy is only a backup

3) The follower copy sends a FETCH request to get the message

4) The log messages are divided into visible messages and invisible messages. The visible messages are the messages that the follower copies keep consistent with the leader copy

In this article, we will briefly talk about the master and slave of mysql.

contrast

1) the mysql scenario can be read/write separation to improve read/write performance; It can also be for high availability;

2) You can set the write of the primary library, the degree of the secondary library, and also set the read and write on the primary library

3) The secondary library synchronizes with the binlog from the master library

4) Unable to solve the master-slave delay problem

Primary/secondary synchronization scenario in mysql

There are several architectural scenarios for master/slave replication in mysql

High availability scenario: If there is only one machine and the system is suspended, then the system is unavailable. If there is active/standby database, the active database can be suspended to make the standby database work.

, speaking, reading and writing separate scene: mysql if only the deployment of a machine in the case of a large amount of data will be prone to performance bottlenecks, therefore would consider deployment of master-slave topology structure, the general approach is to write on the master server, read in from the server, in order to alleviate the pressure of the main server, improve the read/write performance, support more read and write requests.

The standby database is usually set to readonly for the following reasons: 1) The query statements of the operation class are stored in the standby database to prevent misoperations. 2) If double-write occurs during the switchover, the master and slave nodes are inconsistent. 3) Use readonly to check the role of the node. There is no need to worry that setting the standby database to readonly will affect the synchronization of the primary database updates because the executing thread has super super privileges.

With the master/slave topology, ensure that the data stored on the slave and the master are the same, that is, the synchronization mechanism. Considering the case of one master/slave, the master/slave switchover occurs when the following two situations exist: 1) Active switchover scenario, software upgrade, and the master database offline as planned; 2) In the passive switchover scenario, the main library actively goes offline.

What does mysql do to ensure primary/secondary consistency?

The answer is binlog.

So let’s take a look at the binlog. The binlog can be used to perform crash recovery and crash safe. The binlog can be used to notify external services of changes in database data. The binlog can be used for master/standby synchronization.

There are three formats for binlog: row,statement,mixed. Why mixed? For example, if the statement is used and the delete XXX limit 1 command is executed, the same statement may be inconsistent between the master and the slave. If the row command is used, many rows may be written in one update, resulting in a large amount of data.

The process of synchronizing the active and standby mysql servers

Mysql master/slave replication process is as follows: In the main database, data updated by the client is first recorded in the Undo log to prepare for MVCC and transaction rollback. After the update is completed in the memory, data is written to the redo log to prepare for write performance and crash-safe. When the engine layer is updated, data consistency between the engine layer and the server layer is maintained. A two-phase commit is performed, with write operations in binlog in preparation for future backups. At this time, an IO thread will maintain TCP connection with the master library and obtain binlog from the master library. After the master library receives the request, a dump_thread thread will send the binlog to the standby library. After receiving the message, the standby database writes the message to the local relay log, and sql_thread reads and executes the message from the relay log.

Asynchronous replication means that the master database sends a binlog to the standby database and immediately returns an ACK to the user, regardless of whether the standby database receives the ack. The problem is that if the master database is busy, performance deteriorates, or even breaks down, data inconsistency may occur when the secondary database becomes the master database. During the switchover, there are two policies: 1) Availability policy, that is, ensure availability regardless of data consistency; 2) Data loss policy, the database does not serve external services, and the switchover is performed after synchronization.

For asynchronous replication, mysql can set synchronous replication or semi-replication. The primary database waits for the secondary database to send the received message before committing, and then sends the request to the user. The problem is that if there is only one slave library and it is down, the master library will get stuck because it is waiting.

The solution is to configure one more slave library, and set the mysql master library to return only one slave library. To do so, you need at least three machines that perform worse than one.

Primary/secondary delay definition of mysql

If the secondary database executes the primary database’s binlog, it only guarantees final consistency, which is not enough for high availability.

Secondary database delay seconds_behind_master=T3-T1 Specifies the secondary database delay time.

T1: the time when the master completes a transaction and writes to the binlog;

T2: the time when it is passed to the standby database; (T1~T2 time is very short)

T3: the time when the standby database completes the transaction. (Mainly here)

Scenarios and causes of the delay are as follows: 1) Poor performance of the machine where the standby database resides 2) high pressure of the standby database 3) large transactions 4) large table DDL 5) Parallel replication capability of the standby database

Appendix: Distribution Policy

From the perspective of ideas, the first simple strategy is given, single thread -> table distribution policy -> row distribution policy

• Distribution policy by table:

Each worker thread corresponds to a hash table that holds the table involved in the transaction currently in the execution queue of the worker. Key is the library name. Table name, value is a number indicating how many transactions in the queue modify the table.

When each transaction is distributed, there are three conflicts with worker:

1) If there is no conflict with all workers, the transaction will be assigned to the most idle worker

2) If the transaction conflicts with more than one worker, SQL_thead will enter the wait state until only one worker is left in conflict with this transaction;

3) If there is a conflict with only one worker, the transaction will be assigned to the worker with the conflict relationship

Scenario: Multiple table load balancing scenario, hotspot table is similar to single thread replication.

• Row distribution strategy:

If two transactions do not update the same row, they can be executed in parallel on the standby database. The binlog must be row

Similar to table by table, a hash table is assigned to each worker, and the key is the library name + table name + index name + index value, but it consumes more computing resources

The constraint of both schemes is that the table name, primary key, and unique index must be in row format to be able to resolve from binlog. You must have a primary key; No foreign keys

Limitations of row by row: large transaction 1) memory consumption, delete 1 million rows, hash table to record 1 million items; 2) CPU consumption, binlog parsing, hash calculation, high cost for large transactions.

Solution: Set a threshold for a single transaction to degenerate to single-threaded mode if it exceeds the set number of rows

• The official method

• Msyql5.6 By table policy

• MariaDB: Optimization based on group commits

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 database must be executed in parallel on the standby database;

1) One set of committed transactions has the same COMMIT_id, and the next set is commit_id+1. 2)commit_id writes to binlog directly; 3) The standby database distributes transactions of the same commit_id to multiple workers for execution; 4) Take the next batch after all this group is executed

Problem: The goal of simulating the concurrency of the primary database is not really achieved. When a group of transactions is committed, the next group of transactions is in the state of execution at the same time, while the standby database has to wait for the completion of the next group before executing the next group. It’s easy to get dragged down by big things

• msyql5.7

Set the DATABASE to mysql5.6, LOGICAL_CLOCK, mariaDB, and prepare.

Transactions in the executing state cannot be parallel because of lock conflicts, and the redolog prepare state was already detected as a lock. Therefore, it is not necessary to wait until the commit state is reached as long as redolog prepare is reached.

1) A transaction in the prepared state can be executed in parallel in the standby database. 2) A prepared transaction can be executed in parallel with a COMMIT transaction in the standby database.

The binlog_group_COMMIT_SNYC_delay and binlog_group_COMMIT_SYNC_NO_delay_count parameters indicate the number of microseconds after fsync is called and the number of cumulative times after fsync is called, respectively. This command is used to lengthen the time between write and FYNC, reduce the write times of binlog, create more transactions in the prepare phase, increase the parallelism of the secondary database, and make the primary database commit slowly and the secondary database execute fast

• msyql5.7.22

The writeset policy can be configured using either a table policy or a Mariadb policy (whether in commit or prepare state). In order to reduce the amount of computation, the writeset policy is more suitable for various binlog formats. When there is no primary key or foreign key constraint for the table, the scenario is reduced to a single thread.

Appendix: Switching

When the master library is switched from 0 to 1, the service traffic needs to be switched, and the master libraries of the slave library need to be changed to the new master library.

• Location-based active/standby switchover

To change the slave library to the slave library of a master library, run the change master command, and fill in the host, password, username, and other parameters of the master library, that is, the log and location of the master library, master_log_name and master_log_pos. It is difficult to obtain these two parameters accurately, and only one approximate position can be obtained. In order to avoid data loss in the switching process, it is always necessary to find a slightly earlier one, and then skip the transactions already executed on the standby database through judgment.

Get these two parameter procedures: Mysql > select * from mysql0; mysql > select * from mysql0; mysql > select * from mysql1; mysql > select * from mysql0; I get the loci at time T.

How do you skip repeated transactions? Set global sql_slave_skip_counter=1; start slave; It is possible that more than one transaction will be executed repeatedly during the switch, so keep watching when mysql2,3, and 4 are first connected to the laborious mysqL1. Stop each such error and skip until it does not stop again. 1062 insert data unique key conflict, 1032 delete data row not found.

Mysql5.6 uses a new method, GTID(global transaction ID, which is generated at commit time). Each mysql maintains a collection of GtiDs. Main steps: 1) mysql2, 3, 4 from the specified library mysql1, connection is established based on the case of agreement 2) mysql2, 3, 4 sends its own GTID msyql1 3) mysql1 calculate your GTID and forwards the GTID difference set, there have no others in some cases, direct return an error, If it contains someone else’s set, it will find the first transaction from its binlog file that does not have someone else’s set and send it to mysql2.

The differences between the two location-finding methods are as follows: in the master/slave relationship of GTID, the master library determines the difference set according to the GTID set sent by the standby library; In the method of finding colocation, the standby library asks for the master library, the standby library decides, and the master library does not make any judgment. The job of finding loci can be considered automatic within the main library.