MySQL replication is generally well-organized, but if a modification occurs then it will appear in the master server. When you experience any replication issue that is unable to lock the gap, the data administrator requires identifying and solving the issues in the replication.
Two threads, namely the IO_thread and the SQL_thread, function in the master server of MySQL. They help in reading the binary log events and copy the master file to a log file that is local in nature. This local log file may also be called as the relay log. Now, the SQL_thread goes through the events of the relay log and if they are identified as running slow, the threads start their action as soon as possible. Thus, it is important to identify whether the SQL_thread or the IO_thread is running slow by replication.
Since the IO_thread is just going through the master binary logs, it will not be the main cause for the delay. But sometimes the network latency or the connectivity may be the root cause for the slow links. The use of a high bandwidth can be another cause in the case of the slave I/O thread. The SQL_thread can also have replication delay issues. As the thread is busy with all the queries in the stream of replication, sometimes a delay can occur in executing the slave.
Various hardware can be present within the master and the slave with varying workloads and indexes. The OLTP workloads can cause a delay in the locking process. The SQL thread lag can be calculated by the command READ_Master_Log_PoS-Exec_Master_Log_PoS from the master log file parameter. The result from the Seconds_Behind_Master can be misleading since it only calculates the difference between the relay log timestamps and the entry of the relay log. In this case, the configuration option log_slow_slave_statements can be enabled so that the slow queries will be gathered in the slow log.
Let us discuss the main reasons of the replication.
Network With High Latency or Low Bandwidth: The high latency occurs when two servers are located in distant places. The servers can also have a low bandwidth with a high traffic volume. Thus, the traffic within the servers can be decreased by the methods provided below.
- Replication with Statement: The SQL statements can be created with the row based applications where the rows can be easily changed in the database. The application can send the original SQL statement in the replication based on the statements. The statement based replications are much more efficient than the log events. But if you are using UPDATE…LIMIT 1, the function may not properly activate.
- Traffic Minimization: The traffic in the server can easily be controlled by MySQL commands. The parameter slave_compressed_protocol can be used with the log replication. This will result in the decrease in traffic between the servers by almost 80%. Since an extensive computation is required, the CPU can be utilized with some extra efforts. Therefore, the parameters can easily be utilized in both the cases:
- SET GLOBAL slave_compressed_protocol=1 can be used as one of the MySQL commands.
- The configuration file in MySQL can also go like #compress master-slave communication slave_compressed_protocol = 1.
Issues In Slave Disc: When the changes appear, the slave lags occur due to the disc latency. The replication contains only one thread in a single database. Therefore a SSD can be used for accelerating the process in this case.
Though the lagging slave is a difficult problem, it can be resolved if the right process can be applied at the right time. You need to understand the actual reason then solve all the issues regarding it.