MySQL vs MariaDB: тимчасові таблиці на диску — як зупинити їх назавжди

Що насправді означає «тимчасові таблиці на диску» (і чому це важливо)

Коли MySQL або MariaDB не може виконати запит виключно в пам’яті, він створює внутрішню тимчасову таблицю. Це не те саме, що створена користувачем CREATE TEMPORARY TABLE. Внутрішні тимчасові таблиці — це тимчасові робочі простори двигуна, які використовуються для:

• GROUP BY і DISTINCT без корисного індексу
• ORDER BY, який не може бути задоволений індексом (часто при відсутності або неефективності LIMIT)
• похідних таблиць / підзапитів / представлень, які оптимізатор вирішує матеріалізувати
• деяких віконних функцій і складних операцій над наборами

Найболючіше: внутрішні тимчасові таблиці можуть жити в пам’яті або на диску. У старих уявленнях «пам’ятна тимчасова таблиця» означала движок MEMORY/HEAP, а «дискова» — MyISAM. Сучасний MySQL ускладнює це: внутрішні тимчасові таблиці можуть використовувати InnoDB і його тимчасовий простір таблиць. MariaDB має свої особливості. Ваш моніторинговий показник — часто Created_tmp_disk_tables — є запізнілим індикатором, що каже: «ми пролили, ми заплатили за I/O, і тепер вдаємо, що це нормально».

Якщо у вас швидкий локальний NVMe з великою кеш-пам’яттю, ви можете вижити. Якщо ви на мережевому сховищі або на гучному хмарному томі, дискові тимчасові таблиці — це фактично податок на продуктивність, який ви платите при кожному стрибку трафіку.

Жарт №1: Дискова тимчасова таблиця — це як робити податки на біговій доріжці: технічно можливо, емоційно дорого.

MySQL vs MariaDB: де поведінка тимчасових таблиць відрізняється на практиці

Люди люблять сприймати MariaDB як «MySQL з додатковими функціями». Операційно саме так ви потрапляєте на виклик о 2-й ночі. Поведінка тимчасових таблиць не ідентична, і відмінності важливі, коли ви намагаєтесь зупинити дискові спливи «по-справжньому».

1) Вибір движка для внутрішніх тимчасових таблиць — це не однакова історія

MySQL 8.0 широко використовує власну реалізацію внутрішніх тимчасових таблиць, і коли відбувається сплив, він часто потрапляє у тимчасовий простір таблиць InnoDB (що все ще «диск»). Ви побачите артефакти в I/O тимчасових InnoDB і іноді в поведінці зростання ibtmp1.

MariaDB (особливо 10.3+) має інший оптимізатор і внутрішні механізми тимчасових таблиць, і може покладатися на Aria/MyISAM залежно від конфігурації та збірки. Термінологія у статусних лічильниках може виглядати знайомою, але приховувати інші механіки.

2) Статусні лічильники схожі, але не кажуть усієї правди

Created_tmp_disk_tables існує в обох, але те, що «диск» означає, може залежати від версії і внутрішнього движка. «Диск» може означати:

• файл у tmpdir
• сторінки в внутрішньому тимчасовому простору InnoDB
• тимчасову таблицю, що починалась в пам’яті, але конвертувалася після досягнення ліміту

3) Пороги і ліміти відрізняються в крайніх випадках

Обидві СУБД використовують танець між tmp_table_size і max_heap_table_size, щоб вирішити, чи може тимчасова таблиця бути в пам’яті. Але тригери конверсії можуть бути більш тонкими (типи даних, BLOB/TEXT, формат рядка, специфічні операції). Якщо ви просто піднімете ці дві змінні і на цьому зупинитесь, ви зменшите деякі спливи і все одно страждатимете від інших.

4) Зміни від версії до версії важливіші за брендові стереотипи

MySQL 5.7 проти 8.0 може поводитися значно відмінніше, ніж «MySQL проти MariaDB» на високому рівні. Так само MariaDB 10.1 vs 10.6. Будь-яке серйозне тонке налаштування починається з: «Яка саме версія у продакшені, і яке саме навантаження проливається?»

Швидка діагностика — план дій

Якщо у вас є 20 хвилин до наступного розбору інциденту, зробіть це в порядку. План упереджено орієнтований на швидке відокремлення, чи маєте ви справу з формою запиту, порогами пам’яті, або пропускною здатністю/затримками сховища.

Спочатку: доведіть, що це I/O тимчасових таблиць (а не випадковий диск-шум)

1. Перевірте глобальні лічильники тимчасових таблиць і темп їх зміни.
2. Перевірте, чи відповідають повільні запити стрибкам Sort_merge_passes, Created_tmp_disk_tables і збільшенню handler reads.
3. Перевірте затримки I/O на файловій системі, що розміщує tmpdir (або тимчасовий простір InnoDB).

