Налаштування MySQL/MariaDB: оптимізація, реплікація, бекапи

Повний цикл адміністрування MySQL та MariaDB для веб-проектів

Уявіть: сайт на WordPress падає при 1000 одночасних відвідувачів, хоча сервер потужний. Швидше за все, проблема в MySQL — не налаштований InnoDB buffer pool, немає індексів на часті запити, binary log забив диск. Такі сценарії зустрічаються повсюдно: типовий проект втрачає до 30% продуктивності через неоптимальну конфігурацію. Ми вирішуємо ці завдання щодня, маючи за плечима понад 50 успішних проектів з адміністрування MySQL та MariaDB. Гарантуємо стабільну роботу та приріст продуктивності на 20-50% після налаштування.

Проблеми, які ми вирішуємо

Фрагментація таблиць. MyISAM без регулярного OPTIMIZE гальмує вибірки. InnoDB теж фрагментується після частих UPDATE/DELETE. Розмір бази зростає, а продуктивність падає. Повільні запити. Відсутність індексів, N+1 запитів у додатку, неоптимальний JOIN без ключів. Реплікація. Налаштування master-replica без контролю лагу веде до розсинхронізації. Переповнення binary log без ротації займає весь диск — сервер зупиняється. Балансування навантаження. Без ProxySQL читання та запис йдуть на один сервер, створюючи вузьке місце.

Як ми це робимо

Початковий аудит

-- Оцінка стану: розмір баз, фрагментація, повільні запити
SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

Потім аналізуємо slow_query_log та config-файли. На основі даних підбираємо параметри. Наприклад, для бази розміром 50 ГБ з високим навантаженням на запис збільшуємо innodb_log_file_size до 1 ГБ, що скорочує час checkpoint на 40%.

Оптимізація InnoDB

Ключовий параметр — innodb_buffer_pool_size. Для виділеного сервера виділяємо 70-80% RAM. Розмір лог-файлів збільшуємо до 512 МБ — це зменшує частоту checkpoint і прискорює запис. Файл на таблицю (innodb_file_per_table = ON) спрощує бекап та оптимізацію. В результаті типова база на 100 ГБ отримує приріст швидкості запитів на 30-60%.

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = ON
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Як ми оптимізуємо базу даних: крок за кроком

1. Аудит поточної конфігурації та продуктивності.
2. Аналіз повільних запитів та індексів (виявляємо до 90% неоптимальних запитів).
3. Налаштування параметрів InnoDB (buffer pool, log file, IO threads).
4. Налаштування реплікації master-replica з контролем лагу (цільовий лаг < 1 сек).
5. Налаштування автоматичних бекапів (mysqldump для малих баз, XtraBackup для великих).
6. Впровадження моніторингу та алертів (Prometheus + Grafana, ProxySQL для балансування).
7. Документування конфігурації та навчання вашого адміністратора.

Чому важлива реплікація?

Реплікація master-replica дає відмовостійкість: при відмові майстра перемикаємося на репліку. Налаштовуємо її так, щоб лаг не перевищував 1 секунди. Використовуємо ROW-формат binary log — він безпечніший за STATEMENT для додатків зі збереженими процедурами. Приклад налаштування:

-- На майстрі
CREATE USER 'replication'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
SHOW MASTER STATUS;

-- На репліці
CHANGE MASTER TO
  MASTER_HOST = '10.0.0.1',
  MASTER_USER = 'replication',
  MASTER_PASSWORD = 'strong_password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 12345;
START SLAVE;

Резервне копіювання: mysqldump vs XtraBackup

Для баз до 10 ГБ використовуємо mysqldump з --single-transaction — бекап без блокування InnoDB. Для великих баз застосовуємо Percona XtraBackup: він фізично копіює дані, працює до 3 разів швидше і майже не навантажує сервер. Обидва методи автоматизуємо з ротацією та перевіркою цілісності.

# mysqldump для невеликих баз
mysqldump --single-transaction --quick --routines --triggers --flush-logs -u backup -p mydb | gzip > /backups/mydb_$(date +%Y%m%d).sql.gz

# XtraBackup для великих баз
xtrabackup --backup --user=backup --target-dir=/backups/full_$(date +%Y%m%d)

Порівняння методів бекапу

Оптимізація та моніторинг

Регулярно виконуємо дефрагментацію таблиць за допомогою OPTIMIZE TABLE або pt-online-schema-change (без блокування). Моніторинг через performance_schema: відстежуємо топ повільних запитів та блокування. Налаштовуємо алерти при заповненні диска більше 80%.

Що робити при розростанні binary log?

Binary log розростається, якщо не налаштована ротація. Встановіть expire_logs_days = 7 в my.cnf, а також регулярно виконуйте PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY. Для реплікації використовуйте ROW-формат: він компактніший при змішаному навантаженні. Розмір логу можна контролювати алертами — при досягненні 80% диска спрацьовує сповіщення.

[mysqld]
expire_logs_days = 7
max_binlog_size = 500M
binlog_format = ROW

Орієнтовні строки робіт

Строки залежать від складності: аудит займає 1-2 дні, налаштування конфігурації — 2-3 дні, впровадження реплікації — 1-2 дні, налаштування бекапів — 1 день. Повний цикл — від 5 до 10 робочих днів. Вартість розраховується індивідуально і окупається за рахунок зниження навантаження на сервер до 40%. Замовте первинний аудит — ми покажемо реальні точки зростання.

Що входить в роботу

• Аудит поточної конфігурації та продуктивності (з видачею звіту).
• Налаштування InnoDB під навантаження (оптимізація buffer pool, log file, IO).
• Налаштування реплікації master-replica з моніторингом лагу.
• Налаштування автоматичних бекапів (mysqldump/XtraBackup) з ротацією.
• Інтеграція моніторингу (performance_schema, алерти по диску та лагу).
• Документація з конфігурації та процедур відновлення.
• Навчання вашого адміністратора (2-3 години консультацій).
• Гарантійна підтримка 1 місяць після впровадження.

Зв'яжіться з нами для первинного аудиту вашої бази даних. Замовте налаштування MySQL/MariaDB і забудьте про проблеми з продуктивністю. Наша команда гарантує результат: стабільна робота без сюрпризів.

Для додаткової інформації зверніться до документації InnoDB та Wikipedia про реплікацію.

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.