In databases, replication is often used to provide high availability. However, the replication process can load the basis of the server (master). In addition, even the same resource replica may lag behind the master. Let’s see, how it works.
Monitoring
After setting up replication, you should constantly monitor several parameters on the slave:
mysql -e "SHOW SLAVE STATUS \ G"
# Request will return statistics and slave settings
It is worth paying attention to such indicators:
Slave_IO_State: Waiting for master to send event ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Last_Errno: 0 Last_Error: ... Seconds_Behind_Master: 0 ...
# Replica work status
The Slave_IO_Running and Slave_SQL_Running metrics reflect the normal behavior of the replica. Both must be set to Yes. When one of these metrics is No, the text of the replication error will be visible in Last_Error.
The Seconds_Behind_Master parameter reflects the number of seconds by which the slave is behind the master. This indicator should be equal to zero (sometimes it can grow up to several seconds).
Disabling sync_binlog
The sync_binlog parameter defines the logic for synchronizing data from the binlog with the disk. If the value is 1, writing to the disk will occur after each transaction. This makes the storage very reliable, but it loads the disk subsystem on the master extremely hard.
A value of 0 will disable synchronization from Mysql, and the database will rely on the OS to write the log to disk. This value can increase the performance of the master in several times.
You can check the current value in such way:
mysql -e "show variables like 'sync_binlog'"
# Check the binlog synchronization mode
+ --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | sync_binlog | 1 | + --------------- + ------- + # Sync is better to disable You can disable synchronization without restarting the server, for this it is enough to do next: SET GLOBAL sync_binlog = 0; However, do not forget to fix this parameter in my.cnf too, so that it remains after reboot: ... [mysqld] sync_binlog = 0 ...
In .io, we always disable binlog synchronization. This increased the throughput of our Mysql nodes by 2 … 3 times, unloading the disk subsystems of the masters.
Multithreaded replication
Until recently, the Mysql replica worked in just one thread. Then, even if the master and slave are identical in characteristics, the slave can still lag behind the master.
Mysql 5.6 introduces concurrent replication support. It allows the slave to process the binlog in parallel in several threads. This mode is enabled by setting in my.cnf on the slave:
... [mysqld] slave-parallel-workers = 2 ...
# Enable replication in 2 threads
The number sets the number of threads and can take values from 2 to 1024. A value of 0 will disable multithreaded processing of the binlog.
When you enable this setting, Mysql will distribute the binlog processing of different databases between different threads. This will enable Mysql not to wait for the completion of operations in different databases, but to execute them in parallel. You do not need to configure anything on the master.
It is clear that if you have only one database, you will not get a performance gain. But in version Mysql 5.7 for, you can change the type of distribution of operations using the settings in my.cnf:
... [mysqld] slave-parallel-workers = 2 slave-parallel-type = LOGICAL_CLOCK ...
# Changes in parallelization type of binlog processing
In this case, already all operations (more precisely, committed transactions) from the binlog will be processed in parallel.
TL; DR
Monitor Seconds_Behind_Master on a slave, it should be equal to zero.
Disable binlog synchronization on the wizard: sync_binlog = 0.
Use parallel processing of the binlog on the slave: slave-parallel-workers = 2 (or another number of threads) in new versions of Mysql.