Redo InnoDB и бинлог MySQL: двухфазный коммит и sync_binlog

В PostgreSQL коммит ждёт один журнал, WAL (как это устроено). В MySQL их два: redo принадлежит InnoDB, бинлог — серверу и кормит репликацию. Чтобы они не разошлись, сервер ведёт двухфазный коммит: prepare в redo, запись в бинлог, commit в redo. Ниже: сколько fsync стоит коммит, что даёт групповая запись и что остаётся от пары innodb_flush_log_at_trx_commit и sync_binlog после kill -9.

DBA / 01

Коммит MySQL: три шага и что показал опыт

Верхний ряд — порядок шагов коммита. Средний ряд — измерения опыта под тем шагом, к которому они относятся: состояние PREPARED переживает обрыв процесса, бинлог платит один fsync на группу, а без fsync на первом шаге бинлог может уйти вперёд таблицы. Нижний ряд — цена: два синхронных журнала дают 495 TPS на одном соединении, ни одного — 41 992.

Стенд

После инициализации mysqld в контейнере один раз перезапускается, поэтому дождись, пока SELECT 1 проходит дважды подряд: база, созданная до перезапуска, пропадёт. Все запросы идут через клиент с рамками таблиц:

docker run -d --name dba-innodb-redo-binlog -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
alias m='docker exec -i dba-innodb-redo-binlog mysql -uroot -ppg -t'
m -e "CREATE DATABASE lab; CREATE TABLE lab.t (id int PRIMARY KEY, v varchar(20)) ENGINE=InnoDB;
      CREATE TABLE lab.k (id int AUTO_INCREMENT PRIMARY KEY, v char(8)) ENGINE=InnoDB;"
m -e "SELECT @@version, @@innodb_flush_log_at_trx_commit, @@sync_binlog, @@binlog_format, @@innodb_flush_method, @@binlog_order_commits"

+-----------+----------------------------------+---------------+-----------------+-----------------------+------------------------+
| @@version | @@innodb_flush_log_at_trx_commit | @@sync_binlog | @@binlog_format | @@innodb_flush_method | @@binlog_order_commits |
+-----------+----------------------------------+---------------+-----------------+-----------------------+------------------------+
| 8.4.13    |                                1 |             1 | ROW             | O_DIRECT              |                      1 |
+-----------+----------------------------------+---------------+-----------------+-----------------------+------------------------+

Версия сходится с тегом mysql-8.4.13, на который ведут ссылки на код. Бинлог в 8.4 включён по умолчанию (SHOW BINARY LOG STATUS ниже возвращает файл), оба параметра стоят на 1: каждый коммит должен пережить сбой питания.

Опыт 1: один INSERT оставляет след в обоих журналах

Позиция бинлога и LSN redo берутся до и после одной вставки. Каждый вызов клиента отдельный: внутри одной сессии SHOW STATUS отдаёт устаревшее значение.

m -e "RESET BINARY LOGS AND GTIDS"
m -e "SHOW STATUS WHERE Variable_name IN ('Innodb_redo_log_current_lsn','Innodb_redo_log_flushed_to_disk_lsn')"
m -e "SHOW BINARY LOG STATUS"
m -e "INSERT INTO lab.t VALUES (1,'one')"
m -e "SHOW STATUS WHERE Variable_name IN ('Innodb_redo_log_current_lsn','Innodb_redo_log_flushed_to_disk_lsn')"
m -e "SHOW BINARY LOG STATUS"

| Innodb_redo_log_current_lsn         | 101558584 |
| Innodb_redo_log_flushed_to_disk_lsn | 101558584 |
| binlog.000001 |      158 |
-- после INSERT
| Innodb_redo_log_current_lsn         | 101558890 |
| Innodb_redo_log_flushed_to_disk_lsn | 101558890 |
| binlog.000001 |      437 |

Redo вырос на 306 байт, бинлог на 279, и flushed_to_disk_lsn догнал current_lsn: при 1 ответ клиенту не уходит, пока журнал не на диске. Содержимое бинлога видно через SHOW BINLOG EVENTS:

| binlog.000001 | 158 | Anonymous_Gtid |         1 |         237 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 237 | Query          |         1 |         308 | BEGIN                                |
| binlog.000001 | 308 | Table_map      |         1 |         362 | table_id: 124 (lab.t)                |
| binlog.000001 | 362 | Write_rows     |         1 |         406 | table_id: 124 flags: STMT_END_F      |
| binlog.000001 | 406 | Xid            |         1 |         437 | COMMIT /* xid=369333 */              |

