How to check the number of threads in MySQL server-Mysql Tutorial-php.cn

This article mainly introduces the method of checking the number of MySQL server threads, and analyzes the related commands, configurations, parameters and related usage skills of checking the number of mysql threads in the form of examples. Friends who need it can refer to it. I hope it can help everyone. .

mysql restart command:

/etc/init.d/mysql restart

Copy after login

The number of threads of the MySQL server needs to be within a reasonable range, so as to ensure the healthy and smooth operation of the MySQL server. 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 &#39;Thread%&#39;;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+

Copy after login

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 destroying it (provided that the number of caches does not reach the upper 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 relatively resource-consuming. You can appropriately increase the thread_cache_size value in the configuration file and query the server

thread_cache_size configuration:

mysql> show variables like &#39;thread_cache_size&#39;;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+

Copy after login

The server in the example is quite healthy.

Analyze several MySQL parameters related to the number of connections

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

MySQL records a lot of configuration information through system variables, such as the maximum number of connections max_connections:

mysql> show variables like &#39;%connect%&#39;;
+--------------------------+-----------------+
| 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)

Copy after login

This parameter refers to the number of clients connected at the same time. The default value in version 5.1 is 151, so the actual number of supported connections is this value plus one, which is 152, because one connection needs to be reserved for the system administrator to log in to 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), server configuration (especially memory size), and the resources occupied by each connection (memory and load) How much, the response time required by the system, etc. Generally, Linux systems can support hundreds of concurrencies without any problems. 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 &#39;%connect%&#39;;
+--------------------------+-----------------+
| 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)

Copy after login

However, it should be noted that the increase in the number of connections will bring about many chain reactions, which need to be avoided in practice. The negative impact this has.

First let’s take a look at the status output:

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: &#39;&#39;
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
--------------

Copy after login

Here is a Open tablesThe output is 64, which means the current database The number of open tables is 64. It should be noted that this 64 is not the actual 64 tables. Because MySQL is a multi-threaded system, several different concurrent connections may open the same table, which requires different connections. The session allocates independent memory space 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 will also be 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 &#39;table_open%&#39;;
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| table_open_cache | 64  |
+------------------+-------+
1 row in set (0.00 sec)

Copy after login

The table_open_cache parameter here is 64, which means that all MySQL threads can open a total of 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 check the current number of open tables is to use the status command mentioned above. In addition, you can directly query the value of this system variable:

mysql> show status like &#39;open%&#39;;
+--------------------------+-------+
| 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 &#39;open%&#39;;
+--------------------------+-------+
| 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)

Copy after login

Open_tables here is the number of currently open tables. The currently open tables 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 &#39;max_tmp%&#39;;
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| max_tmp_tables | 32  |
+----------------+-------+
1 row in set (0.00 sec)

Copy after login

This parameter specifies the number of temporary tables that can be opened by a single client connection. Check the currently opened temporary table information:

mysql> show global status like &#39;%tmp%table%&#39;;
+-------------------------+-------+
| Variable_name      | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 10478 |
| Created_tmp_tables   | 25860 |
+-------------------------+-------+
2 rows in set (0.00 sec)

Copy after login

You can also compare these two values to determine the creation location of the temporary table. Generally, select the BLOB and TEXT columns, Group by and When the data volume of the Distinct statement exceeds 512 bytes, or when the data of a column selected during union 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 deleted by MySQL. Transfer to disk (determined by tmp_table_size and max_heap_table_size parameters).

继续原来的讨论，增加table_open_cache或 max_tmp_tables 参数的大小后，从操作系统的角度看，mysqld进程需要使用的文件描述符的个数就要相应的增加，这个是由 open_files_limit参数控制的。但是这个参数是OS限制的，所以我们设定的值并不一定总是生效。如果OS限制MySQL不能修改这个值，那么置为0。如果是专用的MySQL服务器上，这个值一般要设置的尽量大，就是没有报Too many open files错误的最大值，这样就能一劳永逸了。当操作系统无法分配足够的文件描述符的时候，mysqld进程会在错误日志里记录警告信息。

mysql> show variables like &#39;open_files%&#39;;+------------------+-------+| Variable_name  | Value |+------------------+-------+| open_files_limit | 1024 |+------------------+-------+1 row in set (0.00 sec)
mysql> show variables like &#39;open_files%&#39;;
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0.00 sec)

Copy after login

对应的，有两个状态变量记录了当前和历史的文件打开信息：

mysql> show global status like &#39;%open%file%&#39;;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files  | 3   |
| Opened_files | 91799 |
+---------------+-------+
2 rows in set (0.01 sec)

Copy after login

MySQL为每个连接分配线程来处理，可以通过threads_connected参数查看当前分配的线程数量：

mysql> show status like &#39;%thread%&#39;;
+------------------------+--------+
| 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)

Copy after login

比较这个threads_connected参数和前面提到的max_connections参数，也可以作为目前的系统负载的参照，决定是否需要修改连接数。

如果查看每个thread的更详细的信息，可以使用processlist命令：

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)

Copy after login

执行这个命令需要有Process_priv权限，具体的权限分配信息可以查看mysql.user表。

对于影响系统运行的thread，可以狠一点，用kill connection|query threadid的命令杀死它。