MySQL vs MariaDB: innodb_buffer_pool_size — помилка налаштування «копіювати‑вставити», що вбиває продуктивність

October 13, 2025 • February 3, 2026 • Читання: 7 хв • Views: 17

Було корисно?

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

Чимало таких випадків походить від одного доброго наміру і рядка, вставленого через копіювання: innodb_buffer_pool_size. Та сама назва параметра в MySQL і MariaDB. Схожа поведінка за замовчуванням. Інша екосистема навколо, інші реалії обліку пам’яті й інші режимі відмов при перевищенні. Це ручка тюнінгу, яка може зробити вас генієм — або людиною, що «оптимізувала» продакшн у кратер.

Що таке buffer pool насправді (і чим він не є)

InnoDB buffer pool — основний кеш для даних і індексів InnoDB. Коли все в порядку, саме звідти виконуються читання й там зберігаються «брудні» сторінки перед скиданням на диск. Коли все йде не так, саме туди йдуть ваші бюджетні пам’ятні ресурси, щоб померти.

Що він кешує

Сторінки даних (рядки таблиць, збережені в сторінках)
Сторінки індексів (вузли B‑дерева)
Записи вставного буфера / change buffer (залежно від навантаження)
Структури адаптивного хеш‑індексу (якщо ввімкнено, залежить від навантаження)

Що він не покриває

Саме тут починається помилка налаштування через копіювання. Люди ставлять buffer pool на «80% RAM» і святкують перемогу, забуваючи, що InnoDB — не єдине, що живе в пам’яті.

Пам’ять на підключення (sort buffers, join buffers, тимчасові таблиці) може значно перевищити ваші очікування під високою конкуренцією.
Кеш бінарного логу, буфери реплікації та стеки потоків існують, навіть якщо ви про них не думаєте.
Лог‑буфер InnoDB та внутрішні структури пам’яті не є «безкоштовними».
Кеш сторінок ОС важливий, навіть з InnoDB. Якщо його задавити, постраждає metadata I/O, binlog I/O і все, що не обслуговується з buffer pool.
Інші демони (агенти бекапу, моніторинг, sidecar) не припиняють споживати пам’ять тільки тому, що ви написали в конфігурації «впевнено».

Як ментальна модель: innodb_buffer_pool_size — це найбільше передбачуване відро, а не вся вечірка в басейні.

Жарт №1: Копіювання конфігурацій баз даних схоже на позичання чужих окулярів: ви точно щось побачите, але не те, що вам потрібно.

MySQL vs MariaDB: чому копіювання‑вставлення шкодить

На рівні параметрів MySQL і MariaDB часто виглядають взаємозамінними. На рівні експлуатації вони — брати, які виросли в різних домівках і мають різні звички.

Та сама «ручка», інші початкові налаштування

У обох продуктів buffer pool — домінантний споживач пам’яті InnoDB. Але результат «встанови побільше» залежить від:

Поведеннь специфічних для версії (особливо щодо скидання сторінок, потоків очищення і того, як відображаються метрики)
Початкових налаштувань пакету дистрибуції (ліміти systemd, обмеження cgroup, розташування tmpdir)
Функцій інших рушіїв (MariaDB і MySQL розійшлися у кількох підсистемах; навіть якщо InnoDB «схожий», точки інтеграції різні)
Різниць в обсервабіті (що легко виміряти змінює те, що безпечно налаштовувати)

Класичний режим відмов при копіюванні‑вставленні

Класична помилка виглядає так:

У вас є MySQL‑сервер, де buffer pool встановлено на 75–80% RAM. Все працює.
Ви розгортаєте MariaDB (або навпаки) на хостах з «такою самою RAM».
Ви повторно використовуєте той самий фрагмент my.cnf зі тим самим розміром buffer pool.
Нове середовище має інші вимоги до запасу: більше підключень, інша поведінка тимчасових таблиць, інші потреби файлового кешу, інша політика ядра щодо пам’яті, інші обмеження cgroup або просто інше навантаження.
Виникає тиск на пам’ять. Ядро починає свопити. Затримки вибухають. Підсистема I/O перетворюється на лампу‑грівачку.

Параметр не змінився. Фізика — змінилася.

Чому перевеликі значення вбивають продуктивність у дуже специфічний спосіб

