Питання на співбесіді: InnoDB і транзакції

MySQL зберігає дані через рушії зберігання (storage engines). Один сервер може мати таблиці на різних рушіях. Від MySQL 5.5 рушій за замовчуванням - InnoDB, а MyISAM лишився спадщиною.

Що дає InnoDB:

• Транзакції (ACID). COMMIT, ROLLBACK, відновлення після збою. У MyISAM транзакцій немає взагалі: DB::transaction() у Laravel на MyISAM-таблиці нічого не відкотить.
• Блокування рядків. Два запити, що оновлюють різні рядки однієї таблиці, не заважають один одному. MyISAM блокує всю таблицю на кожен запис - під навантаженням на запис усі запити стають у чергу.
• Узгоджене читання (MVCC). Читачі не чекають на записувачів: SELECT бачить знімок даних, а не блокується через чужий UPDATE.
• Зовнішні ключі. MyISAM синтаксис FOREIGN KEY приймає, але ігнорує.
• Стійкість до збоїв. Після аварійного вимкнення InnoDB відновлюється за журналом повтору (redo log). MyISAM-таблиці після збою часто доводиться «лагодити» (REPAIR TABLE) з ризиком втратити дані.
• Кластерний індекс. Рядки фізично впорядковані за первинним ключем, тож пошук за ним дуже дешевий.

Чим MyISAM колись приваблював і чому це вже неактуально:

• точний COUNT(*) без умов миттєво (лічильник у метаданих) - InnoDB рахує рядки, бо через MVCC різні транзакції бачать різну кількість;
• повнотекстовий пошук - InnoDB має його від 5.6;
• менший розмір на диску - різниця не варта втрати транзакцій.

Як перевірити й перевести:

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

ALTER TABLE legacy_logs ENGINE = InnoDB;

ALTER ... ENGINE перебудовує таблицю повністю, тож на великих таблицях це варто планувати.

Інші рушії мають вузьке застосування: MEMORY для тимчасових даних (зникають при перезапуску), ARCHIVE для рідко читаних логів, CSV для обміну. Для таблиць застосунку відповідь майже завжди одна - InnoDB.

Докладніше в документації: Вступ до InnoDB

InnoDB за замовчуванням працює на рівні REPEATABLE READ. У PostgreSQL за замовчуванням - READ COMMITTED. Той самий код Laravel на двох базах може поводитися по-різному.

REPEATABLE READ у MySQL означає: звичайні SELECT у межах транзакції бачать один і той самий знімок даних. Знімок фіксується під час першого читання в транзакції:

-- сесія A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- 100, знімок зафіксовано

-- сесія B (автокоміт)
UPDATE accounts SET balance = 50 WHERE id = 1;

-- сесія A
SELECT balance FROM accounts WHERE id = 1;   -- все ще 100
COMMIT;
SELECT balance FROM accounts WHERE id = 1;   -- 50

На READ COMMITTED (PostgreSQL за замовчуванням) другий SELECT у сесії A вже показав би 50: кожен оператор бачить свіжі закомічені дані.

Важливий нюанс MySQL: знімок діє лише для звичайних читань. UPDATE, DELETE і SELECT ... FOR UPDATE працюють з останньою закоміченою версією рядка. Тож у транзакції можна прочитати 100, а UPDATE ... SET balance = balance - 10 порахує від 50. Звідси правило: якщо значення читають, щоб потім на його основі писати, - читати треба з FOR UPDATE.

Як подивитися й змінити рівень:

SELECT @@transaction_isolation;               -- REPEATABLE-READ

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- лише для наступної транзакції

У Laravel рівень можна задати в конфігурації підключення ('isolation_level' => 'READ COMMITTED' для MySQL).

Чотири рівні стандарту SQL: READ UNCOMMITTED (бачить незакомічене), READ COMMITTED, REPEATABLE READ, SERIALIZABLE (у InnoDB звичайні SELECT неявно стають FOR SHARE, якщо вимкнено автокоміт).

Що варто пам'ятати: REPEATABLE READ у InnoDB завдяки блокуванням проміжків (gap locks) захищає від фантомів при блокувальних читаннях - але ціною додаткових блокувань і частіших взаємоблокувань.

