Is innodb_flush_log_at_trx_commit=0 safe for wsrep-cluster?

[Oleksandr Drach]

Hello!

I am wondering how setting "innodb_flush_log_at_trx_commit=0" value may increase MySQL performance.  But on single MySQL-server instance it may lead some data loss (hey, only 1 last second, it's almost nothing :)).

But as I have wsrep-cluser of 4 or 5 servers, is not it absolutely safe to set "innodb_flush_log_at_trx_commit=0" in order to increase performance? Reliability here will be guaranteed by another wsrep nodes as I suppose.

What are your opinions?

Thanks!

[Alex Yurchenko]

Hi Oleksandr,

*As long as you have more than one node* in the cluster running, it
certainly is safe, and is one of synchronous replication selling points:
with rotational disk drives it is faster to send data to another node
than to flush it to disk, perhaps even in WAN case.

Moreover, using innodb_flush_log_at_trx_commit=1 is totally pointless
in Galera cluster because recovery happens from another node. Missing a
few transactions in this case makes no difference and in case of SST
will be completely ignored.

However, innodb_doublewrite should be on at all times!

Regards,
Alex

On 2013-01-25 15:26, Oleksandr Drach wrote:
> Hello!
>
> I am wondering how setting "innodb_flush_log_at_trx_commit=0" value
> may
> increase MySQL performance. But on single MySQL-server instance it
> may
> lead some data loss (hey, only 1 last second, it's almost nothing
> :)).
>

> But as I have *wsrep-cluser of 4 or 5 servers, is not it absolutely

> safe to
> set "innodb_flush_log_at_trx_commit=0" in order to increase

> performance*?

> Reliability here will be guaranteed by another wsrep nodes as I
> suppose.
> What are your opinions?
>
> Thanks!

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Oleksandr Drach]

Great to know it's safe for MySQL-wsrep. It makes queries much-much faster!

Additional thanks for pointing out importance of "innodb_doublewrite"!

[Alex Yurchenko]

On 2013-01-25 22:58, Scott Haas wrote:
> Alex -
> I was looking at codership wiki (
>
> http://www.codership.com/wiki/doku.php?id=info#configuration_and_monitoring)
> and noticed this:
>
> *Optional settings (these are just optimizations made relatively safe
> by
> synchronous replication — you always recover from another node):*
>
> 1. *innodb_flush_log_at_trx_commit=2
> *
>
>
> I assume it's just outdated information in the wiki. I just want to
> make
> absolutely sure that 0 is the correct setting before changing my
> configuration.

Scott, it is not outdated. We do recommend 2 as a more conservative
setting, since normally there seems to be little performance difference
between 0 and 2. Sorry I didn't clarify it in my previous response - I
don't regard the difference between 0 and 2 substantial as the disk
flush behaviour is identical. But we'd be interested to hear if people
have observed any performance issues with 2.

> Thanks,
> scott

[Henrik Ingo]

Scott, others

It may not be clear to everyone:

Both 0 and 2 are "unsafe" when used with a single MySQL server, but
become safe when using a Galera Cluster. The difference is that with 2
InnoDB is guaranteed to flush at least once per second, while with 0
it may flush even less than that. Like Alex says, in practice there
doesn't seem to be much of a difference between 0 or 2. (I've
personally tested very disk-heavy, write-heave workloads and no, there
is no difference.)

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

[Benjamin Vetter]

Hi,

I'd like to know more about how this works for IST.

AFAIK for IST to work we need a starting position, which can be read from grastate.dat (in case of a graceful shutdown)
but can get recovered from innodb directly as well http://www.mysqlperformanceblog.com/...tradb-cluster/

So, after a crash, the node recovers the position from innodb (how does the node knows it crashed, btw?) and as it is recovered from innodb,
it is safe to use, either fsync'd (innodb_flush_log_at_trx_commit=1) or not (innodb_flush_log_at_trx_commit=0 or 2). Correct?

Thanks
  Benjamin

[Daniel Black]

----- Original Message -----
> Hi,
>
> I'd like to know more about how this works for IST.

Keep a big gcache and galera will work out if it can recover from SST or whether IST is sufficient.

> AFAIK for IST to work we need a starting position, which can be read from
> grastate.dat (in case of a graceful shutdown)

yes.

> but can get recovered from innodb directly as well

not afaik.

> http://www.mysqlperformanceblog.com/...tradb-cluster/
> <http://www.mysqlperformanceblog.com/2013/01/31/feature-in-details-incremental-state-transfer-after-a-node-crash-in-percona-xtradb-cluster/>

>
> So, after a crash, the node recovers the position from innodb (how does the
> node knows it crashed, btw?)

Yes.

> and as it is recovered from innodb,
> it is safe to use, either fsync'd (innodb_flush_log_at_trx_commit=1) or not

Is there a chance that all galera nodes will fail due to the same circumstance (e.g. power, common network infrastructure, all on same VM)?

If so keep to =1 or accept the risk/consequences.


> (innodb_flush_log_at_trx_commit=0 or 2). Correct?

http://openquery.com.au/blog/innodbflushlogsontrxcommit-galeracluster