Коли ви встановлюєте innodb_buffer_pool_size надто великим, ви не отримуєте «трохи гірше». Ви отримуєте «іншу систему». Хост починає агресивно звільняти пам’ять, потенційно своплячи частини бази даних, які ви вважали «гарячими». Тепер читання, що були попаданнями в пам’ять, стають серйозними page‑fault. Записи затримуються за I/O. Поведінка скидання InnoDB може стати непередбачуваною, бо ОС тепер виступає планувальником вашого болю.

Навіть без свопа, задавлення кешу сторінок ОС може нашкодити: бінарні логи, relay‑логи, метадані таблиць і журнал файлової системи конкурують за крихти. Ваша БД починає поводитися так, наче вона працює з мережевого диска 2009 року.

Цікавинки та історичний контекст (коротко, корисно)

InnoDB не завжди був дефолтом. Історично MySQL за замовчуванням використовував MyISAM; сучасне сприйняття «все — InnoDB» з’явилося відносно недавно в продакшні.
Раніше buffer pool було простіше пояснити. Старі версії мали менше рухомих частин (і менше поверхонь для спостереження), тому «просто зробіть його великим» виглядало безпечнішим, ніж насправді.
MariaDB відгалузилася від MySQL після змін у Sun/Oracle епосі. Форки зберегли багато імен змінних, що добре для знайомства і погано для надмірної впевненості при копіюванні.
Інстанси buffer pool з’явилися, щоб зменшити контенцію. Розбиття buffer pool на інстанси зменшило мути добутку на мультиядерних системах, але також додало ще одне вимірювання, яке люди вгадують.
Поведінка свопінгу в Linux змінилася з часом. За замовчуванням ядра та використання cgroup у контейнеризованих розгортаннях зробили «пам’ять» менш стабільною обіцянкою, ніж «встановлена RAM».
Performance Schema змінила способи діагностики пам’яті й очікувань по затримках. Краще інструментування дає кращий тюнінг — якщо його ввімкнути та опитувати.
Адаптація SSD змінила криву штрафу. На дисках із обертанням пропуск була повільною; на SSD — «менш повільна», що спокушало команди недооцінювати інвестиції в коректний кеш допоки конкуренція не показала зворотне.
Хмарні типи інстансів перекроїли очікування. Співвідношення RAM до I/O, кредити на бурст, та мережеві диски зробили однаковий розмір buffer pool по-різному поводитися в різних середовищах.
Transparent Huge Pages (THP) став повторним винуватцем проблем. Не налаштування бази даних, але взаємодіє з патернами виділення пам’яті так, що проявляється як паузи і піки затримок.

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

Ось що робити, коли команда застосунків кричить, а у вас є 15 хвилин знайти вузьке місце. Мета — не ідеальний тюнінг. Мета — визначити, чи причетний innodb_buffer_pool_size і чи ви страждаєте від тиску пам’яті, насичення I/O чи блокувань.

Спочатку: підтвердіть, що хост не брешe вам про пам’ять

Перевірте активність свопу і major faults. Якщо свопинг активний — перестаньте зосереджуватися на тюнінгу запитів як на першому кроці.
Перевірте обмеження cgroup. У контейнерах або systemd‑слайсах «free -h» може виглядати нормально, поки база в коробці.
Перевірте логи OOM killer. Якщо ядро вбиває процеси — ваш розмір buffer pool тепер має кримінальний відтінок.

По‑друге: з’ясуйте, чи читання — це попадання в кеш чи диск

Коефіцієнт попадання buffer pool і читання. Шукайте зростання фізичних читань і низький коефіцієнт попадання при стабільному навантаженні.
I/O wait і насиченість пристрою. Якщо диски завантажені, ваш «повільний додаток» може бути тим, що сховище — вузьке місце.

По‑третє: перевірте, чи записи не зависають за скиданнями

Відсоток брудних сторінок. Якщо він високий і не знижується — ви пов’язані з flush.
Вік контрольної точки / тиск логу. Якщо тиск redo‑логу високий, записи будуть затримуватися незалежно від розміру buffer pool.

Дерево рішень (швидко і грубо)

Свопінг або OOM? Спочатку зменшіть використання пам’яті. Зазвичай це означає зменшення buffer pool, контроль пам’яті на підключеннях або обидва підходи.
Без свопу, але I/O насичений і низький hit rate? Buffer pool може бути замалим або навантаження не підходить. Збільшуйте обережно, якщо є запас.
I/O насичений з високим hit rate? Ймовірно, проблема з записами: flush‑налаштування, fsync, binlog або латентність сховища. Не «вирішуйте» це, надуваючи buffer pool.

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