По-друге: визначте топ-1–3 шаблони запитів, що створюють спливи

1. Витягніть топ повільних запитів у вікні інциденту.
2. Запустіть EXPLAIN і шукайте «Using temporary» / «Using filesort» / матеріалізацію.
3. Шукайте GROUP BY і ORDER BY без підтримуючих індексів, а також великі проміжні результати.

По-третє: вирішіть — виправляти SQL, налаштувати пороги або змінити поверхню спливу

1. Якщо один або два запити відповідальні: спочатку виправляйте SQL/індекси. Це найвищий ROI і найстабільніше.
2. Якщо багато запитів проливаються через малі ліміти: піднімайте обмеження тимчасових таблиць обережно, враховуючи конкурентність і тиск пам’яті.
3. Якщо спливи неминучі: перемістіть tmpdir на швидкий локальний SSD/NVMe і переконайтесь, що файлові системи та опції монтування вас не саботують.

Факти та історія, що пояснюють сучасні аномалії

1. Історично MySQL використовував MEMORY для тимчасових таблиць в пам’яті і MyISAM для дискових; ця ментальна модель досі переслідує керівництва з тонкого налаштування.
2. InnoDB став за замовчуванням у MySQL 5.5, змінивши типовий продакшен I/O і зробивши спливи тимчасових таблиць більш помітними на навантажених системах.
3. MySQL 8.0 змінив багато поведінок оптимізатора, включно з обробкою похідних таблиць і деталями реалізації внутрішніх тимчасових таблиць; оновлення можуть змінити частоту спливів без зміни SQL.
4. MariaDB суттєво відійшла від форку; це не просто «drop-in MySQL», особливо щодо можливостей оптимізатора і движків зберігання як Aria.
5. Прапори EXPLAIN «Using temporary; Using filesort» старі, але все ще корисні; вони не гарантують дискового I/O — лише те, що існує тимчасова структура.
6. tmpdir довгі роки був мінним полем продуктивності, бо за замовчуванням вказував на системні тимчасові шляхи, які можуть знаходитися на повільних root-томах.
7. «Дискові тимчасові таблиці» часто є симптомом відсутніх індексів, а не нестачі RAM; додавання пам’яті може замаскувати помилку запиту до наступного стрибка даних.
8. Міграція в хмару ускладнила ситуацію: ефемерні диски, мережеві томи і шумні сусіди перетворюють випадкові спливи на затримкові обриви.

Механіка: що змушує тимчасові таблиці йти на диск

Три великі тригери

1) Обмеження розміру. Сервер оцінює або спостерігає, що тимчасова таблиця росте понад дозволені в пам’яті межі й конвертує її в дискову. Класичні ручки:

• tmp_table_size
• max_heap_table_size

Ефективна ємність тимчасової таблиці в пам’яті зазвичай є мінімумом з цих двох для MEMORY-основаних тимчасових таблиць, але сучасні внутрішні тимчасові таблиці MySQL не завжди MEMORY. Тим не менш, ці змінні залишаються частиною дерева рішень.

2) Типи колонок і формат рядка. Якщо тимчасова таблиця має зберігати BLOB/TEXT колонки або інші структури, що не підтримуються ефективно в пам’яті, вона може пролитися навіть за великого ліміту. Ось чому «просто підніміть tmp_table_size» часто розчаровує.

3) Форма запиту, що створює величезні проміжні результати. Навіть якщо кінцевий результат малий, проміжні результати можуть бути масивними. Класичний винуватець: GROUP BY по колонці з низькою кардинальністю при з’єднанні широкої таблиці без селективних предикатів.

Чому «зупинити дискові тимчасові таблиці» — не один перемикач

Тимчасові таблиці існують тому, що оптимізатор потребує робочого простору. Ви можете зменшити дискові спливи, але ви не можете — і не повинні — намагатися повністю вимкнути тимчасові таблиці глобально. Мета:

• тримати тимчасові структури маленькими
• тримати їх в пам’яті, коли це безпечно
• уникати їх створення взагалі шляхом виправлення SQL і індексів
• коли вони йдуть на диск — робити диск швидким і передбачуваним

Є також пастка конкурентності: підняття лімітів може допомогти одному запиту, але нашкодити системі під навантаженням, коли 200 сесій одночасно виділяють більші тимчасові структури. Ви не хочете виграти бенчмарк і програти продакшен.

Практичні завдання (команди, виводи, рішення)

Ось завдання, які ви реально запускаєте під час пошуку проблем з продуктивністю. Кожне містить: команду, реалістичний вивід, що це означає і яке рішення приймаєте.

Завдання 1: Підтвердити версію і flavor (не можна налаштовувати чутки)

