Processlist

作者：王祥爱可生 DBA 团队成员，主要负责 MySQL 故障处理和性能优化。对技术执着，为客户负责。本文来源：原创投稿*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。--背景信息业务监控发现交易的平均响应时间比之前慢了近一倍，需要排查一下数据库是不是响应慢了。生产MySQL版本为8.0.18，一主3从半同步复制。故障分析首先对比查看了交易正常时段与出现异常的时段各项监控指标（cpu、qps、tps、磁盘IO等）都未发现明显的变化。接下来查看slow log发现了较多的慢SQL，而且是普通的insert语句，执行时长超过1秒。进一步观察对比发现，每次insert慢都是出现在同一秒，insert慢语句条数基本在30条左右，而且出现的间隔都是两分钟或两分钟的倍数。根据这个规律第一感觉是不是定时任务引起的问题。经过对定时任务的排查最终定位到监控脚本，监控脚本为两分钟执行一次。接下来需要排查一下，具体是哪部分导致insert慢。为了快速复现问题，直接在一个从库上使用mysqlslap进行压测。从业务那得知问题insert语句每秒会有60-80次的写入量，压测语句如下：mysqlslap -h127.0.0.1 -uroot -p --concurrency=80 --iterations=10 --create-schema=userdb --query=/root/test.sql --engine=innodb --number-of-queries=50000#test.sqlinsert into userdb.ps (clo1, clo2, clo3, clo4, clo4, clo5, clo6) values (substring(MD5(RAND()),1,20), 'fffffdddddddddd', '0', '', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaddddddddd', '2022-06-17 16:00:38.145', 34);在压测期间执行监控脚本，一边查看slow log，能稳定复现生产的现象。通过排除法，最终定位到几个使用information_schema.processlist表的语句导致了insert慢。那information_schema.processlist为什么会导致insert慢呢？带着这个问题去查看一下官方对information_schema.processlist的描述。The default SHOW PROCESSLIST implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems. The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.根据官方的说明：在使用默认的show processlist会持有全局互斥锁，在业务繁忙的系统上会导致性能问题。同时也给出了解决办法，使用Performance Schema中的processlist代替，此方式不会产生全局互斥锁。performance_schema_show_processlist是MySQL 8.0.22版本引入的新功能。接下来我们来看看官方对Performance Schema中的processlist描述。The SHOW PROCESSLIST statement provides process information by collecting thread data from all active threads. The performance_schema_show_processlist variable determines which SHOW PROCESSLIST implementation to use:The default implementation iterates across active threads from within the thread manager while holding a global mutex. This has negative performance consequences, particularly on busy systems.The alternative SHOW PROCESSLIST implementation is based on the Performance Schema processlist table. This implementation queries active thread data from the Performance Schema rather than the thread manager and does not require a mutex.如果开启参数performance_schema_show_processlist，show processlist使用Performance Schema中的processlist避免了全局互斥锁的问题，如果不开启该参数则show processlist使用information_schema.processlist会产生全局锁。在配置文件[mysqld]下加上performance_schema_show_processlist=on配置。配置完成后，查看performance_schema下的processlist。root@localhost:mysql.sock [(none)]> show variables like 'performance_schema_show_processlist';+-------------------------------------+-------+| Variable_name | Value |+-------------------------------------+-------+| performance_schema_show_processlist | ON |+-------------------------------------+-------+#信息与information_schema.processlist下保持一致root@localhost:mysql.sock [(none)]> select * from performance_schema.processlist\G*************************** 1. row *************************** ID: 5 USER: event_scheduler HOST: localhost DB: NULLCOMMAND: Daemon TIME: 354 STATE: Waiting on empty queue INFO: NULL*************************** 2. row *************************** ID: 8 USER: root HOST: localhost DB: NULLCOMMAND: Query TIME: 0 STATE: executing INFO: select * from performance_schema.processlist2 rows in set (0.00 sec)总结1.使用MySQL 8.0.22之前的版本，在业务繁忙的敏感系统上执行show processlist需要谨慎。2.使用MySQL 8.0.22之后版本， 可以开启performance_schema_show_processlist避免该问题。但依旧不建议频繁查询会话信息。另外查询processlist表导致MySQL 实例crash问题，请参考文章：

Mysql利用show processlist 详解排查详解1、按客户端 IP 分组，看哪个客户端的链接数最多select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;图片.png.2、查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程select * from information_schema.processlist where Command != 'Sleep' order by Time desc;3.找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀 （此处 5分钟 可根据自己的需要调整SQL标红处）可复制查询结果到控制台，直接执行，杀死堵塞进程select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;4、查询线程及相关信息show full processlist5.字段解释:show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。root用户，可以看到全部线程运行情况普通的activiti用户只能看到自己的单独给activiti用户授PROCESS权限，（授权后需要退出重新登录）show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果：select * from information_schema.processlist了解这些基本信息后，下面我们看看查询出来的结果都是什么意思。Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。User: 就是指启动这个线程的用户。Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释Time: 表示该线程处于当前状态的时间。State: 线程的状态，和 Command 对应，下面单独解释。Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。下面我们单独看一下 Command 的值：Binlog Dump: 主节点正在将二进制日志 ，同步到从节点Change User: 正在执行一个 change-user 的操作Close Stmt: 正在关闭一个Prepared Statement 对象Connect: 一个从节点连上了主节点Connect Out: 一个从节点正在连主节点Create DB: 正在执行一个create-database 的操作Daemon: 服务器内部线程，而不是来自客户端的链接Debug: 线程正在生成调试信息Delayed Insert: 该线程是一个延迟插入的处理程序Drop DB: 正在执行一个 drop-database 的操作Execute: 正在执行一个 Prepared StatementFetch: 正在从Prepared Statement 中获取执行结果Field List: 正在获取表的列信息Init DB: 该线程正在选取一个默认的数据库Kill : 正在执行 kill 语句，杀死指定线程Long Data: 正在从Prepared Statement 中检索 long dataPing: 正在处理 server-ping 的请求Prepare: 该线程正在准备一个 Prepared StatementProcessList: 该线程正在生成服务器线程相关信息Query: 该线程正在执行一个语句Quit: 该线程正在退出Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息Register Slave： 正在注册从节点Reset Stmt: 正在重置 prepared statementSet Option: 正在设置或重置客户端的 statement-execution 选项Shutdown: 正在关闭服务器Sleep: 正在等待客户端向它发送执行语句Statistics: 该线程正在生成 server-status 信息Table Dump: 正在发送表的内容到从服务器Time: Unused序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...沈念sama阅读 223,689评论 6赞 521序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些... 文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...茶点故事阅读 69,512评论 6赞 399文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...茶点故事阅读 38,973评论 3赞 343正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...茶点故事阅读 41,117评论 1赞 354序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...茶点故事阅读 42,440评论 3赞 336文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...茶点故事阅读 46,147评论 2赞 363推荐阅读更多精彩内容最近排查一些MySQL的问题，会经常用到 show processlist，所以在这里把这个命令总结一下，做个备忘...矛盾迩阅读 15,504评论 3赞 6观其大纲 第1章 MySQL体系结构和存储引擎第2章 InnoDB存储引擎第3章 文件第4章 表第5章 索引与算法...周少言阅读 6,973评论 0赞 33数据库基础知识 为什么要使用数据库 数据保存在内存优点： 存取速度快缺点： 数据不能永久保存 数据保存在文件优点：...淺時咣阅读 346评论 0赞 1数据库基础知识 为什么要使用数据库 数据保存在内存 优点： 存取速度快 缺点： 数据不能永久保存 数据保存在文件 ...数据库基础知识 1. 为什么要使用数据库 数据保存在内存 优点：存取速度快 缺点：数据不能永久保存 数据保存在文件...

Set to ON, and if it is, omits the step of calculating the transactions that should be skipped or not skipped, so that the old relay logs are not needed and recovery can proceed without them. (Bug #28830834, Bug #92882) When generating C source from SQL scripts, Some utf8-encoded characters were split across lines. Thanks to Przemysław Skibiński for the patch. (Bug #30152555, Bug #96449) SHOW PROCESSLIST output could include statements that had completed and were no longer in process. (Bug #29999818) For Solaris, mysqld.cc contained a prototype for memcntl() that is no longer needed. The prototype has been removed. (Bug #29953495, Bug #95971) For Solaris, -DWITH_SSL=system did not work when compiling with GCC. (Bug #29953460, Bug #95970) MySQL builds configured with -DWITHOUT_SERVER=1 failed. (Bug #29948728, Bug #95740) For the keyring_aws plugin, some valid region values for the keyring_aws_region system variable were rejected. (Bug #29933758) For debug builds, an assertion could be raised during UNION queries when computing the combined data type of a GEOMETRY column and SELECT * FROM (SELECT NULL). (Bug #29916900, Bug #95827) For authentication using an LDAP authentication plugin, if the user DN portion was empty and group mapping was configured, authentication assigned an incorrect user DN and skipped the user search. (Bug #29897624) mysqlpump produced an error when run against a server older than MySQL 5.7. (Bug #29889253) A possible integer overflow due to unsigned integer type casting could lead to later buffer overflow due to arbitrary size memory allocation. (Bug #29878914) Attempted use of a freed object during MeCab plugin initialization caused a segmentation fault. (Bug #29832534) For MySQL installed using RPM packages, an initialization script that tested server connectivity misbehaved if the client account authenticated using an LDAP authentication plugin. (Bug #29786782) Improper locking during storage engine initialization could cause a server exit.

(Bug #29782379) On a GTID-enabled server, concurrent execution of DROP USER and a prepared statement that accessed a view could deadlock. (Bug #29772622) VS2019 produced compilation errors with debug compilation selected due to use of the /ZI flag. Now /Z7 is used instead. (Bug #29691691, Bug #95125) The client library could dereference a null pointer while fetching result set metadata from the server. (Bug #29597896, Bug #30689251) In READ UNCOMMITTED isolation level, a segmentation fault occurred under heavy load from memcached clients. An externally stored BLOB column that was being updated by one transaction was read by another transaction as having a NULL value and a non-zero data length. (Bug #29396364, Bug #93961) Arguments for the TIMESTAMPADD() function could be reversed for prepared statements. (Bug #29268394) For MySQL Community Edition, the cipher order specified by the client was used in preference to the order on the server side, unless the server was configured with an explicit ssl_cipher order. (Bug #26882825) With the thread_pool plugin enabled, the sys.processlist and sys.session views displayed a thread name rather than the actual user name. (Bug #25906021, Bug #85976) The delete_latency column in the sys.schema_index_statistics view incorrectly referred to the SUM_TIMER_INSERT column of the Performance Schema table_io_waits_summary_by_index_usage table rather than the SUM_TIMER_DELETE column. (Bug #25521928) In output from the sys.diagnostics() procedure, the latency column for the user_summary_by_file_io_type view was incorrectly displayed in raw picoseconds rather than as a formatted value. (Bug #25287996) MySQL Enterprise Encryption functions could apply Diffie-Hellman (DH) methods to non-DH keys, resulting in unpredictable results or server exit. (Bug #22839007) Password masking was incomplete for SHOW PROCESSLIST and some INFORMATION_SCHEMA and Performance Schema tables. (Bug #20712046) The -DWITH_EXAMPLE_STORAGE_ENGINE=1 CMake option was ignored but should not have been. If -DWITH_EXAMPLE_STORAGE_ENGINE=0 is given, the EXAMPLE storage engine is built as a plugin. (Bug #70859,

Dropped. Levels: Global, database, routine. CREATE Enable database and table creation. Levels: Global, database, table. CREATE ROLE Enable role creation. Level: Global. CREATE ROUTINE Enable stored routine creation. Levels: Global, database. CREATE TABLESPACE Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global. CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE. Levels: Global, database. CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. Level: Global. CREATE VIEW Enable views to be created or altered. Levels: Global, database, table. DELETE Enable use of DELETE. Level: Global, database, table. DROP Enable databases, tables, and views to be dropped. Levels: Global, database, table. DROP ROLE Enable roles to be dropped. Level: Global. EVENT Enable use of events for the Event Scheduler. Levels: Global, database. EXECUTE Enable the user to execute stored routines. Levels: Global, database, routine. FILE Enable the user to cause the server to read or write files. Level: Global. GRANT OPTION Enable privileges to be granted to or removed from other accounts. Levels: Global, database, table, routine, proxy. INDEX Enable indexes to be created or dropped. Levels: Global, database, table. INSERT Enable use of INSERT. Levels: Global, database, table, column. LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege. Levels: Global, database. PROCESS Enable the user to see all processes with SHOW PROCESSLIST. Level: Global. PROXY Enable user proxying. Level: From user to user. REFERENCES Enable foreign key creation. Levels: Global, database, table, column. RELOAD Enable use of FLUSH operations. Level: Global. REPLICATION CLIENT Enable the user to ask where source or replica servers are. Level: Global. REPLICATION SLAVE Enable replicas to read binary log events from the source. Level: Global. SELECT Enable use of SELECT. Levels: Global, database, table, column. SHOW DATABASES

Color provided. ProcessClose Terminates a named process. ProcessExists Checks to see if a specified process exists. ProcessGetStats Returns an array about Memory or IO infos of a running process. ProcessList Returns an array listing the currently running processes (names and PIDs). ProcessSetPriority Changes the priority of a process. ProcessWait Pauses script execution until a given process exists. ProcessWaitClose Pauses script execution until a given process does not exist. ProgressOff Turns Progress window off. ProgressOn Creates a customizable progress bar window. ProgressSet Sets the position and/or text of a previously created Progress bar window. Ptr Converts an expression into a pointer variant. Random Generates a pseudo-random float-type number. RegDelete Deletes a key or value from the registry. RegEnumKey Reads the name of a subkey according to its instance. RegEnumVal Reads the name of a value according to its instance. RegRead Reads a value from the registry. RegWrite Creates a key or value in the registry. Round Returns a number rounded to a specified number of decimal places. Run Runs an external program. RunAs Runs an external program under the context of a different user. RunAsWait Runs an external program under the context of a different user and pauses script execution until the program finishes. RunWait Runs an external program and pauses script execution until the program finishes. Send Sends simulated keystrokes to the active window. SendKeepActive Attempts to keep a specified window active during Send(). SetError Manually set the value of the @error macro (and optionally @extended, and "Return Value"). SetExtended Manually set the value of the @extended macro. ShellExecute Runs an external program using the ShellExecute API. ShellExecuteWait Runs an external program using the ShellExecute API and pauses script execution until it finishes. Shutdown Shuts down the system. Sin Calculates the sine of a number. Sleep Pause script execution. SoundPlay Play a sound file. SoundSetWaveVolume Sets the system wave volume by percent. SplashImageOn Creates a customizable image popup window. SplashOff Turns SplashText or SplashImage off. SplashTextOn Creates a customizable text popup window. Sqrt Calculates the square-root of a number. SRandom Set Seed for random number generation. StatusbarGetText Retrieves the text from a standard status bar control. StderrRead Reads from the STDERR stream of a previously run child process. StdinWrite Writes a number of characters to the STDIN stream of a previously run child process. StdioClose Closes all resources associated with a process previously run with STDIO redirection. StdoutRead Reads from