Докладніше в документації: Рівні ізоляції транзакцій InnoDB

В InnoDB таблиця і є індексом: рядки зберігаються в B-дереві, впорядкованому за первинним ключем. Це дерево називають кластерним індексом. Окремої «купи» рядків, як у PostgreSQL, немає.

Як InnoDB обирає кластерний індекс:

1. первинний ключ (PRIMARY KEY);
2. якщо його немає - перший UNIQUE-індекс, у якого всі колонки NOT NULL;
3. якщо немає й такого - прихований індекс GEN_CLUST_INDEX за внутрішнім 6-байтовим ідентифікатором рядка, невидимим для запитів.

Вторинні індекси (усі інші) зберігають не адресу рядка, а значення первинного ключа:

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    INDEX (user_id)          -- фактично зберігає пари (user_id, id)
);

SELECT total FROM orders WHERE user_id = 7;
-- 1) пошук у індексі user_id -> отримано id
-- 2) пошук у кластерному індексі за id -> отримано total

Наслідки для практики:

• Пошук за первинним ключем найдешевший - одне проходження дерева, і рядок уже на місці.
• Довгий первинний ключ роздуває всі індекси, бо його копія є в кожному вторинному. BIGINT (8 байтів) проти CHAR(36) для UUID (36 байтів) - відчутна різниця на великій таблиці.
• Випадковий первинний ключ шкодить вставці. Автоінкремент дописує рядки в кінець дерева. Випадкові UUIDv4 вставляють у довільні сторінки, викликають їх розщеплення й фрагментацію.
• Вторинний індекс «безкоштовно» покриває первинний ключ. SELECT id FROM orders WHERE user_id = 7 читає лише індекс, без другого кроку.
• Діапазон за первинним ключем дешевий: WHERE id BETWEEN 1000 AND 2000 читає сусідні сторінки.

Тому в Laravel $table->id() (BIGINT UNSIGNED AUTO_INCREMENT) - вдалий вибір за замовчуванням для InnoDB, а для UUID краще впорядковані за часом значення (UUIDv7, HasUuids).

Докладніше в документації: Кластерні й вторинні індекси

У MySQL оператори визначення схеми (DDL) виконують неявний коміт: перед виконанням вони комітять поточну транзакцію, і самі в транзакцію не потрапляють.

START TRANSACTION;
INSERT INTO settings (name) VALUES ('a');
ALTER TABLE settings ADD COLUMN value TEXT;   -- INSERT уже закомічено
ROLLBACK;                                     -- відкочувати нічого

Що викликає неявний коміт (основне):

• CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE, створення й видалення індексів, подань, процедур;
• LOCK TABLES і UNLOCK TABLES (якщо таблиці були заблоковані);
• START TRANSACTION / BEGIN - комітять попередню незавершену транзакцію;
• адміністративні: ANALYZE TABLE, OPTIMIZE TABLE, CREATE USER, GRANT тощо.

CREATE TEMPORARY TABLE неявного коміту не робить.

Що це означає для Laravel-міграцій:

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('phone')->nullable();
    });

    Schema::table('users', function (Blueprint $table) {
        $table->string('phone')->unique()->change();   // впаде на дублікатах
    });
}

Якщо другий крок падає, перший уже застосований. Міграцію не позначено виконаною, тож повторний запуск упаде на «колонка вже існує». Доведеться вручну прибрати наполовину застосовані зміни.

Атомарний DDL у MySQL 8 означає інше: окремий оператор DDL або виконується повністю, або не виконується (не лишає «напівстворених» таблиць після збою). Але кілька операторів у транзакцію все одно не об'єднати.

PostgreSQL тут відрізняється: DDL у ньому транзакційний, і Laravel обгортає міграцію в транзакцію ($withinTransaction = true), тож упала - відкотилася повністю.

Практичні висновки для MySQL:

• одна міграція - одна логічна зміна;
• ризиковану зміну (унікальний індекс на дані з можливими дублікатами) - окремою міграцією, після перевірки й очищення даних;
• метод down() має вміти відкотити зміни, навіть якщо частину з них не застосовано (if (Schema::hasColumn(...))).