cr0x@server:~$ mysql -e "SELECT VERSION() AS version, @@version_comment AS comment\G"
*************************** 1. row ***************************
version: 8.0.36
comment: MySQL Community Server - GPL

Що це означає: Це MySQL 8.0.x, отже в хід іде поведінка внутрішніх тимчасових таблиць і тимчасовий простір InnoDB.

Рішення: Використовуйте інструменти інструментації MySQL (Performance Schema, sys schema views). Не застосовуйте змінні, специфічні для MariaDB, або старі настанови з MySQL 5.6.

Завдання 2: Перевірити лічильники створених тимчасових таблиць і їх співвідношення

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 184229   |
| Created_tmp_files       | 912      |
| Created_tmp_tables      | 2441102  |
+-------------------------+----------+

Що це означає: Дискові тимчасові таблиці існують і не рідкість. Співвідношення важливе: 184k дискових з 2.4M тимчасових таблиць ≈ 7.5%.

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

Завдання 3: Виміряти темп створення дискових тимчасових таблиць (не лише підсумки)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';" && sleep 10 && mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';"
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 184229 |
+-------------------------+--------+
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 184411 |
+-------------------------+--------+

Що це означає: 182 дискові тимчасові таблиці за 10 секунд: ≈18/с. Це достатньо, щоб створити навантаження на сховище, якщо вони нетривіальні.

Рішення: Якщо затримки сховища корелюють, вважайте це головним підозрюваним, а не фоновим шумом.

Завдання 4: Перевірити пороги пам’яті для тимчасових таблиць

cr0x@server:~$ mysql -e "SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';"
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| tmp_table_size| 16777216 |
+---------------+----------+
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_heap_table_size| 16777216 |
+--------------------+----------+

Що це означає: Обидва значення — 16MB. Це консервативно, і це змусить проливатися на диск для помірно великих GROUP BY/ORDER BY операцій.

Рішення: Розгляньте поступове підвищення, але лише після визначення, чи спливи спричинені розміром, типами даних або формою запиту. Не виставляйте одразу 1G і не тіштесь.

Завдання 5: Перевірити, куди потрапляють тимчасові файли (tmpdir)

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'tmpdir';"
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| tmpdir        | /tmp       |
+---------------+------------+

Що це означає: Спливи тимчасових таблиць потрапляють у /tmp. На багатьох серверах це коренева файловa система, яка часто є найменш бажаним диском.

Рішення: Якщо у вас є локальний NVMe, перемістіть tmpdir на виділений монтувальний пункт зі стабільною продуктивністю і моніторингом вільного місця.

Завдання 6: Перевірити файлову систему /tmp і опції монтування

cr0x@server:~$ df -hT /tmp && mount | grep ' /tmp '
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/nvme0n1p2 ext4  80G   72G  4.5G  95% /
tmpfs          tmpfs 16G   1.2G  15G   8% /tmp
tmpfs on /tmp type tmpfs (rw,nosuid,nodev,relatime,size=16384m)

Що це означає: Тут /tmp фактично — tmpfs (у пам’яті). Це швидко, але це також очікування тиску на пам’ять.

Рішення: Якщо ви бачите ризик OOM або свопінгу, перестаньте використовувати tmpfs для БД тимчасових файлів. Краще швидкий локальний SSD з квотами і моніторингом, якщо у вас немає явного запасу пам’яті і строгих лімітів.

Завдання 7: Перевірити поведінку тимчасового простору InnoDB (типово для MySQL 8.0)

cr0x@server:~$ mysql -e "SHOW GLOBAL VARIABLES LIKE 'innodb_temp_data_file_path'; SHOW GLOBAL STATUS LIKE 'Innodb_temp%';"
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+--------------------------+------------------------+
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Innodb_temp_tablespaces    | 0       |
| Innodb_temp_tables         | 19231   |
| Innodb_temp_table_pages    | 8112    |
+----------------------------+---------+

Що це означає: Є тимчасова активність у InnoDB. Спливи можуть не відображатися як файли в tmpdir, як радили старі інструкції.

Рішення: Слідкуйте також за I/O на тому томі, де лежить datadir, не лише за tmpdir. Якщо ibtmp1 росте або I/O стрибає — у вас друга поверхня спливу.

Завдання 8: Знайти «Using temporary» і «Using filesort» в реальних запитах (швидка вибірка)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT customer_id, COUNT(*) FROM orders WHERE created_at BETWEEN ? AND ? GROUP BY customer_id ORDER BY COUNT(*) DESC
COUNT_STAR: 1932
total_sec: 812.4432

Що це означає: Агрегат GROUP BY + ORDER BY — типовий кандидат на тимчасові таблиці і спливи для сортування.