Событие Xid закрывает транзакцию и несёт её номер. По нему при восстановлении сервер узнаёт, какие транзакции бинлог считает завершёнными.

Порядок шагов: код

Групповой коммит MYSQL_BIN_LOG::ordered_commit проходит стадии flush, sync и commit; группа собирается очередью, и лидер делает работу за всех. На стадии flush лидер сначала просит движок сбросить prepare-записи и только потом пишет бинлог (binlog.cc, fetch_and_process_flush_stage_queue):

  if (!check_and_skip_flush_logs ||
      (check_and_skip_flush_logs && commit_order_thd != nullptr)) {
    /*
      We flush prepared records of transactions to the log of storage
      engine (for example, InnoDB redo log) in a group right before
      flushing them to binary log.
    */
    ha_flush_logs(true);
  }

Сам prepare fsync не ждёт: trx_flush_logs (trx0trx.cc) при групповом коммите получает HA_IGNORE_DURABILITY и пропускает сброс, а состояние PREPARED записывается в сегмент undo (trx_set_prepared_in_tc_low, тот же undo, что описан в статье про MVCC InnoDB). Что делает ha_flush_logs(true) в InnoDB, решает параметр (ha_innodb.cc, innobase_flush_logs):

  if (binlog_group_flush && srv_flush_log_at_trx_commit == 0) {
    /* innodb_flush_log_at_trx_commit=0
    (write and sync once per second).
    Do not flush the redo log during binlog group commit. */

    /* This could be unsafe if we grouped at least one DDL transaction,
    and we removed !trx->ddl_must_flush from condition which is checked
    inside trx_commit_complete_for_mysql() when we decide if we could
    skip the flush. */
    return false;
  }

При 0 функция сразу возвращает управление: prepare-запись остаётся в буфере redo в памяти процесса, а бинлог дальше пишется и, при sync_binlog=1, сбрасывается на диск. Ниже этой развилки при 1 вызывается log_buffer_flush_to_disk с fsync, при 2 — запись в файл без fsync (строки 5939–5940). Именно это окно опыт 3 ловит руками.

Опыт 2: сколько fsync и какой TPS

Нагрузка — процедура, которая делает N вставок с автокоммитом, то есть N отдельных коммитов на одном соединении. Fsync считаются по performance_schema.file_summary_by_event_name (колонка COUNT_MISC) для файлов бинлога и redo:

CREATE PROCEDURE lab.ins(n int) BEGIN
  DECLARE i int DEFAULT 0; SET autocommit=1;
  WHILE i < n DO INSERT INTO lab.k (v) VALUES ('x'); SET i = i + 1; END WHILE;
END;

Для каждой пары значений процедура lab.run ставит SET GLOBAL, очищает таблицу, снимает счётчики, зовёт lab.ins, снова снимает счётчики и пишет строку в lab.res. Последняя строка идёт с SET sql_log_bin = 0: бинлог выключен, остаётся только redo.

+-----------------------+---------+-------+-------+-----------+-------------+
| cfg                   | commits | ms    | tps   | redo_sync | binlog_sync |
+-----------------------+---------+-------+-------+-----------+-------------+
| 1 / 1                 |    5000 | 10093 |   495 |      7505 |        5000 |
| 1 / 0                 |    5000 |  5275 |   947 |      7376 |           0 |
| 2 / 1                 |    5000 |  5070 |   986 |        57 |        5000 |
| 2 / 0                 |   20000 |   476 | 41992 |        20 |           0 |
| 0 / 0                 |   20000 |   339 | 58941 |        22 |           0 |
| 1 / 1 (sql_log_bin=0) |    5000 |  5328 |   938 |      5984 |           0 |
+-----------------------+---------+-------+-------+-----------+-------------+

В cfg первое число — innodb_flush_log_at_trx_commit, второе — sync_binlog. Что видно:

