MVCC в разных СУБД: где лежат старые версии строк

Счётчик, который обновляют тысячи раз в минуту, в одной базе разбухает таблицей, в другой — undo-файлом, в третьей — tempdb, и ломается он в каждой по-своему. Причина одна: MVCC в PostgreSQL и его аналоги хранят старые версии строки, пока их видит чей-то снимок, а где именно они хранятся, определяет архитектура движка. Ниже 200 000 UPDATE одной строки в PostgreSQL и InnoDB с долгим снимком и без него.

DBA / 01

Где старая версия, кто её чистит и как это ломается

Один и тот же вопрос — «куда делась старая версия» — в разных движках превращается в разные аварии: раздутый файл таблицы, раздутый undo, полный tempdb, ORA-01555 или давление на кэш. Зелёные строки измерены в опыте ниже.

Опыт: 200 000 UPDATE одной строки

Один счётчик, одна строка, 200 000 отдельных UPDATE в автокоммите. Сначала без долгих транзакций, потом с открытым снимком. Запланированный миллион пришлось сократить: при штатном fsync PostgreSQL выполнил 200 000 обновлений за 6 минут 38 секунд (502 транзакции в секунду), то есть миллион занял бы около получаса. Во втором прогоне PostgreSQL и в обоих прогонах MySQL запись на диск ослаблена (synchronous_commit = off в PostgreSQL, innodb_flush_log_at_trx_commit = 2 и sync_binlog = 0 в MySQL). Это ускоряет коммиты, но не меняет то, где лежат версии.

PostgreSQL: версии остаются в таблице

docker run -d --name dba-mvcc-across-engines-pg -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-mvcc-across-engines-pg psql -U postgres

SELECT version();
CREATE EXTENSION pgstattuple;
CREATE TABLE hot_counter (id int PRIMARY KEY, n int);
CREATE TABLE hot_held (id int PRIMARY KEY, n int);
INSERT INTO hot_counter VALUES (1, 0);
INSERT INTO hot_held VALUES (1, 0);

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

Первый прогон — штатные настройки, без других транзакций; pgbench запускается внутри контейнера (docker exec -it dba-mvcc-across-engines-pg bash):

echo 'UPDATE hot_counter SET n = n + 1 WHERE id = 1;' > /tmp/upd.sql
pgbench -U postgres -n -f /tmp/upd.sql -t 200000 postgres

SELECT pg_relation_size('hot_counter') AS table_bytes, pg_relation_size('hot_counter_pkey') AS index_bytes;
SELECT n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup, autovacuum_count FROM pg_stat_user_tables WHERE relname = 'hot_counter';

 table_bytes | index_bytes
-------------+-------------
        8192 |       16384

 n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup | autovacuum_count
-----------+---------------+------------+------------+------------------
    200000 |        200000 |          1 |          0 |                7

Таблица осталась в одной странице в 8 192 байта: каждое обновление было HOT-обновлением (n_tup_hot_upd равно n_tup_upd), а старые версии вычищались прямо на странице. Теперь тот же сценарий на таблице hot_held, пока другая сессия держит снимок:

-- сессия 2, остаётся открытой
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT n FROM hot_held;
SELECT pg_sleep(1800);

echo 'UPDATE hot_held SET n = n + 1 WHERE id = 1;' > /tmp/upd2.sql
PGOPTIONS='-c synchronous_commit=off' pgbench -U postgres -n -f /tmp/upd2.sql -t 200000 postgres

SELECT pg_relation_size('hot_held') AS table_bytes, pg_relation_size('hot_held_pkey') AS index_bytes;
SELECT n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'hot_held';
SELECT tuple_count, dead_tuple_count, dead_tuple_len, free_percent FROM pgstattuple('hot_held');

 table_bytes | index_bytes
-------------+-------------
     7249920 |       16384

 n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+---------------+------------+-----------
    200000 |        199116 |          1 |     200048

 tuple_count | dead_tuple_count | dead_tuple_len | free_percent
-------------+------------------+----------------+--------------
           1 |           200000 |        6400000 | 0.4

Таблица выросла с 8 192 до 7 249 920 байт (885 страниц), и в ней лежат 200 000 мёртвых версий по 32 байта (dead_tuple_len 6 400 000). Версии нельзя удалить, пока открытый снимок второй сессии старше них. Закрываем снимок и делаем VACUUM:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%pg_sleep(1800)%' AND pid <> pg_backend_pid();
VACUUM (VERBOSE) hot_held;
SELECT pg_relation_size('hot_held') AS table_bytes;
SELECT tuple_count, dead_tuple_count, free_percent FROM pgstattuple('hot_held');

INFO:  finished vacuuming "postgres.public.hot_held": index scans: 1
pages: 0 removed, 885 remain, 885 scanned (100.00% of total), 0 eagerly scanned
tuples: 200000 removed, 1 remain, 0 are dead but not yet removable

 table_bytes
