What is the maximum number of connections in mysql

The maximum number of mysql connections is 151. The maximum number of connections to the mysql database refers to the maximum number of connections that can be established with the mysql database at the same time. This number is crucial for high-traffic web applications and large enterprise applications. If the maximum number of connections is exceeded, then new connections will fail to build, causing the application to crash or experience performance issues.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

The maximum number of connections to the MySQL database refers to the maximum number of connections that can be established with the MySQL database at the same time. This number is critical for high-traffic web applications and large enterprise applications. If the maximum number of connections is exceeded, new connections cannot be established, causing application crashes or performance issues.

Usually, the maximum number of MySQL connections defaults to 151 and can reach a maximum of 16384.

Check the maximum number of mysql connections:

show variables like '%max_connections%’;

Check the current actual maximum number of connections used:

show global status like 'Max_used_connections';

It is more appropriate for the actual number of connections to be 85% of the maximum number of connections, so the maximum number of connections is We can set the number according to the actual number of connections (if you want to set the maximum number of connections to exceed 1024, you also need to modify the upper limit of the file descriptor).

Thinking: Is it best to set the number of connections to the maximum (16384)?
We set the number of connections to the maximum number of connections supported by MySQL, so that our MySQL server can support many connections at the same time, and we can also rarely encounter the error message:

MySQL: ERROR 1040: Too many connections

.

However, due to the resource limitations of the server, if max_connections is set too large, the database will exhaust the server resources and become unresponsive due to maintaining a large number of connections. Because for every additional connection, MySQL will create a thread to maintain the connection, and the CPU needs to reload the context information of the corresponding thread when switching between threads. When the number of connections continues to increase, the CPU continues to switch among the queries of each connection.

Therefore, when setting a larger max_connections value, there may be a risk that the database performance will decrease due to accepting too many connections, or even become unable to respond due to resource exhaustion.

Database connection pool

The database connection pool maintains a cache of database connections so that the connection can be reused when a request to the database needs to be made in the future. Connection pooling is used to improve the performance of executing commands on the database. Because every time you connect to the database, you need to go through a TCP connection. If you need to connect every time you access the database, then close it, and then connect again the next time you access it, it will be a waste of resources, so we put the created connection in the connection In the pool, take it when you need it, and put it back into the connection pool when you don't need it (exchanging space for time).

When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

Thinking: Is the bigger the database connection pool, the better?
Wouldn’t it be best if we set the database connection pool to be the same as the maximum number of MySQL connections? It can be guaranteed that our service has the maximum number of available connections. Every time a database request comes, we have free connections to use.

In this case, we are back to the problem we encountered above (which may lead to the risk of performance degradation of the server due to maintaining too many database connections, or even the risk of being unable to respond due to exhaustion of resources)

The above is the detailed content of What is the maximum number of connections in mysql. For more information, please follow other related articles on the PHP Chinese website!