Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How MySQL looks at the number of server threads

2025-02-04 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to check the number of server threads, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

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 in order to ensure that the MySQL server runs healthily and smoothly. Threads_created represents the number of threads created, and you can view 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 the client is disconnected, the thread that the server processes this client will be cached to respond to the next client rather than destroy (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 large, it indicates that the MySQL server 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 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.

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 server (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 server, 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.

Thank you for reading this article carefully. I hope the article "how to check the number of server threads in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.

Views: 210

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

  • Hadoop 2.2.X configuration parameter description: hbase-site.xml

    © 2024 shulou.com SLNews company. All rights reserved.

    12
    Report