Database structure setup

Question

We're currently in the process of moving our hosting services to something which will allow us to scale our infrastructure and requirements.

Essentially we're using MySQL to:

• Run our websites.
• Run our blog
• Store millions of rows which will require some heavy queries, but will be cached afterwards.

Our current idea is to setup two MySQL servers, on a master to master replication, with HAProxy on top to load-balance the requests.

As we've never dealt with replication before our main concerns are the following:

• How quickly does replication work?
• What happens if one of the servers go down? Would we change HAProxy to use just the one server?
• How does replication deal with loading and replicating millions of rows each day? This would happen once a day.

Thanks for any help!

Answer 1

In general, actively writing to multiple masters in MySQL is a bad idea.

MySQL Replication is asynchronous, which means there's no guarantee the changes make it to the slave before the changes are committed on the master.

The implication is that something™ (node dies, slave lag, etc) can/will go wrong with one of the masters at any time. If your application and business rules can't handle this appropriately, you are in for some pain.

To answer your bullet points:

• "How quickly does replication work?"

As fast as your server resources allow: network/IO/cpu/memory.

• "What happens if one of the servers go down? Would we change HAProxy to use just the one server?"

Really depends on your HAproxy config, but a typical config would handle it automatically.

• "How does replication deal with loading and replicating millions of rows each day? This would happen once a day."

Depends mainly on your hardware and configuration, and the answer here is: try it out. I've seen many GB loaded with minimal lag, and I've seen it cause hours of lag to apply on the slave. The real question is: can your application handle lag that arises from this, whether it's seconds or hours? Remember, slave applying is serial (until 5.6 with multiple slave threads), so lag would affect all tables until it's caught up.