Shulou(Shulou.com)06/01 Report--

The basic principle of master-slave replication of MySQL is to connect from the library to the master library, and the master library generates a master library DUMP thread. The main task of the DUMP thread is

The binlog log is mined all the time, and then sent to the IO thread of the slave library. After receiving the log stream, the IO thread writes to relay log, another line.

The program relay log thread reads the contents of the sql and then replays the SQL statement.

The main library DUMP thread reads the contents of the binlog file according to the file location information sent from the library, but the DUMP thread does not go to the

Read, and synchronization occurs when the binlog log is written on the main library, then the DUMP thread reads the binlog immediately according to the synchronization mechanism

Documents. When the main library goes to write the binlog, the DUMP thread reads it, and the problem comes quickly, which may lead to read-write conflicts, and sometimes we

This situation is also called "IO jitter". If our server is configured with RAID's cache or uses the file system's cache, when a write operation

When you do it, you may not actually write it to disk, but write it to cache, so if you read it again, it will be directly from cache.

Just read it.

If the master library has multiple slave libraries, the DUMP thread and the server's write binlog thread, read and write contention between DUMP thread and DUMP thread will be more frequent.

Complex, if you use SSD storage, this situation can be well alleviated.

When the DUMP thread receives the synchronization event and starts to perform the DUMP operation, there should be no overload of CPU on the main library, leaving the DUMP thread in the

The waiting time in the run queue is too long.

For libraries that need to be copied by binlog, we use binlog_do_db in the main library and avoid generating binlog for all library operations. But I.

You need to test carefully when using this parameter, as some applications may write libraries in a way that may result in binlog data loss.

The DUMP thread of the master library is sent to the IO thread of the slave library over the network, and the DUMP thread itself does not provide compression, so sufficient bandwidth becomes heavy at this time.

To make up for the deficiency, especially for the transmission across the public network, the compression function of the network equipment can be used at the network level.

When the IO thread receives the binlog, it writes data to the relay log, the speed at which it is stored and whether it is synchronized to disk immediately after each receipt

The relevant parameters become extremely important to the speed of IO thread processing. For example, the three parameters sync_relay_log,sync_master_info and sync_relay_log_info

Specific values may have to be adjusted according to the environment. For example, if sync_relay_log is set to 0, it does not write to disk directly after receiving data, but relies on OS to refresh it to disk.

The principle of the SQL thread is very similar to that of the DUMP thread. When there is a relay log log write, synchronization occurs, then the SQL thread will read the data from it.

Replay it. An important improvement in MySQL 5.6 is the ability for multiple SQL threads to work at the same time, which increases throughput. You can set slave_parallel_workers

To achieve this goal. From other parameters on the library, such as innodb_flush_log_at_trx, etc., although it will speed up the throughput of the sql thread, it may need to be condensed.

Not just for SQL threads.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.