Mysql error max user connections - Исправление ошибок и поиск оптимальных решений проблем

Время прочтения
4 мин

Просмотры 15K

И снова ERROR 1040…

Техподдержка получает много жалоб на эту печально известную ошибку: ERROR 1040: Too many connections — слишком много соединений. Проблема очевидна: приложение или пользователи создают больше соединений, чем допускает сервер, то есть текущее число соединений превышает значение переменной max_connections.

Ситуация уже сама по себе проблема для конечных пользователей, но если еще при этом у вас нет доступа к серверу для диагностики и исправления причины, все становится совсем плохо. Обычно приходится завершать экземпляр и перезапускать его, чтобы восстановить.

Root-пользователь тоже не может подключиться! Почему?!

В правильно настроенной среде пользователь с привилегией SUPER сможет получить доступ к экземпляру и диагностировать причину ошибки 1040, из-за которой не хватает соединений. Это описано в руководстве:

mysqld разрешает max_connections + 1 клиентских соединений. Дополнительное соединение зарезервировано для аккаунтов с привилегиями SUPER. Когда эти привилегии предоставляются администраторам, а не обычным пользователям (которым они и не нужны), администратор, у которого есть еще и привилегия PROCESS, может подключиться к серверу и использовать SHOW PROCESSLIST, чтобы диагностировать проблемы, даже если подключено максимальное число клиентов без привилегий.

Но куча людей дают привилегии SUPER своим пользователям приложения или скрипта — из-за требований приложения (опасно!) или незнания последствий, а потом зарезервированное соединение занимает обычный пользователь, а административный пользователь (обычно root) не может подключиться.

Как гарантировать доступ к экземпляру

Можно использовать хорошо известный хак с GDB, который советовал Ауримас лет 100 назад для ошибки 1040, но теперь есть решения получше. Правда сначала их надо включить.
С Percona Server 5.5.29 и выше и MySQL 8.0.14 и выше можно настроить еще один порт с дополнительным числом соединений. Приложение не будет использовать эти интерфейсы. Они только для администраторов баз данных и агентов мониторинга и проверки работоспособности (см. примечание ниже).

Настройка Percona Server

Начиная с Percona Server 5.5.29 можно просто добавить extra_port в my.cnf, и при следующем перезапуске порт будет доступен и будет ожидать данные по тому же bind_address, что и обычные соединения. Если не настроить переменную extra_port, дополнительного порта по умолчанию не будет.

Еще можно определить extra_max_connections, чтобы задать количество подключений, которое будет обрабатывать этот порт. Количество по умолчанию — 1.

Для примера я занял все подключения к порту обычных пользователей у экземпляра, где уже настроил extra_port и extra_max_connections в my.cnf:

результат

Кстати, extra_port удален в Percona Server 8.0.14 и выше, поскольку в MySQL Community реализован admin_port с теми же функциями. Так что отредактируйте my.cnf при апгрейде до Percona Server 8.0.14 или выше, если вы уже определили extra_port.

Как я уже сказал, для этого нужен MySQL 8.0.14, где применен WorkLog 12138.

Чтобы включить админский интерфейс, нужно определить admin_addres, который должен быть единственным и уникальным (без подстановочных символов) IPv4, IPv6, IPv4-сопоставленным адресом или именем хоста, по которому админский интерфейс будет ожидать передачи данных. Если эта переменная не определена, интерфейс не включен.

Еще можно определить порт, но это не обязательно. По умолчанию это порт 33062. Если этот порт свободен, это значение не нужно настраивать. Если настраиваете, то поместите обе переменные в раздел [mysqld] в my.cnf.

Наконец, можно настроить create_admin_listener_thread (отключено по умолчанию), который создает отдельный поток для обработки входящих соединений. Это может пригодиться в некоторых ситуациях.

Еще одно различие — в документации Oracle сказано, что:

Число административных соединений не ограничено.

(А у нас значение по умолчанию — 1). Не уверен, что это значит, но я бы был осторожен, чтобы случайно не установить 1 млн соединений. Они, конечно, не ограничены, но ресурсы-то все равно потребляют.

Использование для мониторинга и проверок работоспособности

Удобно, что не только люди могут использовать дополнительный интерфейс или порт в экстренной ситуации, когда мы достигли max_connections. К нему может подключиться система мониторинга и проверки работоспособности прокси/балансировщика нагрузки/обнаружения сервисов.

Скрипты мониторинга смогут извлекать данные для диаграмм, чтобы потом вы разобрались, откуда столько соединений. А скрипты проверки работоспособности будут докладывать об ухудшившемся состоянии сервера, и определенный код может указывать, что соединений много, но сервер справляется (то есть может разобраться сам и лучше чуть дольше подождать до отработки отказа).

Обязательно устанавливайте только по одному соединению за раз для мониторинга и проверки работоспособности, чтобы не забивать extra_max_connections в Percona Server и не создать миллион потоков в MySQL. То есть скрипты не должны подключаться снова, если предыдущий запрос или подключение к базе данных еще активны.