Рішення: Витягніть цей точний шаблон запиту, запустіть EXPLAIN і перевірте індексацію. Не налаштовуйте глобальну пам’ять, поки не з’ясуєте, чи це просто відсутній індекс.

Завдання 9: Проаналізувати підозрілий запит і читати колонку «Extra» як детектив

cr0x@server:~$ mysql -e "EXPLAIN SELECT customer_id, COUNT(*) FROM orders WHERE created_at BETWEEN '2025-12-01' AND '2025-12-31' GROUP BY customer_id ORDER BY COUNT(*) DESC\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: range
possible_keys: idx_created_at
          key: idx_created_at
      key_len: 8
          ref: NULL
         rows: 2100000
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort

Що це означає: Двигун очікує просканувати багато рядків, потім побудувати тимчасову таблицю і відсортувати її. Це може пролитися.

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

Завдання 10: Перевірити, чи корелюють спливи з затримками сховища

cr0x@server:~$ iostat -x 1 5
Linux 6.2.0-39-generic (db01) 	12/31/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    3.91   22.14    0.00   55.73

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   980.0  4200.0 49200.0  18.30   0.45  68.0

Що це означає: %iowait і час очікування пристрою високі під час події. Це нагадує тиск записів, викликаний спливами.

Рішення: Якщо шар зберігання є вузьким місцем, зменшення спливів або переміщення їх на швидший носій — нагальне. Інакше ви оптимізуватимете запити і все одно програєте через затримки I/O.

Завдання 11: Перевірити вільне місце й іноді тиск inode там, де живуть тимчасові файли

cr0x@server:~$ df -h /var/lib/mysql /tmp && df -i /var/lib/mysql /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p3  500G  410G   65G  87% /var/lib/mysql
tmpfs            16G  1.2G   15G   8% /tmp
Filesystem       Inodes   IUsed    IFree IUse% Mounted on
/dev/nvme0n1p3 32768000 812344 31955656    3% /var/lib/mysql
tmpfs           4194304    412  4193892    1% /tmp

Що це означає: Datadir має запас, але трохи заповнений. Temp — у tmpfs, тож inode тут не проблема.

Рішення: Якщо дискові тимчасові таблиці потрапляють у datadir (тимчасовий простір InnoDB), переконайтесь, що об’єм datadir має достатньо місця. Якщо /tmp — реальна ФС і майже заповнена, виправте це перед іншими налаштуваннями.

Завдання 12: Проінспектувати поточні запущені інструкції під час спайку

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;"
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                      |
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+
| 112 | app  | 10.0.3.21:51422 | prod | Query   |   18 | Creating sort index          | SELECT ... ORDER BY ...   |
| 145 | app  | 10.0.3.18:49811 | prod | Query   |   22 | Copying to tmp table on disk | SELECT ... GROUP BY ...   |
| 201 | app  | 10.0.3.19:53301 | prod | Query   |   11 | Sending data                 | SELECT ...                |
+-----+------+-----------------+------+---------+------+------------------------------+---------------------------+

Що це означає: Маємо живі докази: «Copying to tmp table on disk» і «Creating sort index». Це сплив і робота сортування зараз.

Рішення: Захопіть тексти цих запитів (з логів додатку або Performance Schema), відтворіть і виправте. Якщо це широко по сесіях — фокусуйтеся на індексах і порогах для тимчасових таблиць.

Завдання 13: Помітити «sort merge passes», що вказують на замалий sort memory

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 18291 |
+-------------------+-------+
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 262144  |
+------------------+---------+

Що це означає: Багато merge passes: буфер сорту замалий для навантаження. Це може збільшувати дискову активність навіть без того, щоб тимчасові таблиці були основним драйвером.

Рішення: Обережно збільшуйте sort_buffer_size лише якщо розумієте конкурентність (це на сесію). Переважно виправляйте запити/індекси першими; роздування буферів — класичний шлях обміняти латентність на інциденти пам’яті.

Завдання 14: Перевірити запас пам’яті перед підвищенням пер-сесійних буферів

cr0x@server:~$ free -m
               total        used        free      shared  buff/cache   available
Mem:           65536       41220        2180        1320       22135       20240
Swap:           4096        1024        3072

Що це означає: Доступна пам’ять ≈20GB, але swap використовується. Це жовтий прапор: система вже іноді відчуває тиск пам’яті.

Рішення: Не «виправляйте дискові тимчасові таблиці» додаванням великих пер-сесійних буферів. Ви тільки перемістите біль із затримок I/O у своп-шторми.

Шаблони запитів, які створюють дискові тимчасові таблиці

Шаблон 1: GROUP BY без підтримуючого індексу

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

