The latest from the MySQL community

After having reviewed the definition my the previous post (The definitions), the next step is to respond to some questions.

Do you need MySQL High-Availability?

That question is quite obvious but some times, it is skipped. It can also be formulated "What is the downtime cost of the service?". In the cost, you need to include lost revenue from the service and you also need to consider less direct impact like loss of corporate image and other marketing costs. If your downtime cost is under $10/h, you can stop reading this document, you don't need HA. For the others, let's move on!

How to determine which MySQL High-Availability solution is best?

What is really tricky with MySQL is the number of possible HA solutions. From the simplest the most complex let's list the most common …

As my first contribution to the MySQL Performance Blog, I joined Percona at the beginning September, I chose to cover the various high-availability (HA) options available for MySQL.  I have done dozen of MySQL HA related engagements while working for Sun/MySQL over the last couple of years using Heartbeat, DRBD and NDB cluster and I'll probably be doing the same at Percona.  I have built my first DRBD based HA solution nearly 10 years ago.

There is quite a lot of confusion surrounding HA solutions for MySQL, I will try to present them objectively, my goal here been not to sell any specific technology but to help people choose the right one for their needs.  This post is first of a series,  I don't yet know how many I will write in the series.

Before we start, it must be stated that high-availability is not only a matter of technical solutions, good management practices covering monitoring, alerting, security and …

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …

One of the better kept secrets about MySQL Cluster appears to be the flexibility available when setting up replication. Rather than being constrained to implementing a single replication scheme, you can mix and match approaches.

Just about every Cluster deployment will use synchronous replication between the data nodes within a node group to implement High Availability (HA) by making sure that at the point a transaction is committed, the new data is stored in at least 2 physical hosts. Given that MySQL Cluster is usually used to store the data in main memory rather than on disk, this is pretty much mandatory (note that the data changes are still written to disk but that’s done asynchronously to avoid slowing down the database).

MySQL Cluster Replication

MySQL asynchronous replication is often used for …

MySQL HA: 1. Desirable – most of the time, 2. Needed – often enough, 3. Available – there are some good options out there. Typical solutions consist of: Heartbeat with DRBD on Linux. Although HB was introduced to Linux, it can be used on Solaris, FreeBSD and others without the DRBD but with other solutions. Sun Cluster, Veritas (commercial), MySQL Proxy (still in [...]

Percona presented two talks at PHP Quebec last week - one on A Tour of MySQL High Availability, and another on Performance Tuning MySQL. There was a great reaction to showcasing some of the quick-wins that can be found by using the Percona patches. Unfortunately, the one thing that I forgot to mention in the slides is that the patches are Open Source and free to use.

Entry posted by morgan | 4 comments

Add to: | …

Wow, check out what just came out from Linbit: The DRBD Management Console. Written in Java (so it runs anywhere), completely open source (GPLv3), and allows you to manage DRBD and Heartbeat based clusters. You can install, configure, see your systems graphically, and a lot more. I’m interested to try the beta out, as soon as I get back to my lab (sitting in the airport now). If you know how to use DRBD/Heartbeat, and use it in production for your MySQL setup, it might be a good application to test out, and improve if need be.

From the screenshots, I’m surprised this isn’t a value added extra that Linbit would like to charge for. Kudos, Linbit, for keeping it GPLv3!

…

 Our latest article over at Database Journal is hot off the presses.  Advanced MySQL Replication – Improving Performance discusses some of the best ways to improve the performance of your MySQL slave setup.  If the slave is constantly getting further and further behind the master database, we discuss a number of techniques which may help you bring it under control.

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER TABLE, for example).

The benefit is that you have another server with all the same data, up and running, ready to serve queries. In theory, it's a truly hot standby (stay with me -- that's not really guaranteed). You don't get this with shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby (passive) master for serving some SELECT queries, taking backups, etc as …

I was just talking to someone who backs up their MySQL servers once a day with mysqldump, and I said in a catastrophe, you’re going to have to reload from a backup; that’s some amount of downtime, plus up to a day of lost data.

And they said “We can just promote a slave, we’ve done [...]