Питання на співбесіді з MySQL

Багато системних змінних MySQL динамічні - їх можна змінити на працюючому сервері. Проблема класичного SET GLOBAL: після перезапуску значення повертається до того, що у my.cnf. Хтось виправив налаштування під час інциденту, забув перенести в конфіг - і через місяць після перезапуску проблема повертається.

SET PERSIST (MySQL 8.0+) змінює значення і зараз, і назавжди:

SET PERSIST max_connections = 500;
SET PERSIST long_query_time = 0.5;

Значення зберігається у файлі mysqld-auto.cnf у каталозі даних (JSON) і застосовується при старті після my.cnf - тобто має пріоритет.

Варіанти:

• SET GLOBAL - лише до перезапуску;
• SET PERSIST - зараз і після перезапуску (для динамічних змінних);
• SET PERSIST_ONLY - лише після перезапуску. Для змінних, які не можна змінити на льоту (наприклад, innodb_buffer_pool_instances): «запам'ятати й застосувати при наступному старті».

Скасувати збережене:

RESET PERSIST max_connections;   -- прибрати одну змінну з mysqld-auto.cnf
RESET PERSIST;                   -- усі

RESET PERSIST не змінює поточного значення - лише прибирає його з файлу.

Звідки взялося поточне значення:

SELECT variable_name, variable_source, variable_path, set_time, set_user
FROM performance_schema.variables_info
WHERE variable_name = 'max_connections';
-- variable_source: COMPILED / GLOBAL (my.cnf) / PERSISTED / DYNAMIC ...

SELECT * FROM performance_schema.persisted_variables;

Видно, хто й коли змінив значення - корисно для розслідування інцидентів.

Права: SET PERSIST вимагає SYSTEM_VARIABLES_ADMIN, а PERSIST_ONLY для read-only змінних - ще й PERSIST_RO_VARIABLES_ADMIN.

Підводні камені:

• два джерела правди. Конфігурація розщеплюється між my.cnf (під контролем Ansible, Docker-образу, Git) і mysqld-auto.cnf (зміни з консолі). Система керування конфігурацією може «не бачити» значення, яке реально діє. Практичне правило: SET PERSIST для оперативних змін, а потім - перенести в основну конфігурацію й зробити RESET PERSIST;
• сервер може не стартувати, якщо збережено некоректне значення для PERSIST_ONLY змінної. Вимкнути читання файлу допомагає persisted_globals_load = OFF;
• секрети в mysqld-auto.cnf (наприклад, деякі змінні плагінів) можна зберігати зашифрованими через keyring;
• керовані хмарні бази (RDS, Cloud SQL) зазвичай не дозволяють SET PERSIST - там параметри змінюються через групи параметрів провайдера.

Зміни, що діють не на всіх одразу: SET GLOBAL/PERSIST змінює глобальне значення, а наявні з'єднання зберігають свої сесійні копії змінних. Нове значення sort_buffer_size побачать лише нові підключення - у постійних воркерах (Horizon, Octane) лише після їх перезапуску.

Докладніше в документації: Збережені системні змінні

До MySQL 8.0 метадані таблиць зберігалися у файлах (.frm) окремо від даних InnoDB. Якщо сервер падав посеред DROP TABLE чи ALTER TABLE, файли й внутрішній словник InnoDB могли розійтися: таблиця «є, але не відкривається», «осиротілі» файли, розбіжність між джерелом і реплікою.

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

Що це дає на практиці:

DROP TABLE t1, t2;

Якщо t2 не існує, раніше t1 встигала видалитися. Тепер оператор падає з помилкою, і жодна таблиця не видаляється. Те саме для RENAME TABLE a TO b, c TO d - або всі перейменування, або жодного.

Чого атомарний DDL не дає - транзакційності:

Atomic DDL is not transactional DDL.

Кожен DDL-оператор, як і раніше, неявно завершує поточну транзакцію (COMMIT) перед виконанням. Тому неможливо:

START TRANSACTION;
ALTER TABLE orders ADD COLUMN source VARCHAR(20);
UPDATE orders SET source = 'web';
ALTER TABLE orders ADD INDEX idx_source (source);   -- якщо впаде, перший ALTER уже застосовано
ROLLBACK;                                            -- нічого не відкотить

Наслідки для міграцій Laravel:

• міграція з кількома змінами схеми не відкочується цілком при помилці посередині: частина колонок уже створена, а запис у таблиці migrations - ні. Повторний запуск падає на «колонка вже існує»;
• Schema:: у PostgreSQL виконується в транзакції й відкочується повністю, а в MySQL - ні. Код міграції, що «працює на PostgreSQL», на MySQL може залишити базу в проміжному стані;
• правило: одна міграція - одна логічна зміна схеми; дані й схему не змішувати в одній міграції; для відкату писати down(), а не розраховувати на транзакцію.

Що ще варто знати:

• атомарність працює для InnoDB; оператори над таблицями інших рушіїв можуть бути неатомарними;
• словник даних тепер у mysql-схемі, а INFORMATION_SCHEMA - представлення над ним, тому запити до неї значно швидші, ніж у 5.7, але статистика таблиць кешується (information_schema_stats_expiry);
• онлайн-зміни великих таблиць - окреме питання: атомарність не скасовує блокувань метаданих і копіювання таблиці при ALGORITHM=COPY.

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