Mysql View Maximum Connections and Modify Maximum Connections

  • 2021-11-24 03:07:28
  • OfStack

MySQL View Maximum Connections and Modify Maximum Connections

1. View the maximum number of connections


show variables like '%max_connections%';

2. Modify the maximum number of connections


set GLOBAL max_connections = 200;

The following article mainly introduces the modification of the maximum number of connections of MySQL. We all know that the default value of MySQL maximum connection number is 100, which is far from enough for applications with many concurrent connections to databases. When the connection request is greater than the default connection number, there will be an error that cannot connect to the database, so we need to properly adjust it by 1. When using MySQL database, you often encounter such a problem, that is, " Can not connect to MySQL server. Too many connections”-mysql 1040 Error, because the number of connections accessing MySQL that have not been released has reached the upper limit of MySQL. Normally, the maximum number of connections for an mysql defaults to 100, with a maximum of 16384.

The number of mysql database connections is too much, which leads to system errors. The system cannot connect to the database. The key is to look at two data:

1. The maximum number of connections allowed by the database system max_connections . This parameter can be set. If not set, the default is 100.

2. The current number of connection threads in the database threads_connected . This is dynamic.

Look at max_connections, max_connections and we'll talk about it later.

If threads_connected == max_connections If the program wants to create a new connection thread, the database system will refuse if the program does not do too much error handling.

Because creating and destroying database connections will consume system resources. Moreover, in order to avoid opening too many connection threads at the same time, the so-called database connection pool technology is generally used in programming 1.

However, database connection pool technology can not avoid the exhaustion of connection resources caused by program errors.

This usually happens when the program fails to release the database connection resources in time or for other reasons. A simple way to check for similar errors is to constantly monitor the changes of threads_connected while refreshing the page. If max_connections is large enough and the value of threads_connected keeps increasing to reach max_connections, then you should check the program. Of course, if database connection pool technology is used, threads_connected will no longer grow when it grows to the maximum number of connection threads in the database connection pool.

View max_connections


show variables like "max_connections";

The results are as follows:


+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 100  |
+-----------------+-------+

View threads_connected


show status like 'Thread_%';

The results are as follows:


+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 0   |
| Threads_connected | 1   |
| Threads_created  | 1   |
| Threads_running  | 1   |
+-------------------+-------+

Settings max_connections

The setting method is in my.cnf Found in the file max_connections 1 item. If there is no item, add 1 line under [mysqld]:


[mysqld] 
max_connections=1000

After modification, restart MySQL. Of course, to make sure the settings are correct, you should look at max_connections under 1.

Note:

1. 1000 written here. Please modify according to actual requirements; 2. The maximum allowable connection number is added, which does not increase the system consumption much. 3. If your mysql uses my. ini as the configuration file, the settings are similar, but the format of the settings should be slightly modified.

Other things to pay attention to:

When programming, because the MySQL statement calls the database, before each execution of the statement, will do a temporary variable to open the database, so when you use the MySQL statement, remember to close the MySQL temporary variable after each call of MySQL.

In addition, for those with large visits, you can consider writing directly to the text. According to the predicted visits, first define 100 file names. When necessary, analyze the data in all text files and then import them into the database.

Summarize


Related articles: