MySQL server maximum connection number how to set the reasonable

MySQL server connection number is not to achieve the maximum of 100% for good, or to specific problems specific analysis, the following on the MySQL server the maximum number of connections to the reasonable set up a detailed analysis for your reference.

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

One is that the amount of traffic is really high, the MySQL server cannot resist, this time to consider increasing the read pressure from the server spread, another case is the MySQL profile 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, and then the maximum number of connections to the server response is queried:

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, should not appear 1040 errors, the more ideal setting is:

Max_used_connections/max_connections * 100%≈85%

The maximum number of connections to the upper limit of 85% of the number of connections, if the ratio is found below 10%, MySQL server connected to the online set too high

There are two common ways to modify the maximum number of connections under Windows.

First: Command line modification.

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

Mysql>show variables like ' max_connections ';(check to see the current maximum number of connections)

Msyql>set Global max_connections=1000 (set the maximum number of connections to 1000, you can 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 for the current MySQL service process, and once MySQL restarts, it will revert to its original state. Because the initialization of MySQL after startup is to read data from its configuration file, this method does not make changes to its configuration file.

Second: Modify the configuration file.

This approach is simple, as long as you modify the MySQL configuration file my.ini or my.cnf parameter max_connections, change it to max_connections=1000, and then restart MySQL. But one of the hardest part is my.ini where to find this file. There are usually two possibilities, one is in the installation directory (this is the ideal situation), the other is in the directory of data files, installation, if there is no person 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 greater, the better, or how large is the appropriate? The size of this parameter should be considered in many factors, such as the number of line threading supported by the platform (Windows can only support to 2048), server configuration (especially memory size), the amount of resources (memory and load) each connection consumes, the response time required by the system, and so on. You can modify this parameter in the global or session scope. The increase in the number of connections will result in many ripple effects, which need to be avoided in practice.

First look at the state of MySQL:

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

Connection id:1
Current database:
Current User:root@localhost
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 table, 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 the information to avoid conflicts. The increase in the number of connections will result in an increase in the number of file descriptors required by MySQL. In addition to the MyISAM table, a shared index file descriptor is also established.

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 use, namely 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 up to 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. To view the current number of open tables (open tables), use the status command mentioned above, and 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. This value may be considered to increase the size of the Table_open_cache parameter if it is too large and if the flush Tables command is not executed frequently.

Next look 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)
Based on these two values, you can determine where a temporary table is created, typically by selecting a BLOB and text column, Group by, and distinct statements with more data than the bytes, or when you select a column with more data than a bytes, The temporary table is created directly on the disk, and when the temporary table in memory becomes larger, it may be automatically transferred to the disk by MySQL (as determined by the tmp_table_size and Max_heap_table_size parameters).

After increasing the size of the Table_open_cache or Max_tmp_tables parameters, from the operating system point of view, the MYSQLD process needs to use the number of file descriptors will increase correspondingly, this 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)
But this parameter is OS Limited, so the values we set are not always valid. If the OS limits MySQL cannot modify this value, then set to 0. If it is a dedicated MySQL server, this value is generally set to the largest possible, is set to no report too many open files error maximum, 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 current and historical file opening 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 assigns threads to each connection and 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)
Compare the threads_connected parameter to the Max_connections parameter mentioned earlier, or you can use as a reference to the current system load to determine whether you need to modify the number of connections.

View details for each thread: Mysql>show processlist; for threads that affect system running: Kill Connection|query threadid command.