Перейти к содержимому
BXWiki

Настройка базы под нагрузку - буферы, соединения, журналы

Настройки базы по умолчанию рассчитаны на скромный сервер. Живой каталог с сотнями тысяч товаров упирается в них на первом же пике. Разбираем параметры, которые меняют отдачу под нагрузкой, и место для их хранения.

Решение

Свой файл настроек

Смотрим, какие файлы настроек читает база:

Окно терминала
head -5 /etc/my.cnf                       # основной файл, его перезапишет обновление
cat /etc/mysql/conf.d/bvat.cnf            # значения, посчитанные автоподстройкой от памяти
cat /etc/mysql/conf.d/z_bx_custom.cnf     # единственный файл для своих значений

Автоподстройка считает буферы и пределы от объёма памяти машины. Результат она держит в отдельном файле окружения и чужих правок в нём не хранит. Правка основного файла живёт до ближайшего пересчёта, а дальше исчезает без всякого предупреждения.

Заводим свой файл и перезапускаем сервер базы:

Окно терминала
cat > /etc/mysql/conf.d/z_bx_custom.cnf <<'EOF'
[mysqld]
innodb_buffer_pool_size = 4G
max_connections         = 200
EOF
systemctl restart mysqld       # без перезапуска значения остаются прежними

Файлы каталога читаются по алфавиту. Префикс z ставит наш файл последним в очереди, и его значения побеждают. Так одна строка своего файла целиком перекрывает и основной файл, и расчёт автоподстройки.

Размер основного буфера

Считаем буфер от объёма оперативной памяти:

Окно терминала
free -m | awk '/Mem:/ {print $2}'                            # всего памяти, мегабайты
mysql -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS mb"  # отдано базе сейчас
grep '^# memory' /etc/mysql/conf.d/bvat.cnf                  # от чего считала автоподстройка

Отдельная машина под базу отдаёт буферу 60-80% памяти. На общем сервере с веб-сервером и PHP доля падает примерно до половины этого объёма. Иначе рабочим процессам PHP не остаётся места, и сервер уходит в подкачку.

Проверяем, что сервер не ушёл в подкачку:

Окно терминала
vmstat 1 5    # столбцы si и so: ненулевые значения означают обмен с диском
# буфер больше свободной памяти уводит базу в подкачку и убивает отдачу целиком

Буфер больше физической памяти - самая дорогая ошибка тюнинга. Сервер живёт неделю или две, а потом уходит в своп вместе со всем сайтом. Отдача падает в разы, а на графиках виден только резкий рост обмена с диском.

Тип таблиц

Смотрим, все ли таблицы на одном движке:

SELECT ENGINE, COUNT(*) FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() GROUP BY ENGINE;
-- ожидаем одну строку InnoDB: смешение движков ломает общую транзакцию
ALTER TABLE b_iblock_element ENGINE = InnoDB;   -- перевод одной таблицы

MyISAM блокирует таблицу целиком, а не строку, и на больших объёмах теряет целостность данных. InnoDB держит построчные блокировки и транзакции, поэтому смешивать движки в одной базе не стоит: транзакция всё равно оборвётся на первой таблице старого движка.

Снимаем строгий режим, которого требует проверка платформы:

/etc/mysql/conf.d/z_bx_custom.cnf
[mysqld]
innodb_strict_mode = OFF   # иначе проверка «Режим работы MySQL» отдаёт ошибку

Проверка конфигурации требует выключенного строгого режима. Требование появилось в модуле main версии 19.0.400 и с тех пор держится в проверке. Строку кладут в свой файл настроек окружения, а не в основной файл сервера базы. Совет дописать её в основной файл встречается часто и всегда обходится дорого.

Предел соединений

Сверяем предел базы с числом рабочих процессов PHP:

