Not the answer you need?
Register and ask your own question!


mrkamelmrkamel EntrantInactive User Role Beginner

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:


  • taka-htaka-h Contributor Current User Role Beginner
    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.
  • mrkamelmrkamel Entrant Inactive User Role Beginner
    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

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


    Check out this as well:

    "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-htaka-h Contributor Current User Role Beginner
    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-htaka-h Contributor Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.