If mysql connection data is not set properly, it may lead to low traffic. Mysql will prompt mysql: Error 1040: Too many connections is wrong, so how can we set the maximum number of connections for mysql reasonably? Let me introduce you.
The connection number of MySQL servers is not to reach the maximum 100%, but to analyze specific problems. The following is a detailed analysis of the reasonable setting of the maximum connection number of MySQL servers for your reference.
We often encounter "mysql: error 1040: too many connections". In general, the default maximum number of MySQL connections is 100, and the maximum number can reach 16384.
One is that the traffic is really high, and MySQL servers can't resist it. At this time, it is necessary to consider increasing the pressure of server decentralized reading. The other is max_connections in MySQL configuration file? Too small:
Mysql & gt displays a variable similar to "max_connections";
+ - + - +
| Variable Name | Value |
+ - + - +
| Maximum number of connections | 256 |
+ - + - +
The maximum number of connections for this MySQL server is 256, and then check the maximum number of connections responded by the server:
Mysql & gt displays global status, such as "Maximum Used Connections";
In the past, the maximum number of MySQL server connections was 245, which did not reach the maximum number of server connections of 256. There should be no 1040 error. The ideal setting is:
Maximum used connections/maximum connections * 100% ≈ 85%
The maximum number of connections accounts for about 85% of the maximum number of connections. If the ratio is found to be lower than 10%, the MySQL server connection setting is too high.
There are two common methods to modify the maximum number of connections under Windows.
The first type: command line modification.
& gtMysql -uuser -ppassword (command line login Mysql)
Mysql & gt displays a variable similar to "max_connections"; (View the current maximum number of connections)
Msyql & gt sets the global max _ connections =1000; (Set the maximum number of connections to 1000 to check whether the setting is successful again. )
Mysql & gt exits (starts)
There is a problem with this method, that is, the maximum number of connections set is only valid in the current mysql service process, and once mysql restarts, it will return to the initial state. Because the initialization work after mysql starts is to read data from its configuration file, and this way does not change its configuration file.
The second type: modify the configuration file.
This method is very simple, just modify the parameter max_connections of MySQL configuration file my.ini or my.cnf to max_connections= 1000, and then restart MySQL. But the most difficult thing is where to find the file my.ini There are usually two possibilities, one is in the installation directory (which is an ideal situation), and the other is in the directory of data files. If the directory is not manually changed during installation, it is generally in the directory under C:/ProgramData/MySQL.
Several parameters related to the number of connections:
When modifying the maximum number of connections, there will be such a question-this? Is the bigger the better, or is it set to the right size? The size of this parameter should be considered comprehensively, such as the number of thread libraries supported by the platform (windows can only support until 2048), the configuration of the server (especially the memory size), the amount of resources occupied by each connection (memory and load), and the response time required by the system. This parameter can be modified globally or in session scope. The increase of the number of connections will bring many chain reactions, and it is necessary to avoid the negative effects in practice.
Look at the status of MySQL first:
Mysql & gt status;
-
Mysql version 14. 14 Release 5.5. 15, applicable to Win32 (x86).
Connection id: 1
Current database:
Current user: root@localhost
SSL: not used
Use delimiter:;
Server version: 5.5. 15 MySQL community server (GPL)
Protocol version: 10
Connection: local host through TCP/IP.
Server Character Set: utf8
Database Character Set: utf8
Client character set: gbk
Connecticut Character Set: gbk
TCP port: 3306
Normal operation time: 1 hour, 3 minutes and 27 seconds
Thread: 12 problem: 18 slow query: 10 open: 33 refresh tables: 5 open tabs.
Les: average of 34 queries per second: 6.256
-
Open tables: 34, that is, the number of open tables in the current database is 34. Note that this 34 is not the actual 34 tables, because MySQL is a multithreaded system, and several different concurrent connections may open the same table, so it is necessary to allocate independent memory space for different connection sessions to store this information to avoid conflicts. Therefore, an increase in the number of connections will lead to an increase in the number of file descriptors required by MySQL. In addition, for MyISAM table, a * * * shared index file descriptor will be established.
At the MySQL database level, several system parameters determine the number of tables that can be opened at the same time and the file descriptors to be used, namely table_open_cache, max_tmp_tables and open_files_limit.
Mysql & gt displays a variable similar to "table_open%";
+ - + - +
| Variable Name | Value |
+ - + - +
| Table Open Cache | 256 |
+ - + - +
0 row in 65438+ set (0.00 second)
Table_open_cache:256, which means that all MySQL threads can open 256 tables at the same time. We can collect the history of the number of open tables in the system and compare it with this parameter to decide whether to increase the size of this parameter. Use the status command mentioned above to check the number of currently Open tables, and you can also directly query system variables? ：
Mysql & gt displays a state similar to "open_tables";
+ - + - +
| Variable Name | Value |
+ - + - +
| Open the form | 3 |
+ - + - +
0 row in 65438+ set (0.00 second)
Open_tables is the number of currently open tables, which can be closed by the flush tables command. This? If it is too large and the flush tables command is not executed frequently, consider increasing the size of the table_open_cache parameter.
Next, look at max_tmp_tables:
Mysql & gt displays a variable similar to "max_tmp%";
+ - + - +
| Variable Name | Value |
+ - + - +
| max_tmp_tables | 32 |
+ - + - +
0 row in 65438+ set (0.00 second)
Max_tmp_tables:32 is the number of temporary tables that can be opened by a single client connection. View the information of the currently open temporary table:
Mysql & gt displays global status, such as "%tmp% table%";
+ - + - +
| Variable Name | Value |
+ - + - +
| Created _ tmp _ disk _ tables | 0 |
| Created _ tmp _ tables | 1 1 |
+ - + - +
2 rows in the collection (0.00 seconds)
According to these two? You can determine the creation location of the temporary table. Usually, when the data amount of BLOB and TEXT columns, Group by and Distinct statements exceeds 565,438+02 bytes, or in the case of union, when the data of a column exceeds 565,438+02 bytes, temporary tables are created directly on the disk. In addition, when the temporary table in memory becomes larger, it may be automatically transferred to disk by MySQL (determined by tmp_table_size and max_heap_table_size parameters).
After increasing the size of the table_open_cache or max_tmp_tables parameter, the number of file descriptors required by the mysqld process will increase from the perspective of the operating system, which is controlled by the open_files_limit parameter.
Mysql & gt displays a variable similar to "open_files%";
+ - + - +
| Variable Name | Value |
+ - + - +
| Open file limit | 2670 |
+ - + - +
0 row in 65438+ set (0.00 second)
But this parameter is limited by OS, so we set it? If the operating system restricts MySQL from modifying this, it doesn't always work? , and then set it to 0. If it is a dedicated MySQL server, this? Generally, it should be set as big as possible, that is, there are not too many biggest errors in opening files? In this way, once and for all. When the operating system cannot allocate enough file descriptors, the mysqld process will record a warning message in the error log.
Therefore, there are two state variables that record current and historical file opening information:
Mysql & gt displays global status, such as "%open% file%";
+ - + - +
| Variable Name | Value |
+ - + - +
| Open file | 0 |
| Open file | 76 |
+ - + - +
2 rows in the collection (0.00 seconds)
MySQL allocates threads for each connection to be processed, and you can check the number of threads currently allocated through the threads_connected parameter:
Mysql & gt displays a status similar to "%thread%";
+ - + - +
| Variable Name | Value |
+ - + - +
| Delayed _ insert _ threads | 0 |
| Performance _ schema _ thread _ class _ lost | 0 |
| Performance mode thread instance is missing | 0 |
| Slow start thread | 0 |
| Thread Cache | 0 |
| Thread _ Connected | 1 |
| Thread _ Created | 1 |
| Thread running | 1 |
+ - + - +
A group of 8 lines (0.00 second)
Comparing the threads_connected parameter with the aforementioned max_connections parameter can also be used as a reference for the current system load to determine whether the number of connections needs to be modified.
View the details of each thread: mysql & gt displays the process list; Kill the command of the thread that affects the system running: kill connection|query threadid.