Detailed explanation of how to view the number of MySQL server threads

This article uses an example to describe how to view the number of MySQL server threads. Share with you for your reference, the details are as follows:

mysql restart command:

/etc/init.d/mysql restart

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

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 the client disconnects, the server's thread processing this client will be cached to respond to the next client instead of being destroyed (provided that the cache number has not reached the upper limit).

Threads_created indicates the number of threads created. If the Threads_created value is too large, it means that the MySQL server has been creating threads, which is also resource-consuming. You can increase the thread_cache_size value in the configuration file appropriately and 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 still quite healthy.

Analyze several parameters related to MySQL connection number

MySQL variables and status are powerful tools for management and maintenance, just like Oracle's spfile and v$ tables.

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 that can connect simultaneously. The default value in version 5.1 is 151. The actual number of connections supported is this value plus one, which 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 based on many factors, such as the number of thread libraries supported by the platform used (Windows can only support up to 2048), the server configuration (especially the memory size), the amount of resources (memory and load) occupied by each connection, the response time required by the system, etc. Generally, Linux systems have no problem supporting hundreds of concurrent connections. This parameter can be modified in the global or session scope:

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 about many chain reactions, and the resulting negative effects need to be avoided in practice.

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

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 there is an Open tables output of 64, which means that 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 multi-threaded system, and several different concurrent connections may open the same table. This requires allocating independent memory space for different connection sessions to store this information to avoid conflicts. Therefore, an increase in the number of connections will lead to an increase in the number of file descriptors required by MySQL. In addition, for MyISAM tables, a shared index file descriptor is created.

At the MySQL database level, there are several system parameters that determine the number of tables that can be opened simultaneously and the file descriptors 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 can open 64 tables at the same time. We can collect the system's historical records of the number of open tables and compare them with this parameter to decide whether to increase the size of this parameter. One way to view the number of currently open tables is to use the status command mentioned above, and the other is to 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. The currently open table can be closed through the flush tables command. The Opened_tables viewed globally is a historical cumulative value. If this value is too large and if the flush tables command is not executed frequently, you can 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 information of the currently opened temporary table:

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 where to create a temporary table. Generally, when BLOB and TEXT columns are selected, the amount of data in Group by and Distinct statements exceeds 512 bytes, or when the data in a certain column of a union select exceeds 512 bytes, a temporary table is created directly 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 the tmp_table_size and max_heap_table_size parameters).

Continuing with the original discussion, when you increase the size of the table_open_cache or max_tmp_tables parameters, from the operating system's perspective, the number of file descriptors that the mysqld process needs to use will increase accordingly, which is controlled by the open_files_limit parameter. However, this parameter is limited by the OS, so the value we set may not always take effect. If the OS restricts MySQL from modifying this value, set it to 0. If it is a dedicated MySQL server, this value should generally be set as large as possible, that is, the maximum value that does not report the Too many open files error, so that you can solve the problem once and for all. When the operating system cannot allocate enough file descriptors, the mysqld process records a warning message in the error log.

mysql> show variables like 'open_files%';+------------------+-------+| Variable_name | Value |+------------------+-------+| open_files_limit | 1024 |+------------------+-------+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 status variables that record the current and historical file opening 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 to handle each connection. 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 with the max_connections parameter mentioned above can also be used as a reference for the current system load to decide whether the number of connections needs to be modified.

If you want to view more detailed information about each thread, you can use 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)

The Process_priv privilege is required to execute this command. For specific privilege allocation information, see the mysql.user table.

For threads that affect system operation, you can be ruthless and kill them using kill connection | query threadid command.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.