This is the 7th day of my participation in Gwen Challenge

One, foreword

In a production case where the Java system is deployed on two machines, the Maximum connection pool size set by the Java system is 200.

The approximate connection diagram is as follows:

Failed to create a connection for the database connection pool of the application server. Procedure

Check the operation steps as follows:

  1. checkmy.cnfconfiguration
  2. To viewMySQLThe log


  1. checkmy.cnfconfiguration

Max_connections (max_connections, max_connections, max_connections, max_connections, max_connections, max_connections)

MySQL > select * from ‘MySQL’; select * from ‘MySQL’; select * from ‘MySQL’;

Log in to MySQL from the command line and run the following command:

show variables link 'max_connections';

214
Copy the code

It turns out that MySQL only made 214 connections.


  1. To viewMySQLThe log

MySQL > select * from MySQL;

Could not increase number of max_open_files to more than mysqld (request: 65535)
Changed limits: max_connections: 214 (request 2000)
Changed limits: table_open_cache: 400 (request 4096)
Copy the code

MySQL found that it was unable to set max_connections to 800 as expected, and had to force a limit of 214!

Linux limits the number of file handles a process can open to 1024, resulting in a maximum number of MySQL connections of 214!

Linux limit file handles, as shown:





Second, the solution

How to solve this problem?

Set the number of file handles in Linux to a larger value:

ulimit -HSn 65535
Copy the code

You can run the following command to check whether the maximum number of file handles is changed:

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

After that, restart the server and MySQL again. Linux’s maximum file handle takes effect and the problem is resolved.

So why is the maximum number of connections in MySQL 214 when Linux is limited to 1024 file handles?

MySQL source code for a calculation formula, internal write dead.