Как увеличить количество подключений MySQL/MariaDB?
При разворачивании Базы данных применяются настройки по умолчанию, которые не во всех случаях считаются оптимальными и требуют настроек под производительность задачи.
Например, ошибка «MySQL server has gone away» указывает на проблему параметров wait_timeout и max_allowed_packet.
А ошибка «too many connections» говорит о превышении лимита максимальных одновременных подключений к базе данных.
В этой статье мы покажем приемы, которые помогут разобраться с настройками базы данных MySQL и MariaDB.
Как посмотреть параметры настроек базы данных MySQL и MariaDB.
1. Подключаемся к базе данных:
mysql -uroot -p
2. Посмотреть максимально возможное количество подключений:
SHOW VARIABLES WHERE `variable_name`='max_connections';
3. Посмотреть максимально возможное количество подключений на пользователя:
SHOW VARIABLES WHERE `variable_name`='max_user_connections';
4. Текущее количество подключений:
SHOW status WHERE `variable_name` = 'threads_connected';
5. Узнать тайм-аут ожидания для запросов:
SHOW VARIABLES WHERE `variable_name`='wait_timeout';
6. Узнать максимальный размер пакета:
SHOW VARIABLES WHERE `variable_name`='max_allowed_packet';
Мы узнали как посмотреть текущие настройки базы данных, разберемся как изменить значения параметров MySQL и MariaDB.
Настройка параметра max_connections
SHOW VARIABLES WHERE `variable_name`='max_connections';
1. В зависимости от версии конфигурационный файл mysql находится:
# vi /etc/my.cnf.d/server.cnf
или
# vi /etc/my.cnf
2. В открывшемся файле конфигурации ищем раздел [mysqld], где добавляем или редактируем следующую строку, которая разрешит до 350 одновременных подключений:
[mysqld] max_connections = 350
Перезагружаем mysql или mariadb, предварительно выйдя из консоли управления БД exit (quit) командой:
# systemctl restart mysql # systemctl restart mariadb # service mysql restart
Что бы выбрать оптимальное значение, необходимо наблюдать за значениями max_connections и threads_connected, начиная со значения в 200 подключений.
ВАЖНЫЙ МОМЕНТ!
3. Параметр max_connections не может быть больше, чем параметр open_files_limit, которое по умолчанию имеет значение 1024.
В этом случае вы можете увидеть в логах ошибку вида «Changed limits: max_open_files: 1024 max_connections: 115 table_cache: 500«.
То есть если параметр max_connections нам нужен 1100, то open_files_limit нужно изменить на 1200 и файл конфигурации будет иметь вид:
[mysqld] max_connections = 1100 open_files_limit = 1200
Но есть еще системный лимит одновременно открытых файлов в самом Linux, значение которого равно 1024 на одного пользователя.
Проверить существующие в системе лимиты:
mysql soft nofile 4096 mysql hard nofile 10240
либо для всех:
* soft nofile 4096 * hard nofile 10240
5. Затем настроим systemd, чтобы служба MySQL, MariaDB могла открывать больше файлов.
Для MySQL:
vi /etc/systemd/system/multi-user.target.wants/mysql.service
Для MariaDB:
mkdir /etc/systemd/system/mariadb.service.d/ vi /etc/systemd/system/mariadb.service.d/limits.conf
[Service] LimitNOFILE=infinity
В конце обязательно выполним следующие команды:
Для MySQL:
systemctl daemon-reload systemctl restart mariadb
Для MariaDB:
systemctl daemon-reload systemctl restart mariadb
Настройка параметра max_user_connections
Данная опция задает ограничение подключений определенному клиенту.
По умолчанию, лимит не задан и равен 0.
SHOW VARIABLES WHERE `variable_name`='max_user_connections';
Добавим в конфигурационный файл еще один параметр на ограничение в 40 подключений от одного клиента.
[mysqld] max_connections = 500 max_user_connections = 40
Не забываем перезагрузить базу данных.
# systemctl restart mysql # systemctl restart mariadb # service mysql restart
Настройка лимита запросов wait_timeout .
Данный параметр имеет временное значение и указывается в секундах, укажем лимит в 15 минут, что равняется 15*60=900
[mysqld] max_connections = 500 max_user_connections = 40 wait_timeout = 1200
Не забываем перезагрузить базу данных.
# systemctl restart mysql # systemctl restart mariadb # service mysql restart
Настройка максимального размера пакета max_allowed_packet.
> SHOW VARIABLES WHERE `variable_name`='max_allowed_packet';
Добавляем или изменяем в конфигурационном файле строку max_allowed_packet и присваиваем значение в 128M.
[mysqld] max_allowed_packet = 128M
Перезагружаем mysql:
# systemctl restart mysql # systemctl restart mariadb # service mysql restart
Спасибо)