Це реальні команди, які можна виконати на Linux‑хості і сервері MySQL/MariaDB. Кожна задача включає (1) команду, (2) що означає типовий вивід, і (3) яке рішення приймати.

Task 1: Confirm which server you’re actually running

cr0x@server:~$ mysql --version
mysql  Ver 15.1 Distrib 10.11.6-MariaDB, for Linux (x86_64) using readline 5.1

Значення: Це MariaDB 10.11.x. Якщо ви очікували Oracle MySQL 8.0 — ваш план дій щойно став художнім фантастичним твором.

Рішення: Використовуйте інструменти і метрики специфічні для MariaDB та будьте обережні з припущеннями щодо поведінки або значень за замовчуванням MySQL 8.0.

Task 2: Check current buffer pool size and related knobs

cr0x@server:~$ mysql -NBe "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_instances','innodb_page_size','innodb_flush_method','innodb_flush_neighbors')"
innodb_buffer_pool_size	25769803776
innodb_buffer_pool_instances	8
innodb_page_size	16384
innodb_flush_method	O_DIRECT
innodb_flush_neighbors	0

Значення: 24 GiB buffer pool, 8 інстансів, 16 KiB сторінки, увімкнено O_DIRECT. O_DIRECT зменшує подвійне буферизацію, але підвищує залежність від правильного розміру buffer pool.

Рішення: Порівняйте цей розмір з реальними обмеженнями пам’яті і загальною слідою MySQL. Не думайте, що «24 GiB нормально», бо на коробці «32 GiB встановлено».

Task 3: Check memory at OS level (and whether swap is being used)

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:            31Gi        29Gi       512Mi       1.2Gi       1.6Gi       1.1Gi
Swap:          4.0Gi       2.6Gi       1.4Gi

Значення: Своп активний і значущий. «Available» пам’яті мало. Ядро під тиском.

Рішення: Припиніть спочатку тюнінг запитів. Плануйте зменшення споживання пам’яті MySQL (buffer pool і/або буфери на підключення), додайте RAM або зменшіть конкуренцію.

Task 4: Verify swapping is current, not historical

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  1 2700000 480000  24000 820000  12   45  1800  2400  900 1400 18  6 48 28  0
 4  2 2702000 470000  24000 810000  20   60  2000  2600 1100 1600 20  7 41 32  0
 2  1 2705000 460000  24000 800000  10   30  1700  2100  980 1500 17  5 52 26  0

Значення: Ненульові si/so вказують на те, що зараз йде своп‑ін і своп‑аут. CPU wa (IO wait) високий.

Рішення: Трактуйте це як аварійну ситуацію: швидко знизьте тиск на пам’ять або очікуйте каскадних затримок.

Task 5: Confirm the service memory constraints (systemd)

cr0x@server:~$ systemctl show mariadb -p MemoryMax -p MemoryHigh -p TasksMax
MemoryMax=0
MemoryHigh=0
TasksMax=3096

Значення: Тут немає явного обмеження systemd по пам’яті. Якщо ви в контейнері, це не розкаже всієї історії.

Рішення: Якщо це в Kubernetes/Docker — перевіряйте ліміти cgroup далі. Якщо це bare metal/VM — переходьте до пам’яті процесу MySQL.

Task 6: Check cgroup memory limit (containers or slices)

cr0x@server:~$ cat /sys/fs/cgroup/memory.max
21474836480

Значення: Групу процесів обмежено 20 GiB. Якщо ваш buffer pool — 24 GiB, ви свідомо спроектували OOM‑подію з паперами.

Рішення: Зменшіть innodb_buffer_pool_size нижче ліміту cgroup з запасом для підключень і накладних витрат ОС, або підніміть ліміт.

Task 7: Find the real MySQL/MariaDB process RSS and mapping behavior

cr0x@server:~$ ps -o pid,cmd,rss,vsz --sort=-rss -C mysqld | head -n 5
 1423 /usr/sbin/mariadbd 28765432 32298124

Значення: RSS ~27.4 GiB, VSZ ~30.8 GiB. Процес уже близько до RAM. Це не включає всю пам’ять ядра або кеш сторінок.

