How to optimize MySQL Replication

The following articles mainly discuss MySQL Replication optimization, we all know that MySQL The actual discussion of Replication is still the majority, and a lot of content is very practical, so I will summarize it and hope it will bring you some help in this aspect.

I have a limited level of English, and it is inevitable that there is something wrong with the translation. If you have any questions, please discuss it :)

MySQL Replication delay type

1. Fixed latency

The data persistence of Slave lags behind that of the Master and cannot be consistent with that of the Master.

Slave Data often lags behind the Master during the day, while it can catch up at night and be consistent with the Master's records.

This type of latency is usually caused by the Slave server load reaching the upper limit or when the daytime traffic is high.

2. Non-fixed latency

Slave Data lags behind the Master for a short period of time and can be recovered.

This type of latency is usually related to Batch Tasks and reports, and inefficient queries may also lead to this type of latency.

Restrictions on MySQL Replication

MySQL Replication is single-threaded, meaning that only one CPU core and one disk can be used effectively. A complicated query or transaction causes the process to be blocked, however, the multi-thread Replication patch for version 5.1 is still in the pre version, and there are many restrictions. If you are interested, you can check it out.

Replication capacity

1. Understand what is Replication capacity

You can pause Replication for one hour. After restarting Replication, observe how long the Slave data can be consistent with that of the Master. The ratio of the time taken from Replication restart to the time when the data is consistent with the Master data to the time when Replication is paused is the capacity of Replication.

2. It is recommended that the Replication capacity be more than three times, that is, the data delay is one hour. Slave can be consistent with the Master data in 20 minutes.

Optimization of MySQL Replication

1. Avoid the following update statements in MySQL 5.0

 
 

  
  
INSERT … SELECT <complex query> 
  
  
UPDATE .... WHERE <complex clause> 
 
 

Complex queries can cause Replication thread congestion. If it is an insert or update statement combined with select, You can execute select separately and save it in the temporary table, and then execute insert or update.

If MySQL 5.1 is used, the row-level ReplicationRBR in the new function can solve this problem. RBR can pass the updated results on the Master node through complex queries directly to Slave, and Slave can directly update the results to the database.

2. Avoid large transactions

Too many transactions will cause Replication to be congested for a long time, and the data will seriously lag behind the Master.

Slave server hardware selection

Faster CPU kernel, there is no advantage for single-threaded Replication multi-core CPU.

Higher-speed hard disks, including higher speed and better high-speed cache hit rate. If you have money, go to SSD.

Scalability of the master-slave structure

1. How to reduce the frequency of write operations

Master write operations will spread to all Slave, so high-frequency write operations will reduce the efficiency of Slave read operations.

At least one Server Load balancer instance must be synchronized to the entire database. Other Server Load balancer instances can only synchronize some tables. Of course, this requires the cooperation of web applications to allocate which queries read which Slave.

Put some update operations into memcached, such as session and counter.

Slave uses myisam Engine

Directly execute some update operations with a large write volume on the slave, instead of using Replication.

2. How to Use Slave hardware resources more effectively

Use Partition

Select tables for synchronization

Archive data on Slave.

Session persistence

Assign different Slave to different application servers for read operations.

Alternatively, different Slave types can be allocated based on different query types.

3. How to maximize the use of Slave

Put the queries that are not sensitive to data updates on the Slave, and the queries that require real-time data are placed on the Master.

Through session persistence, users who have made changes can first see the modified content. Other users can wait for the Slave update before viewing the new content.

For some data, memcached can be used to store the version number of the data. The program that reads the Slave can first compare the data of the Slave with the version of the memcached data. If the data is inconsistent, it will read the master. This method can be used for user and blog information.

You can use show slave status to check the STATUS of Slave before querying, and then select the server based on the returned results. The above content is for MySQL The introduction of Replication, hope you have some gains.