Shulou(Shulou.com)06/01 Report--

MySQL view the maximum number of connections and modify the maximum number of 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 is mainly to introduce to you the modification of the maximum number of connections in MySQL, we all know that the default value of the maximum number of connections in MySQL is 100. this value is far from enough for database applications with many concurrent connections. when the connection request is greater than the default number of connections, there will be an error that you cannot connect to the database, so we need to increase it appropriately. When using MySQL databases, we often encounter such a problem, that is, the "Can not connect to MySQL server. Too many connections"-mysql 1040 error, which is because the number of connections that have not been released to access MySQL has reached the MySQL limit. In general, the maximum number of connections for mysql is 100 by default, and the maximum can reach 16384.

Too many mysql database connections lead to system error. 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 it is not set, the default is 100.

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

We'll talk about the ways to check max_connections and max_connections.

If threads_connected = = max_connections, the database system cannot provide more connections, and if the program still 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 the resources of the system. And in order to avoid opening too many connection threads at the same time, programming now generally uses the so-called database connection pooling technology.

However, database connection pool technology can not avoid program errors leading to the depletion of connection resources.

This usually occurs when the program fails to release database connection resources in time or for other reasons, and an easy way to check for similar errors is to constantly monitor threads_connected changes when the page is refreshed. If the max_connections is large enough and the threads_connected value continues to increase to reach max_connections, then it's time to check the program. Of course, if you use database connection pooling technology, threads_connected will no longer grow when it reaches 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 | +-+-+

Set up max_connections

The setting method is to find a modification of max_connections in the my.cnf file. If there is no such item, add a line under [mysqld]:

[mysqld] max_connections=1000

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

Note:

1. 1000 is written here. Please modify it according to the actual requirements; 2. Add the maximum number of allowable connections, which does not increase the consumption of the system. 3, if your mysql uses my.ini as the configuration file, the setting is similar, but the format should be slightly modified.

Other points to pay attention to:

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

In addition, for the large number of visits, you can consider writing directly into the text, according to the predicted number of visits, first define if it is 100 file names, and then analyze the data in all the text files, and then import them into the database.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.