Приховані витрати стандартних налаштувань MySQL у застосунках на Laravel

Більшість Laravel-додатків працює на MySQL з налаштуваннями за замовчуванням, які підходять для мінімальних апаратних вимог, а не для серйозних виробничих навантажень. Екосистема Laravel створює специфічні патерни роботи з базою даних — запити Eloquent по пов'язаних таблицях, конектування працівників черги, заплановані задачі під час пікового трафіку — які ці стандартні налаштування не можуть обробляти ефективно.

Оптимізація запитів Eloquent є важливою (ознайомтеся з курсом Джонатана Рейніка про парадигми продуктивності Laravel Eloquent), але не забувайте й про сервер бази даних. MySQL має безліч налаштувань, які можуть суттєво впливати на продуктивність та стабільність вашого Laravel-додатку.

# Загальні проблеми продуктивності, з якими стикаються розробники Laravel

Сторінки завантажуються дуже повільно: ваша головна сторінка завантажувалась за 300 мс під час розробки, але тепер займає 4-5 секунд у виробництві. Користувачі залишають сторінки, які відображають Eloquent зв'язки, оскільки база даних постійно читає дані з диска, а не зберігає їх у пам'яті.

Час відгуку API зменшується з часом: кінцеві точки мобільного додатку, які раніше відповідали за 100 мс, тепер потребують 2-3 секунди, навіть якщо ваш код не змінився.

Пошук стає непрактичним із ростом: пошук продуктів працював ідеально з 1,000 товарами, але тепер не справляється з 100,000. Користувачі відмовляються від пошуку, оскільки результати завантажуються занадто довго.

Завдання Laravel не виконуються під час пікового трафіку: працівники черги зависають у статусі "очікує", коли у MySQL закінчуються з'єднання. Завдання фонової відправки електронних листів, що працювали добре в тестовому середовищі, зазнають тайм-аутів, коли реальні користувачі одночасно відвідують ваш сайт.

Адміністративні панелі зависають у робочі години: інтерфейс Laravel Nova швидко завантажується о 3 ночі, але стає непридатним під час активності користувачів. Просте відображення користувачів з об'єднаннями по замовленням і платежам займає понад 15 секунд з реальними даними.

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

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

Отже, як можна вирішити ці проблеми?

# Підхід ручного налаштування

Ці проблеми можна вирішити, аналізуючи дані про продуктивність MySQL та налаштовуючи параметри конфігурації. Це передбачає перевірку виходу SHOW GLOBAL STATUS та зміну таких змінних, як розміри буферів, обмеження підключень і кількість потоків введення/виведення.

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

innodb_buffer_pool_size: це найважливіше налаштування для Laravel-додатків з великою кількістю запитів Eloquent. Воно визначає, скільки даних InnoDB зберігає в пам'яті. Для малих баз даних налаштуйте значення відповідно до розміру вашої бази. Для більших баз виділіть 70-80% доступної оперативної пам'яті і не перевищуйте цю межу, щоб уникнути свопінгу ОС.

innodb_buffer_pool_instances: для Laravel-додатків з високою паралелізацією (великою кількістю працівників черги, запитів API, адміністративних задач) розподіл буферного пулу на кілька інстансів зменшує конкуренцію. Кожен інстанс має бути не менше 1 ГБ. Зверніть увагу, що ця змінна знята з обслуговування в MariaDB 10.5+ і вилучена в MariaDB 10.6+

innodb_log_file_size: Laravel-додатки з великою кількістю процесів черги або частими оновленнями моделей отримують переваги від великих лог-файлів. Рекомендується налаштувати їх таким чином, щоб об'єм міг вмістити приблизно годину записів. Це зменшує затрати на скидання журналів під час інтенсивного оброблення фонових завдань або імпорту даних.

innodb_log_buffer_size: цей тимчасовий буфер містить дані транзакцій до їх запису у лог-файли. Laravel-додатки з великою кількістю записів (наприклад, обробка багатьох завдань черги одночасно) виграють від буферів обсягом від 16 до 64 МБ, що зменшує частоту записів на диск під час пікової активності.

innodb_flush_log_at_trx_commit: це налаштування балансує продуктивність і безпеку даних. Встановлення на 1 забезпечує максимальну надійність (кожна транзакція негайно записується на диск), тоді як 2 скидає логи кожну секунду для кращої продуктивності. Laravel-додатки, які обробляють критичні дані (платежі, облікові записи користувачів), повинні використовувати 1, а додатки, що можуть дозволити незначні втрати даних — 2 для поліпшення продуктивності.

innodb_write_io_threads та innodb_read_io_threads: Laravel-додатки часто мають комбінацію читання та запису — інтенсивні читання під час вивантаження сторінок та спорадичні записи під час обробки черг. Кожен потік може обробляти 256 одночасних I/O запитів. Збільшення кількості потоків з 4 до 8 або 16 може підвищити пропускну здатність на серверах з кількома дисками або високою потужністю I/O.

