Що таке буферний пул InnoDB і як вибрати його розмір?

Question

Accepted Answer

Буферний пул (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), щоб зменшити конкуренцію.

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

Що таке буферний пул InnoDB і як вибрати його розмір?

Схожі питання