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.
Error 1040 Too many connections — ошибка, которая возникает при превышении максимального количества возможных подключений к серверу баз данных со стороны скриптов сайта.
Устраняется, как правило, увеличением лимита.
Обычно превышением количества соединений проявляется как прекративший работать сайт. Ошибку можно увидеть попробовав подключиться к базе данных с реквизитами пользователя, от имени которого работает сайт.
mysql -u USERNAME -p
Enter password:
ERROR 1040 (08004): Too many connections
От имени суперпользователя подключиться удастся в случае если сайт работает не от root.
mysql -u root -p
Лимит существует для пользователя, если сайт работает от root (что именно из-за этого является плохой практикой) и появилась такая ошибка — требуется перезагрузка MySQL.
Интересно в данном случае значение актуальное директивы max_user_connection
show status like ‘%connected%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 151 |
+——————-+——-+
1 row in set (0.01 sec)
А также действующий лимит
show global variables like ‘%connections%’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_connections | 151 |
| max_user_connections | 0 |
+———————-+——-+
2 rows in set (0.00 sec)
Лимит достигнут, скрипты при этом продолжают делать запросы. Исправить ситуацию можно добавив в /etc/mysql/my.cnf такую строку
max_user_connection=500
Затем перезапустив MySQL
/etc/init.d/mysql restart
Без перезапуска того же результата можно добиться установив новое значение глобальной переменной, от имени root в консоли MySQL это делается таким образом:
set global max_connections = 500;
Лимит теперь увеличен и сайт должен вновь стать доступен. Часто причиной является большая активность аудитории и длительные запросы на изменение данных. Они протекают с блокировками, если используются таблицы типа MyISAM получить значительное улучшение можно конвертировав их в InnoDB.
One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:
|
ERROR 1040 (00000): Too many connections |
What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections. Until connections are closed, no new connection will be accepted by the server.
I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.
Accurately Tune the max_connections Parameter
This setting defines the maximum number of connections that a MySQL instance will accept. Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns. Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further connections. Max_connections is a value designed to protect your server, not fix problems related to whatever is hijacking the connections.
Each connection to the server will consume both a fixed amount of overhead for things like the “thread” managing the connection and the memory used to manage it, as well as variable resources (for instance memory used to create an in-memory table. It is important to measure the application’s resource patterns and find the point at which exceeding that number of connections will become dangerous.
Percona Monitoring and Management (PMM) can help you find these values. Look at the memory usage patterns, threads running, and correlate these with the number of connections. PMM can also show you spikes in connection activity, letting you know how close to the threshold you’re coming. Tune accordingly, keeping in mind the resource constraints of the server.
Seen below is a server with a very steady connection pattern and there is a lot of room between Max Used and Max Connections.
Avoiding Common Scenarios Resulting in Overuse of Connections
Having worked in the Percona Managed Services team for years, I’ve had the first-hand opportunity to see where many businesses get into “trouble” from opening too many connections. Conventional wisdom says that it will usually be a bad code push where an application will behave badly by not closing its open connections or by opening too many quickly for frivolous reasons.
There are other scenarios that I’ve seen that will cause this too even if the application is performing “as expected”. Consider an application stack that utilizes a cache. Over time the application has scaled up and grown. Now consider the behavior under load if the cache is completely cleared. The workers in the application might try to repopulate the cache in mass generating a spike that will overwhelm a server.
It is important to consider the systems that use the MySQL server and prevent these sorts of edge case behaviors or it might lead to problems. If possible, it is a good idea to trap errors in the application and if you run into “Too many connections” have the application back off and slip for a bit before a retry to reduce the pressure on the connection pool.
Safeguard Yourself From Being Locked Out
MySQL actually gives you “breathing” room from being locked out. In versions 5.xx the SUPER user has a +1 always available connection and in versions 8.xx there is a +1 for users with CONNECTION_ADMIN privileges. However, many times a system has lax privilege assignments and maybe an application user is granted these permissions and consumes this extra emergency connection. It is a good idea to audit users and be sure that only true administrators have access to these privileges so that if a server does consume all its available connections, an administrator can step in and take action. There are other benefits to being strict on permissions. Remember that the minimum privilege policy is often a best practice for good reason! And not always just “security”.
MySQL 8.0.14+ also allows us to specify admin_address and admin_port to provide for a completely different endpoint, bypassing the primary endpoint and establishing a dedicated admin connection. If you’re running a lower version but are using Percona Server for MySQL, you’ll have the option of using extra_port and extra_max_connections to achieve another way of connecting.
If you are able to log in as an admin account, you may be able to kill connections, use pt-kill to kill open connections, adjust timeouts, ban offending accounts, or raise the max_connections to free up the server.
If you are unable to log in, you may try to adjust the max_connection value on the fly as a last resort. Please see Too many connections? No problem!
Use a Proxy
Another way to alleviate connection issues (or move the issue to a different layer in the stack), is to adopt the user of a proxy server, such as ProxySQL to handle multiplexing. See Multiplexing (Mux) in ProxySQL: Use Case.
Limits Per User
Another variable that MySQL can use to determine if a connection should be allowed is max_user_connections. By setting this value, it puts a limit on the number of connections for any given user. If you have a smaller number of application users that can stand some limit on their connection usage, you can set this value appropriately to prevent total server connection maximum.
For instance, if we know we have 3 application users and we expect those 3 users to never individually exceed 300 connections, we could set max_user_connections to 300. Between the 3 application users, only a total of 900 connections would be allowed. If max_connections was set to 1000, we’d still have 100 open slots.
Another approach in this same vein that is even more granular is to limit connections PER USER account. To achieve this you can create an account like this:
|
CREATE USER ‘user’@‘localhost’ IDENTIFIED BY ‘XXXXXXXX’ WITH MAX_USER_CONNECTIONS 10; |
It is a good idea to limit connections to tools/applications/monitoring that are newly being introduced in your environment and make sure they do not “accidentally” consume too many connections.
Close Unused Connections
MySQL provides the wait_timeout variable. If you observe connections climbing progressively over time and not in a spike (and your application can handle it), you may want to reduce this variable from its default of 28800 seconds to something more reasonable. This will essentially ask the server to close sleeping connections.
These are just a few considerations when dealing with “Too many connections”. I hope they help you. You may also consider further reading on the topic in this previous Percona blog post, MySQL Error: Too many connections.
Что означает ошибка Mysql error 1040: Too many connections? Это ошибка означает, что в данный момент исчерпан лимит на подключение к БД.
Появляется обычно там, где либо слишком сложные запросы (которые долго выполняются), либо там где много одновременных подключений. Ну, например, от 100 пользователей инициирован одновременный запрос к БД, как например в моем варианте. Ясно, что такое в реальном использовании делать нельзя, но для сбора статистики, мне это было нужно. Итак, как же быть?
А все очень просто:
Вариант А
В случае медленных запросов, оптимизируем их, добавляем индексы и т.д.
Вариант Б
Одновременные соединения. В этом случае распределяем их на разное время запуска, чтобы они были выполнены не в одинаковое время. Т.е. убрать вот такие варианты:
|
$otime = date(‘i’); while ( true ) { if ( $otime != date(‘i’) ) { mysql_query(‘SELECT 1’); $otime = date(‘i’); } } |
А может быть оптимизация кода Вам не подойдет, тогда можно увеличить кол-во одновременных подключений. Это делается в файле конфигурации mysql, а именно /etc/my.cnf, вот в этой строчке:
Стандартное значение после установки 100. После внесения изменений, нужно рестартануть демона mysqld, делается это командой:
|
/etc/init.d/mysqld restart |
и теперь можно проверить, все ли ок, sql запросом:
|
SHOW VARIABLES LIKE ‘max_connections’ |
Вот и всё. Не забывайте делать резервные копии перед внесением изменений в конфигурационные файлы!
Похожие статьи
Автор:
| Рейтинг: 5/5 |
Теги:
Ниже приводятся ссылки на блоги ссылающиеся на этот пост:
-
Pingback от Mysql too many connections Поснов Андрей
08.10.2013
При работе с базами данных могут встречаться ошибки. Ниже перечислены частые ошибки и меры по их диагностике и устранению.
- Недоступность базы данных
- Повреждены таблицы БД (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, можно выполнить восстановление с его помощью. Для этого необходимо:
- перейти в интерфейс PMA,
- выбрать нужную базу данных в меню слева,
- отметить в списке таблицы, которые нужно восстановить — то есть таблицы, имена которых фигурируют в ошибках,
- в самом низу страницы нажать на выпадающее меню «С отмеченными» и выбрать вариант «Восстановить».
Без 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
How to interpret “MySQL error 1040 – Too many connections ! ” ?
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:
SHOW STATUS LIKE 'max_used_connections';
Prerequisite – Few points to remember before working or troubleshooting MySQL ” Too many connections ! ” error
- MySQL does not have it’s own thread handling mechanism / implementation and it completely relies on the thread handling implementation of the underlying operating system.
- MySQL system variable max_connections control the maximum number of clients the server permits to connect simultaneously, You may have to increase max_connections if more clients attempt to connect simultaneously then the server is configured to handle (Explained more in detail – “Too many connections”).
- How MySQL connection handling (both connects and disconnects) works ?
- MySQL Clients connects to MySQL Server via a simple and regular TCP-IP connect message though port 3306 on the Server instance
- The incoming connection requests are queued and then processed by the receiver thread one by one, All that receiver thread does is create user thread. It’s actually user thread which handles the client-server protocol for connection management, Which involves allocate and initialize the corresponding THD for user authentication based on credentials stored on THD’s security policies / directories and finally if successfully authorized in the connection phase, the user thread will enter command phase
- The receiver thread will either create a new OS thread or reuse and existing “free” OS thread if available in the thread cache. So we strongly recommend increasing thread cache in cases where number of connections fluctuates between ver few connections and having many connections. But there are three things which a thread might need to wait for: A mutex, a database lock, or IO.
- THD basically is a large data structure used for several purposes like connection management, authorization and even unto to query execution, So how much THD consumes memory is directly proportionate to the query execution complexities and connection traffic.
MySQL error – Too many connections, How to fix ?
Recently one of customers ( among the top 5 largest e-commerce companies in the world ) called us to check how graceful their connection handling works during peak hours of business, They had issues in the past with ” ERROR 1040: Too many connections “ and that clearly explains the maximum number of clients that may be connected to the server has been reached so 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. so wanted us to do a detailed health-check on MySQL connection management and address “Too many connections” error proactively, We have explained below on how we could successfully reproduce this issue and recommended the fix:
Goal: Manage 50,000 connections on MySQL 8.0 (Ubuntu)
The default setting for system variable max_connections is “151”and we are benchmarking 50K connections so the first step before benchmarking is to increase max_connections to 50000. we increased max_connections to 50000 dynamically and what happened after that was not expected, We have copied the results below:
root@MDB1:~# mysql -uroot -pMDB@PassWd2020 -se "select @@max_connections" @@max_connections 697
We got only 697 connections, Let’s interpret MySQL error log before proceeding to next steps.. We have copied the same below:
2020-01-30T19:52:35.136192Z0 [Warning] Changed limits: max_open_files: 5129 (requested 10000) 2020-01-30T19:54:13.241937Z0 [Warning] Changed limits: max_connections: 4152 (requested 10000) 2020-01-30T19:57:47.51617Z0 [Warning] Changed limits: table_open_cache: 533 (requested 15000)
This is due to open files limitations for MySQL so let’s increase now the number of allowed open files for MySQL, The following steps we did to fix this resource limit issue:
- Option 1 – Locate the systemd configuration folder for MySQL and create file /etc/systemd/system/mysqld.service.d/override.conf (file can be called anything ending with .conf).
- Add LimitNOFILE=55000 in the file override.conf
- Add TasksMax=55000 in the file override.conf
- Add LimitNPROC=55000 in the file override.conf
- Option 2 – We can also create/modify the override file by using native systemctl command like: systemctl edit mysql
root@MDB1:~# cat /etc/systemd/system/mysql.service.d/override.conf [Service] LimitNOFILE=55000 TasksMax=55000 LimitNPROC=55000
** MySQL uses some files for additional work and we need to set LimitNOFILE, TasksMax and LimitMPROC higher to get 50000 connections, lets set it to 55000 and reload the systemd daemon and restart the MySQL service.
Reload the systmed daemon and restart the MySQL service:
root@MDB1:~# systemctl daemon-reload root@MDB1:~# systemctl restart mysql
Now let’s check max_connections to confirm the change applied:
root@MDB1:~# mysql -uroot -pMDB@PassWd2020 -se "select @@max_connections" mysql: [Warning] Using a password on the command line interface can be insecure. @@max_connections 50000
Conclusion
We have no fixed value recommendations for system variable max_connections, It completely depends on your application load and how your application does connection handling. We advice our customers to avoid too many connections opened concurrently because each thread connected needs memory and there is also resource intensive context switching causing overall performance degradation, Thanks for reading and comments are welcome !
References
- https://mysqlserverteam.com/mysql-connection-handling-and-scaling/
- http://mysql-nordic.blogspot.com/2019/04/mysql-error-too-many-connections.html
- https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
