MySQL Too Many Connections

What is MySQL?

MySQL is an Open-Source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage RDBs. Initially developed by MySQL AB in 1994, it has been adopted by over 5,000 companies, including Uber, Netflix, Pinterest, Amazon, Airbnb, and Twitter.

Features are:

Easy to access
Since MySQL is open-source, any person can download, use, and modify the software. It can be easy to use and provided free of cost. MySQL's source code can be accessed for study and customization according to needs. It makes use of the GPL, or GNU General Public License, which provides restrictions for what is permissible and not permissible with the program.
Rapid and Trustworthy
MySQL effectively saves information in memory to ensure consistency and prevent duplication. MySQL enables rapid access to and manipulation of data.
Adaptable
The capacity of a system to work well with large or small groups of machines and other types of data is referred to as scalability. The MySQL server was created for handling big databases.
Data Formats
Numerous data types are supported, including float (FLOAT), double (DOUBLE), character (CHAR), variable character (VARCHAR), text, date, time, datetime, timestamp, year, signed and unsigned integers, and many more.
Character Groups
It is compatible with other character sets, such as German, Ujis, Latin 1 (cp1252 character encoding), other Unicode character sets, and so forth.
Be protected
As a result of its customizable password system that verifies the password according to the host before allowing access to the database, it offers a safe interface. When the password is being connected to the server, it is encrypted.
Support with big databases
Large databases, with up to 5,000,000,000 rows, 150,000-200,000 tables, and 40-50 million records, are supported by this software.

What is the error "MySQL Too Many Connections"?

When logging into MySQL, someone may reject and receive an error message MySQL Too Many Connections. When the server reaches the maximum configured number of concurrent connections, this type of error message is displayed. Say, for example, a client application (like a web server) connects to a server and establishes a connection. Thus, the concurrent connections reached the maximum limit, and the error occurred.

Information about maximum allowed connections to a server can be found using the SHOW VARIABLE statement:

$ mysql -u root -p
mysql> : SHOW VARIABLE LIKE 'maximum_connections:'

The possible Output:

variable_name	value
maximum_connection	50

Currently occupied connections can be found by the following statement:

The possible Output:

variable_name	value
Threads_connected	50

If the MySQL server's number of threads_connected equals its maximum number of connections, the client will receive "too many connections" error messages.

Number of new connections allowed= maximum_connections - threads_connected = 50 - 50 = 0

We can also see how many connections are currently active by executing the following statement:

The possible Output:

variable_name	value
Threads_running	10

This means that 40 connections are (50-10=40) idle in the database.

Why is this error caused?

The error "MySQL Too Many Connection" may happen for some different reasons, such as

A considerable amount of traffic
Breaks in Connections
Inefficiency of Application code
Restrictions of Server
Capacity Exhaustion

Let us discuss each.

A considerable amount of traffic: Sometimes MySQL servers deal with requests for huge incoming connections, which may reach the limit of maximum connection, which results in this error. When database servers deal with more clients, this happens.
Breaks in Connections: Using server, client applications neglect to close their database connection properly. After a period of time, this unclosed connection can easily lead to this type of error.
Inefficiency of Application code: Inefficient application code can sometimes create huge database connections due to inefficient connection management.
Restrictions of Server: The max_connection system variable contains a value for the maximum allowed connections. If the limit is reached, the error "Too Many Connections" is encountered.
Capacity Exhaustion: Due to a lack of hardware capabilities, the MySQL server could fail to be capable of managing a large number of connections; therefore, resource constraints must be resolved immediately.

How to prevent the error "too many connections"?

When encountering this type of error, it is important to take necessary action and find the root cause so that we can prevent the error.

Modify MySQL configuration: This is a short-term or temporary solution by increasing the max_connection configuration in the MySQL server.
Eliminate unnecessary connections: By using the MySQL command line tool, kill or eliminate the idle connection from the database.
Improve inefficient code in client application: Breaks in connection cause this error. Make sure that connections are properly closed after using the server.
Apply pooling of connections: The connection pooling helps the server to manage and reuse the database connections, decreasing the concurrent connection. Implement connection pooling in your application.
Scanning replicas and resource balancing: Scan replicas to manage read-heavy workloads, and resource balancing helps to distribute load in the server.
Modify server capabilities: If this error occurs frequently, try to assess the server's hardware resources and optimize them to manage better connection loads.
Put connection timeouts in place: This is one of the best preventions. If you forget to close the connection, then use the timeout time for the idle connections.
Analyse user benefits: The max_user_connection variable should be adjusted.
Wide scaling: Strategies like sharding and horizontal scaling are considered wide scaling, which manages better connections as your application grows.
Frequent Inspection and Upkeep: Establish routine maintenance and monitoring procedures to find problems early on and fix them before they cause "Too Many Connections" errors.

What is the solution for the error "too many connections"?

This error can be solved by increasing the value of the max_connections parameter by runtime; this is a temporary process.

Changing the max_connections parameter (Temporarily)

While the server is operating, enter the following SQL statement to alter this variable temporarily:

$ mysql -u root -p
mysql> SET GLOBAL max_connections = 300;

NOTE: To execute this SQL statement as root, a client disconnect that is currently connected is not required. In excess of the value of max_connections, the server allows one additional connection. For the root user and any other users with SUPER privilege, one connection is set aside.

Upon restarting the MySQL server daemon (mysqld), the value above will revert to its prior value seen in my.cnf or my.ini file. Use the following procedure to create changes that will last forever:

Changing the max_connections parameter (Permanently)

Adding this option to the options file (my.cnf or my.ini, depending on your system) might be a better way to modify the max_connections value and make it effective the next time the server is run. Put the line below in your /etc/my.conf file, assuming that's the one you're using.