Бинлог при sync_binlog=1 стоит ровно один fsync на коммит: 5000 на 5000, при 0 — ноль.
Redo при 1 стоит больше одного: 7505 на 5000 коммитов, около 1,5 на коммит (без бинлога 5984, около 1,2). Почему не ровно один, опыт не разбирает: счётчик COUNT_MISC считает служебные операции файла вместе с fsync.
Два синхронных журнала — две цены: 495 TPS против 938 без бинлога. Отключив любой из двух fsync (1 / 0 или 2 / 1), получаешь около 950–990 TPS: время одного соединения почти целиком уходит на ожидание диска.
Режимы 2 и 0 убирают fsync redo: от 20 до 57 за прогон вместо тысяч, остаются сбросы по таймеру innodb_flush_log_at_timeout (1 с). При 2 / 0 один коммит стоит 24 мкс против 2,0 мс.

Групповая запись: один fsync на нескольких

Тот же 1 / 1, но вместо одного соединения восемь, каждое делает по 1000 коммитов:

for i in 1 2 3 4 5 6 7 8; do
  docker exec dba-innodb-redo-binlog mysql -uroot -ppg -e "CALL lab.ins(1000)" & done; wait

-- до
| wait/io/file/innodb/innodb_log_file |      36550 |
| wait/io/file/sql/binlog             |      20042 |
-- после (8000 строк в таблице, 4,4 с вместе с запуском клиентов)
| wait/io/file/innodb/innodb_log_file |      39335 |
| wait/io/file/sql/binlog             |      22373 |

Бинлог получил 2331 fsync на 8000 коммитов, 0,29 на коммит; redo — 2785, 0,35. Лидер стадии sync сбрасывает файл один раз за всю группу, а остальные соединения ждут. Задержку для накопления группы задают binlog_group_commit_sync_delay и binlog_group_commit_sync_no_delay_count; в опыте они не менялись (по умолчанию 0).

Опыт 3: kill -9 при разных настройках

Таблица lab.k пуста, бинлог сброшен. Фоновая сессия вставляет строки в цикле, через три секунды процесс mysqld убивается сигналом KILL, контейнер запускается заново, и считаются строки таблицы и события Write_rows в бинлоге:

m -e "SET GLOBAL innodb_flush_log_at_trx_commit=0; SET GLOBAL sync_binlog=1; TRUNCATE lab.k; RESET BINARY LOGS AND GTIDS"
docker exec -d dba-innodb-redo-binlog mysql -uroot -ppg -e "CALL lab.ins(1000000)"
sleep 3; docker kill -s KILL dba-innodb-redo-binlog; docker start dba-innodb-redo-binlog
# когда SELECT 1 снова проходит:
m -e "SELECT count(*) AS rows_after_restart, max(id) AS max_id FROM lab.k"
m -e "SHOW BINLOG EVENTS IN 'binlog.000001'" | grep -c Write_rows

Один из прогонов с 0 / 1:

+--------------------+--------+
| rows_after_restart | max_id |
+--------------------+--------+
|               3468 |   3468 |
+--------------------+--------+
binlog.000001: Write_rows events = 3469

В бинлоге 3469 вставок, в таблице 3468: транзакция попала в бинлог на диске, а в InnoDB её нет. Это согласуется с кодом выше: при 0 prepare-запись ждёт в буфере процесса и гибнет вместе с ним (код читался, а не наблюдался отдельно). Реплика, которая прочитает такой бинлог, получит на одну строку больше, чем первичный сервер. Сводка по всем прогонам (значения после рестарта, kill -9 в разные моменты цикла):

`innodb_flush_log_at_trx_commit` / `sync_binlog`	прогонов	бинлог длиннее таблицы
1 / 1	10	0
2 / 1	5	0
0 / 1	11	3, каждый раз на 1 транзакцию

Лог сервера при каждом запуске содержит запись о сверке:

2026-10-27T20:54:26.344395Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2026-10-27T20:54:26.354777Z 0 [System] [MY-010232] [Server] XA crash recovery finished.

Что именно делает эта сверка, написано в комментарии к MYSQL_BIN_LOG::open_binlog (binlog.cc):

  /*
    If the binary log was not properly closed it means that the server
    may have crashed. In that case, we need to call
    binlog::Binlog_recovery::recover()
    to:
      a) collect logged XIDs;
      b) complete the 2PC of the pending XIDs;
      c) collect the last valid position.

    Therefore, we do need to iterate over the binary log, even if
    total_ha_2pc == 1, to find the last valid group of events written.
    Later we will take this value and truncate the log if need be.
  */

