Mysql view the maximum number of connections and modify the maximum number of connections

MySQL View the maximum number of connections and modify the maximum number of connections

1. Check 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 MySQL connections. We all know that the default value of the maximum number of MySQL connections is 100. This value is far from enough for applications with many concurrent database connections. When the connection request is greater than the default number of connections, an error will occur that the database cannot be connected, so we need to increase it appropriately. Can not connect to MySQL server. Too many connections”-mysql 1040 has reached the upper limit of MySQL. Usually, the maximum number of MySQL connections is 100 by default, and can reach 16384 at most.

Too many MySQL database connections cause system errors and the system cannot connect to the database. The key lies in two data:

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

2. The current number of database connection threads: threads_connected . This changes dynamically.

We will talk about how to check max_connections and max_connections below.

If threads_connected == max_connections , the database system cannot provide more connections. At this time, if the program wants to create a new connection thread, the database system will refuse unless the program does too much error handling.

Because creating and destroying database connections will consume system resources. In order to avoid opening too many connection threads at the same time, current programming generally uses the so-called database connection pool technology.

However, database connection pool technology cannot prevent program errors from causing exhaustion of connection resources.

This situation usually occurs when the program fails to release the database connection resources in time or other reasons cause the database connection resources to not be released. A simple way to check for similar errors is to constantly monitor the changes in threads_connected when refreshing the page. If max_connections is large enough and the threads_connected value continues to increase until it reaches max_connections, then you should check your program. Of course, if the database connection pool technology is used, threads_connected will no longer grow when it reaches the maximum number of connection threads in the database connection pool.

Check 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 |
+-------------------+------+

Setting max_connections

To set it, find the max_connections item in the my.cnf file and modify it. If there is no such item, add a line under [mysqld]:

[mysqld] 
max_connections=1000

After the modification is complete, restart MySQL. Of course, you should look at max_connections to make sure it is set correctly.

Notice:

• 1. It says 1000 here. Please modify according to actual requirements;
• 2. The maximum number of allowed connections has been added, which does not significantly increase system consumption.
• 3. If your MySQL uses my.ini as the configuration file, the settings are similar, but the format of the settings needs to be slightly modified.

Other things to note:

When programming, when you use MySQL statements to call the database, a temporary variable will be created before each statement is executed to open the database. Therefore, when you use MySQL statements, remember to close the MySQL temporary variable after each call to MySQL.

In addition, for documents with large traffic, you can consider writing them directly into text. Based on the predicted traffic, first define the file names if there are 100 files. When necessary, analyze the data in all text files and then import them into the database.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links