innodb_buffer_pool_size задає, скільки пам'яті InnoDB віддає під кеш сторінок даних та індексів — це найвпливовіший параметр MySQL для продуктивності на VPS, і водночас один з найчастіше занижених або, навпаки, виставлених на всю доступну RAM без залишку.
Що саме кешує buffer pool
Buffer pool тримає в пам'яті сторінки таблиць та індексів InnoDB, які читалися чи змінювалися, уникаючи повторного читання з диска при повторному звертанні до тих самих даних. Це стосується лише таблиць InnoDB: для MyISAM пам'ять кешується окремим параметром key_buffer_size, а різницю рушіїв розібрано в статті про InnoDB і MyISAM.
Якщо увесь робочий набір даних вміщується в buffer pool цілком, диск майже не використовується для читання — читає його лише запис змін і рідкі операції типу повного сканування таблиці. Якщо набір даних більший за buffer pool, частина сторінок витісняється і перечитується знову, і чим більша ця невідповідність, тим вище навантаження на диск.
Чому не можна просто віддати під буфер усю RAM
Крім InnoDB, на сервері працює операційна система, сама MySQL резервує пам'ять на кожне з'єднання під сортування і join (sort_buffer_size, join_buffer_size), а якщо MySQL ділить VPS з PHP-FPM, Nginx чи Redis, цим сервісам теж потрібна пам'ять. Значення у 100% RAM гарантовано призводить до свопінгу при першому ж стрибку з'єднань.
Практичний орієнтир для сервера, де MySQL — головне навантаження: 50-70% від загальної RAM. Якщо на тому самому VPS працює PHP-FPM і сайт, частку варто знизити до 30-40%, залишаючи решту під процеси PHP, бюджет яких розібрано в статті про розрахунок pm.max_children.
Формула і приклад розрахунку
Формула в загальному вигляді: buffer_pool дорівнює загальній RAM мінус пам'ять під ОС та інші сервіси мінус запас на з'єднання, помножене на обрану частку. На VPS з 4 GB RAM, де MySQL ділить сервер з PHP-FPM і Nginx, після віднімання 1 GB під систему і сервіси залишається приблизно 3 GB, з яких під буфер варто взяти 50-60%, тобто 1.5-1.8 GB як стартову точку.
- Розмір усього робочого набору даних — якщо база менша за буфер, збільшувати його далі безглуздо
- Кількість одночасних з'єднань та їхні sort_buffer_size, join_buffer_size
- Інші сервіси на тому самому VPS — PHP-FPM, Redis, Nginx
- Налаштування swappiness у ядрі — низьке значення знижує ризик витіснення буфера в swap
- Час прогріву після перезапуску — порожній буфер читає з диска, доки не наповниться знову
Таблиця: стартові значення за обсягом RAM
Діапазони в таблиці — точка старту для подальшого заміру на власному навантаженні, а не готова відповідь для будь-якого сервера.
| RAM сервера | MySQL — головний сервіс | MySQL ділить VPS з PHP/Nginx |
|---|---|---|
| 2 GB | 1-1.2 GB | 0.5-0.7 GB |
| 4 GB | 2.5-3 GB | 1.5-1.8 GB |
| 8 GB | 5-6 GB | 3-4 GB |
| 16 GB | 10-12 GB | 6-8 GB |
Як перевірити, що розмір підібраний правильно
Точну відповідь дають статусні змінні, а не таблиця вище: співвідношення Innodb_buffer_pool_reads (сторінок, прочитаних з диска) до Innodb_buffer_pool_read_requests (усіх звертань до сторінок, включно з кешем) показує частку промахів кешу. Якщо після доби роботи під звичайним навантаженням частка промахів помітна і продовжує зростати, буфер варто збільшити; якщо вона майже не змінюється при збільшенні буфера, робочий набір уже кешується цілком.
Окрема і часта плутанина — зростання файлу ibdata1 на диску: воно не пов'язане напряму з innodb_buffer_pool_size і не зменшується при його зміні, розбір того, звідки береться це зростання, в статті про ibdata1 і місце на диску.
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SET GLOBAL innodb_buffer_pool_size = 1610612736;Часті питання
Чи можна змінювати innodb_buffer_pool_size без перезапуску MySQL?
У сучасних версіях MySQL розмір можна змінювати динамічно через SET GLOBAL, але зміна застосовується поступово, і саме значення не зберігається після перезапуску без правки my.cnf.
Великий буфер вирішить проблему повільних запитів?
Ні: буфер прискорює читання вже прочитаних сторінок, але запит без потрібного індексу все одно читає зайві сторінки при кожному виконанні; робота з індексами розібрана в статті про індекси MySQL.
Після перезапуску сервер став повільнішим, хоча buffer_pool_size не змінювався?
Це очікувано: відразу після старту буфер порожній, і перші запити читають сторінки з диска, доки кеш не прогріється знову; швидкість повертається у міру накопичення в буфері робочого набору даних.