mysql show slave status 被阻塞

现象如题
哪位大神碰到过这现象，怎么解决的啊。
没有规律任何时间都有可能发生，没有执行备份，没有ddl语句在执行。
show processlist 如下
"id" "app_id" "host" "port" "pid" "p_user" "p_host" "p_db" "command" "time" "status" "info" "time_ms" "rows_sent" "rows_examined" "create_time"
"1363604" "300" "1.1.1.1" "3306" "82405" "cacti" "127.0.0.1:54386" \N "Query" "1225" "init" "SHOW SLAVE STATUS NONBLOCKING" "1225339" "0" "0" "2017-07-01 04:14:32"
"1363605" "300" "1.1.1.1" "3306" "83012" "cacti" "127.0.0.1:56197" \N "Query" "82" "init" "SHOW SLAVE STATUS NONBLOCKING" "82726" "0" "0" "2017-07-01 04:14:32"
"1363606" "300" "1.1.1.1" "3306" "82819" "cacti" "127.0.0.1:55620" \N "Query" "447" "init" "SHOW SLAVE STATUS NONBLOCKING" "447682" "0" "0" "2017-07-01 04:14:32"
"1363607" "300" "1.1.1.1" "3306" "82585" "cacti" "127.0.0.1:54925" \N "Query" "886" "init" "SHOW SLAVE STATUS NONBLOCKING" "886572" "0" "0" "2017-07-01 04:14:32"
"1363608" "300" "1.1.1.1" "3306" "82745" "cacti" "127.0.0.1:55386" \N "Query" "593" "init" "SHOW SLAVE STATUS NONBLOCKING" "593571" "0" "0" "2017-07-01 04:14:32"
"1363609" "300" "1.1.1.1" "3306" "82984" "cacti" "127.0.0.1:56121" \N "Query" "130" "init" "SHOW SLAVE STATUS NONBLOCKING" "130517" "0" "0" "2017-07-01 04:14:32"
"1363610" "300" "1.1.1.1" "3306" "82548" "cacti" "127.0.0.1:54808" \N "Query" "959" "init" "SHOW SLAVE STATUS NONBLOCKING" "959112" "0" "0" "2017-07-01 04:14:32"
"1363611" "300" "1.1.1.1" "3306" "82366" "cacti" "127.0.0.1:54264" \N "Query" "1303" "init" "SHOW SLAVE STATUS NONBLOCKING" "1303483" "0" "0" "2017-07-01 04:14:32"
"1363612" "300" "1.1.1.1" "3306" "82664" "cacti" "127.0.0.1:55156" \N "Query" "740" "init" "SHOW SLAVE STATUS NONBLOCKING" "740157" "0" "0" "2017-07-01 04:14:32"
"1363613" "300" "1.1.1.1" "3306" "82781" "cacti" "127.0.0.1:55501" \N "Query" "523" "init" "SHOW SLAVE STATUS NONBLOCKING" "523296" "0" "0" "2017-07-01 04:14:32"
"1363614" "300" "1.1.1.1" "3306" "82684" "cacti" "127.0.0.1:55213" \N "Query" "706" "init" "SHOW SLAVE STATUS NONBLOCKING" "706618" "0" "0" "2017-07-01 04:14:32"
"1363615" "300" "1.1.1.1" "3306" "82698" "cacti" "127.0.0.1:55251" \N "Query" "679" "init" "SHOW SLAVE STATUS NONBLOCKING" "679220" "0" "0" "2017-07-01 04:14:32"
"1363616" "300" "1.1.1.1" "3306" "82490" "cacti" "127.0.0.1:54637" \N "Query" "1069" "init" "SHOW SLAVE STATUS NONBLOCKING" "1069452" "0" "0" "2017-07-01 04:14:32"
"1363617" "300" "1.1.1.1" "3306" "82627" "cacti" "127.0.0.1:55044" \N "Query" "813" "init" "SHOW SLAVE STATUS NONBLOCKING" "813096" "0" "0" "2017-07-01 04:14:32"
"1363618" "300" "1.1.1.1" "3306" "82895" "cacti" "127.0.0.1:55853" \N "Query" "301" "init" "SHOW SLAVE STATUS NONBLOCKING" "301676" "0" "0" "2017-07-01 04:14:32"
"1363619" "300" "1.1.1.1" "3306" "83030" "cacti" "127.0.0.1:56246" \N "Query" "52" "init" "SHOW SLAVE STATUS NONBLOCKING" "52734" "0" "0" "2017-07-01 04:14:32"
"1363620" "300" "1.1.1.1" "3306" "82906" "cacti" "127.0.0.1:55873" \N "Query" "287" "init" "SHOW SLAVE STATUS NONBLOCKING" "286905" "0" "0" "2017-07-01 04:14:32"
"1363621" "300" "1.1.1.1" "3306" "82656" "cacti" "127.0.0.1:55128" \N "Query" "757" "init" "SHOW SLAVE STATUS NONBLOCKING" "757089" "0" "0" "2017-07-01 04:14:32"
"1363622" "300" "1.1.1.1" "3306" "82393" "cacti" "127.0.0.1:54347" \N "Query" "1251" "init" "SHOW SLAVE STATUS NONBLOCKING" "1251645" "0" "0" "2017-07-01 04:14:32"
"1363623" "300" "1.1.1.1" "3306" "82822" "cacti" "127.0.0.1:55623" \N "Query" "444" "init" "SHOW SLAVE STATUS NONBLOCKING" "444627" "0" "0" "2017-07-01 04:14:32"
"1363624" "300" "1.1.1.1" "3306" "82528" "cacti" "127.0.0.1:54761" \N "Query" "991" "init" "SHOW SLAVE STATUS NONBLOCKING" "991026" "0" "0" "2017-07-01 04:14:32"
"1363625" "300" "1.1.1.1" "3306" "82616" "cacti" "127.0.0.1:55004" \N "Query" "835" "init" "SHOW SLAVE STATUS NONBLOCKING" "835085" "0" "0" "2017-07-01 04:14:32"
"1363626" "300" "1.1.1.1" "3306" "82569" "cacti" "127.0.0.1:54882" \N "Query" "913" "init" "SHOW SLAVE STATUS NONBLOCKING" "913666" "0" "0" "2017-07-01 04:14:32"
"1363627" "300" "1.1.1.1" "3306" "82973" "cacti" "127.0.0.1:56084" \N "Query" "155" "init" "SHOW SLAVE STATUS NONBLOCKING" "155511" "0" "0" "2017-07-01 04:14:32"

