# innodb\_flush\_log\_at\_trx\_commit **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [July 17, 2014, 9:09am UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621 "2014-07-17T09:09:30Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![mrkamel](https://avatars.discourse-cdn.com/v4/letter/m/6bbea6/32.png) [@mrkamel](https://forums.percona.com/u/mrkamel)\ **Post date:** [July 17, 2014, 9:09am UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/1 "2014-07-17T09:09:30Z")
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: [url][http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/9192-problem-both-nodes-not-sync[/url]](http://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/9192-problem-both-nodes-not-sync%5B/url%5D) ---
**Author:** ![taka-h](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/taka-h/32/1023_2.png) [@taka-h](https://forums.percona.com/u/taka-h)\ **Post date:** [July 17, 2014, 4:40pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/2 "2014-07-17T16:40:44Z")
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. ---
**Author:** ![mrkamel](https://avatars.discourse-cdn.com/v4/letter/m/6bbea6/32.png) [@mrkamel](https://forums.percona.com/u/mrkamel)\ **Post date:** [July 18, 2014, 2:21am UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/3 "2014-07-18T02:21:40Z")
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 [URL=“[Incremental state transfer after a node crash in XtraDB Cluster](http://www.mysqlperformanceblog.com/2013/01/31/feature-in-details-incremental-state-transfer-after-a-node-crash-in-percona-xtradb-cluster/)”][http://www.mysqlperformanceblog.com/...tradb-cluster/[/URL]](http://www.mysqlperformanceblog.com/...tradb-cluster/%5B/URL%5D) 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: [URL][http://galeracluster.com/documentation-webpages/configuration.html#optional-mysql-settings[/URL]](http://galeracluster.com/documentation-webpages/configuration.html#optional-mysql-settings%5B/URL%5D) “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.” ---
**Author:** ![taka-h](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/taka-h/32/1023_2.png) [@taka-h](https://forums.percona.com/u/taka-h)\ **Post date:** [July 21, 2014, 6:59pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/4 "2014-07-21T18:59:20Z")
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. ---
**Author:** ![taka-h](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/taka-h/32/1023_2.png) [@taka-h](https://forums.percona.com/u/taka-h)\ **Post date:** [July 21, 2014, 8:01pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/5 "2014-07-21T20:01:31Z")
Related Issue [url][https://blueprints.launchpad.net/percona-xtrabackup/+spec/galera-gtid-autorecovery[/url]](https://blueprints.launchpad.net/percona-xtrabackup/+spec/galera-gtid-autorecovery%5B/url%5D) ---
**Author:** ![mrkamel](https://avatars.discourse-cdn.com/v4/letter/m/6bbea6/32.png) [@mrkamel](https://forums.percona.com/u/mrkamel)\ **Post date:** [July 22, 2014, 3:48am UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/6 "2014-07-22T03:48:37Z")
Check [url][Redirecting to Google Groups](https://groups.google.com/forum/#!searchin/codership-team/innodb_flush_log_at_trx_commit/codership-team/Ywlcy6kOUHc/NZia51TdV5gJ%5B/url%5D) as i asked there as well ---
**Author:** ![taka-h](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/taka-h/32/1023_2.png) [@taka-h](https://forums.percona.com/u/taka-h)\ **Post date:** [July 22, 2014, 6:44pm UTC](https://forums.percona.com/t/innodb-flush-log-at-trx-commit/3621/7 "2014-07-22T18:44:36Z")
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.