Number of threads for MySQL server view method

The number of threads in the MySQL server needs to be within a reasonable range to ensure that the MySQL server runs smoothly and healthily. Threads_created indicates the number of threads created, and the process status of the MySQL server can be viewed by viewing the threads_created.

1. Mysql> show global status like ' thread% ';
2. +-------------------+-------+
3. | variable_name | Value |
4. +-------------------+-------+
5. | threads_cached | 46 |
6. | threads_connected | 2 |
7. | threads_created | 570 |
8. | threads_running | 1 |
9. +-------------------+-------+

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects, the server processes the client's thread to cache in response to the next customer instead of destroying it (provided the cache count is not up to the limit).

Threads_created indicates the number of threads created, if the threads_created value is found to be too large, it indicates that the MySQL server has been creating threads, which is also a relatively resource-intensive, can appropriately increase the thread_cache_size value in the configuration file, Query server
Thread_cache_size configuration:

1. Mysql> Show variables like ' thread_cache_size ';
2. +-------------------+-------+
3. | variable_name | Value |
4. +-------------------+-------+
5. | Thread_cache_size | 64 |
6. +-------------------+-------+

The server in the example is still quite healthy.

analyze MySQL Several parameters related to connection number

MySQL's variables and status is a powerful tool for managing maintenance, similar to Oracle's SPFile and v$ tables.

MySQL logs many configuration information through system variables, such as the maximum number of connections Max_connections:

Mysql> Show variables like '%connect% ';
+--------------------------+-----------------+
| variable_name | Value |
+--------------------------+-----------------+
| character_set_connection | UTF8 |
| collation_connection | Utf8_general_ci |
| Connect_timeout | 10 |
| Init_connect | SET NAMES UTF8 |
| max_connect_errors | 10 |
| max_connections | 200 |
| max_user_connections | 0 |
+--------------------------+-----------------+
7 Rows in Set (0.00 sec)

This parameter refers to the number of simultaneous connections, in the 5.1 version of the default value is 151, then the actual number of connections supported is this value plus one, that is, 152, because the system administrator to log on to view the information to maintain a connection. 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. The General Linux system supports up to hundreds of concurrency without any problems. This parameter can be modified within the global or session range:

mysql> set global max_connections=151;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%connect% ';
+--------------------------+-----------------+
| variable_name | Value |
+--------------------------+-----------------+
| character_set_connection | UTF8 |
| collation_connection | Utf8_general_ci |
| Connect_timeout | 10 |
| Init_connect | SET NAMES UTF8 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_user_connections | 0 |
+--------------------------+-----------------+
7 Rows in Set (0.00 sec)

However, it is important to note that the increase in the number of connections will have a lot of chain reaction, need to avoid the negative effects in practice.

First, let's look at the output of the status:

mysql> status
--------------
MySQL Ver 14.14 distrib 5.1.49, for Pc-linux-gnu (i686) using READL INE 5.1

Connection id:255260
Current Database:mysql
Current User: [email protected]
Ssl:not in US E
Current Pager:stdout
using outfile: '
using delimiter:;
Server Version:5.1.49-log MySQL Community Server (GPL)
Protocol version:10
Connection:localhost via UNIX socketserver Characterset:utf8
Db characterset:utf8
Client characterset:utf8
Conn. Characterset:utf8
UNIX Socket:/var/lib/mysql/mysql.sock
uptime:161 days 3 hours min. sec

Threads:14 questions:160655492 Slow queries:71 opens:8124 Flush tables:3 Open tables:64 queries per second avg:11.538
--------------

Here is an open tables output 64, this means that the current database open the number of tables is 64, to note that this 64 is not the actual 64 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 | |+------------------+-------+1 row in Set (0.00 sec)

The Table_open_cache parameter here is 64, which means that all MySQL threads can open 64 tables at the same time, we can collect the history of the number of open tables of the system and compare this parameter to determine whether to increase the size of this parameter. To view the current number of open tables one is to use the status command mentioned above, and you can also directly query the value of this system variable:

mysql> Show status like ' open% '; +--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| Open_files               | 3     | | Open_streams             | 0     | | Open_table_definitions   | 8     | | Open_tables              | 8     | | Opened_files             | 91768 | | opened_table_definitions | 0     | | Opened_tables            | 0     |+--------------------------+-------+7 rows in Set (0.00 sec)

