Shulou(Shulou.com)06/01 Report--

Let's learn how to check the number of threads in MySQL. I believe you will benefit a lot after reading it. The text is not much in essence. I hope you want to see how to view the number of threads in MySQL.

Mysql restart command:

/ etc/init.d/mysql restart

The number of threads of MySQL CVM needs to be within a reasonable range to ensure that MySQL CVM runs healthily and smoothly. Threads_created indicates the number of threads created. You can check the process status of MySQL CVM by viewing 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 CVM configuration file, when the client is disconnected, the thread of the CVM processing this client will be cached to respond to the next customer instead of being destroyed (provided that the cache number does not reach the limit).

Threads_created indicates the number of threads created. If the Threads_created value is found to be too high, it indicates that the MySQL CVM has been creating threads, which is also resource-consuming. You can appropriately increase the thread_cache_ size value in the configuration file to query the CVM.

Thread_cache_size configuration:

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

The CVM in the example is quite healthy.

Parsing several parameters related to the number of connections in MySQL

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

MySQL records a lot of configuration information through the system variable, 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, then the actual number of connections supported is this value plus one, that is, 152, because a connection is reserved for the system administrator to log in to check the information. The size of this parameter takes into account many factors, such as the number of thread libraries supported by the platform used (windows can only support 2048), the configuration of the CVM (especially the amount of memory), the amount of resources consumed by each connection (memory and load), the response time required by the system, and so on. In general, there is no problem for Linux systems to support hundreds of concurrency. You can modify this parameter within the range 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 a lot of chain reactions, which need to be avoided in practice.

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

Mysql > status-mysql Ver 14.14 Distrib 5.1.49, for pc-linux-gnu (i686) using readline 5.1Connection id: 255260Current database: mysqlCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile:''Using delimiter:; Server version: 5.1.49-log MySQL Community Server (GPL) Protocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. Characterset: utf8UNIX socket: / var/lib/mysql/mysql.sockUptime: 161days 3 hours 42 min 38 secThreads: 14 Questions: 160655492 Slow queries: 71 Opens: 8124 Flush tables: 3 Open tables: 64 Queries per second avg: 11.538-

There is an Open tables output of 64, which means that the current number of tables open in the database 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, so it is necessary to allocate independent memory space for different connection session to store this information to avoid conflicts. Therefore, the increase in the number of connections will result in an increase in the number of file descriptors required by MySQL. In addition, for MyISAM tables, a shared index file descriptor is created.

Then 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 | 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 history of the number of tables opened by 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 status command mentioned above, and 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 (8158 sec) |

Open_tables is the number of currently open tables, and you can close the currently open tables with the flush tables command. The Opened_tables viewed in the global scope is a historical cumulative value. If this value is too large, and if you do not execute the flush tables command frequently, consider increasing the size of the table_open_cache parameter.

Next, take a look at the max_tmp_tables parameters:

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 determine the creation location of the temporary table. Generally, when the data volume of BLOB and TEXT columns, Group by and Distinct statements exceeds 512 bytes, or when the data of a column in union exceeds 512 bytes, the temporary table will be created directly on disk. In addition, when the temporary table in memory becomes larger, it may also be automatically transferred to disk by MySQL (determined by tmp_table_size and max_heap_table_size parameters).

To continue the original discussion, after increasing the size of the table_open_cache or max_tmp_tables parameters, from the operating system's point of view, the number of file descriptors that the mysqld process needs to use will increase accordingly, which is controlled by the open_files_limit parameter. But this parameter is limited by OS, so the value we set does not always take effect. If the OS limit MySQL cannot change this value, set it to 0. If it is a dedicated MySQL CVM, this value should be set as high as possible, that is, there is no maximum Too many open files error reported, so that it can be done once and for all. When the operating system cannot allocate enough file descriptors, the mysqld process logs warnings 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 (1024 sec)

Correspondingly, there are two state variables that record 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 assigns threads to each connection for processing, and you can view the number of threads currently allocated 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 previously mentioned max_connections parameter can also be used as a reference for the current system load to determine whether the number of connections needs to be modified.

To 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 Binlog Dump 47208 | NULL | Binlog Dump | 11574424 | Has sent all binlog to slave Waiting for binlog to be updated | NULL | 140991 | mogile | 192.168.0.333341714 | mogilefs | Sleep | 0 | NULL | 140992 | mogile | 192.168.0.33Suzhou 41715 | mogilefs | Sleep | 3 | NULL | 140993 | mogile | 192.168.0.3341722 | mogilefs | Sleep | 2 | NULL | 140994 | mogile | 192.168.0.3333 | mogile | mogilefs | Sleep | 1 | NULL | 140995 | mogile | 192.168.0.332441724 | mogilefs | Sleep | 3 | | NULL | | 254914 | mogile | 192.168.0.333343028 | mogilefs | Sleep | 11074 | NULL | 254915 | mogile | 192.168.0.33Suzhou 43032 | mogilefs | Sleep | 11091 | NULL | 255144 | mogile | 192.168.0.3347514 | mogilefs | Sleep | 11090 | NULL | 255157 | mogile | 192.168.0.3347535 | mogilefs | Sleep | 11087 | NULL | 255162 | mogile | 192.168.0.3347549 | mogilefs | Sleep | 11074 | NULL | 255260 | | | root | localhost | mysql | Query | 0 | NULL | show processlist | | 255352 | maopaodev | 192.168.0.78 mysql 55399 | maopaodb | Sleep | 3172 | | NULL | 255353 | maopaodev | 192.168.0.78 virtual 55400 | NULL | Sleep | 8926 | NULL | +-| -+ 14 rows in set (0.00 sec)

Process_priv permission is required to execute this command. For more information on permission assignment, please see the mysql.user table.

For the thread that affects the operation of the system, you can be ruthless and kill it with the command of kill connection | query threadid.

After reading this article on how to view the number of threads in MySQL, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.