SQLSTATE[HY000] [1040] Too many connections solution

What should I do when my website encounters SQLSTATE[HY000] [1040] Too many connections?

This error indicates MySQL The server's current number of connections exceeds the configured maximum allowed (151 by default), causing new connections to be rejected. This often occurs on high-concurrency websites (such as WordPress and Laravel) due to the application's failure to release connections promptly, sudden traffic spikes, or insufficient configuration. Don't worry, this issue can usually be fixed quickly, but monitoring is essential to prevent recurrence.

One of the most common errors encountered in the MySQL world is the infamous Error 1040:

In practice, this means that the MySQL instance has reached its maximum allowed client connection limit. The server will not accept any new connections until the connection is closed.

I want to discuss some practical advice for preventing this situation, or how to recover if you find yourself in this situation.

Fine-tune the max_connections parameter

This setting defines the maximum number of connections that a MySQL instance can accept. The reason for setting a maximum number of connections depends on the server's available resources and the application's usage patterns. Allowing uncontrolled connections may cause the server to crash, which may be worse than preventing further connections. Max_connections is a value intended to protect the server, not to fix problems related to hijacked connections.

Each connection to the server consumes a fixed amount of overhead, such as "threads" to manage the connection and memory used to manage the connection, as well as variable resources (such as memory used to create in-memory tables). It is important to measure the resource patterns of your application and find the point at which you are in danger of exceeding this number of connections.

Percona Monitoring and Management (PMM) can help you find these values. View patterns in memory usage and running threads, and correlate them with the number of connections. PMM can also display spikes in connection activity, letting you know how close you are to your thresholds. Adjust accordingly based on your server's resource constraints.

What you see below is a server with a very stable connection pattern, with a lot of room between maximum usage and maximum connections.

MySQL: How to fix Too many connections error: (HY000/1040): Too many connections

First adjust the MySQL configuration file, then restart MySQL

# # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # # * Basic Settings # #user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr #datadir = /var/lib/mysql #tmpdir = /tmp # Broken reverse DNS slows down connections considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolve # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 512M max_allowed_packet = 2G thread_stack = 192K thread_cache_size = 1024 tmp_table_size = 1024M max_heap_table_size = 1024M open_files_limit = 0 #key_buffer_size = 128M #max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched #myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 myisam_recover_options = BACKUP max_connections = 9999 table_open_cache = 40000 # # * Logging and Replication # # Note: The configured log file or its directory need to be created # and be writable by the mysql user, eg: # $ sudo mkdir -m 2750 /var/log/mysql # $ sudo chown mysql /var/log/mysql # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # Recommend only changing this at runtime for short testing periods if needed! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # When running under systemd, error logging goes via stdout/stderr to journald # and when running legacy init error logging goes to syslog due to # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf # Enable this if you want to have error logging into a separate file #log_error = /var/log/mysql/error.log # Enable the slow query log to see queries with especially long duration #log_slow_query_file = /var/log/mysql/mariadb-slow.log #log_slow_query_time = 10 #log_slow_verbosity = query_plan,explain #log-queries-not-using-indexes #log_slow_min_examined_row_limit = 1000 long_query_time =5 # Recommended 1-5 seconds, based on load testing slow_query_log = 1 # Enable slow query log (if not enabled) slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbosity = query_plan,explain # Record execution plan log_queries_not_using_indexes = 1 # Record queries not using indexes log_slow_admin_statements = 1 # Record administrative statements # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replica, see README.Debian about other # settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_size = 100M # # * SSL/TLS # # For documentation, please read # https://mariadb.com/kb/en/securing-connections-for-client-and-server/ #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem #require-secure-transport = on # # * Character sets # # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8G innodb_buffer_pool_size = 48G innodb_buffer_pool_instances = 16 innodb_log_file_size = 12G innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # this is only for embedded server [embedded] # This group is only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] # This group is only read by MariaDB-10.11 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mariadb-10.11]