innodb_flush_log_at_trx_commit

[mrkamel]

Hi,

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?

BTW is removing grastate.dat the best way to force a SST like mentioned here: http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/9192-problem-both-nodes-not-sync

[taka-h]

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.

[mrkamel]

Hi, thanks for your reply. However, I don’t know if i understand correctly.

My current knowledge is:

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

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).

Wrong?

Check out this as well: http://galeracluster.com/documentation-webpages/configuration.html#optional-mysql-settings

“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.”

[taka-h]

Hi, thanks for your reply

I’m sorry, I think what you pointed out may right.

I’m also checking grastate.dat sync timing on the code.

[taka-h]

Related Issue
https://blueprints.launchpad.net/percona-xtrabackup/+spec/galera-gtid-autorecovery

[mrkamel]

Check https://groups.google.com/forum/#!searchin/codership-team/innodb_flush_log_at_trx_commit/codership-team/Ywlcy6kOUHc/NZia51TdV5gJ as i asked there as well

[taka-h]

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

Thank you.