durability of commits

[L. Overbeck]

Hi everyone,

I have a question about the durability of commits when using Galera
Cluster.

I know that when using MySQL Cluster a successfully commited
transaction means that the changes (i.e. the log files) made by this
transaction have been replicated among the nodes, but it does not mean
that the changes have been flushed to disk on any of the nodes (this
happens asynchronously). This is not acceptable for really critical
data, because e.g. an electrical power outage might cause the loss of
some transactions.

When using InnoDB, the parameter innodb_flush_log_at_trx_commit should
be set to 1 in order to avoid the problem described above. Is this
also true for Galera Cluster, i.e. are all changes (in the form of
write sets or however) flushed to disk on all nodes or what the is the
guarantee given here when a transaction has been commited
successfully?

The other thing that I do not like about MySQL Cluster is that it only
supports the isolation level commited read, whereas what I need would
be repeatable read. But I think this should not be a problem, as this
is up to InnoDB, right?

[Henrik Ingo]

Hi Ludger

On Mon, May 21, 2012 at 7:27 PM, L. Overbeck
<ludger....@googlemail.com> wrote:
> I have a question about the durability of commits when using Galera
> Cluster.
>
> I know that when using MySQL Cluster a successfully commited
> transaction means that the changes (i.e. the log files) made by this
> transaction have been replicated among the nodes, but it does not mean
> that the changes have been flushed to disk on any of the nodes (this
> happens asynchronously). This is not acceptable for really critical
> data, because e.g. an electrical power outage might cause the loss of
> some transactions.
>
> When using InnoDB, the parameter innodb_flush_log_at_trx_commit should
> be set to 1 in order to avoid the problem described above. Is this
> also true for Galera Cluster, i.e. are all changes (in the form of
> write sets or however) flushed to disk on all nodes or what the is the
> guarantee given here when a transaction has been commited
> successfully?

As Galera works with InnoDB, this setting continues to work as usual.
In fact, you can choose:

- set innodb_flush_log_at_trx_commit=0 (or =2) and transactions are
not committed to disk, but they are durable in the sense that they
have been synchronously replicated to multiple nodes. This is the same
as in MySQL Cluster. Personally I recommend this settting.

- set innodb_flush_log_at_trx_commit=1 and transactions are flushed
to disk when committing, but only on the one node where the
transaction was executing. It is also replicated to all other nodes at
commit time, but not yet written to disk.

So you can choose whichever you feel is better. Philosophically, if we
want to argue which is better, safer or more durable, we will
eventually have to accept that durability is a relativistic concept.
Many people feel safe when data is written to disk, but what if the
disk breaks? What if the controller is broken and is writing garbage
to the disk? What if there is an InnoDB bug and the tablespace is
corrupted?

Heikki Tuuri (creator of InnoDB) once said: "Durability is an
interesting concept. If I write a commit to disk, it is said to be
durable. But if I then make a backup, it is even more durable."

> The other thing that I do not like about MySQL Cluster is that it only
> supports the isolation level commited read, whereas what I need would
> be repeatable read. But I think this should not be a problem, as this
> is up to InnoDB, right?

Yes.

But again, note that semantics are different when using a distributed
cluster. You can set "READ UNCOMMITTED" isolation level, but you will
obviously not see uncommitted data from other nodes than the one you
are executing on. In practice this is not a problem, just wanted to
point out that isolation levels are a bit different if you look at it
cluster-wide.

If you only execute transactions against a single node (and others are
hot spares) then InnoDB behavior is exactly the same as in standalone
MySQL.

henrik


--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559