Hello,
I have some question about InnoDB crash recovery.
Q: I understand transaction and write to disk sequence as following
figure. Is this correct?
w/
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = on
sync_binlog = 1
skip-innodb_doublewrite
------------------------------------------------------------------------
BEGIN
INSERT \
+-- store --> biglog_cache (memory)
+-- fsync? --> innodb_log_file (disk)
(A) ======>
COMMIT \
+-- fdatasync --> binlog (disk)
(B) ======>|
+-- (prepare) fsync? --> innodb_log_file (disk)
(C) ======>|
+-- (commit) fsycn? --> innodb_log_file (disk)
(D) ======>
------------------------------------------------------------------------
Q: How InnoDB crash recovery when suddenly OS crash at (A)..(D)?
(A) roll back by innodb_log_file.
(B) roll back by innodb_log_file and remove INSERT from binlog.
(C) roll back by innodb_log_file and remove INSERT from binlog.
(D) roll forward by innodb_log_file.
Q: If sync_binlog = 0, what happen?
If still binlog did not sync to disk ...
(B),(C) does mysqld failed to removing INSERT from binglog in crash
recovery sequence?
(D) INSERT in binlog is vanished? If so, table data in replicated
master and slave is collapsed? (master has INSERTed row but
slave doest not have.)
Q: If innodb_support_xa = off, what happen?
(B),(C) When roll back by innodb_log_file, mysqld does or does not
remove INSERT from binlog?
Q: What is best setting for crash recovery?
I suppose, "innodb_support_xa = on and sync_binlog = 1" is best
setting.
But mysqld is VERY VERY slower when enable innodb_support_xa and/or
sync_binlog.
xa=on + sync_binlog=0 is 2 times slower than xa=off + sync_binlog=0.
xa=on + sync_binlog=1 is 4..7 times slower than xa=off + sync_binlog=0.
# This benchmark done with hard disk that have 128MB write cache and
# battery backup unit. but xa=on + sync_binlog=1 is very slower...
Please any answer or advice.
--
HIROSE, Masaaki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