Докладніше в документації: Оператори з неявним комітом

Пропуски в AUTO_INCREMENT - нормальна поведінка, а не помилка. Лічильник гарантує унікальність і зростання, але не неперервність.

Звідки беруться пропуски:

• Відкат транзакції. Значення видається під час вставки й не повертається при ROLLBACK:

START TRANSACTION;
INSERT INTO orders (total) VALUES (100);   -- отримав id = 41
ROLLBACK;
INSERT INTO orders (total) VALUES (200);   -- id = 42, 41 пропущено назавжди

• Невдала вставка. Порушення унікальності чи іншого обмеження після того, як номер уже виділено.
• INSERT ... ON DUPLICATE KEY UPDATE та INSERT IGNORE. Номер може бути виділений, навіть коли рядок оновлено чи пропущено. Таблиця з частими upsert-ами може «з'їдати» номери значно швидше, ніж росте кількість рядків.
• Масові вставки. Для INSERT ... SELECT і LOAD DATA, коли кількість рядків заздалегідь невідома, InnoDB виділяє номери блоками (1, 2, 4, 8...), і залишок блоку пропадає.
• Видалення рядків - звісно, теж.

Режим блокування лічильника innodb_autoinc_lock_mode. У MySQL 8 за замовчуванням 2 (interleaved): паралельні вставки отримують номери без табличного блокування, тож номери з різних транзакцій можуть перемежовуватися. Це швидко, але безпечно лише з рядковим форматом бінарного журналу (за замовчуванням ROW).

Після перезапуску сервера: від MySQL 8.0 лічильник зберігається (записується в журнал повтору). У 5.7 і раніше після перезапуску він ставав MAX(id) + 1, тож видалені «хвостові» номери могли видатися повторно.

Чого не робити:

• не використовувати id як номер рахунку чи накладної, якщо закон вимагає неперервної нумерації, - для цього окрема послідовність, яку видають у транзакції з блокуванням;
• не «ущільнювати» id після видалень - на них посилаються зовнішні ключі, кеші, URL.

Про переповнення: INT UNSIGNED закінчується на ~4,29 млрд. Таблиця з частими upsert-ами може дістатися ліміту раніше, ніж здається. Тому в Laravel id() створює BIGINT UNSIGNED.

Докладніше в документації: AUTO_INCREMENT в InnoDB

Узгоджене читання - це спосіб, яким InnoDB виконує звичайні SELECT: вони читають знімок бази на певний момент і не ставлять жодних блокувань. Якщо рядок змінила інша транзакція, InnoDB відновлює його попередню версію з undo-журналу.

Тому читачі не чекають на записувачів, а записувачі - на читачів.

Коли фіксується знімок:

• REPEATABLE READ (за замовчуванням): під час першого читання в транзакції, а не на START TRANSACTION. Усі наступні звичайні SELECT транзакції бачать той самий знімок.
• READ COMMITTED: кожен оператор бере новий знімок.

START TRANSACTION;          -- знімка ще немає
-- тут інша сесія комітить зміну
SELECT * FROM t;            -- знімок фіксується зараз: зміну видно

START TRANSACTION WITH CONSISTENT SNAPSHOT;  -- знімок фіксується одразу

WITH CONSISTENT SNAPSHOT використовує, наприклад, mysqldump --single-transaction: дамп усіх таблиць узгоджений на одну мить.

Знімок не діє на зміни даних. Це найчастіша пастка:

START TRANSACTION;
SELECT COUNT(*) FROM jobs WHERE status = 'new';     -- 0 (знімок)
-- інша сесія вставляє і комітить 5 рядків 'new'
UPDATE jobs SET status = 'taken' WHERE status = 'new';  -- оновить 5 рядків!
SELECT COUNT(*) FROM jobs WHERE status = 'taken';   -- 5: свої зміни видно
COMMIT;

UPDATE і DELETE працюють з останніми закоміченими версіями. Після цього транзакція бачить рядки, які вона змінила, - навіть якщо «за знімком» їх не існувало.

