Базовая оптимизация MySQL или MariaDB на сервере сводится к нескольким шагам: выделить достаточно памяти под буфер InnoDB, включить журнал медленных запросов, добавить недостающие индексы, ограничить число соединений под реальные ресурсы и регулярно чистить «мусор» в таблицах. Для типичного сайта на WordPress или 1С-Битрикс этого хватает, чтобы база перестала быть узким местом.
Ниже — понятная инструкция для владельца сайта на VPS: какие параметры менять, как рассчитать значения, как найти медленные запросы и что можно сделать на виртуальном хостинге, где доступа к настройкам сервера нет.
Как понять, что тормозит именно база данных
Медленный сайт не всегда означает медленную базу. Признаки того, что проблема в MySQL:
- процесс mysqld или mariadbd постоянно занимает большую долю процессора;
- страницы с фильтрами каталога, поиском и личным кабинетом открываются заметно дольше простых страниц;
- в админке CMS долго сохраняются товары или записи;
- при росте посещаемости появляются ошибки «Too many connections» или «Error establishing a database connection».
Быстро посмотреть нагрузку можно утилитой top или htop, а активные запросы — прямо в консоли MySQL:
sudo mysql -e "SHOW FULL PROCESSLIST;"
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"Если в списке процессов постоянно висят одни и те же долгие запросы, значит, есть что оптимизировать. Общий подход к диагностике — в статье почему сайт тормозит.
MySQL или MariaDB: есть ли разница
MariaDB — ответвление MySQL, совместимое с ним для подавляющего большинства сайтов. Во многих дистрибутивах Linux по умолчанию ставится именно MariaDB. Параметры, о которых пойдёт речь, называются одинаково и работают похоже. Различия начинаются в продвинутых функциях и в некоторых значениях по умолчанию, поэтому перед изменениями всегда смотрите, что у вас установлено:
mysql --versionГде находятся настройки
Конфигурация хранится в файлах my.cnf. На Ubuntu и Debian свои параметры удобно помещать в отдельный файл, чтобы их не перезаписало обновление пакета:
- MySQL:
/etc/mysql/mysql.conf.d/mysqld.cnfили свой файл в/etc/mysql/conf.d/; - MariaDB:
/etc/mysql/mariadb.conf.d/50-server.cnfили свой файл в той же папке.
Параметры пишутся в секции [mysqld]. После изменения сервис нужно перезапустить:
sudo systemctl restart mysql
# или
sudo systemctl restart mariadbВажно: перед любыми изменениями сделайте резервную копию базы (
mysqldump) и сохраните исходный файл конфигурации. Ошибка в my.cnf может помешать базе запуститься, и сайт перестанет работать до исправления.
Главные параметры для сайта
innodb_buffer_pool_size
Самый важный параметр. Это объём оперативной памяти, в которой InnoDB держит данные и индексы. Если база помещается в буфер целиком, большинство запросов обслуживается из памяти, а не с диска. Ориентиры:
- если на сервере работает только база — до 60–70% оперативной памяти;
- если на одном VPS сайт, PHP и база (типичный случай) — 25–40% памяти, чтобы хватило PHP-FPM и системе;
- нет смысла ставить значение сильно больше размера всех баз.
Размер баз можно узнать запросом:
SELECT table_schema, ROUND(SUM(data_length + index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema;max_connections
Максимальное число одновременных подключений. Слишком большое значение опасно: при всплеске нагрузки каждый поток займёт память, и сервер может уйти в swap. Для небольшого сайта обычно хватает 100–150 соединений; значение должно соответствовать числу процессов PHP-FPM.
innodb_log_file_size и журнал изменений
Размер журнала транзакций влияет на скорость записи. На сайтах с частыми изменениями (магазин, обмен с 1С) слишком маленький журнал замедляет запись. В новых версиях MySQL вместо него используется innodb_redo_log_capacity — проверяйте документацию своей версии.
tmp_table_size и max_heap_table_size
Определяют, какого размера временные таблицы держатся в памяти. Если сложные запросы с сортировкой и группировкой часто создают временные таблицы на диске, умеренное увеличение этих параметров (их ставят одинаковыми) помогает.
Кэш запросов
В MySQL 8 query cache удалён. В MariaDB он есть, но на сайтах с частыми изменениями чаще мешает, чем помогает. Вместо него используйте объектный кэш на стороне приложения — Redis или Memcached.
Пример стартовой конфигурации
Ниже ориентировочные значения для VPS с 4 ГБ памяти, где на одном сервере работают nginx, PHP-FPM и база. Это отправная точка, а не универсальный рецепт.
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 100
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1| Память VPS | innodb_buffer_pool_size (сайт и база на одном сервере) | max_connections |
|---|---|---|
| 2 ГБ | 512M | 50–80 |
| 4 ГБ | 1G–1,5G | 100 |
| 8 ГБ | 2G–3G | 150 |
| 16 ГБ | 5G–6G | 200 |
Журнал медленных запросов и индексы
Настройки сервера дают общий выигрыш, но самые большие проблемы обычно в конкретных запросах. Журнал медленных запросов (slow query log) записывает всё, что выполняется дольше заданного порога. Через несколько дней работы посмотрите сводку:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.logКоманда покажет десять запросов, на которые ушло больше всего времени. Для каждого можно выполнить EXPLAIN и посмотреть, использует ли он индекс:
EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = 'price';Если в колонке type стоит ALL, а rows — десятки тысяч, запрос перебирает всю таблицу. Часто помогает индекс по нужному полю. Добавлять индексы в таблицы CMS лучше вместе с разработчиком: лишние индексы замедляют запись и могут конфликтовать с обновлениями.
Совет: для автоматической подсказки по параметрам существуют утилиты MySQLTuner и Percona Toolkit. Запускайте их после нескольких суток работы сервера — тогда статистика будет репрезентативной. Рекомендации проверяйте, а не применяйте вслепую.
Чистка базы данных
Со временем в базе копится лишнее: ревизии записей, просроченные временные данные, логи плагинов, брошенные корзины, сессии. Это увеличивает размер таблиц и замедляет запросы. Что можно делать регулярно:
- Ограничить число ревизий записей в CMS и удалить старые.
- Очищать временные данные (в WordPress — устаревшие transient-записи).
- Удалять таблицы плагинов, которые больше не используются.
- Настроить очистку журналов и статистики в Битриксе и модулях магазина.
- Периодически выполнять
OPTIMIZE TABLEдля таблиц, где было много удалений, — в спокойное время, так как операция нагружает сервер.
Что можно сделать на виртуальном хостинге
На хостинге изменить my.cnf нельзя: сервер базы общий для многих клиентов. Но вам доступны чистка базы, удаление лишних плагинов, кэширование на уровне CMS и индексы через phpMyAdmin. Если база всё равно упирается в лимиты тарифа, это сигнал к переезду на VPS. У и можно взять VPS с NVMe-дисками и настроить MySQL под свой проект — актуальные конфигурации и цены сверяйте на сайтах провайдеров. Подробнее в нашем обзор Timeweb и Timeweb Cloud и в статье переезд с хостинга на VPS.
Частые вопросы
Можно ли просто поставить максимум памяти под базу?
Нет. Если база заберёт почти всю память, PHP-FPM и системе не хватит ресурсов, сервер начнёт использовать swap, и сайт станет медленнее, а не быстрее.
Что делать при ошибке Too many connections?
Сначала найдите причину: долгие запросы, зависшие процессы, боты. Увеличение max_connections — временная мера, она лишь откладывает проблему и повышает расход памяти.
Нужно ли выносить базу на отдельный сервер?
Для большинства малых и средних сайтов — нет. Это имеет смысл для крупных магазинов и проектов с высокой нагрузкой, о чём мы писали в статье масштабирование сайта.
Как часто делать резервные копии базы?
Для магазина — минимум ежедневно, для сайта-визитки — раз в несколько дней. Хранить копии нужно вне сервера.
Если не хотите разбираться сами
Мы в Pear Advert анализируем медленные запросы, подбираем параметры MySQL и MariaDB под ресурсы сервера и чистим базы сайтов на WordPress и 1С-Битрикс. Если не хотите разбираться сами — поможем подобрать и настроить сервер в рамках услуги аренда сервера и хостинга.
В статье есть партнёрские ссылки: если вы зарегистрируетесь по ним, студия получит вознаграждение, для вас цена не меняется.
