[MySQL] too many connections error


Writing time : 2018-05-12 03:24:49

too many connections error

The too many connections error in MySQL is an error that occurs when the number of connected connections is greater than the maximum number of connections set.

You can check the maximum number of connections set with the command below.

If there are more than 151 connections, too many connections error will occur.

MariaDB> show variables like 'max_connections';  
+-----------------+-------+  
| Variable_name   | Value |  
+-----------------+-------+  
| max_connections | 151   |  
+-----------------+-------+  

In the normal case, even if there are many connections, this error does not occur because too many connections are made when processing is completed.

If you check the connection status with the command below, the maximum number of connected connections is 4. (Max_used_connections)

MariaDB> show status like '%connections%';  
+--------------------------+-------+  
| Variable_name            | Value |  
+--------------------------+-------+  
| Aborted_connects         | 0     |  
| Connections              | 8     |  
| Max_used_connections     | 4     |  
| Ssl_client_connects      | 0     |  
| Ssl_connect_renegotiates | 0     |  
| Ssl_finished_connects    | 0     |  
| Threads_connected        | 4     |  
+--------------------------+-------+  
7 rows in set (0.00 sec)  

When I google it, there are a few cases where the connection count increases abnormally, but my case was due to dead lock.

Due to the dead lock of a specific table, all newly called pages were dead locked and not processed, so the connection was not disconnected and the number of connections continued to increase.

It is said to be closed after the time set as the timeout value, but the default timeout time is 28800 seconds (8 hours).

The deadlock can be checked with the command below.

Whether to lock all tables in MySQL

MariaDB>show open tables;  

Whether the DB is locked or not

MariaDB>show open tables from 'db명'  

Lock information of a specific table in the DB

MariaDB>show open tables from 'db명' like '테이블명'  

To fix the deadlock, check the process in the process list and terminate the process using the kill ID command.

MariaDB > show processlist;  


+-------+------+-----------+--------+---------+------+-------+------------------+----------+  
| Id    | User | Host      | db     | Command | Time | State | Info             | Progress |  
+-------+------+-----------+--------+---------+------+-------+------------------+----------+  
|  6309 | root | localhost | llt_ex | Sleep   | 2513 |       | NULL             |    0.000 |  
|  6332 | root | localhost | llt_ex | Sleep   | 5474 |       | NULL             |    0.000 |  
| 10670 | root | localhost | llt_ex | Query   |    0 | NULL  | show processlist |    0.000 |  
+-------+------+-----------+--------+---------+------+-------+------------------+----------+  


MariaDB > kill Id;  

Reference URL


http://ellordnet.tistory.com/58

Previous post