Блокувальні читання (SELECT ... FOR UPDATE / FOR SHARE) теж читають останню версію, а не знімок, і ставлять блокування.

Практичні наслідки:

• шаблон «прочитати, перевірити в PHP, записати» без FOR UPDATE дає гонитву: рішення приймається на основі знімка, а запис - поверх свіжих даних;
• довга транзакція тримає старий знімок, і InnoDB не може очистити старі версії рядків (див. purge), тож база росте й сповільнюється;
• DDL на кшталт ALTER TABLE інколи не узгоджується зі знімком: якщо таблицю перестворено після фіксації знімка, транзакція отримає помилку «Table definition has changed».

Докладніше в документації: Узгоджене неблокувальне читання

Блокувальні читання читають останню закомічену версію рядка (а не знімок) і ставлять на нього блокування до кінця транзакції. Поза транзакцією (в автокоміті) вони майже марні: блокування знімається одразу.

FOR SHARE (раніше LOCK IN SHARE MODE) - спільне блокування. Інші теж можуть читати з FOR SHARE, але змінити рядок чи взяти FOR UPDATE не можуть до коміту.

Типове застосування - перевірити, що батьківський рядок існує й не зникне, поки вставляємо дочірній:

START TRANSACTION;
SELECT id FROM projects WHERE id = 5 FOR SHARE;
INSERT INTO tasks (project_id, title) VALUES (5, 'Нове завдання');
COMMIT;

FOR UPDATE - виключне блокування: так, ніби рядок уже оновлюють. Інші FOR UPDATE, FOR SHARE і зміни чекають. Звичайні SELECT без блокувань читають далі зі знімка.

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- перевірка в застосунку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

У Laravel: ->sharedLock() і ->lockForUpdate() всередині DB::transaction().

NOWAIT - не чекати: якщо рядок заблоковано, одразу помилка (ER_LOCK_NOWAIT). Зручно для інтерфейсу «запис редагує інший користувач».

SKIP LOCKED - пропустити заблоковані рядки. Класична черга завдань, де кілька воркерів не беруть одне й те саме:

START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- позначити взяті й закомітити

Саме так драйвер черг database у Laravel вибирає завдання на MySQL 8+.

Нюанси InnoDB:

• блокується не «рядок за умовою», а записи індексу, які пройшов пошук. Без індексу на колонці з умови FOR UPDATE заблокує всі прочитані рядки таблиці;
• на REPEATABLE READ додаються блокування проміжків (gap locks), тож може блокуватися й вставка в діапазон;
• SKIP LOCKED повертає неузгоджену картину даних - для черг це нормально, для звітів ні;
• FOR UPDATE OF orders у запиті з JOIN блокує лише рядки вказаної таблиці.

Докладніше в документації: Блокувальні читання

InnoDB блокує не лише існуючі рядки, а й проміжки між записами індексу. Так на рівні REPEATABLE READ він захищається від фантомів - рядків, що «з'являються» між двома блокувальними читаннями однієї транзакції.

Види блокувань:

• record lock - блокування одного запису індексу;
• gap lock - блокування проміжку між записами (без самих записів). Забороняє лише вставку в цей проміжок;
• next-key lock - запис плюс проміжок перед ним. Саме так InnoDB блокує при пошуку й скануванні індексу на REPEATABLE READ.

Приклад, що дивує:

-- у таблиці є рядки з age = 20 і age = 30, індекс на age
-- сесія A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 21 AND 29 FOR UPDATE;  -- 0 рядків

-- сесія B
INSERT INTO users (age) VALUES (25);    -- чекає!
INSERT INTO users (age) VALUES (35);    -- може теж чекати: next-key lock
                                        -- на запис 30 разом із проміжком

Сесія A не знайшла жодного рядка, але заблокувала проміжок, щоб повторний запит дав той самий результат.

Коли блокування ширше, ніж очікуєш:

• немає індексу на колонці з умови - сканується вся таблиця, і блокуються всі записи з проміжками. UPDATE ... WHERE email = ? без індексу на email фактично блокує таблицю для вставок;
• неунікальний індекс - блокуються проміжки з обох боків знайдених записів;
• пошук за унікальним індексом з рівністю блокує лише сам запис, без проміжку.