Рішення: Якщо є своп/тиск — зменшуйте buffer pool і/або інші буфери; розгляньте обмеження підключень.

Task 8: Check MySQL status: buffer pool usage, reads, and dirty pages

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free')"
Innodb_buffer_pool_pages_total	1572864
Innodb_buffer_pool_pages_free	1024
Innodb_buffer_pool_read_requests	9842331123
Innodb_buffer_pool_reads	83422119
Innodb_buffer_pool_pages_dirty	210432
Innodb_buffer_pool_wait_free	9821

Значення: Buffer pool практично повний (лише 1024 вільні сторінки). Фізичних читань багато. wait_free вказує на потоки, що чекають на вільні сторінки — класичний знак тиску і/або поганого скидання.

Рішення: Якщо пам’ять хоста натягнута — ви, можливо, перебралися з розміром і свопите. Якщо пам’ять хоста в порядку — збільшуйте buffer pool обережно або виправляйте I/O/flushing. Не вгадуйте: зіставляйте з метриками ОС.

Task 9: Compute buffer pool hit ratio (quick approximation)

cr0x@server:~$ mysql -NBe "SELECT ROUND((1- (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads')/(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'))*100,2) AS hit_ratio_percent;"
99.15

Значення: Hit ratio ≈ 99.15%. Звучить добре, але це може бути недостатньо на великій шкалі. 0.85% промахів при мільярдах запитів — це багато читань з диска.

Рішення: Якщо I/O насичений і промахи корелюють з навантаженням — buffer pool може бути замалим. Якщо I/O насичений, але промахи не зростають — дивіться на записи/binlog/fsync/flush.

Task 10: Check dirty page percentage and flushing pressure

cr0x@server:~$ mysql -NBe "SELECT ROUND(100*(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_dirty')/(SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_total'),2) AS dirty_pct;"
13.38

Значення: Брудні сторінки ≈ 13%. Саме по собі не шалено. Але якщо цей відсоток росте і тримається високо з блокуваннями — ви відстаєте зі скиданням.

Рішення: Якщо латентність записів зростає і dirty pct залишається високим — перевірте пропускну здатність I/O, налаштування flush та розмір/тиск redo‑логу. Не «вирішуйте» це більшим buffer pool, якщо немає запасу по I/O.

Task 11: Check current connection count and max connections

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
Threads_connected	482
max_connections	2000

Значення: 482 активних підключень. З дозволеним max_connections у 2000 ви можете отримати «смерть пам’яті» від тисячі стеків потоку й буферів на сесію.

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

Task 12: Inspect per-connection memory defaults that often explode

cr0x@server:~$ mysql -NBe "SHOW VARIABLES WHERE Variable_name IN ('sort_buffer_size','join_buffer_size','read_buffer_size','read_rnd_buffer_size','tmp_table_size','max_heap_table_size','thread_stack')"
sort_buffer_size	262144
join_buffer_size	262144
read_buffer_size	131072
read_rnd_buffer_size	262144
tmp_table_size	16777216
max_heap_table_size	16777216
thread_stack	299008

Значення: Тут значення за замовчуванням помірні, але «помірне × сотні підключень» накопичується, а тимчасові таблиці можуть виливатися на диск або споживати пам’ять залежно від патернів запитів.

Рішення: Якщо ви бачите тиск на пам’ять при високій конкуренції — не тільки зменшуйте buffer pool, а й виправляйте пулінг підключень і запити, що створюють великі тимчасові таблиці.

Task 13: Check whether temp tables are hitting disk

cr0x@server:~$ mysql -NBe "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
Created_tmp_disk_tables	1284921
Created_tmp_tables	4422103

Значення: Значна частка тимчасових таблиць створюється на диску. Це додатковий I/O, часто помилково діагностований як «buffer pool занадто малий».

Рішення: Перегляньте запити та індекси; розгляньте підвищення лімітів tmp tables лише якщо довели наявність пам’ятного запасу і що навантаження від цього виграє.

Task 14: Identify IO saturation and latency on the database volume

cr0x@server:~$ iostat -x 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    6.11   31.45    0.00   44.22

Device            r/s     w/s   rkB/s   wkB/s  await  aqu-sz  %util
nvme0n1         620.0  1140.0 49600.0 88400.0   9.80    3.10  96.00

