Расскажите друзьям о статье...
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter

Как увеличить количество подключений 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
Расскажите друзьям о статье...
Share on VK
VK
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.