MySQL Maximum connections setting

When using the MySQL database, you often encounter a problem that is "Can not connect to MySQL server." Too many connections "-mysql 1040 error because the number of connections that have not yet been released to MySQL has reached the maximum MySQL limit. Typically, the maximum number of connections to MySQL is 100 by default, up to 16384.

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 see 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. 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 the temporary table was created, and the amount of data for the Blob and text columns, Group by, and distinct statements is generally selected to exceed bytes. Or union, when the data in a select column exceeds bytes, a 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 (Tmp_table_size and MAX_HEAP_ The table_size parameter is determined). 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) but this parameter is OS-bound, so the value we set doesn't always have to take effect. 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 can view the number of currently assigned 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 Maximum connections setting