Mysql too many connections как исправить

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

Количество просмотров 16K

И снова 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

This issue occurs mostly when the maximum allowed concurrent connections to MySQL has exceeded.
The max connections allowed is stored in the gloobal variable max_connections.
You can check it by show global variables like max_connections; in MySQL

You can fix it by the following steps:

Step1:

Login to MySQL and run this command: SET GLOBAL max_connections = 100;

Now login to MySQL, the too many connection error fixed. This method does not require server restart.

Step2:

Using the above step1 you can resolve ths issue but max_connections will roll back to its default value when mysql is restarted.

In order to make the max_connection value permanent, update the my.cnf file.

Stop the MySQL server: Service mysql stop

Edit the configuration file my.cnf: vi /etc/mysql/my.cnf

Find the variable max_connections under mysqld section.

[mysql]

max_connections = 300

Set into higher value and save the file.

Start the server: Service mysql start

Note:
Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections.

MySQL pre-allocate memory for each connections and de-allocate only when the connection get closed. When new connections are querying, system should have enough resources such memory, network and computation power to satisfy the user requests.

Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional request. And finally. it is very important to close the connections which are completed transaction on the server.

При работе с базами данных могут встречаться ошибки. Ниже перечислены частые ошибки и меры по их диагностике и устранению.

• Недоступность базы данных
• Повреждены таблицы БД (Table is marked as crashed)
• Ошибка 2006: MySQL server has gone away
• Ошибка 1040: Too many connections
• Ошибка 1292: Incorrect date value

Недоступность базы данных

Необходимо подключиться к серверу по SSH и выполнить следующие проверки:

1. Проверить, запущена ли служба MySQL:

service mysql status

Пример вывода для запущенной службы:

Если в выводе отсутствует слово running, служба не запущена. В этом случае необходимо попытаться ее запустить:

service mysql start

После этого надо проверить работу сайта и сделать следующую проверку, если ошибка сохраняется.

2. Проверить состояние дискового пространства.

Просмотреть общий и занятый объем на диске командой:

df -h

Доступное пространство должно быть на основном разделе. Если свободное пространство закончилось, необходимо освободить место или перейти на тариф выше. Для работы с дисковым пространством можно использовать утилиты ncdu или du.

Если на диске достаточно свободного места, но ошибка сохраняется, надо проверить состояние inodes.

Если не удается решить ошибку самостоятельно, то нужно обратиться в техническую поддержку.

Повреждены таблицы БД (Table is marked as crashed)

При возникновении ошибок вида «Warning: Table … is marked as crashed» необходимо выполнить восстановление таблиц.

Если на сервере установлен phpMyAdmin, можно выполнить восстановление с его помощью. Для этого необходимо:

1. перейти в интерфейс PMA,
2. выбрать нужную базу данных в меню слева,
3. отметить в списке таблицы, которые нужно восстановить — то есть таблицы, имена которых фигурируют в ошибках,
4. в самом низу страницы нажать на выпадающее меню «С отмеченными» и выбрать вариант «Восстановить».

Без phpMyAdmin можно выполнить необходимые действия при подключении по SSH. Для восстановления одной таблицы нужно выполнить команду:

mysqlcheck -r имя_базы имя_таблицы -uroot -p

Для восстановления всех таблиц в базе используется команда:

mysqlcheck -r имя_базы -uroot -p

Также можно выполнить проверку всех таблиц в базе с помощью команды:

mysqlcheck -r -A -uroot -p

Ошибка 2006: MySQL server has gone away

Ошибка MySQL server has gone away означает, что сервер закрыл соединение. Это происходит, как правило, в двух случаях: превышение таймаута ожидания или получение сервером слишком большого пакета.

В обоих случаях для устранения ошибки потребуется внести правки в конфигурационный файл MySQL. Это делается при подключении к серверу по SSH или с помощью веб-консоли в панели управления.

Конфигурационный файл может располагаться по различным путям, например:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

Чтобы определить, в какой файл необходимо вносить изменения, можно использовать команду вида:

