ERROR 1040(HY000): Too many connections: There are Too many connections in the DB connection pool.

The database has a connection pool of its own. When each of your systems is deployed on a machine, the system instance/service instance deployed on that machine also has a connection pool of its own. Each connection Socket in your system corresponds to a Socket connection in the DB connection pool.

When MySQL tells you Too many connections, it is telling you that its pool of connections is full and that your business system cannot make new connections to it.

case

The DB is deployed on a 64 GB memory machine, and the Java service system connected to the physical machine is deployed on two machines. The maximum size of the Connection pool of the Java system is 200, that is, each Java service system node can establish a maximum of 200 connections with MySQL, and a total of 400 connections can be established.

If Too many Connections is reported, 400 network Connections cannot be established. That’s too little. This is a high-configuration machine!

My.cnf: max_connections = 800; max_connections = 800; So why can’t two machines just make 400 connections?

Log in to the MySQL machine and run the following command:

show variables like 'max_connections'
Copy the code

As you can see, MySQL currently has only 214 connections! Does MySQL care about this parameter?

Check MySQL startup log:

Could not increase number of max_open_files to more than mysqld (request: 65535) Changed limits: max_connections: 214 (requested 2000)

Changed limits: table_open_cache: 400 (requested 4096)
Copy the code

MySQL found that it could not set max_connections to 800 as we expected, so it forced 214! The maximum number of MySQL connections is 214 because Linux limits the number of open file handles to 1024.

Limiting the number of Linux file handles also limits the maximum number of MySQL connections.

How to solve

The core command is as follows:

ulimit -HSn 65535
Copy the code

You can then check if the maximum number of file handles has been changed with the following command:

cat /etc/security/limits.conf cat /etc/rc.local
Copy the code

The max_connections parameter is set in my.cnf. Restart the server and MySQL. The maximum file handle for Linux and the maximum number of connections for MySQL will take effect.

At this point, try to connect the business system to DB, there is no problem.

Why is the maximum number of connections in MySQL 214 when the Linux maximum file handle limit is 1024? MySQL source code is a calculation formula, calculate down to this result.

By default, Linux limits the amount of machine resources you can use per process, including:

  • Limits on file handles that can be opened
  • Limit on the number of child processes that can be opened
  • Network cache limitations
  • Maximum memory size that can be locked

Since Linux OS is designed to prevent your process from using up all the resources on the machine at once, it does this by default.

For us, a common problem is the limitation of file handles.

Because if Linux limits you to too few file handles for a process, we can’t create a lot of network connections, and our system processes won’t work properly. For example, when MySQL runs, it is actually a process on Linux, so it actually needs to establish a large number of connections with many business systems. As a result, you limit its maximum number of file handles, so it can not establish too many connections!

Therefore, if you deploy a system such as DB system, MQ system, storage system, Cache system in production environment, you need to adjust some kernel parameters of Linux, this file handle number must be adjusted, usually set to 65535.

MQ, such as Kafka, may not be able to create enough threads when deployed in production if Linux kernel parameters are not optimized.

Therefore, the ulimit command can be used to set the amount of resources that can be used by each process

# Limit the amount of resources a process can use
ulimit -a
Copy the code

  • Core file Size Size limit of the dump file when the process crashes
  • Max Locked Memory Maximum locked memory size
  • Open Files Maximum number of file handles that can be opened
  • Max user processes is the maximum number of child processes that can be advanced. Resources for a persistent setup process

After setting, be sure to change the ground to the/etc/security/limits file and print restrictions

Therefore, after running the ulimit-hsn 65535 command, use the following command to check whether it lands in the configuration file.

cat /etc/security/limits.conf

cat /etc/rc.local
Copy the code