Replication tutorial notes – part 2

This is a continuation of the MySQL User Conference replication notes part one.

The session is opening up talking about failover. The shared disk in this case is drbd. DRBD is a fine product for replicating block devices of single disk systems. It’s made redundant by raid and doesn’t provide as much protection as binary log failover. You can find my notes on why I don’t recomment DRBD for MySQL in drbd in the real world.

Lars went a bit quick through the other two configurations. I’ll try to review the slides and post comments.

The next configuration is using federated. The federated storage engine has many problems that make it almost useless for any production deployment. Mats says, “Federated isn’t the fastest engine in the world”. That’s an understatement. Join on two tables as they describe it is almost impossible. Aside from the performance issues this is my favorite limitation of the federated engine, “There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.”

Lars mentions Jeremy’s failover design that is recommended by Proven Scaling. Thanks Lars!

There is a lot of confusion about the difference between row based and statement based replication. Hopefully my row based replication post can clear up some of the confusion.

For keeping things simple and easy to debug I recommend sticking with either row or statement based replication. Teaching the implications of each to application developers is going to be more difficult than sticking with one model. The exception is things that can’t use row based replication like DML statements.

When using a SAN make sure you have redundant SANs, your backups aren’t on the same SAN as mysql and that if you have to use a shared san you have good control over the resources. MySQL is very sensitive to i/o latency increases. If someone else does a large operation on the SAN it can increase the i/o latency in mysql causing operations to take longer which can bring down your application.

I don’t recommend using a hardware load balancer to manage write load between masters. There is a risk of sending writes to both masters at the same time. In a properly configured dual master setup half the writes will be rejected on a read only error. The worst case is that writes go to both masters causing replication error and inconsistent data.

The tutorial is wrapping up now. I look forward to using row based replication 5.1. Thanks Lars and Mats.

4 Comments

  1. Olly says:

    Nice notes, I doubt I could write such good notes while sitting in a presentation.

    Found this here quite confusing: “The exception is things that can’t use row based replication like DML statements.”

  2. Eric Bergen says:

    What I mean is that if you choose to stick to row based replication there are a few types of queries that are going to be replicated as statements such as alter,create,drop table. This is unavoidable.

  3. Todd Lyons says:

    “I don’t recommend using a hardware load balancer to manage write load between masters. There is a risk of sending writes to both masters at the same time. In a properly configured dual master setup half the writes will be rejected on a read only error. The worst case is that writes go to both masters causing replication error and inconsistent data.”

    Why does configuring the offset setting for master-master configurations not solve this? Is there some inherent problem with master-master that will only be reached when you scale up in size and/or throughput?

  4. Eric Bergen says:

    The problem is that there is no longer a single source of truth for the data. If you restore a master from a backup then one master has records that aren’t on another. It can get very messy very quickly. It’s better to partition masters and have a single source of truth for each record at all times.

Leave a Reply