Сервер собирает Xid из последнего бинлога и завершает двухфазный коммит для тех транзакций, которые InnoDB держит в состоянии PREPARED. Транзакция, чью prepare-запись InnoDB не успел сохранить, в этот список не попадает: завершать нечего, и расхождение остаётся.

docker kill убивает процесс, но не ОС: страничный кэш ядра остаётся целым, и данные, уже переданные в write(), доходят до диска (почему write() ещё не гарантия, разобрано в статье про fsync). Поэтому 2 и sync_binlog=0 выглядят здесь безопасно. Их настоящая цена, потеря записи за последнюю секунду при отказе питания или ядра, этим опытом не воспроизводится.

Опыт 4: PREPARED переживает обрыв

Состояние между шагами 1 и 3 можно создать руками через XA: XA PREPARE делает ровно prepare и останавливается.

m -e "TRUNCATE lab.t; INSERT INTO lab.t VALUES (1,'committed')"
docker exec -d dba-innodb-redo-binlog mysql -uroot -ppg -e \
  "XA START 'x1'; INSERT INTO lab.t VALUES (2,'prepared'); XA END 'x1'; XA PREPARE 'x1'; DO SLEEP(600)"
docker kill -s KILL dba-innodb-redo-binlog; docker start dba-innodb-redo-binlog
m -e "XA RECOVER"; m -e "SELECT * FROM lab.t"
m -e "SET innodb_lock_wait_timeout=3; INSERT INTO lab.t VALUES (2,'again')"
m -e "XA COMMIT 'x1'; SELECT * FROM lab.t"

+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            2 |            0 | x1   |
+----------+--------------+--------------+------+
+----+-----------+
| id | v         |
+----+-----------+
|  1 | committed |
+----+-----------+
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
+----+-----------+
| id | v         |
+----+-----------+
|  1 | committed |
|  2 | prepared  |
+----+-----------+

После рестарта транзакция x1 на месте, строка 2 не видна, а вставка того же ключа упирается в её блокировку. XA COMMIT завершает второй этап. Для внутреннего коммита с бинлогом решение принимает сервер по Xid; здесь его принимает пользователь, но состояние PREPARED в redo и undo одно и то же.

Что из этого следует

Цена коммита в MySQL — два fsync, а не один. На одном соединении 1 / 1 дал 495 TPS, почти вдвое меньше, чем без бинлога (938). Прежде чем искать причину в запросах, проверь, на каком диске лежат бинлог и redo.
Параллельность дешевле, чем ослабление настроек. Восемь клиентов уложили 8000 коммитов в 2331 fsync бинлога. Если приложение умеет коммитить из нескольких соединений, групповой коммит возвращает большую часть потерянного, не трогая надёжность.
innodb_flush_log_at_trx_commit = 0 с sync_binlog = 1 — худшая пара. Бинлог надёжнее таблицы: после обрыва реплика видит транзакцию, которой нет на источнике. 2 / 1 в таких же прогонах расхождения не дало.
Если хочешь послабление, выбирай осознанно, что теряешь. 2 / 0 ускорил соединение в 85 раз, но без отказа ОС этого не видно, а при отказе теряется хвост обоих журналов. Сравни с synchronous_commit = off в PostgreSQL, где теряется только хвост WAL, а расхождения со вторым журналом нет.
Скорость сброса страниц привязана к redo, а не к бинлогу. Размер innodb_redo_log_capacity влияет на то, как ровно InnoDB сбрасывает страницы (разбор адаптивного сброса); бинлог на это не влияет.

Чек-лист

Узнай текущую пару: SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog, @@binlog_format. На первичном сервере с репликой должно быть 1 и 1.
Измерь, чего стоит fsync: счётчики COUNT_MISC для wait/io/file/sql/binlog и wait/io/file/innodb/innodb_log_file в performance_schema.file_summary_by_event_name до и после нагрузки.
Перед тем как менять пару, реши по строке таблицы выше, что ты готов потерять: хвост обоих журналов (2 / 0) или согласованность бинлога с таблицей (0 / 1). Вторым вариантом не пользуйся.
Если упёрся в fsync бинлога, а не redo, подними параллельность клиентов; задержки группы (binlog_group_commit_sync_delay) вводи только с замером до и после.
Убери контейнер опыта вместе с томом: docker rm -f -v dba-innodb-redo-binlog.