grep -Rl ‘имя_параметра’ /etc/*

Например:
grep -Rl ‘wait_timeout’ /etc/*

или:
grep -Rl ‘max_allowed_packet’ /etc/*

С ее помощью можно выяснить, в каких файлах прописан нужный параметр, и изменить в них его значение.

Таймаут

Чтобы увеличить таймаут ожидания, необходимо скорректировать значение параметра wait_timeout

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Далее нужно изменить значение параметра wait_timeout на более высокое. Значение указывается в секундах: чтобы увеличить время ожидания до 10 минут, необходимо указать значение 600:

wait_timeout = 600

После перезапустить службу MySQL:

service mysql restart

Размер пакетов

Скорректировать максимально допустимый размер пакетов можно увеличением параметра max_allowed_packet.

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Дале нужно изменить значение параметра max_allowed_packet на более высокое (значение указывается в мегабайтах):

max_allowed_packet = 64M

После перезапустить службу MySQL:

service mysql restart

Ошибка 1040: Too many connections

Ошибка «Too many connections» означает, что исчерпан лимит подключений к базе данных. Ошибка связана с медленными запросами, которые выполняются слишком долго (в этом случае требуется оптимизация кода) либо в числе одновременных подключений. В этом случае можно попробовать решить проблему увеличением лимита подключений (параметр max_connections) в конфигурационном файле MySQL.

В пункте выше было описано, как определить расположение файла my.cnf.

Следует открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

И заменить значение параметра на более высокое, например:

max_connections = 200

После перезапустить службу MySQL:

service mysql restart

Ошибка 1292: Incorrect date value

При попытке добавить данные в таблицу MySQL без указания даты может выдаваться ошибка:

ERROR 1292 (22007): Incorrect date value: ‘0000-00-00’ for column ‘columnname’ at row 1

Из-за этой ошибки может нарушаться работа импорта в 1С.

Для исправления ошибки необходимо:

1.Открыть файл /etc/mysql/my.cnf:

nano /etc/mysql/my.cnf

2. В строке, начинающейся с sql-mode=, удалить следующие значения:

NO_ZERO_IN_DATE

NO_ZERO_DATE

STRICT_ALL_TABLES

3. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart

Примечание:

Если строка вида sql-mode= отсутствует, необходимо:

1. В файл /etc/mysql/my.cnf после параметра [mysqld] добавить строку:

sql-mode=»ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION»

2. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart
 

Over the years of being a DBA, I had to deal with all kinds of problems in the database. One of the most common problems I faced is the one related to the well-known error “ERROR 1040 (08004): Too many connections”. A lot has been written about this error. Still, the users keep falling into this trap, maybe because of a poorly configured database, a change in the application components, or just because of a sudden increase of connections in the application. At some point, we all face this issue in our careers, not only once but many times. The main objective of this blog post is to point out the new administrative connections allowed on MySQL 8, as these connections can save us from restarting the instance in case this happens.

Default behavior

We know that the amount of connections allowed in the database is defined by the parameter “max_connections.” The default value for this parameter is 151, and it can be changed dynamically, which means without a database restart. If the connections in the database are maxed out, we will hit the dreadful message “ERROR 1040 (08004): Too many connections”. It is important to remember that out of the box, MySQL allows one extra connection, this connection is reserved for the users with “SUPER” privilege (already deprecated here) or the CONNECTION_ADMIN privilege.

I’ll show an example of this feature; for this example, I have an instance with “max_connections=20”, and I have three users, user “monitor1” has only the PROCESS privilege, user “admin1” has the privileges PROCESS and CONNECTION_ADMIN, finally user “admin2” has the privilege SUPER (deprecated). We will see how MySQL treats these connections in the event of having an instance maxed out on user connections:

As you can see, one single connection with a user with “CONNECTION_ADMIN” or “SUPER” privilege is allowed, however, when user “monitor1” tried to connect, it was not possible because it did not have any of those privileges. Once we gain access to the database, we can easily increase the connections by changing the variable “max_connections” online and then checking the origin of the problem. It is important to remember that only one of these connections is allowed, so please don’t grant these privileges to any user, or you could still be locked out of your database.

Usually, when this problem occurs, and we cannot gain access to MySQL, the immediate solution is to restart the database and deal with all consequences that this causes, but hey… that is better than rejecting connections for several minutes during the business’s normal operating hours. There is another alternative to gain access to the database, which is by using GDB, but it is not always possible, and Too many connections? No problem! is an article we wrote about this tool in the past, the article is a bit old but still valid.

Side note for Percona Server for MySQL and MariaDB

Percona Server for MySQL, in versions before 8.0.14, had another way to access the database instance, similar to the new feature introduced in version 8.0.14. It was by enabling variables “extra_port” and “extra_max_connections,” and the usage of these variables is out of the scope of this blog post, but the objective of such variables was to allow connections to the database even when the database maximum connections have been reached. Remember that those variables were removed on version 8.0.14, and if found in the config file, the instance will not start, and an error will be shown. Like Percona Server for MySQL, MariaDB had a similar implementation for the same variables. Documentation for MariaDB can be found here.

New feature

Starting with MySQL 8.0.14, a new “Administrative Connections” or “Administrative Network Interface” feature was introduced. This feature allows connections to the database through an administrative port, there is no limit on the number of administrative connections. The difference between this feature and the single connection shown in the previous example is that this is a different port, and it does not limit the connections to only one but more than one connection if required. This should allow us to access the database when the user connections are maxed out and work from there to increase the connections or kill some of the application connections.

The easiest way to enable the “Administrative Connections” is to define the “admin_address” variable, this is the IP address that the administrative connections will listen to, for example, if you only want to allow local connections, you can define this variable as “127.0.0.1”, or if you want to connect through the network, you can define this variable as the server’s IP address. This variable is not dynamic, which means it will require a database restart. By default, this variable is empty, meaning the administrative interface is disabled. Another related variable is “admin_port”; this variable defines the port MySQL will listen to for the administrative connections, the default value for this variable is 33062. Once you define both variables and restart the database, you will see a message indicating the admin interface is ready for connections in the error log:

Now that the admin interface is configured, you need to define the users that can access this administrative connection. These users will require the “SERVICE_CONNECTION_ADMIN” privilege; otherwise, they won’t be able to connect to it. Following our initial example, I have granted the “SERVICE_CONNECTION_ADMIN” to the user “admin1” but not to user “admin2”

Testing connection to the admin interface, we see that only user “admin1” is allowed, while user “admin2” connection is rejected for lacking privilege “SERVICE_CONNECTION_ADMIN.” Also, we can confirm user “admin1” is connected to port 33062, which is the port used for the admin interface.

Conclusion

If you are using MySQL 8.0.14 or higher, you should enable the admin interface, as we have seen, enabling this feature is super easy and leverages a great feature by allowing access to the database to DBAs in case of an event of “ERROR 1040 (08004): Too many connections”. This new feature does not affect normal database performance and brings great power to DBAs.  Please consider adding the privilege “SERVICE_CONNECTION_ADMIN” only to administrative users, not application users, the idea is not to abuse this feature. If you are still using a lower version of Percona Server for MySQL, please remember you can configure variables  “extra_port” and “extra_max_connections” to access your database in case you face a max connections issue.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

max_connections = 500 max_user_connections = 500

+ ----------------- + ------- + | Имя_переменной | Значение | + ----------------- + ------- + | max_connections | 151 | + ----------------- + ------- +

Вы можете быть заинтересованы