Що робити: Побудуйте композитний індекс, який відповідає вашому фільтру і групувальній колонці у корисному порядку, наприклад (created_at, customer_id) або (customer_id, created_at) залежно від селективності і форми запиту. Потім перевірте через EXPLAIN і фактичне виконання.

Шаблон 2: ORDER BY по виразу (або колонці, відмінній від фільтра)

ORDER BY COUNT(*) DESC (порядок агрегатів), ORDER BY LOWER(email), ORDER BY DATE(created_at) — вони часто змушують filesort і тимчасові структури.

Що робити: Якщо потрібно сортування, розгляньте попереднє обчислення значень для сортування, переписування запиту з використанням індексованих колонок або прийняття наближеного порядку. Якщо бізнес хоче «top N», переконайтесь, що запит справді використовує LIMIT і може це використати.

Шаблон 3: DISTINCT по широких рядках

SELECT DISTINCT * — SQL-еквівалент прохання дедуплікувати весь датаверхаус. Отримаєте тимчасову таблицю, тиск пам’яті і диск.

Що робити: Вибирайте лише потрібні колонки. Використовуйте звужений DISTINCT ключ. Або краще: виправте логіку join, щоб дублікати не утворювались спочатку.

Шаблон 4: Похідні таблиці, що матеріалізуються

Підзапити і представлення, які «виглядають чистими», можуть матеріалізуватися в тимчасові таблиці, особливо коли включають агрегацію або сортування. MySQL і MariaDB відрізняються в моменти, коли вони вибирають матеріалізацію, і оновлення версій можуть змінити поведінку.

Що робити: Тестуйте через EXPLAIN і слідкуйте за матеріалізацією похідної таблиці. У багатьох випадках переписування похідної таблиці в JOIN з правильними індексами зменшує тимчасову роботу.

Шаблон 5: З’єднання великих наборів перед фільтрацією

Якщо оптимізатор вибирає порядок з’єднання, який створює великий проміжний результат і фільтрує пізніше, ви побачите вибух у тимчасових і сортувальних структурах.

Що робити: Забезпечте, щоб селективні предикати були sargable (використовувались індекси). Додайте індекси, що підтримують умову з’єднання і фільтр. За необхідності використовуйте підказки оптимізатора обережно (і документуйте їх як заряджену зброю).

Налаштування, що мають значення (і ті, що марнують ваш час)

Ручки, які реально впливають на спливи тимчасових таблиць

tmp_table_size і max_heap_table_size

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

• Ефективний розмір тимчасової таблиці в пам’яті обмежений меншим із цих двох (в класичних випадках).
• Підняття їх збільшує потенційне споживання пам’яті на сесію під конкурентністю.
• Вони не виправлять спливи, спричинені типами даних або планами запитів, що генерують величезні проміжні результати.

Рекомендація: Піднімайте поступово (наприклад, 16MB → 64MB → 128MB), тільки після ідентифікації топ-винуватців спливів. Відстежуйте темпи і p95 латентності. Якщо ви одразу підскочите до 512MB — ви створюєте інцидент пам’яті, з яким не стикалися.

tmpdir

Це контролює, куди потрапляють деякі дискові тимчасові структури. Якщо tmpdir на повільному накопичувачі — кожен сплив стає I/O штормом.

Рекомендація: Розміщуйте tmpdir на швидкому локальному SSD/NVMe з моніторингом і достатнім вільним місцем. Уникайте кореневого тому. Уникайте мережевих ФС. І не ставте на tmpfs, якщо у вас немає жорстких гарантій, що ви не отримаєте OOM під піком.

internal_tmp_mem_storage_engine (MySQL)

У деяких версіях MySQL це впливає на те, чи використовують внутрішні тимчасові таблиці MEMORY або реалізацію TempTable в пам’яті. Це може змінити продуктивність і поведінку пам’яті, а також взаємодіяти з тим, як обробляються BLOB/TEXT.

Рекомендація: Не змінюйте це як перший крок. Це важіль для специфічних робочих навантажень після підтвердження поведінки движка у вашій версії.

Пер-сесійні буфери: sort_buffer_size, join_buffer_size, read_rnd_buffer_size

Вони можуть зменшити дискову роботу для сортувань і джойнів, але це пер-сесійні налаштування і вони можуть роздмухати використання пам’яті. Продакшен-системи не виконують один запит за раз. Вони виконують 400 трохи неправильних запитів одночасно.

Рекомендація: Тримайте ці буфери консервативними. Спочатку виправляйте SQL і індекси. Якщо потрібно тонко налаштувати буфери — робіть це з математикою конкурентності і навантажувальним тестуванням, а не на відчуттях.

Налаштування, які люди часто крутять, але вони зазвичай не вирішують проблему