Значення: Пристрій ~96% завантажений, await ≈ 10ms. Якщо це NVMe — 10ms підозріло високо і зазвичай означає чергування та контенцію.

Рішення: Якщо I/O близький до насичення, більший buffer pool може допомогти читанням, якщо є запас RAM, але він не вирішить затримки записів, спричинені fsync/binlog/flush. Підтвердіть, який тип I/O домінує.

Task 15: Check top IO consumers (is it mysqld or something else?)

cr0x@server:~$ sudo iotop -o -b -n 3
Total DISK READ: 45.21 M/s | Total DISK WRITE: 86.73 M/s
  PID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1423 be/4 mysql     42.10 M/s   80.22 M/s   12.00 %  92.00 % mariadbd
 2211 be/4 root       0.10 M/s    6.20 M/s    0.00 %   4.00 % backup-agent

Значення: MariaDB домінує в I/O і має своп‑ін (SWAPIN%). Це кошмарна комбінація: I/O‑обмеження та трешинг свопа.

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

Task 16: Check InnoDB engine status for the story behind the metrics

cr0x@server:~$ mysql -NBe "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2025-12-29 10:12:19 0x7f3c9c2fe700 INNODB MONITOR OUTPUT
=====================================
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 27487790694
Dictionary memory allocated 612345678
Buffer pool size   1572864
Free buffers       1024
Database pages     1559000
Old database pages 574000
Modified db pages  210432
Pending reads 0
Pending writes: LRU 0, flush list 48, single page 0
Pages made young 112334455, not young 99887766
...

Значення: Видно pending writes і modified pages. Pending flush list вказує на тиск при скиданні; dictionary memory показує додаткову накладну пам’ять поза buffer pool.

Рішення: Якщо pending writes лишаються ненульовими і ростуть — перевірте пропускну здатність I/O і налаштування скидання; якщо dictionary memory величезна — розгляньте надмірність схеми/індексів і тиск кешування метаданих.

Три корпоративні міні‑історії з передової

Міні‑історія 1: Інцидент через хибне припущення

Вони мігрували сервіс, що обслуговує клієнтів, з MySQL на MariaDB, бо відділ закупівель полюбив ліцензування, а інженери — ідею «drop‑in сумісності». Файл конфігурації поїхав за ними, разом з гордим innodb_buffer_pool_size, встановленим як солідний відсоток RAM.

Розгортання в staging виглядало нормально. Звісно — staging мав меншу конкуренцію і менше фонового завдання. У продакшні нові вузли MariaDB жили в середовищі контейнерного рантайму з лімітом пам’яті, про який ніхто з бази даних не знав. Хост мав багато RAM. Контейнер — ні.

Через дві години після переходу крива затримок плавно почала підніматися. Потім перетворилася на піки. Спочатку звинувачували запити. Потім балансувальник. Потім «хмару». Зрештою хтось виконав одну команду в потрібному місці і побачив ліміт cgroup.

Buffer pool був більшим за ліміт пам’яті. Ядро зробило те, що роблять ядра: OOM‑kill або своп‑трешинг, поки сервіс не виглядав як привид. Вони зменшили buffer pool, обмежили підключення — і система відновилася, не змінивши жодного запиту.

Хибне припущення було не в MySQL проти MariaDB. Воно полягало в тому, що «RAM на коробці» означає «RAM доступну для mysqld». У 2025‑му це не припущення. Це баг.

Міні‑історія 2: Оптимізація, що обернулася проти

Інша команда мала write‑heavy навантаження і помітила зростання читань з диска. Хтось запропонував класичне рішення: зробити buffer pool величезним, щоб більше сторінок залишалося «гарячими». Вони агресивно підняли його під час вікна обслуговування, спостерігали покращення hit rate і святкували.

Потім почався лаг репліки. Не відразу. За кілька днів, під час пікового трафіку, репліка відстала і не наздоганяла. Праймрі був «в порядку», але репліки тонули. Тому користувачі все ще відчували біль — бо читання були спрямовані на репліки.

Корінь не був містичним. При збільшеному buffer pool кеш сторінок ОС був задавлений. Бінарні логи і relay‑логи перестали кешуватися ефективно. Потік IO та SQL на репліках перетворився на постійний парад промахів кеша і очікувань fsync. До того ж великий buffer pool дозволив накопичувати більше «брудних» даних, підвищуючи вартість відновлення під час сплесків.

