When MySQL error occurs, the following steps can be solved through the following steps: 1. Log in to MySQL and execute SHOW STATUS LIKE 'Threads_connected' and SHOW VARIABLES LIKE 'max_connections' to confirm whether the number of connections exceeds the limit; 2. Check whether there is a prompt for "Too many connections" in the log; 3. Temporarily increase the max_connections value and take effect by dynamically setting or modifying the configuration file; 4. Check PROCESSLIST and KILL to free up idle connections; 5. Long-term optimization includes reasonably configuring connection pool parameters, optimizing slow query, introducing cache layer, doing current limit fuses, and establishing monitoring and alarm mechanisms.

MySQL has an "Too many connections" error, usually because the number of connections reaches the system limit. This problem seems simple, but if not handled in time, it will affect service stability. The core reason is that the current number of connections exceeds the maximum number of connections allowed by MySQL .

Let’s take a look at how this problem occurs and how it can be solved from several common perspectives.

How to confirm that the number of connections exceeds the limit?

The most direct way is to log in to MySQL and execute the following command:

 SHOW STATUS LIKE 'Threads_connected';

This command displays the number of connections currently established. Compare the values ??of max_connections :

 SHOW VARIABLES LIKE 'max_connections';

If the former is close to or equal to the latter, then the problem lies here.

You can also observe MySQL logs. There is usually a prompt like "Too many connections" when there is a connection rejection.

Why are there too many connections?

There are several common reasons for this problem:

• The application does not close the database connection correctly
  For example, if you do not call close() after using the connection in the code, or if you use the connection pool but the resource is not released.

• A large number of requests are called to the database in a short time
  Especially in high concurrency scenarios, such as flash sale, snap-up and other activities, if there is no current limit control, it is easy to instantly explode the number of connections.

• Unreasonable connection pool configuration
  For example, the maximum number of connection pools is set too high, or the idle connection is not released in time.

• Slow query causes long-term use of connection
  If some SQLs execute for a long time, these connections will be occupied and cannot be reused.

How to solve this problem temporarily?

When an emergency occurs, you can try the following ways to alleviate it:

• Modify the MySQL configuration file (usually my.cnf or my.ini) and increase max_connections value:

   [mysqld]
  max_connections = 1000

  Then restart MySQL and take effect.

• If you cannot restart, you can adjust dynamically in MySQL (be careful not to set it too high):

   SET GLOBAL max_connections = 1000;

• View the current connection list and manually disconnect the idle connection:

   SHOW PROCESSLIST;
  KILL <connection_id>;

  Note that only kill connections that have status Sleep or have not responded for a long time.

  ---

  What are the suggestions for long-term optimization?

  To fundamentally avoid this problem, we need to start from the architecture and code level:

  • Reasonably configure connection pool parameters
    Control the maximum number of connections and set appropriate timeout and idle recovery mechanism. For example, mainstream connection pools such as HikariCP and Druid support these configurations.

  • Optimize slow query
    Use slow query logs to find time-consuming statements, add indexes, split complex queries, and reduce full table scanning.

  • Introducing the cache layer
    Put some data that reads more and writes less into Redis or local cache to reduce direct access to the database.

  • Make current limiting and fuse mechanisms
    Added a current limiting strategy at the business layer, such as how many requests are allowed to access the database per second, and if it exceeds it, it will be queued or an error will be returned.

  • Monitoring and alarm
    Use Prometheus Grafana or other tools to monitor the number of connections in real time and warning in advance.

  ---

  Basically these methods. This problem itself is not complicated, but it is easy to be ignored, especially when there are very few connections in the development stage, and it will be exposed as soon as the pressure test is launched. Only by detecting and handling early can the service be avoided from lapse.

  The above is the detailed content of mysql too many connections error. For more information, please follow other related articles on the PHP Chinese website!