From 8b2d4047774ead6223adf2b98e30b9e6ca9ca3d2 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 3 Jul 2017 10:35:30 -0400 Subject: Add option for innodb_flush_log_at_trx_commit = 2 for Galera only The innodb_flush_log_at_trx_commit flag changes the timing of when the log buffer is written to disk for writes. At its default of 1, transactions are written to disk and the buffer flushed on a per-transaction basis; but when set to 2, the flush of the buffer proceeds only once per second. This removes the durability guarantee for the single node. However the central concept of Galera is that durability is achieved via the cluster as a whole, in that transactions are replicated to other nodes before the commit succeeds (though not necessarily written to disk unless wsrep_causal_reads is set). In this model, data would only be lost of all nodes of the Galera cluster were killed within one second of each other. Percona's blog post at https://www.percona.com/blog/2014/11/17/typical-misconceptions-on-galera-for-mysql/ recommends that the value of 2 should be considered "safe" for a Galera cluster unless you are in fact worried that all three nodes will be powered off simultaneously. The value here is added as an option only, defaulting to the usual default of "1", flush per transaction. Change-Id: Id5a30f1daf978e094a74db2d284febbc9ae64bb3 --- ...innodb_flush_log_at_trx_commit-eb7d99749ca3c911.yaml | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 releasenotes/notes/innodb_flush_log_at_trx_commit-eb7d99749ca3c911.yaml (limited to 'releasenotes') diff --git a/releasenotes/notes/innodb_flush_log_at_trx_commit-eb7d99749ca3c911.yaml b/releasenotes/notes/innodb_flush_log_at_trx_commit-eb7d99749ca3c911.yaml new file mode 100644 index 0000000..7dbd5a7 --- /dev/null +++ b/releasenotes/notes/innodb_flush_log_at_trx_commit-eb7d99749ca3c911.yaml @@ -0,0 +1,17 @@ +--- +features: + - | + Enable innodb_flush_log_at_trx_commit configuration for Galera only. +upgrade: + - | + Setting the innodb_flush_log_at_trx_commit flag to the value of "2" instead + of its default value of "1" means that the underlying MySQL/MariaDB engine + will no longer flush transactions to disk on a per-transaction basis; + instead, flushes occur once per second. This leads to far fewer + disk writes and can dramatically improve write performance, at the cost + of durability (e.g. will lose the last second's worth of transactions) + if the database engine is ungracefully shut down. The + clustered nature of Galera mitigates this risk in that transactions + are replicated to other nodes before completion, and the setting of + "2" is considered to be generally safe for a Galera cluster, with the + exception case of simultaneous power loss for all nodes. -- cgit 1.2.3-korg