mysql - Is it dangerous to use transaction log flush delay - e.g set innodb_flush_log_at_trx_commit to 0? -

mysql - Is it dangerous to use transaction log flush delay - e.g set innodb_flush_log_at_trx_commit to 0? -

is true risks of having such alternative turned on higher loosing lastly n seconds of transactions?

let me explain why i'm asking question. imagine db server processed transaction t, , modified set of index pages result of this. few of these index pages flushed disk, few others aren't. operations made t aren't flushed on-disk transaction log yet. , turn off power.

so when db server restarts , tries recover, doesn't see t operations in transaction log. of indexes reflect these operations, since index pages modified t flushed before powerfulness failure. i.e. result, t partially applied - , probably, in such way leads inconsistent index construction (e.g. primary index of table x doesn't reflect changes made t, of secondary indexes does).

so wonder of possible if delay transaction log flushing, , if not, database server prevent (mysql 5.5 w/innodb interesting case me).

got total reply on question @ quora:

"checkpoints (dirty page flushing) fsync transaction log too, avoid situation mentioned. background thread timer isn't source of log flushes. note, raise insane amount of warnings, lsns in pages ahead of transaction log lsns.

generally safe run innodb transaction log flushing disabled, long don't need lastly info (and if run replicated setup, have deal info missing on master, may exist on slave)."

mysql transactions recovery