MySQL :: MySQL 8.0 Reference Manual :: B.5.2.6 Too many connections

MySQL 8.0 Reference Manual / ... / Too many connections

B.5.2.6 Too many connections

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections permitted is controlled by the max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Apache Web server. (Previously, the default was 100.) If you need to support more connections, you should set a larger value for this variable.

mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN or SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.6.29, “SHOW PROCESSLIST Syntax”.

The maximum number of connections MySQL supports depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at least 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform.

Increasing open-files-limit may be necessary. Also see Section 2.5, “Installing MySQL on Linux”, for how to raise the operating system limit on how many handles can be used by MySQL.

PREV HOME UP NEXT

User Comments

Posted by Rob Williams on April 27, 2004

Detect "Too many connections" error and show alternate web page


<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password");
if (mysql_errno() == 1203) {
  // 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
  header("Location: http://your.site.com/alternate_page.php");
  exit;
}
?>

Posted by Femi Hasani on May 29, 2004


<?
ini_set('display_errors', '0');
$link = mysql_connect("localhost", "user", "pass");
if (mysql_errno() == 1040 OR mysql_errno() == 1203) {
    define("DB_HOST", "some_ip:3306");//remote
    define("DB_NAME", "db");  //database_name
    define("DB_USER", "user"); //database user name
    define("DB_PASSWORD","pass");  //database (user) password
}
else
{
    define("DB_HOST", "localhost");
    define("DB_NAME", "db_name2");  //database_name
    define("DB_USER", "user"); //database user name
    define("DB_PASSWORD","pass");  //database (user) password
}
ini_set('display_errors', '1');
//by feha at www.vision.to
?>

Posted by Álvaro G. Vicario on October 6, 2004

You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:

[mysqld]
set-variable=max_connections=250

Posted by Álvaro G. Vicario on January 13, 2005

A note por PHP developers. You can find this error if your scripts open persistent connections, wich aren't closed even if the script terminates. Use mysql_connect() instead of mysql_pconnect() unless you have a good reason. In particular, check this setting in third-party scripts (such as osCommerce).

Server administrators can disable persistent connections for PHP scripts in php.ini file:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=Off

Scripts won't fail, they'll just use non-persistent connections silently.

Posted by Gary Lawrence Murphy on December 15, 2004

Another symptom for PHP users, the "max_connections" error being returned to the browsers and the "show processlist" filling up with sleeping threads: This can sometimes be alleviated by using the PHP .htaccess option to lower the connect timeout from the default 60 seconds.

php_value mysql.connect_timeout 20

Posted by Bob Stein on August 15, 2005

The osCommerce setting mentioned by ?varo is in the catalog/includes/configure.php file:

define('USE_PCONNECT', 'false'); // use persistent connections?

It defaults to true, so mysql_pconnect() is used, and you get the error message "Warning: mysql_pconnect(): Too many connections ..." Change to false for mysql_connect() to be used.

Posted by Remo G. on August 19, 2005

Please note that an instruction of the form
set-variable=max_connections=500
should be placed in the [mysqld] section. Otherwise MySQL will ignore it.

Posted by Arjen van Kol on April 17, 2007

A lot of sites only suffer from this problem when Google or any other search bot is visiting. The best way to resolve this is to add the 'Crawl-delay' parameter in your robots.txt or to set it to a higher number of seconds.

Posted by Maresa Nirwan on June 3, 2009

I found that maxing out all available connections is not the only way to have this error. If your disk on your MySQL server is full, you can also get this error. Clearing up disk space will rid of the error.

Full explanation of what I've encountered here: http://www.microshell.com/database/mysql/mysql-too-many-connection-errors/

Posted by Hugh Murat on August 25, 2009

The following can also be used to change max_connections:

mysql> SET GLOBAL max_connections = 200;

However, this only lasts until the MySQL Server restarts.

Posted by Jonathan Miller on November 18, 2009

On Solaris, you maybe limited to ulimits "open files".

i.e.

do ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
open files (-n) 256
pipe size (512 bytes, -p) 10
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 29995
virtual memory (kbytes, -v) unlimited

Here "open files = 256" which is a very low setting.

At "open files = 1024" I was limited to 214 max connections.

In order the get the 500 + I wanted, I had to set "open files = 4096"

i.e. ulimit -n 4096

Posted by oscar duron on November 27, 2014

Beware of mysql max connections on some hosting server providers who do not mention it in contract but they set max connections = 1 and if you need more you have to pay for premium services.