Изменить лимиты базы данных MySQL/MariaDB

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

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

Параметр (ед. изм.) и описание Возможные варианты для значений
Умолчание Минимум Максимум
max_connections (кол-во)
Максимально разрешенное количество одновременных подключений.
151 1 100000
max_user_connections (кол-во)
Максимально разрешенное количество одновременных подключений для пользователя.
0 0 4294967295
wait_timeout (секунды)
Тайм-аут ожидания для запросов.
28800 1 31536000
max_allowed_packet (байты)
Лимит на максимальный размер пакета.
67108864 1024 1073741824
innodb_lock_wait_timeout (секунды)
Время, в течение которого будет ожидаться выполнение транзакции для базы типа INNODB. После завершения этого времени, СУБД откажется от выполнения запроса.
50 1 1073741824

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

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

mysql -uroot -p

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

> SHOW VARIABLES WHERE `variable_name`='<Имя параметра>’;

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

Принцип настройки

Для применения настройки можно выполнить запрос SQL со следующим синтаксисом:

SET GLOBAL <имя параметра> = <значение>;

Данная настройка будет использоваться до перезапуска СУБД. Чтобы применить ее навсегда, необходимо отредактировать конфигурационный файл. Местоположение данного файла может зависеть от версии MySQL/MariaDB. Возможные варианты:

  • /etc/my.cnf
  • /etc/my.cnf.d/server.cnf

В конфигурационном файле мы должны найти раздел [mysqld] и добавить в него значение для нужной нам опции:

<имя параметра> = <значение>

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

systemctl restart mysql || systemctl restart mariadb

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

Это приведет к перебою в работе СУБД.

Настройка max_connections

Конфигурационный файл:

[mysqld]

max_connections = 500

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

Задать текущее значение в SQL:

> SET GLOBAL max_connections = 500;

Посмотреть текущее значение:

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

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

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

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

Опция max_user_connections

В отличие от max_connections, данная опция задает лимит для подключений конкретному клиенту.

Конфигурационный файл:

[mysqld]

max_connections = 500
max_user_connections = 50

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

Задать текущее значение в SQL:

> SET GLOBAL max_user_connections = 50;

Посмотреть текущее значение:

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

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

Конфигурационный файл:

[mysqld]

wait_timeout = 1200

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

Задать текущее значение в SQL:

> SET GLOBAL wait_timeout = 1200;

Посмотреть текущее значение:

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

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

Конфигурационный файл:

[mysqld]

max_allowed_packet = 128M

Задать текущее значение в SQL:

> SET GLOBAL max_allowed_packet = 134217728;

Посмотреть текущее значение:

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

Время ожидания транзакции innodb_lock_wait_timeout

Конфигурационный файл:

[mysqld]

innodb_lock_wait_timeout = 500

Задать текущее значение в SQL:

> SET GLOBAL innodb_lock_wait_timeout = 500;

Посмотреть текущее значение:

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

Moovix » Блог » Изменить лимиты базы данных MySQL/MariaDB