Вот тот же пример, но с MySQL.

Для Percona Server 8.0.14 и выше процесс будет тем же, что и для MySQL Community.

Помогите! Мне нужно войти, но все порты заняты!

Если это та самая причина, по которой вы читаете этот пост, используйте безумный хак с GDB (без обид, Ауримас, просто выглядит рисково :-D) или завершите экземпляр. К счастью, экземпляр почти всегда можно аккуратно завершить с помощью SIGTERM (-15) вместо SIGKILL (-9). Так сервер выполнит чистую остановку, и у потоков будет шанс нормально завершить работу. Просто следуйте инструкциям:

1) Получите PID:

marcos.albe in ~/ pgrep -x mysqld;
650

2) Отправьте SIGTERM в этот PID:

marcos.albe in ~/ kill -15 650;

3) Следите в журнале ошибок, как выполняется завершение работы. Это будет выглядеть примерно так:

2019-07-11T13:43:28.421244Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-07-11T13:43:28.521238Z 0 [Note] Shutting down slave threads
2019-07-11T13:43:28.521272Z 0 [Note] Forcefully disconnecting 0 remaining clients

Это означает начало процесса завершения работы. Экземпляр будет завершен, когда вы увидите подобную строку:

2019-07-11T13:43:31.292836Z 0 [Note] /opt/percona_server/5.7.26/bin/mysqld: Shutdown complete

Источник

One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

Prevent MySQL ERROR 1040

There are number of causes for running out of connections, the most common ones involving when the Web/App server is creating unexpectedly large numbers of connections due to a miss-configuration or some script/application leaking connections or creating too many connections in error.

The solution I see some people employ is just to increase max_connections to some very high number so MySQL “never” runs out of connections. This however can cause resource utilization problems – if a large number of connections become truly active it may use a lot of memory and cause the MySQL server to swap or be killed by OOM killer process, or cause very poor performance due to high contention.

There is a better solution: use different user accounts for different scripts and applications and implement resource limiting for them. Specifically set max_user_connections:

mysql> GRANT USAGE ON *.* TO ‘batchjob1’@‘localhost’

—> WITH MAX_USER_CONNECTIONS 10;

This approach (available since MySQL 5.0) has multiple benefits:

Security – different user accounts with only required permissions make your system safer from development errors and more secure from intruders
Preventing Running out of Connections – if there is a bug or miss-configuration the application/script will run out of connections of course but it will be the only part of the system affected and all other applications will be able to use the database normally.
Overload Protection – Additional numbers of connections limits how much queries you can run concurrently. Too much concurrency is often the cause of downtime and limiting it can reduce the impact of unexpected heavy queries running concurrently by the application.

In addition to configuring max_user_connections for given accounts you can set it globally in my.cnf as “max_user_connections=20.” This is too coarse though in my opinion – you’re most likely going to need a different number for different applications/scripts. Where max_user_connections is most helpful is in multi-tenant environments with many equivalent users sharing the system.

Источник

Как изменить лимиты в MySQL/MariaDB

Обновлено: 19.08.2021
Опубликовано: 26.08.2016

Тематические термины: 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’;

Источник

Как увеличить количество подключений 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

Источник

Problem

When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are “too many connections“. This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about connections to a server can be found using the SHOW STATUS statement:

$ mysql –u root –p
SHOW STATUS LIKE 'max_used_connections';

First, you should ensure that your applications are closing connections to the server when they are no longer needed. However, you can solve this error by increasing the value of the max_connections variable and possibly decreasing the value of wait_timeout if you expect that many of the connections to your server are not being actively used.

Solution

The maximum number of connections threads allowed for the server is contained in the system variable max_connections. The default value is 151. To see the value to which this variable is set, run the following SQL command:

$ mysql –u root –p
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Changing the max_connections parameter (Temporarily)

To change the value of the system variable max_connections, the –max_connections option can be used. To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p
mysql> SET GLOBAL max_connections = 512;

Note: It’s not necessary to have a client which is currently connected to the server disconnect to run this SQL statement as root. The server permits one connection more than the value of the max_connections. One connection is reserved for the root user or any other accounts that has SUPER privilege.

When the MySQL server daemon (mysqld) is restarted the above value will set back to the default value of 151. To make changes permanent use the below method.

Changing the max_connections parameter (Permanently)

A better method to change max_connections parameter would be to add this option to the options file (my.cnf or my.ini, depending on your system) so that it takes effect next time the server is restarted. Assuming you are using /etc/my.cnf file, add the below line to it.

# vi /etc/my.cnf
max_connections = 512

Now restart the mysqld daemon for the changes to take effect.

For CentOS/RHEL 6:

For CentOS/RHEL 7:

# systemctl restart mysqld

What is the Maximum Value for max_connections on Linux?

The upper limit for how large you can configure max_connections is largely determined by the operating system in addition to the limit of 100,000 that MySQL sets. Linux has a limit called max open files, this is defined “per login” and says the maximum number of files a process can open. The default is 1024 (which you can see using ulimit -n).

Источник