Вони трохи зменшили buffer pool, подбали, щоб redo/binlog I/O мали запас, і підтвердили компроміс метриками. «Оптимізація» не була універсальною — вона була неправильною в контексті. Продуктивність — це бюджет, а не бажання.

Міні‑історія 3: Нудна, але правильна практика, що врятувала

Цей випадок без гламуру, саме тому він працював. Компанія з мішаним флотом MySQL і MariaDB мала стандартну практику: кожен хост бази даних звітував (1) ефективні ліміти пам’яті, (2) mysqld RSS, (3) активність свопа, і (4) метрики buffer pool в одному дашборді. Не «приємна опція». А як умова розгортання.

Коли команда просила більший buffer pool «бо читання повільні», SRE вимагали worksheet по місткості: поточний RSS, очікувана кількість підключень, обмеження пам’яті на підключення і виміряний профіль I/O. Немає worksheet — немає змін.

Одної п’ятниці новий сервіс випадково відкрив значно більше підключень, ніж очікували. База не впала. Вона стала повільнішою, але залишилася в строю. Чому? Бо вони залишили запас і встановили розумні обмеження підключень. Buffer pool не був встановлений «про‑мега» відсотком; він був встановлений згідно з тим, що система могла дозволити собі.

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

Як безпечно підбирати innodb_buffer_pool_size

Ось упереджена порада: припиніть використовувати одне правило‑відсоток по всьому флоту. Використовуйте метод. Якщо хочете правило великого пальця — заслужіть його вимірюванням.

Крок 1: Знайте модель розгортання

Bare metal / VM: Зазвичай RAM передбачувана, але ви все одно ділитеся з кешем ОС і фоновими службами.
Контейнер / cgroup‑обмеження: Ліміт — це істина. Все інше — оповідання.

Крок 2: Бюджетуйте пам’ять явно (не махайте рукою на «інші»)

Практичний продакшн‑бюджет виглядає так:

Buffer pool: великий шматок
Пам’ять на підключення: оцінка в найгіршому випадку, або вмикайте ліміти, щоб найгірший випадок не відбувся
Накладні InnoDB: dictionary, внутрішні структури, adaptive hash (якщо використовуєте), purge тощо
ОС і кеш файлової системи: особливо важливо для binlog/relay log і метаданих
Операційний запас: бекапи, зміни схем, сплески навантаження і те, що ваш майбутній я зробить щось ризиковане

Крок 3: Уважайте своп як ознаку поразки

В базах даних своп рідко буває «добрим». Це обрив продуктивності. Якщо бачите активний своп‑ін/своп‑аут на хості бази — це не «використання всіх ресурсів». Це задуха.

Жарт №2: Якщо ваша база свопить, вона не «мультизадачна». Вона просто намагається пригадати, куди поклала ключі.

Крок 4: Використовуйте інстанси buffer pool правильно

Інстанси buffer pool зменшують контенцію, але існує розумний діапазон:

Замало інстансів при великих пулах збільшує контенцію.
Занадто багато інстансів додає накладні витрати і знижує ефективність. Деякі метрики також стають шумнішими.

Виберіть розумне число для вашого розміру і CPU, потім вимірюйте. Не копіюйте «8», бо колись бачили це у блозі з ери SATA SSD.

Крок 5: Не задавлюйте I/O, задавивши кеш ОС

Особливо в середовищах з інтенсивною реплікацією, I/O бінарних логів і relay‑логів може вирішувати долю системи. Навіть якщо читання InnoDB обслуговуються з buffer pool, у вашій операційній реальності є логи, метадані і інші файлові операції. Якщо віддати все buffer pool, ви змусите решту системи звертатися до диска на кожному кроці. Тоді ви звинуватите InnoDB. Класика.

Один вислів про надійність (парафраз)

Парафразована ідея: надійність походить із проєктування з урахуванням відмов, а не з припущення, що їх не буде. — John Allspaw

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

Цей розділ має бути некомфортно знайомим.

1) Симптом: раптові піки затримок після «настроювання пам’яті»

Корінь: Buffer pool встановлено занадто високим, хост починає свопити або агресивно звільняти пам’ять.

Виправлення: Зменшіть innodb_buffer_pool_size, щоб відновити запас; обмежте підключення; переконайтеся, що своп вимкнено або не використовується; перевірте ліміти cgroup.