• innodb_buffer_pool_size сам по собі: допомагає загальному I/O, але не запобігає потребі в робочому просторі для тимчасових таблиць.
• Налаштування потоків/конкурентності: можуть змінити патерни навантаження, але не виправлять причину спливів.
• «Зробіть диск швидшим» як єдиний план: це приховає поганий SQL до наступного етапу росту даних.

tmpdir, файлові системи і сховище: зробіть спливи менш болючими

Іноді уникнути спливів неможливо. Існують звітні запити. Відбувається ad-hoc аналітика. Хтось запустить «місячний звіт» о 9:55 ранку в робочий день і клянеться, що це терміново.

Тож ставтеся до I/O спливів як до класу навантаження:

• Розміщуйте тимчасові I/O на передбачуваних носіях (локальний SSD/NVMe переважно).
• Розділяйте домени відмови: не дозволяйте тимчасовим файлам заповнити ту саму ФС, що й datadir.
• Моніторьте ємність і латентність для тома тимчасового монтування явно.
• Використовуйте опції файлової системи, що відповідають вашому ризиковому апетиту. Для томів лише під тимчасові файли ви можете прийняти меншу стійкість. Але робіть це свідомо і документуйте.

Практичний макет tmpdir

Поширений підхід у продакшені:

• /var/lib/mysql на резервованому сховищі (або керованому томі з гарантією стійкості)
• /var/lib/mysqltmp на швидкому локальному диску, окрема файлова система
• tmpdir=/var/lib/mysqltmp у конфігурації

Це не зупиняє спливи, але не дає тимчасовим спливам знищувати невірну частину вашої системи.

Жарт №2

Якщо ваші тимчасові таблиці живуть на кореневій файловій системі, ви не керуєте базою даних; ви керуєте романом напруження.

Три міні-історії з практики

Міні-історія №1: Інцидент через неправильне припущення

Середня SaaS-компанія швидко мігрувала зі старішої версії MySQL на нову — вікно патчування безпеки, увага керівництва, стандартна історія. Команда припустила просту річ: спливи тимчасових таблиць видно як файли в /tmp, отже вони моніторять /tmp і вважали роботу завершеною.

Наступного понеділка дашборди загорілись червоним. Потоки додатку зависали в очікуванні відповідей БД, і затримка зберігання зростала. Дивно, що використання /tmp виглядало спокійним. Інженер на виклику першу годину ганявся за привидами: «Якщо /tmp не заповнений, то це не тимчасові таблиці».

Справжня проблема: поведінка внутрішніх тимчасових таблиць змінилася в новій версії, і вони били по тимчасовому простору InnoDB на томі datadir. Том datadir був надійний, але не швидкий, і він був поділений з іншими «шумними» робочими навантаженнями того ж класу зберігання.

Коли вони почали графити правильні лічильники (активність тимчасових InnoDB і await на пристрої datadir), картина стала ясною. Виправлення не було гламурним: перемістити поверхні спливу на швидкий локальний том, а потім виправити два топ GROUP BY запити, які виробляли масивні проміжні результати.

Вони оновили рукопис із однією важливою фразою: «Дискові тимчасові таблиці можуть не потрапляти в tmpdir.» Ніхто не аплодував, але виклики стали тихішими.

Міні-історія №2: Оптимізація, що вистрілила собі в ногу

Команда е-комерсу мала повторюваний повільний звіт. Інженер вирішив «вирішити» проблему, різко піднявши tmp_table_size і max_heap_table_size. На стендовому бенчмарку запит покращився. Усі потиснули один одному руки і перейшли далі.

Продакшен мав інші ідеї. Під час пікового розпродажу конкурентність була набагато вища. Кілька сесій одночасно запускали важкий звіт — плюс нормальний трафік додатку. Великі тимчасові таблиці довше жили в пам’яті, що звучало добре, допоки сервер не почав свопитись. Латентність вибухнула. БД не впала відразу; стала робити гірше — стала непередбачувано повільною.

Аналіз після інциденту показав, що «оптимізація» не зменшила загальну роботу; вона змінила місце її виконання. Дискові спливи стали тиском на пам’ять, що перетворилося на своп, що призвело до загального гальмування. Початковий запит все ще був погано індексований і все ще сканував великий діапазон; тепер він ще більше конкурував за RAM.

Кінцеве виправлення було нудним і ефективним: додати covering index, що підтримує фільтр і ключ групування звіту, і обмежити запуск звіту на репліці з ресурсними лімітами. Вони залишили тимчасові розміри трохи вищими, але в межах, виправданих математикою конкурентності.

Міні-історія №3: Сумний, але правильний підхід, що врятував день

Фінансова платформа мала строгий процес змін. Люди скаржилися, що це повільно. У них також була звичка виконувати реалістичні навантажувальні тести для змін конфігурації БД, включно з «що трапиться, коли три погані запити запустяться одночасно». Це було нудно, але це працювало.

