问题描述:

I understand that mysql replication uses two different threads on slave -

  1. Slave I/O thread to read binlog from master and write it to local relay log
  2. Slave SQL thread to execute the sql statements from the local relay log. This thread is for executing update, delete and create sqls.

What about select queries on slave? Can SELECT queries interfere with the replication process? Or is there a different thread that execute SELECT queries?

I mean, can slow select queries on slave make the replication to lag behind master ?

网友答案:

In short queries can interfere with the replication it is not the threading that matters here but the locking being applied (ACID vs. threading). A update query from the master that is being replicated to the slave can be blocked by a select query on the slave. However the replication sub-system will deal with these query locking issues most of the time. If you don’t mind dirty reads you can set the transaction serialization isolation level on the slave to something less restrictive to mitigate the risk. However make sure dirty reads is acceptable see this link for more information: http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

You are concerned about lag and that is not something you can eliminate in any replication schema there will be lag. There is almost always a network in between the master and slave. This will introduce lag right from the start. For example large replicated statements could saturate the network bandwidth and this is probably going to happen more often than a query blocking the replication. Replication never was and never will be instantaneous. So your point about lag can be answered like this lag is something you HAVE to deal with not something you can completely eliminate.

Dont get me wrong replication can be fast but it is NEVER instantaneous.

Another thing to keep in mind is that replication can fail and you need to plan for that as well. It will happen at some point and being prepared for it is essential. So basically you will have lag no matter how you do replication and you need to be able to deal with it. Also be prepared for replication failing at some point and how you will recover from it.

While replication can be useful in many places you need to make sure you are preparing for it on many levels such as adequate network infrastructure, how to deal with it during disaster recovery(failover), monitoring it during production and how to get it back online when it breaks.

相关阅读:
Top