MySQL ERROR 1040: Too many connections

created at 07-05-2021 views: 3

As the title, this chapter mainly talks about some handling experience when the server appears ERROR 1040: Too many connections error.

max_connections check

## View the maximum number of connections
SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 512   |
+-----------------+-------+

## View the maximum number of connections used
SHOW VARIABLES LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 499   |
+----------------------+-------+

plan of solution

There are generally two solutions to this problem, the solution is very easy, we only need to increase the number of max_connections connections.

Increase the maximum number of mysql connections in the current session

SET GLOBAL max_connections = 1000;

The above mysql connection value is temporarily increased to 1000, but it only applies to the current session. Once we restart the mysql service or restart the system, the value will be reset to the default value.

Permanently increase the maximum number of mysql connections
In order to permanently increase the number of mysql connections, we need to edit the mysql configuration file, namely /etc/my.cnf.

sudo vim /etc/my.cnf

modify:

max_connections = 1000

Save the file and restart MySQL to take effect.

How much is appropriate?

Max_connextions is not the bigger the better, so how to configure it?

method 1

To improve the concurrency of MySQL depends largely on the memory, the official provides a memory calculation method about innodb:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * (sort_buffer_size + read_buffer_size + binlog_cache_size)
+ max_connections * 2MB

method 2

Installation proportional expansion:

max_used_connections / max_connections * 100% = [85, 90]%

If the maximum number of connections/maximum number of connections reaches the range of 80% to 90%, it is recommended to optimize or expand the capacity.

Expand

The following also involves several common situations that affect MySQL performance:

Thread

SHOW STATUS LIKE  'Threads%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 1      | 
| Threads_connected | 217    |
| Threads_created   | 29     |
| Threads_running   | 88     |
+-------------------+--------+

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 10     |
+-------------------+-------+
  • Threads_cached: the number of threads in the cache
  • Threads_connected: the number of connections currently open
  • Threads_created: The number of threads created to handle connections.
  • Threads_running: the number of threads that are not sleeping

If Threads_created is large, you may have to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created / Connections

View table lock status

SHOW GLOBAL STATUS LIKE 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 90    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
  • Table_locks_immediate: The number of table lock requests obtained immediately
  • Table_locks_waited: The number of times that the table lock request cannot be obtained immediately and needs to be waited. This value is too high, indicating that there may be a performance problem, and affect the release of the connection

Slow query

show variables like '%slow%';

+---------------------------+----------------------------------------------+
| Variable_name             | Value                                        |
+---------------------------+----------------------------------------------+
| slow_launch_time          | 2                                            |
| slow_query_log            | On                                           |
+---------------------------+----------------------------------------------+

Thread details

## View the details of each thread
SHOW PROCESSLIST;
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+
| Id     | User     | Host             | db           | Command | Time  | State       | Info             |
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+
|      3 | xxxadmin | localhost        | NULL         | Sleep   |     1 | cleaning up | NULL             |
|      4 | xxxadmin | localhost        | NULL         | Sleep   |     0 | cleaning up | NULL             |
|      5 | xxxadmin | localhost        | NULL         | Sleep   |     6 | cleaning up | NULL             |
+--------+----------+------------------+--------------+---------+-------+-------------+------------------+

summary

Of course, the above is just a rough solution. No matter which method is used, it needs to be expanded based on actual business scenarios.

In addition, for production environments, it is also necessary to set appropriate alarm thresholds.

Finally, during programming, because the MySQL statement is used to call the database to execute SQL, a thread is allocated to operate MySQL, so after the end of the call, the connection needs to be recycled to avoid leakage.

Please log in to leave a comment.