Перед квартальним піком трафіку вони переглянули топ творців тимчасових таблиць за підсумками Performance Schema. Вони не ганяли кожен запит — лише топ-винуватців, що корелювали з p95 латентністю. Для кожного вимагали або зміну індексу, перепис запиту, або документоване прийняття того, що він проливається і має запускатися на звітній репліці.

Вони також впровадили просте правило для сховища: шляхи спливів тимчасових файлів живуть на виділених швидких томах з алертами по ємності і латентності. Не тому, що це було хитро, а тому, що це прибрало весь режим відмов: насичення кореневої ФС і несподіваний I/O контеншн.

Коли прийшов стрибок трафіку, вони іноді все ще проливали на диск. Але це було контрольовано, вимірювано і не заважало транзакційному I/O. Інцидент, який не стався, не отримує бюджету, але він того вартий.

Типові помилки: симптом → корінь → виправлення

1) Симптом: Created_tmp_disk_tables швидко зростає в робочі години

Корінь: Невелика кількість частих запитів робить GROUP BY/ORDER BY без підтримки індексами.

Виправлення: Знайдіть топ digest-винуватців, додайте композитні/covering індекси, зменшіть кількість проміжних рядків. Потім перевіряйте темп, а не суми.

2) Симптом: раптові стрибки затримки сховища, але /tmp спокійний

Корінь: Спливи відбуваються в тимчасовому простору InnoDB на томі datadir (поширено у MySQL 8.0).

Виправлення: Моніторте активність тимчасових InnoDB і латентність пристрою datadir. Розгляньте переміщення робочого навантаження, покращення запитів і гарантію, що клас сховища datadir витримає тимчасовий I/O.

3) Симптом: сервер свопиться після того, як ви «вирішили» спливи підняттям тимчасових розмірів

Корінь: Пер-сесійна пам’ять зросла під конкурентністю; сумарне використання пам’яті перевищило доступну RAM.

Виправлення: Відкотіть екстремальні збільшення буферів. Розрахуйте гірший сценарій пам’яті. Віддавайте перевагу виправленням запитів/індексів. Піднімайте ліміти поступово з навантажувальним тестуванням.

4) Симптом: тимчасові таблиці йдуть на диск навіть з великим tmp_table_size

Корінь: Тимчасова таблиця містить BLOB/TEXT або інші властивості, що примушують дискове представлення, або запит викликає матеріалізацію, незважаючи на ваші налаштування розміру.

Виправлення: Звужуйте вибір колонок, уникайте широких DISTINCT, переписуйте похідні таблиці і підтверджуйте поведінку движка для вашої точної версії.

5) Симптом: «Copying to tmp table on disk» у processlist, але запит «малий»

Корінь: Кінцевий вихід малий; проміжний результат великий. Порядок з’єднання і фільтри створюють великий робочий простір.

Виправлення: Додайте селективні предикати, індексуйте ключі з’єднання і забезпечте sargable умови. Перевірте через EXPLAIN і фактичні оцінки рядків.

6) Симптом: tmpdir заповнений, MySQL дає помилки, і додатки падають дивними способами

Корінь: tmpdir на малій файловій системі (часто root), важкий сплив робочого навантаження, відсутні алерти.

Виправлення: Перемістіть tmpdir на виділений том. Додайте алерти по вільному місцю. Обмежте небезпечні звіти або перемістіть їх з основного інстансу.

Контрольні списки / покроковий план

Покроково: як зупинити дискові тимчасові таблиці розумно

1. Опишіть середовище: підтвердіть точну версію MySQL/MariaDB, розклад зберігання, розташування tmpdir.
2. Виміряйте темп спливів: розрахуйте Created_tmp_disk_tables/sec у нормальні і інцидентні вікна.
3. Корелюйте з диском: перевірте device await, %iowait і метрики латентності томів.
4. Визначте топ-винуватців: витягніть топ digest запитів за загальним часом і частотою; ізолюйте кандидатів з GROUP BY/ORDER BY/DISTINCT/похідними таблицями.
5. Поясніть і відтворіть: запустіть EXPLAIN для репрезентативних запитів; підтвердіть «Using temporary/filesort» і великі оцінки рядків.
6. Спочатку виправляйте SQL/індекси: композитні індекси для фільтра+group, covering-індекси, приберіть SELECT * в DISTINCT-запитах.
7. Потім налаштуйте пороги: піднімайте tmp_table_size/max_heap_table_size помірно, якщо темпи спливів все ще високі і є запас пам’яті.
8. Зміцніть шлях tmp I/O: перемістіть tmpdir на швидке виділене сховище, налаштуйте алерти по ємності і уникайте спільного використання з ОС.
9. Запобіжні засоби: обмежте важкі звіти на репліках, плануйте задачі і обмежуйте конкурентність на рівні додатку при потребі.
10. Перевірте результат: порівняйте темпи спливів до/після, p95 латентності і await на дисках. Якщо це не змінилося — ви не виправили справжню проблему.

