ADMINS24

Как изменить лимит соединений с MySQL

Администрирование
Обновлено Обновлено: Опубликовано Опубликовано:

Тематические термины: MySQL, MariaDB.

По умолчанию, настройки лимитов СУБД могут оказаться не оптимальными. В некоторых случаях, это может привести к накапливанию очередей и низкой производительности приложений или, вовсе, привести к ошибке «MySQL server has gone away» — необходимо увеличить стандартные значения.

В рамках данной инструкции мы рассмотрим следующие лимиты:

  1. Максимально разрешенное количество одновременных подключений (max_connections). По умолчанию, равно 151.
  2. Максимально разрешенное количество одновременных подключений для пользователя (max_user_connections). По умолчанию, равно 0 — лимита нет.
  3. Тайм-аут ожидания для запросов (wait_timeout). По умолчанию, 28800 секунд.
  4. Лимит на максимальный размер пакета (max_allowed_packet). По умолчанию, 1048576 байт (1 Мб).

Просмотр текущих значений

Выполняется в оболочке mysql — для подключения вводим:

mysql -uroot -p

Посмотреть максимально разрешенное количество подключений:

> SHOW VARIABLES WHERE `variable_name`=’max_connections’;

Максимально разрешенное количество подключений на пользователя:

> SHOW VARIABLES WHERE `variable_name`=’max_user_connections’;

Посмотреть текущее количество подключений:

> SHOW status WHERE `variable_name` = ‘threads_connected’;

Тайм-аут ожидания для запросов:

> SHOW VARIABLES WHERE `variable_name`=’wait_timeout’;

Максимальный размер пакета:

> SHOW VARIABLES WHERE `variable_name`=’max_allowed_packet’;

Настройка max_connections

Открываем конфигурационный файл mysql:

# vi /etc/my.cnf.d/server.cnf

* в более ранних версиях данный файл находится по пути /etc/my.cnf

В директиве [mysqld] добавляем или изменяем следующую строку:

[mysqld]
…
max_connections = 500

* в данном примере мы разрешим 500 одновременных подключений к MySQL. При превышении данного значения будет отображаться ошибка too many connections.

Перезагружаем mysql:

# systemctl restart mysql || systemctl restart mariadb

* в некоторых системах перезагрузка сервера баз данных выполняется командой service mysql restart или service mysqld restart или service mysql-server restart

Оптимальное значение

Для данного лимита нет золотого стандарта — маленькое значение может привести к выстраиванию очередей запросов, большое — к перегрузке серверного оборудования. Правильнее всего постоянно наблюдать за значениями max_connections и threads_connected и определить для себя свой, так называемый, Best Practices.

На первое время, для сервера можно поставить лимит в 200-300 подключений.

Опция max_user_connections

В отличие от max_connections, данная опция задает лимит для подключений конкретному клиенту. Настройка также выполняется в конфигурационном файле СУБД:

[mysqld]
…
max_connections = 500
max_user_connections = 50

* в данном примере сервер будет принимать 500 одновременных подключений, но от каждого клиента, максимум — 50.

По умолчанию, лимит не задан и равен 0. Посмотреть текущее значение можно командой:

> SHOW VARIABLES WHERE variable_name = ‘max_user_connections’;

Лимит запросов wait_timeout 

Настройка также выполняется в конфигурационном файле /etc/my.cnf:

[mysqld]
…
wait_timeout = 1200

* в данном примере устанавливаем лимит на 20 минут.

Перезагружаем mysql:

systemctl restart mysql || systemctl restart mariadb

Максимальный размер пакета max_allowed_packet

Открываем конфигурационный файл my.cnf:

vi /etc/my.cnf

[mysqld]
…
max_allowed_packet = 128M

Перезагружаем mysql:

systemctl restart mysql || systemctl restart mariadb