To solve the connection limitation and timeout problems of MySQL, first, check and adjust the maximum number of connections, view the current max_connections through SHOW VARIABLES and increase the value appropriately in my.cnf; second, set the wait_timeout and interactive_timeout parameters reasonably to extend the survival time of idle connections; third, optimize the connection behavior of the application side, use the connection pool, release the connection in time, and avoid zombie connections; finally, ensure that the system-level file descriptor limits meet the MySQL connection requirements, modify limits.conf and confirm that they take effect.

MySQL's connection limitations and timeout problems are often pain points that many developers encounter when deploying or maintaining applications. The most common manifestation is the sudden occurrence of "Too many connections" or automatic disconnection of the connection. Although these problems are not complicated, if not handled properly, they will affect system stability. Let’s talk about how to solve and prevent these problems from several key points.

1. View and adjust the maximum number of MySQL connections

The default maximum number of connections in MySQL is usually 151. This number may be used for small projects, but is far from enough for services with more concurrent access.

You can view the current settings through the following command:

 SHOW VARIABLES LIKE 'max_connections';

If you find that it is really too small, you can modify the max_connections value in the configuration file (usually my.cnf or my.ini ), for example:

 [mysqld]
max_connections = 500

Then restart MySQL and take effect. But don't set it too high, because each connection will take up memory resources, and excessive increase may lead to exhausting server resources.

2. Reasonably set the connection timeout time

MySQL has several parameters related to connection timeout, the most common ones are wait_timeout and interactive_timeout , which control the disconnection time (in seconds) of non-interactive and interactive connections, respectively.

The default value is generally 8 hours (28800 seconds). If your application often has connections disconnected, it may be that this value is too small.

You can view the current settings through the following statement:

 SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

It is recommended to adjust the increase appropriately according to the business scenario, such as changing it to one day (86400 seconds):

 [mysqld]
wait_timeout = 86400
interactive_timeout = 86400

Restart also needs to take effect. Of course, this doesn't solve all connection problems, it just avoids disconnecting due to being idle for too long.

3. Check and optimize application-side connection behavior

Many times, the connection problem is not caused by the wrong MySQL settings, but by the improper use of the application layer. Common ones are:

• A new connection is created every time you request it, no connection pool is used
• Forgot to close the database connection, causing connection accumulation
• Take up connections for a long time without release

If you are using PHP, Python, Java and other languages, it is recommended to use the connection pooling mechanism, such as:

• Java can use HikariCP and Druid
• Python can use SQLAlchemy's connection pool
• Although PHP is usually automatically released at the end of each request, if a resident process framework (such as Swoole) is used, you need to pay attention to multiplexing connections.

Additionally, it can be run in MySQL:

 SHOW PROCESSLIST;

See which connections are currently running and if there are any connections that are in Sleep for a long time. These are often "zombie connections" that have not been released.

4. Cooperate with system-level resource management

The number of connections to MySQL is also limited by operating system levels, such as the maximum number of open files. Because each connection is essentially a socket file descriptor.

You can use the following command to view the current limit:

 ulimit -n

If this value is smaller than the number of connections set by MySQL, even if you set max_connections=500, you won't actually use that much. At this time, you need to modify the system limits.conf file, such as:

 mysql soft nofile 65536
mysql hard nofile 65536

Also check whether the MySQL startup scripts have these restrictions enabled.

Basically that's it. Reasonably setting the upper limit of connection, controlling timeout time, optimizing application-side behavior, and cooperating with system-level resource restrictions, most connection-related problems can basically be solved. It doesn't seem complicated, but the details are easy to ignore, especially in the early stages of launch.

The above is the detailed content of Managing MySQL Connection Limits and Timeouts. For more information, please follow other related articles on the PHP Chinese website!