MySQL中kill和show命令hang住一列的举例分析
发布时间：2021-12-28 11:57:09 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关MySQL中kill和show命令hang住一列的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 一、问题描述 问题描述是这位朋友发给我的如下： 问题描述 收到从库xx.xx.xx.xx报警mysql宕机，实际上mys
|
这篇文章给大家分享的是有关MySQL中kill和show命令hang住一列的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 一、问题描述 问题描述是这位朋友发给我的如下： 问题描述 收到从库xx.xx.xx.xx报警mysql宕机，实际上mysql没有宕机，而是因为下面这条SQL向表tmp_mds_cust_dealer_advisor_rela01插入的数据太多(这个表的数据文件高达184G，binlog文件binlog.001675高达138G).在binlog最终刷到磁盘的期间导致 SHOW GLOBAL STATUS 等命令阻塞。观察了当时的cpu，内存，磁盘情况，压力都不大。为什么刷binlog到磁盘会阻塞 SHOW GLOBAL STATUS 等命令 ？ show processlist如下： dbadmin@(none) 08:49:50>show processlist; +---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+ | 862489 | dbadmin | 127.0.0.1:41012 | NULL | Killed | 413 | executing | SHOW GLOBAL STATUS LIKE 'uptime' | 0 | 0 | | 862490 | dbadmin | 127.0.0.1:41014 | information_schema | Killed | 902 | cleaning up | NULL | 0 | 0 | | 862491 | dbadmin | 127.0.0.1:41015 | information_schema | Killed | 902 | cleaning up | NULL | 0 | 0 | | 862493 | dbadmin | 127.0.0.1:41019 | information_schema | Killed | 165 | cleaning up | NULL | 0 | 0 | | 862495 | dbadmin | 127.0.0.1:41022 | information_schema | Killed | 1149 | cleaning up | NULL | 0 | 0 | | 862508 | monitor | 127.0.0.1:41041 | mysql | Killed | 411 | executing | show global status where Variable_name in ( 'Threads_connect | 0 | 0 | | 1140925 | monitor | 127.0.0.1:64924 | mysql | Query | 414 | executing | show global status where Variable_name in ('Com_select','Com_insert','Com_update','Com_del | 0 | 0 | | 1153172 | system user | | NULL | Connect | 82688 | Waiting for master to send event | NULL | 0 | 0 | | 1153173 | system user | | NULL | Connect | 55564 | System lock | NULL | 0 | 0 | | 1168129 | dbadmin | 127.0.0.1:9377 | information_schema | Killed | 123 | cleaning up | NULL | 0 | 0 | | 1181458 | dbadmin | 127.0.0.1:11163 | NULL | Killed | 413 | cleaning up | NULL | 0 | 0 | | 1181459 | dbadmin | 127.0.0.1:11164 | NULL | Killed | 406 | cleaning up | NULL | 0 | 0 | | 1181460 | dbadmin | 127.0.0.1:11166 | NULL | Killed | 396 | cleaning up | NULL | 0 | 0 | | 1181461 | dbadmin | 127.0.0.1:11177 | NULL | Killed | 393 | cleaning up | NULL | 0 | 0 | | 1181462 | dbmonitor | 127.0.0.1:11226 | NULL | Query | 387 | executing | SHOW /*!50001 GLOBAL */ STATUS | 0 | 0 | | 1181463 | dbadmin | 127.0.0.1:11242 | NULL | Killed | 386 | cleaning up | NULL | 0 | 0 | | 1181464 | dbadmin | 127.0.0.1:11328 | NULL | Killed | 376 | cleaning up | NULL | 0 | 0 | | 1181465 | dbadmin | 127.0.0.1:11408 | NULL | Killed | 366 | cleaning up | NULL | 0 | 0 | | 1181466 | dbadmin | 127.0.0.1:11436 | NULL | Killed | 363 | cleaning up | NULL | 0 | 0 | | 1181467 | dbadmin | 127.0.0.1:11465 | NULL | Killed | 356 | cleaning up | NULL | 0 | 0 | | 1181468 | dbadmin | 127.0.0.1:11467 | NULL | Killed | 346 | cleaning up | NULL | 0 | 0 | | 1181469 | dbadmin | 127.0.0.1:11469 | NULL | Killed | 336 | cleaning up | NULL | 0 | 0 | | 1181470 | dbmonitor | 127.0.0.1:11471 | NULL | Query | 327 | executing | SHOW /*!50001 GLOBAL */ STATUS | 0 | 0 | | 1181471 | dbadmin | 127.0.0.1:11477 | NULL | Killed | 326 | cleaning up | NULL | 0 | 0 | | 1181472 | dbadmin | 127.0.0.1:11478 | NULL | Killed | 321 | cleaning up | NULL | 0 | 0 | | 1181473 | dbadmin | 127.0.0.1:11481 | NULL | Killed | 316 | cleaning up | NULL | 0 | 0 | | 1181474 | dbadmin | 127.0.0.1:11483 | NULL | Killed | 306 | cleaning up | NULL | 0 | 0 | | 1181475 | dbadmin | 127.0.0.1:11484 | NULL | Killed | 303 | cleaning up | NULL | 0 | 0 | | 1181476 | dbadmin | 127.0.0.1:11485 | NULL | Killed | 296 | cleaning up | NULL | 0 | 0 | | 1181477 | dbadmin | 127.0.0.1:11557 | NULL | Killed | 286 | cleaning up | NULL | 0 | 0 | | 1181478 | dbadmin | 127.0.0.1:11640 | NULL | Killed | 276 | cleaning up | NULL | 0 | 0 | | 1181479 | dbmonitor | 127.0.0.1:11711 | NULL | Query | 267 | executing | SHOW /*!50001 GLOBAL */ STATUS | 0 | 0 | | 1181480 | dbadmin | 127.0.0.1:11730 | NULL | Killed | 266 | cleaning up | NULL | 0 | 0 | | 1181481 | dbadmin | 127.0.0.1:11783 | NULL | Killed | 256 | cleaning up | NULL | 0 | 0 | | 1181482 | dbadmin | 127.0.0.1:11784 | NULL | Killed | 249 | cleaning up | NULL | 0 | 0 | | 1181483 | dbadmin | 127.0.0.1:11785 | NULL | Killed | 246 | cleaning up | NULL | 0 | 0 | | 1181484 | dbadmin | 127.0.0.1:11786 | NULL | Killed | 243 | cleaning up | NULL | 0 | 0 | | 1181485 | dbadmin | 127.0.0.1:11788 | NULL | Killed | 236 | cleaning up | NULL | 0 | 0 | | 1181486 | dbadmin | 127.0.0.1:11792 | NULL | Killed | 226 | cleaning up | NULL | 0 | 0 | | 1181487 | dbadmin | localhost | NULL | Killed | 218 | cleaning up | NULL | 0 | 0 | .... +---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+75 rows in set (0.01 sec) pstack 这位朋友给出了pstack 实际上是最有用的。 Thread 55 (Thread 0x7f55318b2700 (LWP 2821)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000006cde10 in inline_mysql_mutex_lock (src_file=0xbc9938 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc", src_line=1760, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 THD::release_resources (this=this@entry=0x7f557629d000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc:1760#5 0x000000000078f1e7 in threadpool_remove_connection (thd=0x7f557629d000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:257#6 0x00000000007902b6 in connection_abort (connection=0x7f556fea9610) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1363#7 handle_event (connection=0x7f556fea9610) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1564#8 worker_main (param=param@entry=0x134f400 <all_groups+4096>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#9 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555d6f7600) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#10 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#11 0x00007f769b7713dd in clone () from /lib64/libc.so.6Thread 54 (Thread 0x7f553898b700 (LWP 2962)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000006cde10 in inline_mysql_mutex_lock (src_file=0xbc9938 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc", src_line=1760, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 THD::release_resources (this=this@entry=0x7f557469c000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc:1760#5 0x000000000078f1e7 in threadpool_remove_connection (thd=0x7f557469c000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:257#6 0x00000000007902b6 in connection_abort (connection=0x7f556fea9760) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1363#7 handle_event (connection=0x7f556fea9760) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1564#8 worker_main (param=param@entry=0x134f600 <all_groups+4608>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#9 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555d6f76a0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#10 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#11 0x00007f769b7713dd in clone () from /lib64/libc.so.6.... Thread 50 (Thread 0x7f55337ff700 (LWP 3002)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x0000000000738116 in inline_mysql_mutex_lock (src_file=0xbd31e8 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc", src_line=7315, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 fill_status (thd=thd@entry=0x7f5576042000, tables=tables@entry=0x7f55d2c1d358, cond=0x0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7315#5 0x000000000073e692 in do_fill_table (join_table=0x7f55d2c1e9e0, table_list=0x7f55d2c1d358, thd=0x7f5576042000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7986#6 get_schema_tables_result (join=join@entry=0x7f55d2c1dcb8, executed_place=executed_place@entry=PROCESSED_BY_JOIN_EXEC) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:8087#7 0x000000000071f425 in JOIN::prepare_result (this=this@entry=0x7f55d2c1dcb8, columns_list=columns_list@entry=0x7f55337fc960) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:823#8 0x00000000006d9e7c in JOIN::exec (this=0x7f55d2c1dcb8) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_executor.cc:116#9 0x0000000000723c05 in mysql_execute_select (free_join=true, select_lex=0x7f5576044880, thd=0x7f5576042000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:1100#10 mysql_select (thd=thd@entry=0x7f5576042000, tables=0x7f55d2c1d358, wild_num=0, fields=..., conds=0x0, order=order@entry=0x7f5576044a48, group=group@entry=0x7f5576044980, having=0x0, select_options=2684652288, result=result@entry=0x7f55d2c1dc90, unit=unit@entry=0x7f5576044238, select_lex=select_lex@entry=0x7f5576044880) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:1221#11 0x0000000000724465 in handle_select (thd=thd@entry=0x7f5576042000, result=result@entry=0x7f55d2c1dc90, setup_tables_done_option=setup_tables_done_option@entry=0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:110#12 0x0000000000581a38 in execute_sqlcom_select (thd=thd@entry=0x7f5576042000, all_tables=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:5665#13 0x00000000006fd731 in mysql_execute_command (thd=thd@entry=0x7f5576042000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:2952#14 0x0000000000702c98 in mysql_parse (thd=thd@entry=0x7f5576042000, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f55337fe020) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:6950#15 0x00000000007044ab in dispatch_command (command=<optimized out>, thd=thd@entry=0x7f5576042000, packet=<optimized out>, packet_length=30) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:1442#16 0x0000000000706219 in do_command (thd=thd@entry=0x7f5576042000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:1054#17 0x000000000078f368 in threadpool_process_request (thd=0x7f5576042000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:311#18 0x000000000079033d in handle_event (connection=0x7f556fea9ca0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1553#19 worker_main (param=param@entry=0x134fc00 <all_groups+6144>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#20 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555e413e80) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#21 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#22 0x00007f769b7713dd in clone () from /lib64/libc.so.6..... Thread 46 (Thread 0x7f5531a79700 (LWP 3181)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x0000000000738116 in inline_mysql_mutex_lock (src_file=0xbd31e8 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc", src_line=7315, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 fill_status (thd=thd@entry=0x7f55761e9000, tables=tables@entry=0x7f55d441d358, cond=0x0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7315#5 0x000000000073e692 in do_fill_table (join_table=0x7f55d441e9e0, table_list=0x7f55d441d358, thd=0x7f55761e9000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7986#6 get_schema_tables_result (join=join@entry=0x7f55d441dcb8, executed_place=executed_place@entry=PROCESSED_BY_JOIN_EXEC) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:8087#7 0x000000000071f425 in JOIN::prepare_result (this=this@entry=0x7f55d441dcb8, columns_list=columns_list@entry=0x7f5531a76960) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:823#8 0x00000000006d9e7c in JOIN::exec (this=0x7f55d441dcb8) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_executor.cc:116#9 0x0000000000723c05 in mysql_execute_select (free_join=true, select_lex=0x7f55761eb880, thd=0x7f55761e9000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:1100#10 mysql_select (thd=thd@entry=0x7f55761e9000, tables=0x7f55d441d358, wild_num=0, fields=..., conds=0x0, order=order@entry=0x7f55761eba48, group=group@entry=0x7f55761eb980, having=0x0, select_options=2684652288, result=result@entry=0x7f55d441dc90, unit=unit@entry=0x7f55761eb238, select_lex=select_lex@entry=0x7f55761eb880) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:1221#11 0x0000000000724465 in handle_select (thd=thd@entry=0x7f55761e9000, result=result@entry=0x7f55d441dc90, setup_tables_done_option=setup_tables_done_option@entry=0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_select.cc:110#12 0x0000000000581a38 in execute_sqlcom_select (thd=thd@entry=0x7f55761e9000, all_tables=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:5665#13 0x00000000006fd731 in mysql_execute_command (thd=thd@entry=0x7f55761e9000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:2952#14 0x0000000000702c98 in mysql_parse (thd=thd@entry=0x7f55761e9000, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f5531a78020) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:6950#15 0x00000000007044ab in dispatch_command (command=<optimized out>, thd=thd@entry=0x7f55761e9000, packet=<optimized out>, packet_length=30) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:1442#16 0x0000000000706219 in do_command (thd=thd@entry=0x7f55761e9000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_parse.cc:1054#17 0x000000000078f368 in threadpool_process_request (thd=0x7f55761e9000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:311#18 0x000000000079033d in handle_event (connection=0x7f556fec3070) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1553#19 worker_main (param=param@entry=0x1350400 <all_groups+8192>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#20 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555d6f7b00) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#21 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#22 0x00007f769b7713dd in clone () from /lib64/libc.so.6..... Thread 43 (Thread 0x7f5531b3c700 (LWP 3340)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000006cde10 in inline_mysql_mutex_lock (src_file=0xbc9938 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc", src_line=1760, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 THD::release_resources (this=this@entry=0x7f5573f8a000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc:1760#5 0x000000000078f1e7 in threadpool_remove_connection (thd=0x7f5573f8a000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:257#6 0x00000000007902b6 in connection_abort (connection=0x7f556fe17ac0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1363#7 handle_event (connection=0x7f556fe17ac0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1564#8 worker_main (param=param@entry=0x1350a00 <all_groups+9728>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#9 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555e413fc0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#10 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#11 0x00007f769b7713dd in clone () from /lib64/libc.so.6Thread 42 (Thread 0x7f5531bbe700 (LWP 3353)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000006cde10 in inline_mysql_mutex_lock (src_file=0xbc9938 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc", src_line=1760, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 THD::release_resources (this=this@entry=0x7f55747ed000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc:1760#5 0x000000000078f1e7 in threadpool_remove_connection (thd=0x7f55747ed000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:257#6 0x00000000007902b6 in connection_abort (connection=0x7f556fec36a0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1363#7 handle_event (connection=0x7f556fec36a0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1564#8 worker_main (param=param@entry=0x1350c00 <all_groups+10240>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_unix.cc:1606#9 0x0000000000b050e3 in pfs_spawn_thread (arg=0x7f555e414060) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/storage/perfschema/pfs.cc:1860#10 0x00007f769d0fddf3 in start_thread () from /lib64/libpthread.so.0#11 0x00007f769b7713dd in clone () from /lib64/libc.so.6 二、分析大多数线程的等待MUTEX 实际上分析pstack可以发现，基本所有的等待线程可以分为： KILL SESSION等待 Thread 111 (Thread 0x7f5531afb700 (LWP 15742)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000006cde10 in inline_mysql_mutex_lock (src_file=0xbc9938 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc", src_line=1760, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 THD::release_resources (this=this@entry=0x7f55762ba000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_class.cc:1760#5 0x000000000078f1e7 in threadpool_remove_connection (thd=0x7f55762ba000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/threadpool_common.cc:257..... 等待的MUTEX为： void THD::release_resources() { mysql_mutex_assert_not_owner(&LOCK_thread_count); DBUG_ASSERT(m_release_resources_done == false); mysql_mutex_lock(&LOCK_status); //等待的MUTEX add_to_status(&global_status_var, &status_var); mysql_mutex_unlock(&LOCK_status); SHOW GLOBAL STATUS等待 Thread 91 (Thread 0x7f553373c700 (LWP 17935)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd77 in _L_lock_974 () from /lib64/libpthread.so.0#2 0x00007f769d0ffd20 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x0000000000738116 in inline_mysql_mutex_lock (src_file=0xbd31e8 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc", src_line=7315, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 fill_status (thd=thd@entry=0x7f5573db0000, tables=tables@entry=0x7f55736f6178, cond=0x7f55736f5d70) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7315#5 0x000000000073e692 in do_fill_table (join_table=0x7f5564145c78, table_list=0x7f55736f6178, thd=0x7f5573db0000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7986..... 等待的MUTEX为： int fill_status(THD *thd, TABLE_LIST *tables, Item *cond){ ....... /* Avoid recursive acquisition of LOCK_status in cases when WHERE clause represented by "cond" contains subquery on I_S.SESSION/GLOBAL_STATUS. */ if (thd->fill_status_recursion_level++ == 0) mysql_mutex_lock(&LOCK_status); //等待的MUTEX 实际上我们发现他们的共同点为都需要等待获取MUTEX：LOCK_status， 三、分析那个线程获取了MUTEX：LOCK_status而又处于等待 那么可以在整个pstack中试图找出获取LOCK_status的线程，实际上就是 Thread 80 (Thread 0x7f553916a700 (LWP 1306)):#0 0x00007f769d103f7d in __lll_lock_wait () from /lib64/libpthread.so.0#1 0x00007f769d0ffd41 in _L_lock_790 () from /lib64/libpthread.so.0#2 0x00007f769d0ffc47 in pthread_mutex_lock () from /lib64/libpthread.so.0#3 0x00000000008a0014 in inline_mysql_mutex_lock (src_file=0xc31a00 "/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc", src_line=7536, that=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/include/mysql/psi/mysql_thread.h:688#4 MYSQL_BIN_LOG::set_status_variables (this=this@entry=0x135e6c0 <mysql_bin_log>, thd=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:7536#5 0x00000000008a007b in show_binlog_vars (thd=<optimized out>, var=0x7f5539166c70, buff=<optimized out>) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:9476#6 0x00000000007375ff in show_status_array (thd=thd@entry=0x7f5573c2c000, wild=wild@entry=0x0, variables=0x7f75f43f9030, value_type=value_type@entry=OPT_GLOBAL, status_var=0x7f55391671b0, prefix=prefix@entry=0xc2ce39 "", table=0x7f556345d010, ucase_names=false, cond=cond@entry=0x7f55636380d0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:2635#7 0x0000000000737f69 in fill_status (thd=thd@entry=0x7f5573c2c000, tables=tables@entry=0x7f5563638578, cond=0x7f55636380d0) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7321#8 0x000000000073e692 in do_fill_table (join_table=0x7f5563636610, table_list=0x7f5563638578, thd=0x7f5573c2c000) at /mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/sql_show.cc:7986.... 显然这也是一个show global status命令。而他正在等待获取另外一把MUTEX：LOCK_log如下： 因此我们能在show processlist中发现大量的会话都处于挂起状态。 负载不高是因为这里基本都是MUTEX等待CPU负载不高，IO不高是因为这里处于的是flush阶段还没有做到FSYNC阶段。 当然大事物是有可能堵塞SHOW GLOBAL STATUS 命令的，如本例。 最后还是那句话MySQL不适合大事物。 感谢各位的阅读！关于“MySQL中kill和show命令hang住一列的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！ （编辑：瑞安网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