Окно терминала
grep pm.max_children /etc/php-fpm.d/*.conf           # сколько процессов поднимет PHP
mysql -e "SHOW VARIABLES LIKE 'max_connections'"     # сколько соединений примет база
mysql -e "SHOW STATUS LIKE 'Max_used_connections'"   # пик с момента запуска базы

Предел базы держат выше числа рабочих процессов веб-сервера. Запас считают на cron, обмен с учётной системой и на разовые консольные скрипты. Каждое соединение стоит памяти, поэтому предел поднимают вместе с пересчётом основного буфера.

Журнал медленных запросов

Узнаём каталог, куда серверу базы разрешено писать:

SHOW VARIABLES LIKE 'secure_file_priv';   -- разрешённый каталог для файлов сервера
SET GLOBAL slow_query_log = 1;            -- проверка на живом сервере, до правки файла

Разрешённый каталог задаёт переменная secure_file_priv, и путь журнала обязан лежать внутри него. Значение, выставленное запросом на живой базе, теряется при первом же перезапуске службы.

Включаем журнал медленных запросов насовсем:

[mysqld]
slow_query_log      = 1
long_query_time     = 3                                   # порог в секундах
slow_query_log_file = /var/lib/mysql-files/mysql-slow.log # каталог из secure_file_priv

На MySQL 8 путь журнала обязан лежать внутри этого каталога. Файл в чужом каталоге не даёт серверу базы стартовать, а не просто отключает журнал. Сайт при этом отдаёт отказ подключения, и симптом выглядит как поломка самого сайта.

Проверка и откат

Сверяем значения, которые применились на самом деле:

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size', 'max_connections', 'innodb_strict_mode', 'slow_query_log'
);
-- значения из файла применяются только после перезапуска сервера базы

Штатная сверка есть и в административной части сайта. Раздел Настройки > Производительность > Конфигурация сравнивает подсистемы сервера с эталоном. Отчёт прямо подсвечивает типовые промахи площадки, включая базу не на движке InnoDB.

Откатываем правку, если база не поднялась:

Окно терминала
mv /etc/mysql/conf.d/z_bx_custom.cnf /root/z_bx_custom.cnf.bak
systemctl restart mysqld
tail -40 /var/log/mysql/error.log     # строка с именем непонятого параметра

Непонятый параметр останавливает запуск базы целиком, и сайт получает отказ подключения вместо страниц. Имя виноватого параметра всегда названо в журнале ошибок сервера базы, поэтому строки возвращают по одной, а не весь файл разом.

Типичные проблемы

Настройки базы вернулись к прежним значениям после обновления.

Правка внесена в основной файл окружения, а он перезаписывается при каждом обновлении целиком. Свои значения переживают обновление только в отдельном файле z_bx_custom.cnf, который окружение не трогает.

База съедает всю память, и сервер уходит в подкачку за неделю.

Размер буфера задан без учёта PHP и веб-сервера, живущих на той же самой машине. Долю памяти считают от свободного остатка, а не от полного объёма оперативной памяти.

Проверка «Режим работы MySQL» отдаёт ошибку строгого режима.

Параметр innodb_strict_mode оставлен включённым, хотя проверка конфигурации требует обратного начиная с модуля main 19.0.400. Значение меняют в своём файле настроек, а затем обязательно перезапускают сервер базы данных.

После включения журнала медленных запросов база не стартует.

Путь журнала лежит вне каталога, который разрешает переменная secure_file_priv на MySQL версии 8 и выше. Сервер базы отказывается запускаться целиком и пишет причину отказа в свой журнал ошибок.

Перевод таблиц на InnoDB не ускорил сайт, а замедлил.

Движок сменили, а размер основного буфера остался прежним, рассчитанным ещё под старый MyISAM. InnoDB держит данные и индексы в этом буфере, и без запаса памяти выигрыша не будет.

Частые вопросы

Сколько памяти отдавать под основной буфер?

На выделенной под базу машине - 60-80% оперативной памяти. На общем сервере долю снижают примерно до половины, потому что рабочим процессам PHP тоже нужна память. Превышать физический объём нельзя ни при каких условиях: подкачка сводит выигрыш к нулю.

Почему после перезагрузки мои значения снова стали чужими?

Служба автоподстройки пересчитывает настройки от объёма памяти и перезаписывает свой файл. Она не трогает только файл z_bx_custom.cnf, поэтому свои значения кладут туда.

Нужно ли переводить старые таблицы с MyISAM на InnoDB?

Да, если база смешанная. Транзакция не охватывает таблицы MyISAM, а блокировка целой таблицы под нагрузкой останавливает всех остальных. Перевод делают по одной таблице и обязательно после резервной копии.

Как понять, что предел соединений подобран верно?

По пику занятых соединений с момента запуска базы. Пик, упирающийся в предел, означает отказы части посетителей, а пик вдвое ниже предела - лишнюю память, занятую впустую.

Можно ли применить настройки без перезапуска базы?

Часть переменных меняется на живом сервере запросом, и это удобно для проверки гипотезы. Но такое значение теряется при первом же перезапуске, поэтому подтверждённую правку всё равно переносят в файл настроек.

Смежное

Первоисточник