Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to deal with the Too many connections problem in mysql. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1. Problem presentation

Too many connections error occurred in application login

Check and find that the mysql database server has reached the max_connections limit.

Mysql > show variables like 'max_connections'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_connections | 1900 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show processlist

The number of sessions has reached 1900.

The thread_pool setting does not prevent the number of sessions from rising.

Mysql > show variables like 'thread_pool%'

+-+ +

| | Variable_name | Value |

+-+ +

| | thread_pool_algorithm | 0 | |

| | thread_pool_high_priority_connection | 0 | |

| | thread_pool_max_unused_threads | 0 | |

| | thread_pool_prio_kickup_timer | 1000 | |

| | thread_pool_size | 16 | |

| | thread_pool_stall_limit | 6 | |

+-+ +

6 rows in set (0.00 sec)

2. Problem handling

Restart the mysql service. After restarting the mysql service, it is true that the number of session in mysql dropped, but soon the number of sessions rose to 1900.

It is not that the server-side session of mysql is not released, but that the application-side session is not released.

Restart the two servers of application, and the number of sessions in mysql returns to normal.

3. Conclusion

Let's first take a look at the session persistence time on the mysql server side:

Mysql > show variables like'% wait_timeout%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_lock_wait_timeout | 50 | |

| | lock_wait_timeout | 31536000 | |

| | wait_timeout | 28800 | |

+-+ +

3 rows in set (0.00 sec)

Mysql > show variables like'% interactive_timeout%'

+-+ +

| | Variable_name | Value |

+-+ +

| | interactive_timeout | 28800 | |

+-+ +

1 row in set (0.00 sec)

Interactive_timeout: the number of seconds the server waits for activity before closing the interactive connection. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option in mysql_real_connect (). See wait_timeout again.

Wait_timeout: the number of seconds the server waits for activity before closing a non-interactive connection. When a thread starts, the session wait_ timeout value is initialized based on the global wait_ timeout value or the global interactive_ timeout value, depending on the client type (defined by the connection option CLIENT_INTERACTIVE of mysql_real_connect ()), see also interactive_timeout

In this way, the two variables are jointly controlled, so both of them must be modified. To go further, the values of the two variables wait_timeout range from 1 to 2147483 (Windows) and 1 to 31536000 (linux). The values of interactive_time vary with wait_timeout, and their default values are 28800.

The system variable of MySQL is controlled by the configuration file, and when it is not configured in the configuration file, the system uses the default value, which is 28800. If you want to modify it, you can only modify it in the configuration file. There is a mysql.ini configuration file under% MySQL HOME%/bin under Windows. After opening it, add two variables and assign values.

To solve this problem:

1. Use connection pooling at client side (in MySQL Connector) to reduce the number of active connections between the client and the server.

Is to install MySQL Connector on the client

2 、 Improve the application design to reduce the number of active connections needed and to reduce the time the connection has to stay active.

Reduce the number of concurrency from the application side and reduce the retention time of each session

3. Increase the number of connections handled by MySQL server by adjusting max_connections (keep in mind that this consumes additional RAM and is still limited)

Increase the maximum number of connections setting on the mysql server side, but it will consume a lot of memory

The second method is recommended. Because the current application session duration is 10 minutes, it is recommended to reduce this value.

This is the end of the article on "how to deal with Too many connections problems in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.