Как изменить лимиты в MySQL/MariaDB
Обновлено:
Опубликовано:
Тематические термины: MySQL, MariaDB.
По умолчанию, настройки лимитов СУБД могут оказаться не оптимальными. В некоторых случаях, это может привести к накапливанию очередей и низкой производительности приложений или, вовсе, привести к ошибке «MySQL server has gone away» — необходимо увеличить стандартные значения.
Просмотр значений
Как будем настраивать
max_connections
max_user_connections
wait_timeout
max_allowed_packet
innodb_lock_wait_timeout
В рамках данной инструкции мы рассмотрим следующие лимиты:
| Параметр (ед. изм.) и описание | Возможные варианты для значений | ||
| Умолчание | Минимум | Максимум | |
| 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';