Gap locks не конфліктують між собою - дві транзакції можуть тримати блокування одного проміжку. Звідси класичне взаємоблокування: обидві роблять SELECT ... FOR UPDATE за неіснуючим ключем, обидві отримують gap lock, обидві намагаються вставити - deadlock.

READ COMMITTED вимикає gap locks для пошуку й сканування (лишаються лише для перевірки зовнішніх ключів і дублікатів). Блокувань і взаємоблокувань менше, але фантоми можливі.

Як побачити блокування:

SELECT engine_transaction_id, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

lock_mode вигляду X,GAP, X,REC_NOT_GAP чи просто X (next-key) показує, що саме тримає транзакція.

Докладніше в документації: Блокування next-key і фантомні рядки

Взаємоблокування (deadlock) - дві транзакції чекають одна на одну: A тримає рядок 1 і хоче рядок 2, B тримає рядок 2 і хоче рядок 1. InnoDB помічає цикл і відкочує одну з транзакцій (зазвичай ту, що змінила менше рядків). Вона отримує помилку 1213, друга продовжує роботу.

Перше правило: deadlock - не аварія. У системі з паралельними записами вони трапляються. Застосунок має бути готовим повторити транзакцію повністю:

DB::transaction(function () use ($from, $to, $amount) {
    // ...
}, attempts: 3);

Laravel повторює замикання при взаємоблокуванні. Важливо: повторюється вся транзакція, тож побічні ефекти (листи, HTTP-запити) - лише після коміту (DB::afterCommit() чи afterCommit у подіях і джобах).

Друге - з'ясувати причину:

SHOW ENGINE INNODB STATUS\G
-- розділ LATEST DETECTED DEADLOCK: обидві транзакції,
-- їхні запити, які блокування тримають і чого чекають

Там видно лише останній deadlock. Щоб писати всі в журнал помилок:

SET PERSIST innodb_print_all_deadlocks = ON;

Типові причини й ліки:

• Різний порядок захоплення рядків. Переказ з A на B і з B на A одночасно. Ліки - завжди блокувати в одному порядку (наприклад, за зростанням id):

$accounts = Account::whereIn('id', [$from, $to])->orderBy('id')->lockForUpdate()->get();

• Відсутній індекс. UPDATE ... WHERE без індексу блокує все проскановане - ймовірність перетину різко зростає.
• Блокування проміжків. Два SELECT ... FOR UPDATE за неіснуючим ключем, а потім два INSERT. Ліки - INSERT ... ON DUPLICATE KEY UPDATE замість «перевірити, потім вставити», або READ COMMITTED.
• Довгі транзакції. Чим довше тримаються блокування, тим більше перетинів. Важкі обчислення й зовнішні виклики - поза транзакцією.
• Масові оновлення. Великий UPDATE по частинах з однаковим порядком.

Що не допомагає: збільшувати innodb_lock_wait_timeout - він стосується звичайного очікування, а не циклів, які InnoDB розриває одразу.

Докладніше в документації: Взаємоблокування в InnoDB

Буферний пул (buffer pool) - головна область пам'яті InnoDB. Там кешуються сторінки таблиць і індексів (по 16 КБ). Запит, якому вистачило даних у пам'яті, не йде на диск. Зміни теж спершу пишуться в сторінки буферного пулу («брудні» сторінки), а на диск скидаються пізніше у фоні.

Розмір - найважливіше налаштування MySQL. Параметр innodb_buffer_pool_size, за замовчуванням лише 128 МБ - для продакшену майже завжди замало.

Як вибирати:

