{"id":1595,"date":"2015-01-14T05:48:00","date_gmt":"2015-01-14T05:48:00","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=1595"},"modified":"2015-01-14T05:48:00","modified_gmt":"2015-01-14T05:48:00","slug":"resolving-mysql-many-connections-error","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2015\/01\/14\/resolving-mysql-many-connections-error\/","title":{"rendered":"Resolving mysql Error “Cannot connect to DB: Too many connections”"},"content":{"rendered":"

There are 2 different limits for MYSQL. Max Connections and Max user connections. By default, the limit is 151 for max connections and 0 or unlimited for user connections.<\/p>\n

MySQL server has a default limit of 151 simultaneous connections. Most connections to the SQL server run very quickly so even a large amount of queries should not cause a server to hit this limit. This problem usually occurs when a query takes too long to execute or locks a table preventing other queries from executing (and building up the number of simultaneous connections).<\/p>\n

Once the server hits the limit of connections, it will refuse to accept new queries and return error 1203: Too many connections.<\/p>\n

If the server is currently at the limit of connections, it will still accept one more connection from a MySQL account with super user privileges (like user root). You can use this connection to view the currently running queries or try to kill the queries that are running for too long.<\/p>\n

Check for the settings. Log into the mysql server. Run the following:
\nMax Connections<\/p>\n

\nMariaDB [(none)]> SHOW VARIABLES LIKE "max_connections";\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| max_connections | 151   |\n+-----------------+-------+\n1 row in set (0.00 sec)\n\n<\/pre>\n
Max Use
\nMariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘max_use%’;
\n+———————-+——-+
\n| Variable_name        | Value |
\n+———————-+——-+
\n| max_user_connections | 0     |
\n+———————-+——-+
\n1 row in set (0.00 sec)
\nHow to update max_connections Value<\/p>\n
Before increasing this value, make sure your server has enough resources to handle more queries. Now execute below query in mysql terminal to set this value temporarily. Remember that this value will reset on next mysql reboot.<\/p>\n
\n\nMariaDB [(none)]> SET GLOBAL max_connections = 250;\nQuery OK, 0 rows affected (0.00 sec)\n\n<\/pre>\n
To set this value permanently, edit mysql configuration file on your server and set following variable. The configuration file location may change as per your operating system. By default you can find this at \/etc\/my.cnf on CentOS and RHEL based system and \/etc\/mysql\/my.cnf on Debian based system. Under [mysqld]:<\/p>\n
\nmax_connections = 250\n<\/pre>\n
Now restart mysql service and check value again with above given command. This time you will see that value is set to 250.<\/p>\n
\nMariaDB [(none)]> SHOW VARIABLES LIKE "max_connections";\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| max_connections | 250   |\n+-----------------+-------+\n1 row in set (0.00 sec)\n\n<\/pre>\n
Another simple way to temporarily resolve this issue is to restart the MySQL server. On CentOS\/RHEL:<\/p>\n
\n # systemctl restart mariadb.service\n<\/pre>\n
You can also enable the slow query log which will record queries that take longer than a specified amount of time (two seconds by default) for later review and optimization.<\/p>\n
\nnano \/etc\/my.cnf\nslow_query_log = 1\nslow_query_log_file = \/var\/log\/mariadb\/slow-query.log\n<\/pre>\n
Restart<\/p>\n
\n # systemctl restart mariadb.service\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
There are 2 different limits for MYSQL. Max Connections and Max user connections. By default, the limit is 151 for max connections and 0 or unlimited for user connections. MySQL server has a default limit of 151 simultaneous connections. Most connections to the SQL server run very quickly so even a large amount of queries … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-1595","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/1595","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=1595"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/1595\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=1595"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=1595"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=1595"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}