mysql> show global status like ' open% '; +--------------------------+-------+| variable_name | Value |+--------------------------+-------+| Open_files | 3 | | Open_streams | 0 | | open_table_definitions | 10 | | Open_tables | 11 | | Opened_files | 91791 | | opened_table_definitions | 1211 | | Opened_tables | 8158 |+--------------------------+-------+7 rows in Set (0.00 sec)

Here is the number of open_tables that are currently open, and you can close the currently open table by using the Flush Tables command. Opened_tables, which is viewed globally, is a historical cumulative value. 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 the Max_tmp_tables parameter:

mysql> Show variables like ' max_tmp% '; +----------------+-------+| variable_name | Value |+----------------+-------+| Max_tmp_tables | |+----------------+-------+1 row in Set (0.00 sec)

This parameter specifies the number of temporary tables that a single client connection can open. To view the temporary table information that is currently open:

mysql> show global status like '%tmp%table% '; +-------------------------+-------+| variable_name | Value |+-------------------------+-------+| Created_tmp_disk_tables | 10478 | | Created_tmp_tables | 25860 |+-------------------------+-------+2 rows in Set (0.00 sec)

You can also compare these two values to determine the location of the temporary table creation, generally select the Blob and text column, Group by and distinct statement data volume exceeds bytes, or union when the data of select column exceeds bytes, 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).

Continue the original discussion, increase the size of the Table_open_cache or Max_tmp_tables parameters, from the operating system perspective, the MYSQLD process needs to use the number of file descriptors will be increased accordingly, this is by the Open_files_ controlled by the limit parameter. 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 not reported 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.

Mysql> Show variables like ' open_files% '; +------------------+-------+| variable_name | Value |+------------------+-------+| Open_files_limit | 1024x768 |+------------------+-------+1 row in Set (0.00 sec)

Mysql> Show variables like ' open_files% ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

| Open_files_limit | 1024 |

+------------------+-------+

1 row in Set (0.00 sec)

Correspondingly, 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 | 3 | | Opened_files | 91799 |+---------------+-------+2 rows in Set (0.01 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 |
| Slow_launch_threads | 0 |
| threads_cached | 0 |
| threads_connected | 14 |
| threads_created | 255570 |
| threads_running | 2 |
+------------------------+--------+
6 rows in Set (0.00 sec)

Comparing this threads_connected parameter to the previously mentioned max_connections parameter can also be used as a reference to the current system load to determine if the number of connections needs to be modified.

If you look at more detailed information for each thread, you can use the Processlist command:

Mysql> show Processlist;
+--------+-----------+--------------------+----------+-------------+----------+-------------------------------- --------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------+--------------------+----------+-------------+----------+-------------------------------- --------------------------------+------------------+
| 8293 | Repl | 192.168.0.33:47208 | NULL | Binlog Dump | 11574424 | Have sent all binlog to slave; Waiting for Binlog to be updated | NULL |
| 140991 | Mogile | 192.168.0.33:41714 | MogileFS | Sleep | 0 | | NULL |
| 140992 | Mogile | 192.168.0.33:41715 | MogileFS | Sleep | 3 | | NULL |
| 140993 | Mogile | 192.168.0.33:41722 | MogileFS | Sleep | 2 | | NULL |
| 140994 | Mogile | 192.168.0.33:41723 | MogileFS | Sleep | 1 | | NULL |
| 140995 | Mogile | 192.168.0.33:41724 | MogileFS | Sleep | 3 | | NULL |
| 254914 | Mogile | 192.168.0.33:43028 | MogileFS | Sleep | 11074 | | NULL |
| 254915 | Mogile | 192.168.0.33:43032 | MogileFS | Sleep | 11091 | | NULL |
| 255144 | Mogile | 192.168.0.33:47514 | MogileFS | Sleep | 11090 | | NULL |
| 255157 | Mogile | 192.168.0.33:47535 | MogileFS | Sleep | 11087 | | NULL |
| 255162 | Mogile | 192.168.0.33:47549 | MogileFS | Sleep | 11074 | | NULL |
| 255260 | Root | localhost | MySQL | Query | 0 | NULL | Show Processlist |
| 255352 | Maopaodev | 192.168.0.78:55399 | Maopaodb | Sleep | 3172 | | NULL |
| 255353 | Maopaodev | 192.168.0.78:55400 | NULL | Sleep | 8926 | | NULL |
+--------+-----------+--------------------+----------+-------------+----------+-------------------------------- --------------------------------+------------------+
Rows in Set (0.00 sec)

Executing this command requires PROCESS_PRIV permissions, and the specific permission assignment information can be viewed in the Mysql.user table.

For the thread that affects the system's operation, you can kill it with the kill Connection|query threadid command.