innodb_thread_concurrency: це контролює максимальну кількість потоків, які можуть виконуватись одночасно в InnoDB. Значення за замовчуванням 0 означає необмежену кількість потоків, що добре працює для більшості систем. Для Laravel-додатків з високою конкуренцією та ресурсними конфліктами встановлення певного обмеження може бути вигідним, хоча оптимальні значення значно варіюють залежно від навантаження.

innodb_purge_threads: Laravel-додатки, які часто оновлюють моделі (профілі користувачів, інвентар, аналітичні дані), генерують старі версії рядків, які потрібно очистити. Ця змінна визначає, скільки потоків InnoDB використовує для очищення записів, помічених для видалення. Збільшення значення з за замовчуванням може допомогти підтримувати продуктивність в міру зростання вашої програми.

innodb_flush_method: це контролює, як InnoDB записує дані на диск. Налаштування O_DIRECT обходить кеш файлів операційної системи, що часто корисно для Laravel-додатків, оскільки InnoDB ефективніше управляє своїм власним буферним пулом, ніж покладається на кешування на рівні ОС.

# Декілька інструментів для ручного налаштування MySQL для Laravel-додатків

MySQLTuner: Perl-скрипт, який аналізує вашу поточну конфігурацію MySQL і пропонує поліпшення на основі фактичних статистичних даних. Дуже корисний для виявлення проблем з виділенням пам'яті, які впливають на запити Eloquent Laravel.

Tuning-Primer Script: ще один діагностичний інструмент, що перевіряє вашу установку MySQL і надає рекомендації щодо розмірів буферів, обмежень підключень та налаштувань кешу запитів, які впливають на продуктивність Laravel-додатків.

Percona Toolkit: набір розширених командних рядків для аналізу продуктивності MySQL. Інструменти, такі як pt-query-digest, допомагають виявляти повільні запити Laravel, тоді як pt-mysql-summary забезпечує комплексний аналіз сервера.

phpMyAdmin Advisor: вбудована функція phpMyAdmin, яка аналізує вашу конфігурацію MySQL і пропонує оптимізації. Це зручно, якщо ви вже використовуєте phpMyAdmin для управління базою даних.

Mysqlreport: генерує детальні звіти про статистику продуктивності MySQL, допомагаючи виявити вузькі місця в областях, які зазвичай впливають на Laravel-додатки.

MySQL Memory Calculator: інструмент, такий як MySQL Memory Calculator від Releem (https://releem.com/tools/mysql-memory-calculator), допомагає оцінити оптимальний розподіл пам’яті між різними буферами MySQL на основі доступної оперативної пам’яті сервера.

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

Ось чому багато команд звертаються до автоматизації.

# Автоматизація налаштування MySQL

Замість того, щоб вручну коригувати безліч змінних MySQL, що впливають на продуктивність Laravel, ви можете використовувати автоматизовані інструменти, які постійно моніторять базові патерни вашого додатка та пропонують відповідні конфігурації. Ці інструменти аналізують такі фактори, як використання ЦП, утилізація пам'яті, патерни запитів і поведінка з'єднань, щоб рекомендувати налаштовані значення для критичних змінних.

Releem — один з таких інструментів, який працює шляхом встановлення легкого моніторингового агента на вашому сервері. Він збирає статистику навантаження, специфічну для вашого Laravel-додатку, і надає рекомендації для більш ніж 30 змінних MySQL/MariaDB, яких згадувалось раніше, й інших. Сервіс пропонує зміни через веб-інтерфейс з попереднім переглядом різниць.

Автоматизований підхід справляється зі складністю балансування кількох взаємопов'язаних змінних. Наприклад, при збільшенні innodb_buffer_pool_size також враховується, чи потрібно налаштувати innodb_buffer_pool_instances для збереження оптимальної паралелізації підключень вашого Laravel-додатку.

# Результати в реальному світі

Згідно з дослідженням Releem та спільноти Laravel (повне дослідження тут), налаштування MySQL може істотно покращити продуктивність Laravel без зміни коду.

У дослідженні тестували Aimeos, популярний фреймворк електронної комерції для Laravel, на стандартному сервері AWS з налаштуваннями MySQL за замовчуванням. Додаток не міг впоратись навіть з 10 одночасними користувачами — сторінки тайм-аутали, й база даних була перевантажена.

Після налаштування MySQL той самий додаток легко впорався з усім навантаженням. Час відгуку сторінок зменшився з понад 1 секунди до менш ніж 800 мс, а продуктивність бази даних майже потроїлася — з 12 до 35 запитів на секунду. Використання ЦП значно зменшилось.

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

# Подальші кроки

Ви можете вручну налаштувати MySQL або скористатися автоматизованими інструментами й витрачати свій час на розробку нових функцій замість керування конфігураціями.

Releem пропонує автоматизоване налаштування MySQL для Laravel-додатків через аналіз навантаження та рекомендації конфігурацій.