I’d like to set innodb_flush_log_at_trx_commit = 0 or 2.
is this setting “safe” for xtradb cluster, since the data is (virtually) syncrously replicated?
Especially, in case a node with innodb_flush_log_at_trx_commit = 0 crashes,
do i have to force a SST? or will IST work correctly even if innodb_flush_log_at_trx_commit = 0?
Do i miss any other implications of innodb_flush_log_at_trx_commit = 0 or 2 regarding xtradb cluster?
Hi, I think It’s not safe and innodb_flush_log_at_trx_commit=1 would be recommended.
we may lost write ahead log (ib_log) maximum 1 seconds
gcache write is ahead of log flushing so the other nodes sometimes may progress by the crashed node.
So, we can’t guarantee we can recover by IST (we can’t recovery node with keeping consistensy when crashing recovery)
SST may be recommended at the situation from the perspective of data consistency.
And i’d assume: if it is recovered from innodb, it should be safe to use, either fsync’d (innodb_flush_log_at_trx_commit=1) or not (innodb_flush_log_at_trx_commit=0 or 2).
“Compared with the default value 1, you can achieve better performance by setting the value to 2, but an operating system crash or a power outage can erase the last second of transactions. However, this risk is handled by synchronous replication—you can always recover the node from another node.”
thank you for your information. It was very helpful.
I misunderstanded position recovery of the galera.
I also confirmed trx_sys_read_wsrep_checkpoint function can get the global sequence number even if database crashed