-------------
     7249920

 tuple_count | dead_tuple_count | free_percent
-------------+------------------+--------------
           1 |                0 |        99.65

VACUUM удалил все 200 000 версий, но файл остался в 7 249 920 байт: на 885 страницах свободно 99,65 %, а pages: 0 removed значит, что хвост файла не усечён, потому что живая строка лежит в последней странице. Место вернётся в таблицу, но не в файловую систему.

InnoDB: версии уходят в undo

docker run -d --name dba-mvcc-across-engines-my -e MYSQL_ROOT_PASSWORD=pg mysql:8.4
docker exec -it dba-mvcc-across-engines-my mysql -uroot -ppg

После инициализации mysqld в контейнере один раз перезапускается: дождись, пока SELECT 1 проходит дважды подряд. Цикл обновлений — процедура bump; при включённом автокоммите каждый EXECUTE внутри неё — отдельная транзакция. Сброс журналов ослаблен сразу, оба прогона идут с ним:

SELECT version();
CREATE DATABASE t;
CREATE TABLE t.hot_counter (id INT PRIMARY KEY, n INT) ENGINE=InnoDB;
CREATE TABLE t.hot_held    (id INT PRIMARY KEY, n INT) ENGINE=InnoDB;
INSERT INTO t.hot_counter VALUES (1, 0);
INSERT INTO t.hot_held VALUES (1, 0);
DELIMITER //
CREATE PROCEDURE t.bump(tbl VARCHAR(64), cnt INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET @q = CONCAT('UPDATE t.', tbl, ' SET n = n + 1 WHERE id = 1');
  PREPARE s FROM @q;
  WHILE i < cnt DO EXECUTE s; SET i = i + 1; END WHILE;
  DEALLOCATE PREPARE s;
END//
DELIMITER ;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;
SELECT FILE_NAME, TOTAL_EXTENTS*EXTENT_SIZE AS undo_bytes FROM information_schema.FILES WHERE FILE_TYPE = 'UNDO LOG';

+-----------+
| version() |
+-----------+
| 8.4.13    |
+-----------+
+------------+------------+
| FILE_NAME  | undo_bytes |
+------------+------------+
| ./undo_001 |   16777216 |
| ./undo_002 |   16777216 |
+------------+------------+

Первый прогон — CALL t.bump('hot_counter', 200000); без чужих транзакций, 6,4 с. Сразу после него trx_rseg_history_len в information_schema.INNODB_METRICS равен 1 204, а undo-файлы те же 16 777 216 байт: purge успевает за потоком. Второй прогон — hot_held, пока другая сессия держит снимок:

-- сессия 2, остаётся открытой
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT n FROM t.hot_held;
SELECT SLEEP(1800);

CALL t.bump('hot_held', 200000);
SELECT COUNT AS history_list_length FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len';
SELECT FILE_NAME, TOTAL_EXTENTS*EXTENT_SIZE AS undo_bytes FROM information_schema.FILES WHERE FILE_TYPE = 'UNDO LOG';
SELECT TABLE_NAME, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 't';

+---------------------+
| history_list_length |
+---------------------+
|              200001 |
+---------------------+
+------------+------------+
| FILE_NAME  | undo_bytes |
+------------+------------+
| ./undo_001 |   50331648 |
| ./undo_002 |   50331648 |
+------------+------------+
+-------------+-------------+
| TABLE_NAME  | DATA_LENGTH |
+-------------+-------------+
| hot_counter |       16384 |
| hot_held    |       16384 |
+-------------+-------------+

С открытым снимком undo-файлы выросли на 2 × 33 554 432 = 67 108 864 байт, около 335 байт на версию, а таблица hot_held осталась в 16 384 байтах: UPDATE менял строку на месте, старая версия ушла в undo. Файлы undo растут крупными шагами, поэтому это оценка сверху; в статье про undo InnoDB миллион обновлений, зафиксированных пачками по 1 000, дал тот же прирост в 64 МиБ. Закрываем снимок (KILL с Id сессии 2 из SHOW PROCESSLIST) и опрашиваем trx_rseg_history_len: через 1 секунду он равен 0, а ls -l /var/lib/mysql/undo_00* в контейнере по-прежнему показывает по 50 331 648 байт. Автоусечение undo (innodb_undo_log_truncate = 1) срабатывает только после innodb_max_undo_log_size, по умолчанию 1024 МиБ, поэтому у 48 МиБ его не будет.

Что в коде

Общая причина роста в обоих движках — горизонт чистки не продвигается, пока жив старый снимок. В PostgreSQL эту проверку делает очистка страницы при чтении, heap_page_prune_opt:

	prune_xid = ((PageHeader) page)->pd_prune_xid;
	if (!TransactionIdIsValid(prune_xid))
		return;

	/*
	 * Check whether prune_xid indicates that there may be dead rows that can
	 * be cleaned up.
	 */
	vistest = GlobalVisTestFor(relation);

	if (!GlobalVisTestIsRemovableXid(vistest, prune_xid))

Страница чистится, только если prune_xid старше горизонта всех снимков (pruneheap.c, строки 213–223). В первом прогоне горизонта ничто не держало, и единственная страница вычищалась прунингом по ходу обновлений, во втором горизонт держал backend_xmin открытой сессии.

В InnoDB purge перед каждой партией копирует самый старый из активных снимков и удаляет только то, что этому снимку не нужно (trx0purge.cc, trx_purge):

  rw_lock_x_lock(&purge_sys->latch, UT_LOCATION_HERE);

  trx_sys->mvcc->clone_oldest_view(&purge_sys->view);

  rw_lock_x_unlock(&purge_sys->latch);

Подробнее про ReadView и gap-блокировки в документации MySQL: многоверсионность InnoDB.

В WiredTiger (MongoDB 8.0, тег r8.0.34) версии записи — односвязный список WT_UPDATE в памяти страницы кэша (btmem.h, WT_UPDATE):

    wt_shared WT_UPDATE *next; /* forward-linked list */

    uint32_t size; /* data length */

#define WT_UPDATE_INVALID 0   /* diagnostic check */
#define WT_UPDATE_MODIFY 1    /* partial-update modify value */
#define WT_UPDATE_RESERVE 2   /* reserved */
#define WT_UPDATE_STANDARD 3  /* complete value */
#define WT_UPDATE_TOMBSTONE 4 /* deleted */
    uint8_t type; /* type (one byte to conserve memory); also read-only after initialization */

Пока страница в кэше, версии живут в этой цепочке; при записи страницы функция __wt_hs_insert_updates копирует нужные старые обновления в history store (hs_rec.c, строки 334–342). Эксперимента на MongoDB в этой статье нет, поэтому о размерах здесь говорить нечего: для наблюдения в serverStatus есть метрики кэша tracked dirty bytes in the cache и bytes allocated for updates (документация MongoDB).

Oracle и SQL Server: по документации

Код этих движков закрыт, поэтому остаётся документация. Oracle хранит старые версии в undo-табличном пространстве и гарантирует их лишь в пределах UNDO_RETENTION: период хранения undo — минимальное время, в течение которого Oracle пытается сохранить старую информацию undo до перезаписи. Если читателю нужны записи, которые писатели уже перезаписали, он получает ORA-01555 — snapshot too old; документация советует увеличить undo_retention или использовать большие сегменты отката.

В SQL Server при включённых READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION версии всех изменений пишутся в version store, который без ускоренного восстановления базы лежит в tempdb. Если tempdb заполнен, изменения продолжают успешно проходить, а чтения могут падать, потому что нужная версия не создана. Документация руководства по версиям строк также описывает счётчики Version Generation rate и Version Cleanup rate — по ним оценивают размер хранилища.

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

Горячий счётчик в PostgreSQL дроби или выноси. Каждое обновление создаёт версию в той же таблице, и под долгим снимком 200 000 обновлений одной строки заняли 885 страниц вместо одной. Вместо одной строки заведи несколько строк-«корзин» (счётчик суммируется по ним) или копи изменения в журнальной таблице и агрегируй периодически. Про очистку и её узкие места — VACUUM и autovacuum.
В InnoDB таблица не растёт, но растёт undo. Следи не за размером таблицы, а за history list length и размером undo_*: тот же сценарий дал 200 001 запись и +64 МиБ undo, а после закрытия снимка файлы не уменьшились. Алерт ставь на возраст самой старой транзакции в information_schema.INNODB_TRX.
Одна открытая транзакция портит всё независимо от движка. В PostgreSQL она держит горизонт очистки (backend_xmin), в InnoDB — purge. Убирай idle in transaction и таймауты на клиентах раньше, чем покупать диск.
Проверяй, где у движка предел. У Oracle — UNDO_RETENTION и ORA-01555 у читателей, у SQL Server с RCSI — свободное место в tempdb, у MongoDB — доля грязных данных в кэше WiredTiger. Эти пределы в статье не измерялись, их значения бери из своей системы.

Чек-лист

Выясни, где движок хранит старые версии, и мониторь именно это место: размер таблицы и n_dead_tup в PostgreSQL, history list length и undo_* в InnoDB, tempdb у SQL Server, метрики кэша у WiredTiger.
Найди самую старую открытую транзакцию: pg_stat_activity.backend_xmin в PostgreSQL, information_schema.INNODB_TRX в MySQL.
Перед тестом «горячей строки» повтори опыт на своей нагрузке с открытым снимком и без него: разница показывает запас.
Не жди, что VACUUM или purge вернут файлы операционной системе: 7 249 920 и 50 331 648 байт остались после очистки.
Убери контейнеры: docker rm -f dba-mvcc-across-engines-pg dba-mvcc-across-engines-my.

Где лежат старые версии строк: PostgreSQL, InnoDB, Oracle, SQL Server и WiredTiger