这篇文章给大家分享的是有关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>showprocesslist;+---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+|Id|User|Host|db|Command|Time|State|Info|Rows_sent|Rows_examined|+---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+|862489|dbadmin|127.0.0.1:41012|NULL|Killed|413|executing|SHOWGLOBALSTATUSLIKE'uptime'|0|0||862490|dbadmin|127.0.0.1:41014|information_schema|Killed|902|cleaningup|NULL|0|0||862491|dbadmin|127.0.0.1:41015|information_schema|Killed|902|cleaningup|NULL|0|0||862493|dbadmin|127.0.0.1:41019|information_schema|Killed|165|cleaningup|NULL|0|0||862495|dbadmin|127.0.0.1:41022|information_schema|Killed|1149|cleaningup|NULL|0|0||862508|monitor|127.0.0.1:41041|mysql|Killed|411|executing|showglobalstatuswhereVariable_namein('Threads_connect|0|0||1140925|monitor|127.0.0.1:64924|mysql|Query|414|executing|showglobalstatuswhereVariable_namein('Com_select','Com_insert','Com_update','Com_del|0|0||1153172|systemuser||NULL|Connect|82688|Waitingformastertosendevent|NULL|0|0||1153173|systemuser||NULL|Connect|55564|Systemlock|NULL|0|0||1168129|dbadmin|127.0.0.1:9377|information_schema|Killed|123|cleaningup|NULL|0|0||1181458|dbadmin|127.0.0.1:11163|NULL|Killed|413|cleaningup|NULL|0|0||1181459|dbadmin|127.0.0.1:11164|NULL|Killed|406|cleaningup|NULL|0|0||1181460|dbadmin|127.0.0.1:11166|NULL|Killed|396|cleaningup|NULL|0|0||1181461|dbadmin|127.0.0.1:11177|NULL|Killed|393|cleaningup|NULL|0|0||1181462|dbmonitor|127.0.0.1:11226|NULL|Query|387|executing|SHOW/*!50001GLOBAL*/STATUS|0|0||1181463|dbadmin|127.0.0.1:11242|NULL|Killed|386|cleaningup|NULL|0|0||1181464|dbadmin|127.0.0.1:11328|NULL|Killed|376|cleaningup|NULL|0|0||1181465|dbadmin|127.0.0.1:11408|NULL|Killed|366|cleaningup|NULL|0|0||1181466|dbadmin|127.0.0.1:11436|NULL|Killed|363|cleaningup|NULL|0|0||1181467|dbadmin|127.0.0.1:11465|NULL|Killed|356|cleaningup|NULL|0|0||1181468|dbadmin|127.0.0.1:11467|NULL|Killed|346|cleaningup|NULL|0|0||1181469|dbadmin|127.0.0.1:11469|NULL|Killed|336|cleaningup|NULL|0|0||1181470|dbmonitor|127.0.0.1:11471|NULL|Query|327|executing|SHOW/*!50001GLOBAL*/STATUS|0|0||1181471|dbadmin|127.0.0.1:11477|NULL|Killed|326|cleaningup|NULL|0|0||1181472|dbadmin|127.0.0.1:11478|NULL|Killed|321|cleaningup|NULL|0|0||1181473|dbadmin|127.0.0.1:11481|NULL|Killed|316|cleaningup|NULL|0|0||1181474|dbadmin|127.0.0.1:11483|NULL|Killed|306|cleaningup|NULL|0|0||1181475|dbadmin|127.0.0.1:11484|NULL|Killed|303|cleaningup|NULL|0|0||1181476|dbadmin|127.0.0.1:11485|NULL|Killed|296|cleaningup|NULL|0|0||1181477|dbadmin|127.0.0.1:11557|NULL|Killed|286|cleaningup|NULL|0|0||1181478|dbadmin|127.0.0.1:11640|NULL|Killed|276|cleaningup|NULL|0|0||1181479|dbmonitor|127.0.0.1:11711|NULL|Query|267|executing|SHOW/*!50001GLOBAL*/STATUS|0|0||1181480|dbadmin|127.0.0.1:11730|NULL|Killed|266|cleaningup|NULL|0|0||1181481|dbadmin|127.0.0.1:11783|NULL|Killed|256|cleaningup|NULL|0|0||1181482|dbadmin|127.0.0.1:11784|NULL|Killed|249|cleaningup|NULL|0|0||1181483|dbadmin|127.0.0.1:11785|NULL|Killed|246|cleaningup|NULL|0|0||1181484|dbadmin|127.0.0.1:11786|NULL|Killed|243|cleaningup|NULL|0|0||1181485|dbadmin|127.0.0.1:11788|NULL|Killed|236|cleaningup|NULL|0|0||1181486|dbadmin|127.0.0.1:11792|NULL|Killed|226|cleaningup|NULL|0|0||1181487|dbadmin|localhost|NULL|Killed|218|cleaningup|NULL|0|0|....+---------+-------------+-----------------+--------------------+---------+-------+----------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+75rowsinset(0.01sec)

pstack
这位朋友给出了pstack 实际上是最有用的。

.....Thread112(Thread0x7f75ef237700(LWP82013)):#00x00007f769d10425dinread()from/lib64/libpthread.so.0#10x00000000008f12bainread(__nbytes=5242880,__buf=0x7f5565800000,__fd=181)at/usr/include/bits/unistd.h:45#2my_read(Filedes=181,Buffer=0x7f5565800000"\224\200錦224\234\215錦212℃\234\211閈231\220錦205\217竆b",Count=5242880,MyFlags=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/mysys/my_read.c:50#30x00000000008dbd7dinmy_b_fill(info=0x7f556546cb80)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/mysys/mf_iocache2.c:226#40x000000000089cba9inMYSQL_BIN_LOG::do_write_cache(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,cache=cache@entry=0x7f556546cb80)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6005#50x00000000008a5f7binMYSQL_BIN_LOG::write_cache(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,cache_data=cache_data@entry=0x7f556546cb78)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6144#60x00000000008a6282inbinlog_cache_data::flush(this=0x7f556546cb78,thd=0x7f55764d6000,bytes_written=0x7f75ef2349e0,wrote_xid=0x7f75ef2349c0)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:1261#70x00000000008a6336inflush(wrote_xid=0x7f75ef2349c0,bytes_written=<syntheticpointer>,thd=0x7f55764d6000,this=0x7f556546ca00)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:778#8MYSQL_BIN_LOG::flush_thread_caches(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6779#90x00000000008a6469inMYSQL_BIN_LOG::process_flush_stage_queue(this=this@entry=0x135e6c0<mysql_bin_log>,total_bytes_var=total_bytes_var@entry=0x7f75ef234ad0,rotate_var=rotate_var@entry=0x7f75ef234ab0,out_queue_var=out_queue_var@entry=0x7f75ef234ae0)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6835#100x00000000008a674cinMYSQL_BIN_LOG::ordered_commit(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,all=all@entry=true,skip_commit=skip_commit@entry=false)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:7265#110x00000000008a7284inMYSQL_BIN_LOG::commit(this=0x135e6c0<mysql_bin_log>,thd=0x7f55764d6000,all=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6738#120x00000000005bfa07inha_commit_trans(thd=thd@entry=0x7f55764d6000,all=all@entry=true,ignore_global_read_lock=ignore_global_read_lock@entry=false)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/handler.cc:1513#130x000000000078722eintrans_commit(thd=thd@entry=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/transaction.cc:230#140x0000000000888242inXid_log_event::do_commit(this=this@entry=0x7f557591d380,thd=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/log_event.cc:7420#150x0000000000888670inXid_log_event::do_apply_event(this=0x7f557591d380,rli=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/log_event.cc:7632#160x000000000088425ainLog_event::apply_event(this=this@entry=0x7f557591d380,rli=rli@entry=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/log_event.cc:3057#170x00000000008bb52einapply_event_and_update_pos(ptr_ev=ptr_ev@entry=0x7f75ef236860,thd=thd@entry=0x7f55764d6000,rli=rli@entry=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:3818#180x00000000008befbbinexec_relay_log_event(rli=0x7f555da1a000,thd=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:4315#19handle_slave_sql(arg=arg@entry=0x7f555da14000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:6206#200x0000000000b050e3inpfs_spawn_thread(arg=0x7f557009e0c0)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#210x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#220x00007f769b7713ddinclone()from/lib64/libc.so.6...Thread80(Thread0x7f553916a700(LWP1306)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd41in_L_lock_790()from/lib64/libpthread.so.0#20x00007f769d0ffc47inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000008a0014ininline_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=<optimizedout>)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#4MYSQL_BIN_LOG::set_status_variables(this=this@entry=0x135e6c0<mysql_bin_log>,thd=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:7536#50x00000000008a007binshow_binlog_vars(thd=<optimizedout>,var=0x7f5539166c70,buff=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:9476#60x00000000007375ffinshow_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#70x0000000000737f69infill_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#80x000000000073e692indo_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#9get_schema_tables_result(join=join@entry=0x7f5563638ed8,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#100x000000000071f425inJOIN::prepare_result(this=this@entry=0x7f5563638ed8,columns_list=columns_list@entry=0x7f5539167960)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#110x00000000006d9e7cinJOIN::exec(this=0x7f5563638ed8)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#120x0000000000723c05inmysql_execute_select(free_join=true,select_lex=0x7f5573c2e880,thd=0x7f5573c2c000)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#13mysql_select(thd=thd@entry=0x7f5573c2c000,tables=0x7f5563638578,wild_num=0,fields=...,conds=0x7f55636380d0,order=order@entry=0x7f5573c2ea48,group=group@entry=0x7f5573c2e980,having=0x0,select_options=2684619520,result=result@entry=0x7f5563638eb0,unit=unit@entry=0x7f5573c2e238,select_lex=select_lex@entry=0x7f5573c2e880)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#140x0000000000724465inhandle_select(thd=thd@entry=0x7f5573c2c000,result=result@entry=0x7f5563638eb0,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#150x0000000000581a38inexecute_sqlcom_select(thd=thd@entry=0x7f5573c2c000,all_tables=<optimizedout>)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#160x00000000006fd731inmysql_execute_command(thd=thd@entry=0x7f5573c2c000)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#170x0000000000702c98inmysql_parse(thd=thd@entry=0x7f5573c2c000,rawbuf=<optimizedout>,length=<optimizedout>,parser_state=parser_state@entry=0x7f5539169020)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#180x00000000007044abindispatch_command(command=<optimizedout>,thd=thd@entry=0x7f5573c2c000,packet=<optimizedout>,packet_length=739)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#190x0000000000706219indo_command(thd=thd@entry=0x7f5573c2c000)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#200x000000000078f368inthreadpool_process_request(thd=0x7f5573c2c000)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#210x000000000079033dinhandle_event(connection=0x7f5573b5b880)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#22worker_main(param=param@entry=0x1351e00<all_groups+14848>)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#230x0000000000b050e3inpfs_spawn_thread(arg=0x7f555d6f7ce0)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#240x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#250x00007f769b7713ddinclone()from/lib64/libc.so.6.....Thread55(Thread0x7f55318b2700(LWP2821)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000006cde10ininline_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=<optimizedout>)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#4THD::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#50x000000000078f1e7inthreadpool_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#60x00000000007902b6inconnection_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#7handle_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#8worker_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#90x0000000000b050e3inpfs_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#100x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#110x00007f769b7713ddinclone()from/lib64/libc.so.6Thread54(Thread0x7f553898b700(LWP2962)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000006cde10ininline_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=<optimizedout>)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#4THD::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#50x000000000078f1e7inthreadpool_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#60x00000000007902b6inconnection_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#7handle_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#8worker_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#90x0000000000b050e3inpfs_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#100x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#110x00007f769b7713ddinclone()from/lib64/libc.so.6....Thread50(Thread0x7f55337ff700(LWP3002)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x0000000000738116ininline_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=<optimizedout>)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#4fill_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#50x000000000073e692indo_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#6get_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#70x000000000071f425inJOIN::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#80x00000000006d9e7cinJOIN::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#90x0000000000723c05inmysql_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#10mysql_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#110x0000000000724465inhandle_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#120x0000000000581a38inexecute_sqlcom_select(thd=thd@entry=0x7f5576042000,all_tables=<optimizedout>)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#130x00000000006fd731inmysql_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#140x0000000000702c98inmysql_parse(thd=thd@entry=0x7f5576042000,rawbuf=<optimizedout>,length=<optimizedout>,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#150x00000000007044abindispatch_command(command=<optimizedout>,thd=thd@entry=0x7f5576042000,packet=<optimizedout>,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#160x0000000000706219indo_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#170x000000000078f368inthreadpool_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#180x000000000079033dinhandle_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#19worker_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#200x0000000000b050e3inpfs_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#210x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#220x00007f769b7713ddinclone()from/lib64/libc.so.6.....Thread46(Thread0x7f5531a79700(LWP3181)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x0000000000738116ininline_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=<optimizedout>)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#4fill_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#50x000000000073e692indo_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#6get_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#70x000000000071f425inJOIN::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#80x00000000006d9e7cinJOIN::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#90x0000000000723c05inmysql_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#10mysql_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#110x0000000000724465inhandle_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#120x0000000000581a38inexecute_sqlcom_select(thd=thd@entry=0x7f55761e9000,all_tables=<optimizedout>)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#130x00000000006fd731inmysql_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#140x0000000000702c98inmysql_parse(thd=thd@entry=0x7f55761e9000,rawbuf=<optimizedout>,length=<optimizedout>,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#150x00000000007044abindispatch_command(command=<optimizedout>,thd=thd@entry=0x7f55761e9000,packet=<optimizedout>,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#160x0000000000706219indo_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#170x000000000078f368inthreadpool_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#180x000000000079033dinhandle_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#19worker_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#200x0000000000b050e3inpfs_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#210x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#220x00007f769b7713ddinclone()from/lib64/libc.so.6.....Thread43(Thread0x7f5531b3c700(LWP3340)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000006cde10ininline_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=<optimizedout>)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#4THD::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#50x000000000078f1e7inthreadpool_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#60x00000000007902b6inconnection_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#7handle_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#8worker_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#90x0000000000b050e3inpfs_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#100x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#110x00007f769b7713ddinclone()from/lib64/libc.so.6Thread42(Thread0x7f5531bbe700(LWP3353)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000006cde10ininline_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=<optimizedout>)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#4THD::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#50x000000000078f1e7inthreadpool_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#60x00000000007902b6inconnection_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#7handle_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#8worker_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#90x0000000000b050e3inpfs_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#100x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#110x00007f769b7713ddinclone()from/lib64/libc.so.6二、分析大多数线程的等待MUTEX

实际上分析pstack可以发现，基本所有的等待线程可以分为：

KILL SESSION等待

Thread111(Thread0x7f5531afb700(LWP15742)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000006cde10ininline_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=<optimizedout>)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#4THD::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#50x000000000078f1e7inthreadpool_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为：

voidTHD::release_resources(){mysql_mutex_assert_not_owner(&LOCK_thread_count);DBUG_ASSERT(m_release_resources_done==false);mysql_mutex_lock(&LOCK_status);//等待的MUTEXadd_to_status(&global_status_var,&status_var);mysql_mutex_unlock(&LOCK_status);

SHOW GLOBAL STATUS等待

Thread91(Thread0x7f553373c700(LWP17935)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd77in_L_lock_974()from/lib64/libpthread.so.0#20x00007f769d0ffd20inpthread_mutex_lock()from/lib64/libpthread.so.0#30x0000000000738116ininline_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=<optimizedout>)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#4fill_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#50x000000000073e692indo_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为：

intfill_status(THD*thd,TABLE_LIST*tables,Item*cond){......./*AvoidrecursiveacquisitionofLOCK_statusincaseswhenWHEREclauserepresentedby"cond"containssubqueryonI_S.SESSION/GLOBAL_STATUS.*/if(thd->fill_status_recursion_level++==0)mysql_mutex_lock(&LOCK_status);//等待的MUTEX

实际上我们发现他们的共同点为都需要等待获取MUTEX：LOCK_status，

那么可以在整个pstack中试图找出获取LOCK_status的线程，实际上就是

Thread80(Thread0x7f553916a700(LWP1306)):#00x00007f769d103f7din__lll_lock_wait()from/lib64/libpthread.so.0#10x00007f769d0ffd41in_L_lock_790()from/lib64/libpthread.so.0#20x00007f769d0ffc47inpthread_mutex_lock()from/lib64/libpthread.so.0#30x00000000008a0014ininline_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=<optimizedout>)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#4MYSQL_BIN_LOG::set_status_variables(this=this@entry=0x135e6c0<mysql_bin_log>,thd=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:7536#50x00000000008a007binshow_binlog_vars(thd=<optimizedout>,var=0x7f5539166c70,buff=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:9476#60x00000000007375ffinshow_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#70x0000000000737f69infill_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#80x000000000073e692indo_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如下：

/*fill_statusMYSQL_BIN_LOG::ordered_commitCopyoutcurrentvaluesofstatusvariables,forSHOWSTATUSorinformation_schema.global_status.ThisiscalledonlyunderLOCK_status,sowecanfillinastaticarray.*/voidMYSQL_BIN_LOG::set_status_variables(THD*thd){....mysql_mutex_lock(&LOCK_log);//这里是需要获取MUTEXLOCK_logif(!have_snapshot){set_binlog_snapshot_file(log_file_name);binlog_snapshot_position=my_b_tell(&log_file);//获取binlog的位置这里就是状态值Binlog_snapshot_position}mysql_mutex_unlock(&LOCK_log);.....

显然这里要获取的状态值Binlog_snapshot_position ，因此必须获取LOCK_log MUTEX，来保证没有并发的线程正在写入binlog物理文件，当然这里的写入是指写入操作系统的缓存而非FSYNC ，：

mysql>showglobalstatuslike'%pos%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|Binlog_snapshot_position|6363|+--------------------------+-------+1rowinset(2.05sec)四、分析堵塞的根源

那么堵塞线程 (LWP 17935) 的很可能是开始说的那个大事物正在做flush了因此我们找到slave的 sql_thread的栈帧如下：

Thread112(Thread0x7f75ef237700(LWP82013)):#00x00007f769d10425dinread()from/lib64/libpthread.so.0#10x00000000008f12bainread(__nbytes=5242880,__buf=0x7f5565800000,__fd=181)at/usr/include/bits/unistd.h:45#2my_read(Filedes=181,Buffer=0x7f5565800000"\224\200錦224\234\215錦212℃\234\211閈231\220錦205\217竆b",Count=5242880,MyFlags=<optimizedout>)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/mysys/my_read.c:50#30x00000000008dbd7dinmy_b_fill(info=0x7f556546cb80)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/mysys/mf_iocache2.c:226#40x000000000089cba9inMYSQL_BIN_LOG::do_write_cache(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,cache=cache@entry=0x7f556546cb80)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6005#50x00000000008a5f7binMYSQL_BIN_LOG::write_cache(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,cache_data=cache_data@entry=0x7f556546cb78)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6144#60x00000000008a6282inbinlog_cache_data::flush(this=0x7f556546cb78,thd=0x7f55764d6000,bytes_written=0x7f75ef2349e0,wrote_xid=0x7f75ef2349c0)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:1261#70x00000000008a6336inflush(wrote_xid=0x7f75ef2349c0,bytes_written=<syntheticpointer>,thd=0x7f55764d6000,this=0x7f556546ca00)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:778#8MYSQL_BIN_LOG::flush_thread_caches(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6779#90x00000000008a6469inMYSQL_BIN_LOG::process_flush_stage_queue(this=this@entry=0x135e6c0<mysql_bin_log>,total_bytes_var=total_bytes_var@entry=0x7f75ef234ad0,rotate_var=rotate_var@entry=0x7f75ef234ab0,out_queue_var=out_queue_var@entry=0x7f75ef234ae0)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:6835#100x00000000008a674cinMYSQL_BIN_LOG::ordered_commit(this=this@entry=0x135e6c0<mysql_bin_log>,thd=thd@entry=0x7f55764d6000,all=all@entry=true,skip_commit=skip_commit@entry=false)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/binlog.cc:7265.....#150x0000000000888670inXid_log_event::do_apply_event(this=0x7f557591d380,rli=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/log_event.cc:7632#160x000000000088425ainLog_event::apply_event(this=this@entry=0x7f557591d380,rli=rli@entry=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/log_event.cc:3057#170x00000000008bb52einapply_event_and_update_pos(ptr_ev=ptr_ev@entry=0x7f75ef236860,thd=thd@entry=0x7f55764d6000,rli=rli@entry=0x7f555da1a000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:3818#180x00000000008befbbinexec_relay_log_event(rli=0x7f555da1a000,thd=0x7f55764d6000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:4315#19handle_slave_sql(arg=arg@entry=0x7f555da14000)at/mnt/workspace/percona-server-5.6-binaries-release/label_exp/centos6-64/percona-server-5.6.25-73.0/sql/rpl_slave.cc:6206#200x0000000000b050e3inpfs_spawn_thread(arg=0x7f557009e0c0)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#210x00007f769d0fddf3instart_thread()from/lib64/libpthread.so.0#220x00007f769b7713ddinclone()from/lib64/libc.so.6

我们很清楚的看到这里正处于flush阶段

MYSQL_BIN_LOG::process_flush_stage_queue

源码中如下：

/*Stage#1:flushingtransactionstobinarylogWhileflushing,weallownewthreadstoenterandwillprocesstheminduetime.Oncethequeuewasempty,wecannotreapanythingmoresinceitispossiblethatathreadenteredandappointeditselfleaderfortheflushphase.*/if(change_stage(thd,Stage_manager::FLUSH_STAGE,thd,NULL,&LOCK_log))//这里获取MUTEXLOCK_log知道flush结束{DBUG_PRINT("return",("ThreadID:%lu,commit_error:%d",thd->thread_id,thd->commit_error));DBUG_RETURN(finish_commit(thd));}五、结论

本例子中等待MUTEX有如下的图：

image.png

因此我们能在show processlist中发现大量的会话都处于挂起状态。

负载不高是因为这里基本都是MUTEX等待CPU负载不高，IO不高是因为这里处于的是flush阶段还没有做到FSYNC阶段。

当然大事物是有可能堵塞SHOW GLOBAL STATUS 命令的，如本例。

最后还是那句话MySQL不适合大事物。

感谢各位的阅读！关于“MySQL中kill和show命令hang住一列的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！