How to improve the performance of MySQL replication && multithreading transfer binary log

How to improve the performance of replication:

Delay:

For MySQL replication, in the absence of a failure, the master and slave data are out of sync, and the delay is divided into the following two scenarios:

Recurrent delay: The data gap between asynchronous synchronizations is larger, cyclical, and cyclic.

Transient delay: unexpected condition, resulting in delay

The main reasons are:

Network bandwidth

/ o

How to reduce replication delay??

1, it is best to use the intranet or line link transmission binlog data (Gigabit network card, not enough, bounding technology, extended bandwidth) in the MY.CNF to enforce the use of intranet IP transmission data bind-address=ip

2. Save the binary on a separate storage medium (boost I/O)

3, buy multi-core CPU, use multi-threaded way to transfer binary log ()

4, if the binary log is not the row format, then try not to insert or update when the use of SELECT, statement mode will give the master transmission of the binary time caused by a large pressure

5, try to reduce master's write I/O (memcached) (both write their own binary log, also responsible for reading their own binary log to the slave server), the lower the I.O on master, the faster the Binlog will be transferred to slave,

Add memcached cache layer, the database last made a buffer pool into memory, by memcached Management, periodic data synchronization to the database, the large concurrent write operations, combined into a small amount of write operations. This reduces master write I/O

Architecture Design 1:

Master-Slave server can use different storage engine, master using InnoDB, transaction, row lock and other advanced lock features, slave on the use of MyISAM, better read performance, save memory, easy backup. You can also use different data types, such as

Use char on Master on Varchar,slave. Not only saves space, but also uses the static table characteristics of the MyISAM.

In the M-s--muti slave scenario, the relay slave can also use the Blackhole storage engine, Blackhole storage engine value logging, and no write data, which can improve the performance of the trunk log, but this scenario does not support the Gtids mode

Replication, because blackhole can only be used with binary logs in statement format, row and mixed formats are not supported.

In the read/write separation, the main memory server uses different indexes with different schemes, master can only retain the primary key or unique index and other guaranteed data relations index, and slave for the query to do index optimization,

Architecture Design 2:

Let the update frequent, and need real-time data query put on master, and then through the persistent session, let the user who has changed to see the results, others wait for synchronization after the synchronization of replication,

If development is not done, use the following memcached to add memcached before the data

Let user changes to the database first saved to memcached, Memcached is an in-memory storage engine (independent third-party technology), due to high memory read and write performance, can be read and write very frequent update data here, every 5 minutes

, and then synchronize the data to master, for another client, let him first to the master to find data, that is, in the master memcached read the data, read slave. This allows the changed data to be returned by memory to the user in priority, which reduces

Reduce hard disk IO, for those already synchronized after the data separates him to slave, so that master can be done with the data update about the write operation, completely separate master read.

Dynamic cache is a kind of dynamic data cache that users request more frequently, so that subsequent access users can fetch data directly from memcached. memcached

Replication capacity:

Refers to: Replication delay degree,

Pause the replication for a period (M), re-open, and observe how long the slave can reach the master consistent (N)

Replication Capacity =n:m

It is recommended to keep capacity above 3 times times, i.e. 1:3 (1 hours m, 20 minutes N)

Multi-threaded Transfer binary log:

mysql5.6 start to support multi-threaded transfer of binary logs

Only works in Gtids mode

Only operations that are performed on different libraries can be used for multi-threaded transmissions. Operations with different tables in the same library can only be transferred individually with a single process.

My.cnf

[Mysqld]

Slave-parallel-workers=n default is 0 means no threads are turned on

N Depending on the CPU's number of cores to determine, a few cores write a few, and the number of databases one by one corresponding, several libraries are several threads to transmit binary logs, QPS tens of thousands of words, multi-threaded transmission will have a significant performance improvement

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1606236

How to improve the performance of MySQL replication && multithreading transfer binary log