• на виділеному під MySQL сервері - зазвичай 50-75% оперативної пам'яті. Решта потрібна ОС, з'єднанням (буфери сортування, з'єднань на кожну сесію), тимчасовим таблицям;
• якщо на тому ж сервері працюють PHP, Redis, черги - менше, залежно від їхніх потреб;
• ідеал - щоб робочий набір (часто читані таблиці й індекси) поміщався в пул повністю. Вся база в пам'ять не обов'язкова.

Для виділеного сервера є ще варіант innodb_dedicated_server = ON: InnoDB сам визначає розмір пулу й журналу повтору з обсягу пам'яті.

Як перевірити, чи вистачає:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests - логічні читання
-- Innodb_buffer_pool_reads         - довелося йти на диск

SELECT * FROM sys.innodb_buffer_stats_by_table LIMIT 10;  -- хто займає пул

Частка reads / read_requests має бути дуже малою (соті частки відсотка). Помітне зростання - робочий набір не вміщується.

Як влаштоване витіснення. Пул використовує LRU з «точкою вставки посередині»: нові сторінки потрапляють не на початок списку, а в «старшу» частину (останні 3/8). Так одноразове повне сканування великої таблиці (наприклад, дамп) не витісняє з пам'яті гарячі дані.

Корисне:

• розмір можна змінювати на льоту (SET PERSIST innodb_buffer_pool_size = ...), зміна відбувається поступово блоками;
• під час зупинки MySQL зберігає список гарячих сторінок, а під час старту завантажує їх (innodb_buffer_pool_dump_at_shutdown / load_at_startup, увімкнено за замовчуванням), тож після перезапуску кеш «прогрівається» швидше;
• на великих пулах він ділиться на кілька екземплярів (innodb_buffer_pool_instances), щоб зменшити конкуренцію.

Докладніше в документації: Буферний пул

Блокування метаданих (metadata lock, MDL) захищає структуру таблиці: поки хтось працює з таблицею в транзакції, змінити її схему не можна. Будь-який запит до таблиці, навіть звичайний SELECT, бере спільне MDL і тримає його до кінця транзакції. ALTER TABLE потребує виключного MDL.

Класичний сценарій аварії:

-- сесія 1: хтось у консолі
START TRANSACTION;
SELECT * FROM orders LIMIT 1;   -- MDL взято, транзакція не закрита
-- ... пішов на обід

-- сесія 2: деплой
ALTER TABLE orders ADD COLUMN note TEXT;   -- чекає на MDL

-- сесії 3..N: звичайний трафік
SELECT * FROM orders WHERE id = 5;         -- теж чекають!

Найнеприємніше - останній крок: ALTER стоїть у черзі за виключним блокуванням, і всі нові запити до таблиці стають у чергу за ним. Таблиця фактично недоступна, хоча сам ALTER навіть не почався. Пул з'єднань застосунку швидко вичерпується.

Звідки беруться довгі транзакції:

• відкрита транзакція в консолі чи GUI-клієнті з вимкненим автокомітом;
• воркер черги, що відкрив транзакцію й чекає на зовнішній API;
• довгий звіт чи дамп без --single-transaction.

Як знайти винуватця:

SELECT * FROM sys.schema_table_lock_waits\G
-- хто чекає, хто блокує, і готова команда KILL для блокувальника

SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

Як захистити деплой:

• задати короткий тайм-аут очікування для сесії міграцій. За замовчуванням lock_wait_timeout - рік:

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN note TEXT;   -- не дочекався за 5 с - помилка, а не простій

• повторювати міграцію кілька разів з паузою;
• перед міграцією перевіряти innodb_trx на довгі транзакції;
• обмежувати довжину транзакцій у застосунку й тайм-аут неактивних сесій (wait_timeout).

Онлайн DDL не рятує: навіть ALGORITHM=INSTANT бере виключне MDL на коротку мить на початку й наприкінці - і так само може застрягти за довгою транзакцією.

Докладніше в документації: Блокування метаданих

За замовчуванням (innodb_file_per_table = ON) кожна таблиця InnoDB живе у власному файлі .ibd. Коли рядки видаляють, InnoDB позначає місце вільним усередині файлу й використовує його для нових рядків цієї ж таблиці. Операційній системі місце не повертається.

-- таблиця логів займає 40 ГБ
DELETE FROM activity_log WHERE created_at < NOW() - INTERVAL 90 DAY;
-- файл activity_log.ibd усе ще 40 ГБ, але всередині багато вільних сторінок

Як оцінити вільне місце:

SELECT table_name,
       ROUND(data_length / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb,
       ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_free DESC;

Цифри приблизні (оцінка за статистикою), але порядок величин видно.

Як повернути місце ОС:

• OPTIMIZE TABLE - для InnoDB це перебудова таблиці (ALTER TABLE ... FORCE): створюється нова компактна копія, стара видаляється. Виконується онлайн (паралельні зміни дозволені), але потребує вільного диска на розмір таблиці й створює навантаження;
• TRUNCATE TABLE - якщо треба очистити все: файл перестворюється порожнім, миттєво;
• секціонування за датою й ALTER TABLE ... DROP PARTITION - найкращий варіант для логів: стара частина видаляється разом із файлом, без DELETE мільйонів рядків.

Чи потрібно повертати місце взагалі? Часто ні. Якщо таблиця знову виросте до того ж розміру, вільне місце просто використається. Перебудова має сенс, коли таблиця назавжди зменшилася в рази або диск закінчується.

Системний табличний простір ibdata1 не зменшується ніколи. Якщо таблиці колись створили з innodb_file_per_table = OFF, їхні дані лежать там, і звільнити місце можна лише перенесенням даних у новий інстанс. Тому вимикати file-per-table немає причин.

Масове видалення варто робити частинами (DELETE ... LIMIT 10000 у циклі): один гігантський DELETE - це довга транзакція, величезний undo-журнал і затримка реплік.

Докладніше в документації: Табличні простори file-per-table

Формат рядка визначає, як InnoDB розкладає колонки рядка по сторінках. Є чотири: REDUNDANT, COMPACT, DYNAMIC і COMPRESSED. За замовчуванням - DYNAMIC, і його варто лишати.

Головна відмінність - довгі колонки (TEXT, BLOB, JSON, довгі VARCHAR):

• DYNAMIC: якщо значення не вміщується в сторінку, воно цілком переноситься на окремі сторінки переповнення, а в рядку лишається 20-байтовий вказівник;
• COMPACT: у рядку лишаються перші 768 байтів значення, решта - на сторінках переповнення. Широка таблиця з багатьма TEXT швидко впирається в ліміт.

Звідки «Row size too large» - насправді два різні ліміти:

1. 65 535 байтів на рядок - ліміт MySQL для визначення таблиці. Рахується максимальний розмір усіх колонок. VARCHAR(255) в utf8mb4 важить до 1020 байтів, тож двісті таких колонок уже не вмістяться. TEXT/BLOB додають лише 9-12 байтів, бо зберігаються окремо.
2. Майже половина сторінки (~8 КБ при 16-КБ сторінках) - ліміт InnoDB для частини рядка, що лишається в сторінці. Помилка може з'явитися не при створенні таблиці, а при вставці, коли реальні дані не вмістилися.

-- рішення для 1-го ліміту: довгі VARCHAR перевести в TEXT
ALTER TABLE products MODIFY description TEXT;

-- перевірити формат
SELECT name, row_format FROM information_schema.innodb_tables WHERE name LIKE 'app/%';

Другий наслідок формату - довжина ключа індексу:

• DYNAMIC/COMPRESSED: до 3072 байтів на індекс;
• COMPACT/REDUNDANT: лише 767 байтів.

Звідси історичний Schema::defaultStringLength(191) у Laravel: 191 × 4 байти (utf8mb4) = 764 < 767. На MySQL 5.7.7+ і 8.x з DYNAMIC це обмеження вже не потрібне - VARCHAR(255) під унікальним індексом важить 1020 байтів, що вміщується в 3072.

COMPRESSED стискає сторінки, економлячи диск ціною процесора й складнішої роботи буферного пулу. Сьогодні частіше вибирають стиснення на рівні файлової системи чи сховища.

Практичне правило: не тримати в одній таблиці десятки довгих колонок. Рідко потрібні великі поля краще винести в окрему таблицю 1:1 - це зменшує і ризик помилки, і обсяг даних, які читаються з кожним рядком.

Докладніше в документації: Формати рядків InnoDB

InnoDB змінює сторінки даних у буферному пулі, а на диск скидає їх пізніше. Щоб закомічені зміни не пропали при збої, працює журнал повтору (redo log): перед комітом опис змін послідовно записується в журнал. Послідовний запис невеликих записів значно дешевший за запис випадкових сторінок по 16 КБ.

Відновлення після збою: під час старту InnoDB читає журнал від останньої контрольної точки й повторно застосовує зміни, що не встигли потрапити у файли даних. Незакомічені транзакції відкочуються за undo-журналом.

innodb_flush_log_at_trx_commit визначає, що відбувається під час COMMIT:

fsync - найдорожча частина коміту, тож 2 і 0 дають помітний виграш на дрібних транзакціях. Але це свідома відмова від стійкості. Прийнятно для реплік, що легко перестворити, чи для тимчасового масового імпорту - не для основної бази з грошима.

Друга половина стійкості - sync_binlog (за замовчуванням 1): fsync бінарного журналу на кожен коміт. Без нього після збою репліки можуть отримати транзакції, яких немає на джерелі, чи навпаки.

Подвійний запис (doublewrite buffer) захищає від «розірваних» сторінок: якщо живлення зникло посеред запису 16-КБ сторінки, на диску лишиться напівзаписана сторінка, яку журнал повтору не виправить. Тому InnoDB спершу пише сторінки в окрему область doublewrite, а потім на місце. Після збою пошкоджена сторінка відновлюється з копії.

Розмір журналу повтору (innodb_redo_log_capacity з MySQL 8.0.30, за замовчуванням 100 МБ). Замалий журнал змушує часто робити контрольні точки й агресивно скидати сторінки - запис стає ривками. Завеликий подовжує відновлення після збою. Орієнтир - щоб журнал вміщав щонайменше годину запису під піковим навантаженням.

Що варто знати: групування комітів (group commit) дає змогу кільком транзакціям поділити один fsync, тож налаштування 1 під паралельним навантаженням коштує менше, ніж здається на синтетичному тесті з одним клієнтом.

Докладніше в документації: Журнал повтору (redo log)

Undo-журнал зберігає попередні версії рядків. Коли транзакція змінює рядок, стара версія йде в undo. Це потрібно для двох речей: відкоту транзакції і узгодженого читання - інші транзакції відновлюють зі undo версію, яку вони мають бачити за своїм знімком.

Purge - фонова очистка: коли жодна активна транзакція вже не може побачити стару версію, purge-потоки видаляють її з undo і остаточно прибирають рядки, позначені як видалені (DELETE в InnoDB лише позначає рядок).

Довжина історії (history list length) - кількість ще не очищених записів undo:

SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS: History list length 1234567

SELECT count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

Як одна транзакція шкодить усім. Purge не може видалити жодну версію, новішу за найстаріший відкритий знімок. Якщо транзакцію відкрито годину тому (навіть тільки для читання на REPEATABLE READ), за годину нагромаджуються всі старі версії всіх змінених рядків:

• зростає історія й розмір undo-табличних просторів;
• запити, що читають часто змінювані рядки, проходять довгі ланцюжки версій - звичайні SELECT сповільнюються;
• вторинні індекси розбухають від невичищених записів;
• коли транзакція нарешті закривається, purge довго «наздоганяє», створюючи навантаження.

Типові винуватці:

• забутий START TRANSACTION у консолі чи GUI;
• mysqldump --single-transaction великої бази на основному сервері;
• довгий звіт у транзакції;
• воркер, що відкрив транзакцію й чекає на зовнішній сервіс.

Як знайти:

SELECT trx_id, trx_mysql_thread_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Що робити:

• моніторити довжину історії й вік найстарішої транзакції, ставити сповіщення;
• великі звіти й дампи виконувати на репліці;
• для аналітичних читань, яким не потрібен один знімок на кілька запитів, - READ COMMITTED: знімок береться на кожен оператор і не тримається довго;
• max_execution_time для SELECT і тайм-аут неактивних сесій.

Розмір undo на диску: undo-табличні простори автоматично усікаються (innodb_undo_log_truncate = ON), коли перевищують innodb_max_undo_log_size (1 ГБ) і purge їх звільнив. Але доки довга транзакція жива, усікання неможливе.

Докладніше в документації: Налаштування purge