I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.
In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.
The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.
Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.
I recently ran into a situation where a Delete across a large time period was taking an extensive amount of time to run when replicated to the Slave, although the query ran very quickly when running on the Master. On the Master, the query took
After much head scratching and verifying that indexes existed on both Master and Slave, we took a few minutes to compare the query execution plan using EXPLAIN. It was quickly obvious that the Slave was using a different index than the Master when executing the query. We quickly update the table statistics using Analyze Table, and re-ran EXPLAIN. After the Analyze Table, the problem was solved, and after restarting replication using Start Slave, the offending query quickly executed in 2 minutes.
Moral of the Story: Running Analyze Table on larger tables every couple days is probably a good idea.
