MySQL server maximum number of connections how to set the reasonable [go]

If MySQL connection data set unreasonable may lead to very small traffic MySQL will prompt mysql:error 1040:too many connections error, then how to be considered reasonable to set the maximum number of MySQL connection, I would like to introduce to you.

MySQL server connection number is not to reach the maximum of 100%, or to specific problems specific analysis, the following on the MySQL server maximum connection number of reasonable settings for your reference.

We often meet the "Mysql:error 1040:too many connections" situation, in general, the maximum number of MySQL connections default is 100, the maximum can reach 16384.

One is that the traffic is really high, the MySQL server is not resistant, this time to consider increasing the spread from the server to read the pressure, another situation is the MySQL configuration file max_connections value is too small:

Mysql> Show variables like ' max_connections ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+

The maximum number of connections for this MySQL server is 256, then query the maximum number of connections the server responds to:

Mysql> show global status like ' Max_used_connections ';

MySQL server in the past the maximum number of connections is 245, did not reach the maximum number of server connections 256, there should be no 1040 error, the more ideal setting is:

Max_used_connections/max_connections * 100%≈85%

The maximum number of connections is about 85% of the maximum number of connections, if the ratio is found below 10%, MySQL server connection is set too high

There are two ways to modify the maximum number of connections that are commonly used under Windows.

First: command-line modification.

>mysql-uuser-ppassword (command line login MySQL)

Mysql>show variables like ' max_connections ';(look at the maximum number of connections currently available)

Msyql>set global max_connections=1000; (set the maximum number of connections to 1000 to see if the setting is successful again)

Mysql>exit (launched)

The problem with this approach is that the maximum number of connections set is only valid in the MySQL current service process and will revert to its original state once MySQL restarts. Because the initialization after MySQL starts is to read the data from its configuration file, this method does not make changes to its configuration file.

The second type: Modify the configuration file.

This approach is simple, just modify the MySQL configuration file my.ini or my.cnf parameters max_connections, change it to max_connections=1000, and then restart MySQL. But one of the hardest is My.ini where this file is found. There are usually two possible, one in the installation directory (this is the ideal case), and the other is in the data file directory, when installed if there is no man to change the directory, generally in the C:/programdata/mysql down the directory.

Several parameters related to the number of connections:

When you modify the maximum number of connections, there is a question-is the value bigger, better, or how big it is to fit? The size of this parameter should be considered in a number of factors, such as the number of line libraries supported by the platform used (Windows only supports 2048), the configuration of the server (especially the memory size), how much each connection consumes (memory and load), the response time required by the system, and so on. This parameter can be modified within the global or session range. The increase in the number of connections will have a lot of ripple effects and need to be avoided in practice.

First look at the status of MySQL:

mysql> status;
--------------
MySQL Ver 14.14 distrib 5.5.15, for Win32 (x86)

Connection id:1
Current database:
Current User: [email protected]
Ssl:not in use
Using delimiter:;
Server version:5.5.15 MySQL Community Server (GPL)
Protocol version:10
Connection:localhost via TCP/IP
Server Characterset:utf8
Db Characterset:utf8
Client CHARACTERSET:GBK
Conn. Characterset:gbk
TCP port:3306
Uptime:1 Hour 3 min sec

Threads:12 questions:18 Slow queries:10 opens:33 Flush tables:5 Open tab
Les:34 Queries per second avg:6.256
--------------

Open tables:34, that is, the number of open tables for the current database is 34, note that this 34 is not the actual 34 tables, because MySQL is a multithreaded system, several different concurrent connections may open the same table, This requires allocating separate memory space for different connection sessions to store this information to avoid collisions. The increase in the number of connections will result in an increase in the number of file descriptors required by MySQL. In addition, for the MyISAM table, a shared index file descriptor is also created.

At the MySQL database level, there are several system parameters that determine the number of tables that can be opened at the same time and the file descriptors to be used, that is, Table_open_cache, Max_tmp_tables, and Open_files_limit.

Mysql> Show variables like ' table_open% ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Table_open_cache | 256 |
+------------------+-------+
1 row in Set (0.00 sec)

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 this parameter to determine whether to increase the size of this parameter. View the current number of open tables (open tables) with the status command mentioned above, and you can also directly query the value of this system variable:

Mysql> Show status like ' Open_tables ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_tables | 3 |
+---------------+-------+
1 row in Set (0.00 sec)

Open_tables is the number of currently open tables, and the flush Tables command closes the currently open table. If this value is too large and if you do not perform the flush Tables command frequently, consider increasing the size of the Table_open_cache parameter.

Next look at Max_tmp_tables:
Mysql> Show variables like ' max_tmp% ';
+----------------+-------+
| variable_name | Value |
+----------------+-------+
| Max_tmp_tables | 32 |
+----------------+-------+
1 row in Set (0.00 sec)
Max_tmp_tables:32 is the number of temporary tables that a single client connection can open. To view information about a temporary table that is currently open:
Mysql> show global status like '%tmp%table% ';
+-------------------------+-------+
| variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 11 |
+-------------------------+-------+
2 rows in Set (0.00 sec)
Depending on these two values, you can determine where the temporary table was created, generally selecting the Blob and text columns, the Group by and distinct statements with more data than bytes, or when the data for a select column exceeds bytes when the union is selected. A temporary table is created directly on disk, and in addition to the temporary table in memory, it may be automatically transferred to disk by MySQL (as determined by the tmp_table_size and Max_heap_table_size parameters).

Increase the size of the Table_open_cache or max_tmp_tables parameter, from the operating system point of view, the MYSQLD process needs to use a corresponding increase in the number of file descriptors, which is controlled by the open_files_limit parameters.
Mysql> Show variables like ' open_files% ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Open_files_limit | 2670 |
+------------------+-------+
1 row in Set (0.00 sec)
However, this parameter is OS-limited, so the values we set are not always valid. If the OS limit MySQL cannot modify this value, then set to 0. If it is a dedicated MySQL server, this value is generally set to as large as possible, is set to no report too many open files error maximum value, so that can be once and for all. When the operating system cannot allocate enough file descriptors, the MYSQLD process logs a warning message in the error log.
Accordingly, there are two state variables that record the current and historical file open information:
Mysql> show global status like '%open%file% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_files | 0 |
| Opened_files | 76 |
+---------------+-------+
2 rows in Set (0.00 sec)
MySQL allocates threads for each connection to handle, and you can view the number of currently allocated threads through the threads_connected parameter:
Mysql> Show status like '%thread% ';
+------------------------------------------+-------+
| variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| threads_cached | 0 |
| threads_connected | 1 |
| threads_created | 1 |
| threads_running | 1 |
+------------------------------------------+-------+
8 rows in Set (0.00 sec)
Comparing the threads_connected parameter with the previously mentioned max_connections parameter, it can also be used as a reference to the current system load to determine if the number of connections needs to be modified.

View details for each thread: Mysql>show processlist; Kill the command that affects the system running: Kill Connection|query ThreadID.

MySQL server maximum number of connections how to set the reasonable [go]