Мінімальний чекліст «щоб потім не шкодувати»

• tmpdir не на кореневому диску (якщо тільки root справді не швидкий і не має потрібного розміру).
• Ви можете графувати темп створення дискових тимчасових таблиць, а не лише підсумки.
• Ви знаєте топ-5 digest-запитів, що створюють тимчасові таблиці.
• У вас є політика для звітних запитів (репліка, розклад, ліміти).
• Зміни, що стосуються тимчасової пам’яті, навантажувально тестуються під конкурентністю.

Питання та відповіді

1) Чи можна повністю відключити дискові тимчасові таблиці?

Ні, реалістично — ні. Ви можете зменшити частоту спливів і зробити їх менш шкідливими. Але деякі операції потребують робочого простору, і на масштабі ви іноді проливатимете. Справжній виграш — усунути патологічні спливи, спричинені поганими планами і відсутніми індексами.

2) Якщо я виставлю tmp_table_size на 1G, чи залишаться тимчасові таблиці в пам’яті?

Іноді так для деяких тимчасових таблиць. Це також може спричинити виснаження пам’яті під конкурентністю або не допомогти, коли типи даних/операції примушують дискове представлення. Великі значення — інструмент, а не стратегія.

3) Чому я бачу «Using temporary», але Created_tmp_disk_tables не зростає?

Тому що тимчасова структура може існувати в пам’яті. «Using temporary» вказує на використання тимчасової таблиці, а не на те, що вона пролилася на диск. Також лічильники можуть залежати від внутрішнього движка і версії.

4) MySQL vs MariaDB: яка з них краще у уникненні дискових тимчасових таблиць?

Жодна не виграє за замовчуванням. Найбільші фактори — це ваша версія, поведінка оптимізатора, шаблони SQL і індекси. Обирайте за екосистемою та операційною придатністю, а потім налаштовуйте за вимірами.

5) Чи варто ставити tmpdir на tmpfs, щоб зробити спливи «швидкими»?

Тільки якщо у вас є великий запас пам’яті і ви готові до моделі відмови. tmpfs робить спливи швидкими аж до моменту, коли воно спричиняє агресію ядра. Для більшості продакшенів безпечнішим вибором є виділений швидкий диск.

6) Чи завжди дискові тимчасові таблиці — погано?

Ні. Поодинокі спливи — нормальні. Погано, коли спливи стійко високо корелюють з латентністю і I/O wait, або коли вони заповнюють файлові системи і викликають помилки.

7) Як зрозуміти, чи сплив іде в tmpdir або в тимчасовий простір InnoDB?

Використовуйте комбінацію доказів: I/O і ємність файлової системи tmpdir, I/O datadir, статусні лічильники тимчасових InnoDB і стани у processlist. Не покладайтесь на один метрик.

8) Який найефективніший спосіб виправити «Copying to tmp table on disk»?

Виправте план запиту: додайте правильний індекс, зменшіть розмір проміжних результатів і уникайте широких DISTINCT і сортування виразів. Потім забезпечте, щоб спливи падали на швидке передбачуване сховище.

9) Це проблема сховища чи бази даних?

Обидва — і тому це дратує. Поганий SQL створює спливи; повільне або контендоване сховище перетворює спливи на відмови. Розглядайте це як повно-стекове питання продуктивності: план запиту + пороги пам’яті + шлях I/O.

Висновок: наступні кроки, які справді змінюють ситуацію

Дискові тимчасові таблиці — це не моральна вада. Це підказка. Вони кажуть, що оптимізатор потребував робочого простору і не помістив його в пам’ять — або ж операція вимагала дискового представлення. Ваше завдання — вирішити, чи цей робочий простір — легітимна робота, чи симптом недбалого SQL і відсутніх індексів.

Зробіть наступні три речі:

1. Виміряйте темп спливів у моменти болю (Created_tmp_disk_tables/sec) і корелюйте його з латентністю пристрою.
2. Виправте топ-винуватців індексами і переписуванням запитів. Не налаштовуйте глобально, поки не з’ясуєте, що проливається.
3. Забезпечте, щоб спливи потрапляли кудись адекватно: виділене швидке tmp сховище, алерти по ємності і план для звітних навантажень.

Правило надійності, перефразована ідея від Werner Vogels: надійні системи не будуються надією; їх будують, проектуючи під відмову і вимірюючи реальність.