解决方案 »

  1.   

    slave io 线程被阻塞，但是不影响正常的业务表查询。
      

  2.   

    pt-pmp 跟踪堆栈输出
    Mon Jul  3 14:17:56 CST 2017
    Missing separate debuginfo for /lib64/libssl.so.10
    Try: yum --enablerepo='*-debug*' install /usr/lib/debug/.build-id/be/cfb85a8bc084042d5bf2ba9e66325ce798b659.debug
    Missing separate debuginfo for /lib64/libcrypto.so.10
    Try: yum --enablerepo='*-debug*' install /usr/lib/debug/.build-id/cb/da444a7109874c5350ae9ceef3f82f749b347f.debugUnhandled dwarf expression opcode 0xfa
         40 epoll_wait(libc.so.6),io_poll_wait(threadpool_unix.cc:292),listener(threadpool_unix.cc:292),get_event(threadpool_unix.cc:1161),worker_main(threadpool_unix.cc:1161),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
         30 pthread_cond_timedwait,inline_mysql_cond_timedwait(mysql_thread.h:1199),get_event(mysql_thread.h:1199),worker_main(mysql_thread.h:1199),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
         22 libaio::??(libaio.so.1),os_aio_linux_collect(os0file.cc:5201),os_aio_linux_handle(os0file.cc:5201),fil_aio_wait(fil0fil.cc:5907),io_handler_thread(srv0start.cc:515),start_thread(libpthread.so.0),clone(libc.so.6)
         14 __lll_lock_wait(libpthread.so.0),_L_lock_995(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),inline_mysql_mutex_lock(mysql_thread.h:688),show_slave_status(rpl_slave.cc:3161),mysql_execute_command(sql_parse.cc:3296),mysql_parse(sql_parse.cc:7056),dispatch_command(sql_parse.cc:1485),threadpool_process_request(threadpool_common.cc:315),handle_event(threadpool_unix.cc:1564),worker_main(threadpool_unix.cc:1564),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
          1 sigwait(libpthread.so.0),signal_hand(mysqld.cc:3487),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
          1 sigwaitinfo(libc.so.6),timer_notify_thread(posix_timers.c:78),start_thread(libpthread.so.0),clone(libc.so.6)
          1 select(libc.so.6),os_thread_sleep(os0thread.cc:304),srv_master_sleep(srv0srv.cc:2945),srv_master_thread(srv0srv.cc:2945),start_thread(libpthread.so.0),clone(libc.so.6)
          1 select(libc.so.6),os_thread_sleep(os0thread.cc:304),page_cleaner_sleep_if_needed(buf0flu.cc:2584),buf_flush_page_cleaner_thread(buf0flu.cc:2584),start_thread(libpthread.so.0),clone(libc.so.6)
          1 select(libc.so.6),os_thread_sleep(os0thread.cc:304),page_cleaner_sleep_if_needed(buf0flu.cc:2584),buf_flush_lru_manager_thread(buf0flu.cc:2584),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_wait,os_cond_wait(os0sync.cc:196),os_event_wait_low(os0sync.cc:196),srv_purge_coordinator_suspend(srv0srv.cc:3305),srv_purge_coordinator_thread(srv0srv.cc:3305),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_wait,os_cond_wait(os0sync.cc:196),os_event_wait_low(os0sync.cc:196),buf_dump_thread(buf0dump.cc:613),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_wait,inline_mysql_cond_wait(mysql_thread.h:1162),MYSQL_BIN_LOG::wait_for_update_relay_log(mysql_thread.h:1162),next_event(rpl_slave.cc:8099),exec_relay_log_event(rpl_slave.cc:8099),handle_slave_sql(rpl_slave.cc:8099),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,os_cond_wait_timed(os0sync.cc:159),os_event_wait_time_low(os0sync.cc:159),srv_monitor_thread(srv0srv.cc:1953),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,os_cond_wait_timed(os0sync.cc:159),os_event_wait_time_low(os0sync.cc:159),srv_error_monitor_thread(srv0srv.cc:2186),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,os_cond_wait_timed(os0sync.cc:159),os_event_wait_time_low(os0sync.cc:159),lock_wait_timeout_thread(lock0wait.cc:503),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,os_cond_wait_timed(os0sync.cc:159),os_event_wait_time_low(os0sync.cc:159),ib_wqueue_timedwait(ut0wqueue.cc:154),fts_optimize_thread(fts0opt.cc:3059),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,os_cond_wait_timed(os0sync.cc:159),os_event_wait_time_low(os0sync.cc:159),dict_stats_thread(dict0stats_bg.cc:349),start_thread(libpthread.so.0),clone(libc.so.6)
          1 pthread_cond_timedwait,inline_mysql_cond_timedwait(mysql_thread.h:1199),timer_thread(mysql_thread.h:1199),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
          1 poll(libc.so.6),handle_connections_sockets(mysqld.cc:6703),mysqld_main(mysqld.cc:6111),__libc_start_main(libc.so.6),_start
          1 __lll_lock_wait(libpthread.so.0),_L_lock_854(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),inline_mysql_mutex_lock(mysql_thread.h:688),queue_event(mysql_thread.h:688),handle_slave_io(rpl_slave.cc:5027),pfs_spawn_thread(pfs.cc:1860),start_thread(libpthread.so.0),clone(libc.so.6)
      

  3.   

    好些是这个问题
    mysql slave hang wait for update relay logshow slave status 只是个被害者。实际上slave异常了。
      

  4.   

    处理过程纪录
    1 yum update glibc
    2 yum update 
    3 reboot 操作系统暂时没有再出现故障
      

  5.   

    相关案例
    http://www.cnblogs.com/exmyth/p/6996033.html
    http://blog.csdn.net/wjf870128/article/details/69384636
      

  6.   

    结论  centos6.6 版本 2.6.32-504.el6.x86_64 内核有bug，在高并发情况会导致mysql hang。