MySQL Replication tuning is generally focused on preventing slave servers from falling behind. This can be an inconvenience or a total disaster depending on how reliant you are on consistency (if you are completely reliant on consistency, of course, MySQL Replication is not the solution for you).
In this chapter, we focus on tips for preventing slaves from "falling behind" the master.
INSERT SELECT
is a common and convenient SQL command, however, it it is best avoided by using MySQL Replication. This is because anything other than a trivial SELECT
will significantly increase the load on the single thread running on the slave, and cause replication lag. It makes far more sense to write a SELECT
and then an INSERT
based on the result of this request.
MySQL replication, as discussed in detail in Chapter 5, High Availability with MySQL Replication, uses one thread per discrete task. This unfortunately means that to prevent replication "lag", it is necessary...