2) Симптом: висока завантаженість I/O, але коефіцієнт попадання buffer pool «добрий»

Корінь: I/O домінують записи (redo, flush, binlog) або тимчасові таблиці, а не читання InnoDB.

Виправлення: Перевірте брудні сторінки, pending flushes, поведінку fsync для binlog, використання тимчасових таблиць; налаштуйте flushing і сховище; не збільшуйте buffer pool сліпо.

3) Симптом: репліки відстають більше після збільшення buffer pool

Корінь: Кеш сторінок ОС був задавлений; relay log/binlog I/O стали некешованими; репліки стали обмежені латентністю I/O.

Виправлення: Трохи зменшіть buffer pool; забезпечте запас для лог‑I/O; розгляньте винесення логів на окремі пристрої при потребі.

4) Симптом: продуктивність деградує лише під час пікових підключень

Корінь: Сплески пам’яті на підключення, забагато потоків, тимчасові таблиці створюють I/O і тиск пам’яті.

Виправлення: Використовуйте пулінг підключень; зменшіть max_connections; перегляньте розміри буферів на сесію; виправте запити, що виливаються на диск.

5) Симптом: OOM killer вбиває mysqld після перезапуску

Корінь: Buffer pool та інші алокації відбуваються швидко при старті; у поєднанні з лімітами cgroup або малим запасом це призводить до OOM.

Виправлення: Правильно підберіть buffer pool відповідно до реальних лімітів; забезпечте запас при прогріванні; розгляньте поетапні рестарти і попередні перевірки.

6) Симптом: «Ми збільшили buffer pool, але нічого не покращилось»

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

Виправлення: Вимірюйте обсяг промахів buffer pool, а не лише співвідношення; визначте головні події очікування; аналізуйте латентність I/O; тонко налаштовуйте запити та індекси за реальними вузькими місцями.

Чеклісти / покроковий план

Checklist A: Pre-change safety checks (do this before touching buffer pool)

Підтвердіть flavor і версію сервера (mysql --version).
Підтвердіть ліміти пам’яті (systemd і cgroups).
Занотуйте поточні метрики buffer pool: розмір, вільні сторінки, читання, wait_free, брудні сторінки.
Занотуйте метрики ОС: використання свопа, major faults, завантаження I/O, латентність I/O.
Занотуйте метрики підключень: Threads_connected, max_connections і пікові значення.
Перевірте використання тимчасових таблиць і створення дискових tmp таблиць.

Checklist B: Step-by-step tuning plan that won’t ruin your weekend

Визначте мету. Ви намагаєтеся зменшити читальний I/O? Знизити піки затримок? Зупинити своп? Це різні проблеми.
Встановіть ціль запасу. Оберіть конкретне число (наприклад, зберігайте кілька GiB вільними і тримайте своп неактивним).
Регулюйте buffer pool малими кроками. Великі стрибки ховають причинно‑наслідкові зв’язки.
Слідкуйте за потрібними метриками протягом як мінімум одного циклу навантаження. Не п’ять хвилин. Цикл.
Якщо з’явився своп — відкотіться негайно. Не «почекаємо й подивимось». Своп сам по собі не вилікується під піковим навантаженням.
Після стабілізації перегляньте патерни запитів. Якщо ви створюєте тимчасові таблиці або робите повні скани, buffer pool — не єдиний важіль.

Checklist C: Emergency mitigation when you are already swapping

Зменшіть вхідне навантаження (rate limiting, read shedding, вимкніть неважливі задачі).
Швидко знизьте конкуренцію підключень (обмеження пулу в додатку, ліміти проксі).
Зменшіть розмір buffer pool при першій безпечній нагоді (може потребувати рестарту залежно від версії).
Зупиніть ресурсоємні фоні задачі (аналітичні запити, бекапи, якщо вони трешать I/O).
Переконайтеся, що своп‑активність припинилася (vmstat, iotop).

FAQ

1) Can I use “80% of RAM” for innodb_buffer_pool_size on both MySQL and MariaDB?

Можна, але ви обираєте забобон замість інженерії. Почніть з консервативного розміру, що залишає реальний запас, і регулюйте на основі активності свопа, метрик I/O та поведінки навантаження.

2) Why did increasing innodb_buffer_pool_size make performance worse?

