1

I'm setting up a database which needs to be needs to provide High Availabilty.

My primary concern is high performance and robustness (I don't want something that will fail fast and badly). The database is accessed by the application at an average of 300 qps. It's will run on Xen VMs and it has some InnoDB tables as well as MyISAM tables. The VMs are connected via ethernet 100Mbit/s ethernet cables.

Which of the two - MySQL replication or DRBD - would you recommend in such a situation?

Or should I use DRBD to make the master database Highly Available and use MySQL replication on the slaves?

I'm a developer so these things are all not so easy for me to make a sound judgement.

MrD
  • 235
  • 5
  • 11

3 Answers3

0

If you plan to have an active passive scenario, replication is quite easy to setup and doesn't need a shared drive. It will run as master - slave setup, where the slave connects to the master for updates.

There are a few things to keep in mind for this:

  • do you want to have automatic failover or manual?
  • you need to monitor the replication status if you want to do it manually?
  • this is done at the database layer so you can do it over the internet.

For DRBD, you will implement the solution at block device level. Basically you write to a hard drive that is replicated to a different machine. You can achieve HA by using a heartbeat.

Some things to keep in mind:

  • this is done at block level so you don't have that much control over it. With replication you can skip offsets.
  • the drbd adds a layer of complexity.

In the end comes down to what you need and how you use the database.

Wim Kerkhoff
  • 901
  • 1
  • 5
  • 12
silviud
  • 2,687
  • 2
  • 18
  • 19
0

Replication with monitoring and auto failover is much easier to implement and maintain than a DRBD based solution, especially if you're not used to working with raw devices. If you have application servers that are read only db clients you can run a master-master setup and have twice as many clients (connections) from front or middle tier machines...

Most reliable and scalable would probably be MySQL clustering, but that takes, realistically, at least 4 db hosts to implement well. I'd run plain MySQL replication with monit, an 8 core box can easily do 10k qps in my experience when mysql is tuned properly, and system load is virtually nill, of course fast disks and tons of RAM help. In fact, with Inno and MyISAM together you'll want fast disks, and be sure to do table maintenance regularly.

dixie_flatline
  • 137
  • 3
0

There are advantages and disadvantages to both.

MySQL replication can be added to any existing setup fairly easily, and does not require fancy block devices. However, the replication can easily break due to key conflicts and so on. Make sure that nobody can write to your slaves. Set read_only in the my.conf file to guarantee that nobody can change tables on the slave. What I have found works best is to setup Master-Master replication but run one node in read_only mode.

If the replication breaks due to broken SQL query, then you will need to revert to a known good state or just recopy over all the tables from the master.

The advantage of master-slave configuration is that you can also send read queries to the slave for both redundancy and performance. You can also easily promote it to master.

Wim Kerkhoff
  • 901
  • 1
  • 5
  • 12