Detailed Explanation of Viewing Method of Thread Number of MySQL Server

In this paper, the example tells the method of viewing the number of threads in MySQL server. Share it for your reference, as follows:

mysql Restart Command:

/etc/init.d/mysql restart

The number of threads of MySQL server needs to be within a reasonable range, so as to ensure the healthy and stable operation of MySQL server. Threads_created represents the number of threads created, and you can see the process status of the MySQL server by looking at Threads_created.

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+

If we set thread_cache_size in the MySQL server configuration file, when a client is disconnected, the server's thread processing that client will be cached in response to the next client instead of being destroyed (provided the cache number is below the upper limit).

Threads_created indicates the number of threads that have been created. If the value of Threads_created is found to be too large, it indicates that MySQL Server 1 is directly creating threads, which also consumes resources. You can appropriately increase the value of thread_cache_size in the configuration file to query the server

thread_cache_size configuration:

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+

The server in the example is quite healthy.

Analysis of several parameters related to the number of connections in MySQL

variables and status of MySQL are sharp tools for management and maintenance, just like spfile and v $tables of Oracle.

MySQL records a lot of 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 clients connected at the same time. In version 5.1, the default value is 151, so the actual number of supported connections is this value plus 1, that is, 152, because one connection is reserved for the system administrator to log in and view information. The size of this parameter should be considered by integrating many factors, such as the number of thread libraries supported by the platform used (windows can only support 2048), the configuration of the server (especially the memory size), the amount of resources (memory and load) occupied by each connection, and the response time required by the system. 1 Linux system supports hundreds of concurrency without any problem. You can modify this parameter within the scope of global or session:

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 should be noted that the increase in the number of connections will bring many chain reactions, and it is necessary to avoid the negative effects in practice.

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

mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1
Connection id: 255260
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.49-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 161 days 3 hours 42 min 38 sec
Threads: 14 Questions: 160655492 Slow queries: 71 Opens: 8124 Flush tables: 3 Open tables: 64 Queries per second avg: 11.538
--------------

Here's a Open tables At output time 64, that is to say, the number of tables opened in the current database is 64. It should be noted that this 64 is not the actual 64 tables, because MySQL is a multithreaded system, and several different concurrent connections may open the same table, which requires allocating independent memory space for different connections session to store this information to avoid conflicts. Therefore, the 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, there are several system parameters that determine the number of tables that can be opened simultaneously and the file descriptor to be used, namely table_open_cache, max_tmp_tables, and open_files_limit.

mysql> show variables like 'table_open%';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| table_open_cache | 64  |
+------------------+-------+
1 row in set (0.00 sec)

The table_open_cache parameter here is 64, which means that all MySQL threads 1 can open 64 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. One way to see the current number of open tables is to use the one mentioned above status Command, in addition, you can 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, Open_tables is the number of currently open tables, and the currently open tables can be closed by flush tables command. Opened_tables viewed globally is a historical cumulative value. If this value is too large, and if there is no frequent execution, flush tables Command, 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 | 32  |
+----------------+-------+
1 row in set (0.00 sec)

This parameter specifies the number of temporary tables that can be opened by a single client connection. 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 judge the creation position of the temporary table. Generally, BLOB and TEXT columns are selected, and the data amount of Group, by and Distinct statements exceeds 512 bytes, or when the data of a column of select exceeds 512 bytes, the temporary table is directly created on the disk. In addition, when the temporary table in memory becomes larger, it may also be automatically transferred to the disk by MySQL (determined by tmp_table_size and max_heap_table_size parameters).

Continuing with the previous discussion, increasing the size of the table_open_cache or max_tmp_tables parameter increases the number of file descriptors required by the mysqld process from an operating system perspective, which is controlled by the open_files_limit parameter. However, this parameter is limited by OS, so the value we set does not always take effect. Set to 0 if OS restricts MySQL from modifying this value. If it is a dedicated MySQL server, this value should be set as large as possible, that is, the maximum value of Too many open files error is not reported, so that it can be done forever. When the operating system cannot allocate enough file descriptors, the mysqld process records a warning message in the error log.

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+

0

Correspondingly, there are two state variables that record the current and historical file opening information:

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+

1

MySQL allocates threads for each connection to process, and you can see the number of threads currently allocated through the threads_connected parameter:

mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+

2

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

If you view more detailed information about 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 | Has 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 |
+--------+-----------+--------------------+----------+-------------+----------+----------------------------------------------------------------+------------------+
14 rows in set (0.00 sec)

Executing this command requires Process_priv permission, which can be found in the mysql. user table.

For thread that affects the operation of the system, you can ruthless 1 point and use kill connection | query threadid The order to kill it.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.