Найчастіше: ви спровокували своп або задавили кеш сторінок ОС настільки, що не‑InnoDB I/O (логи, метадані, тимчасові файли) стали дисковими. Більший кеш не кращий, якщо він змушує ядро виганяти потрібні речі з пам’яті.

3) Is swapping always bad for MySQL/MariaDB?

Активний своп майже завжди шкідливий. Невелика кількість свопа, що не змінюється, може бути історичною залишковістю; безперервний своп‑ін/своп‑аут під навантаженням — аварійна ситуація по продуктивності.

4) How do I know if reads are the problem or writes are the problem?

Корелюйте промахи buffer pool (Innodb_buffer_pool_reads), завантаження пристрою/await (iostat -x) і pending flushes/брудні сторінки InnoDB (SHOW ENGINE INNODB STATUS). Якщо I/O насичений, але промахи не зростають — підозрюйте записи або тимчасові таблиці.

5) Does using O_DIRECT change how I should size the buffer pool?

Так. З O_DIRECT ви зменшуєте подвійне буферування і більше покладаєтеся на кеш InnoDB. Це може покращити передбачуваність, але також карає за недооцінку розміру і робить планування запасу критичнішим.

6) Should I set innodb_buffer_pool_instances manually?

Тільки якщо маєте причину і вимірюєте контенцію та пропускну здатність. Замало інстансів може створити вузьке місце на mutex; занадто багато — накладні витрати. Поставте розумне значення і перегляньте, якщо бачите симптоми контенції.

7) Why is my buffer pool hit ratio high but latency still bad?

Бо коефіцієнт попадання — не пропускна здатність, і він приховує абсолютну кількість промахів. Також затримки можуть походити від записів, поведінки fsync, очікувань блокувань, реплікації, мережі або латентності сховища навіть при попаданнях у кеш.

8) What’s the safest way to change innodb_buffer_pool_size?

Найбезпечніше: плановане вікно змін, малі кроки, план відкату і моніторинг. Чи динамічне змінення можливе — залежить від версії і поведінки рушія; не припускайте, що online resize безболісний під навантаженням.

9) Is MariaDB “worse” or “better” than MySQL for buffer pool behavior?

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

10) If I have plenty of RAM, should I just max out the buffer pool?

Ні. Вам все одно потрібен кеш ОС, запас для підключень і місце для операційних сплесків (бекапи, зміни схем, failover). Використовуйте RAM для стабільності, а не для «виграти скріншот бенчмарку».

Подальші кроки (що робити цього тижня)

Робіть це по черзі. Це нудно. Це працює.

Зробіть інвентаризацію реальності: на кожному вузлі БД зафіксуйте версію MySQL/MariaDB, ліміти cgroup/systemd, RAM, налаштування свопа і поточний розмір buffer pool.
Побудуйте мінімальний дашборд: активність свопа, mysqld RSS, device await/%util, buffer pool reads vs read requests, відсоток брудних сторінок, Threads_connected.
Визначте політику запасу: вирішіть, що означає «безпечно» (жодного активного свопа; мінімальна доступна пам’ять; ліміт підключень).
Впровадьте дисципліну підключень: якщо не можете прогнозувати конкуренцію — не можете прогнозувати пам’ять. Використовуйте пулінг; зменште max_connections до того, що реально підтримуєте.
Налаштуйте buffer pool вимірено: збільшуйте або зменшуйте на основі спостережуваного I/O і поведінки свопа, а не на унаследованих відсотках.
Напишіть runbook, який хотіли б мати: включіть швидкий план діагностики і 12+ команд вище. Майбутнє «ви» буде втомленим і не схильним терпіти розмиті поради.

Якщо візьмете лише один урок: innodb_buffer_pool_size — це не «встановив і забув» магічне число. Це контракт між InnoDB, ОС, вашим навантаженням і оперативною дисципліною. Поруште контракт — і продакшн застосує штрафні санкції.

Схожі статті

Підводні камені Docker для MySQL/MariaDB: налаштування за замовчуванням, що псують продуктивність MariaDB vs PostgreSQL на 8GB VPS: як безпечно масштабувати клієнтів MySQL vs PostgreSQL: «Раптово стало повільно» — 15‑хвилинний план діагностики для обох MariaDB проти ClickHouse: як перенести аналітику, коли звіти працюють повільно MySQL чи PostgreSQL на VPS з 4 ГБ ОЗП: що налаштувати насамперед для вебсайтів