Strengths of Master – Blackhole – Slave – Slave setup:

• Limits the number of replication threads on the master.
• Death of a slave does not affect the system.
• Slaves can be cheaper commodity boxes.

Weaknesses:

• Death of the master means slaves must be reconfigured even with failover.
• A large number of writes makes slaves as busy as master.
• Execution of DDL statements on the master that fail may succeed on the blackhole server and thus be incorrectly replicated (e.g. A foreign key constraint fails because on parent record exists on the master will not exist on the blackhole slave).
• Blackhole is not transactional, thus if InnoDB is used, any rollback will not occur. This has the potential to corrupt the secondary slaves.
• Extra moving part.

Recommended Environment:

• OLTP applications where replication is dying because the Slave I/O thread is dying.

For extremely busy applications using a large number of slaves, the resources on the master necessary to send the binary log to the slave(s) can lead to trouble. This is especially true under peak loads.

The solution is to use an intermediary slave implementing all tables in the blackhole storage engine. The blackhole storage engine does not make DML writes. Thus no resource or disk activity occurs other than writing the relay and binary log. From the binary log, each of the slaves (12,13,14) in the diagram can connect to blackhole and read the binary log while only one thread for the blackhole slave is created on the master. This means there is only one thread on the master while the blackhole slave (11) manages the 3 threads from the slaves.

From a performance perspective this is the best you can expect from replication. If more performance is required, then it is time to begin considering the NDB storage engine and MySQL Cluster.

To avoid using the extra machine required by this setup, it is possible to place the blackhole slave on one of the slave boxes.