Shulou(Shulou.com)06/01 Report--

Problem phenomenon:

MySQL instance Hang resides, links accumulate continuously and then reach the upper limit of the number of connections. All operations involving transactions and connections are stuck, and the CPU and load are low.

Problem handling:

The MySQL master library Hang is not available. The temporary solution is to restart the instance or switch to the standby database to keep the business continuously available.

The cause of the problem:

There is a problem with calling methods in log_get_lsn in the error monitoring thread (srv_error_monitor_thread), which can not solve the problems that may be caused by memory barrier when mutex_exit exits.

Problem solving:

This problem can be resolved by upgrading the version to 5.6.29 or later. The official fix information is shown below:

Problem analysis process:

Use pt-pmp to classify the on-site information. For more information, please see document cti.pt_pmp, which is briefly described as follows:

1456 _ _ lll_lock_wait (libpthread.so.0), _ L_lock_975 (libpthread.so.0), pthread_mutex_lock (libpthread.so.0), inline_mysql_mutex_lock (mysql_thread.h:690), THD::release_resources (sql_class.cc:1559), one_thread_per_connection_end (mysqld.cc:2751), do_handle_one_connection (sql_connect.cc:989), handle_one_connection (sql_connect.cc:898) Pfs_spawn_thread (pfs.cc:1860), start_thread (libpthread.so.0), clone (libc.so.6)

1456 threads were blocked while getting the Lock_status

16 _ lll_lock_wait (libpthread.so.0), _ L_lock_975 (libpthread.so.0), pthread_mutex_lock (libpthread.so.0), inline_mysql_mutex_lock (mysql_thread.h:690), fill_status (mysql_thread.h:690), do_fill_table (sql_show.cc:7416), get_schema_tables_result (sql_show.cc:7416), JOIN::prepare_result (sql_select.cc:823) JOIN::exec (sql_executor.cc:116), mysql_execute_select (sql_select.cc:1100), mysql_select (sql_select.cc:1100), handle_select (sql_select.cc:110), execute_sqlcom_select (sql_parse.cc:5134), mysql_execute_command (sql_parse.cc:2612), mysql_parse (sql_parse.cc:6386), dispatch_command (sql_parse.cc:1340), do_command (sql_parse.cc:1037) Do_handle_one_connection (sql_connect.cc:982), handle_one_connection (sql_connect.cc:898), pfs_spawn_thread (pfs.cc:1860), start_thread (libpthread.so.0), clone (libc.so.6)

16 threads were blocked while getting the Lock_status

1 pthread_cond_wait,os_cond_wait (os0sync.cc:214), os_event_wait_low (os0sync.cc:214), sync_array_wait_event (sync0arr.cc:424), mutex_spin_wait (sync0sync.cc:580), mutex_enter_func (sync0sync.ic:218), pfs_mutex_enter_func (sync0sync.ic:218), srv_export_innodb_status (sync0sync.ic:218), innodb_export_status (ha_innodb.cc:12442) Show_innodb_vars (ha_innodb.cc:12442), show_status_array (sql_show.cc:2597), fill_status (sql_show.cc:6749), do_fill_table (sql_show.cc:7416), get_schema_tables_result (sql_show.cc:7416), JOIN::prepare_result (sql_select.cc:823), JOIN::exec (sql_executor.cc:116), mysql_execute_select (sql_select.cc:1100) Mysql_select (sql_select.cc:1100), handle_select (sql_select.cc:110), execute_sqlcom_select (sql_parse.cc:5134), mysql_execute_command (sql_parse.cc:2612), mysql_parse (sql_parse.cc:6386), dispatch_command (sql_parse.cc:1340), do_command (sql_parse.cc:1037), do_handle_one_connection (sql_connect.cc:982), handle_one_connection (sql_connect.cc:898) Pfs_spawn_thread (pfs.cc:1860), start_thread (libpthread.so.0), clone (libc.so.6)

This thread gets the Lock_status, but is blocked when it gets the srv_innodb_monitor_mutex

1 pthread_cond_wait,os_cond_wait (os0sync.cc:214), os_event_wait_low (os0sync.cc:214), sync_array_wait_event (sync0arr.cc:424), mutex_spin_wait (sync0sync.cc:580), mutex_enter_func (sync0sync.ic:218), pfs_mutex_enter_func (sync0sync.ic:218), log_print (sync0sync.ic:218), srv_printf_innodb_monitor (srv0srv.cc:1233), innodb_show_status (ha_innodb.cc:12491) Innobase_show_status (ha_innodb.cc:12491), ha_show_status (handler.cc:6980), mysql_execute_command (sql_parse.cc:2836), mysql_parse (sql_parse.cc:6386), dispatch_command (sql_parse.cc:1340), do_command (sql_parse.cc:1037), do_handle_one_connection (sql_connect.cc:982), handle_one_connection (sql_connect.cc:898), pfs_spawn_thread (pfs.cc:1860) Start_thread (libpthread.so.0), clone (libc.so.6)

This thread gets the srv_innodb_monitor_mutex, but is blocked when getting log_sys- > mutex

1 pthread_cond_wait,os_cond_wait (os0sync.cc:214), os_event_wait_low (os0sync.cc:214), sync_array_wait_event (sync0arr.cc:424), mutex_spin_wait (sync0sync.cc:580), mutex_enter_func (sync0sync.ic:218), pfs_mutex_enter_func (sync0sync.ic:218), mtr_add_dirtied_pages_to_flush_list (sync0sync.ic:218), mtr_log_reserve_and_write (mtr0mtr.cc:270) Mtr_commit (mtr0mtr.cc:270), trx_purge_free_segment (trx0purge.cc:392), trx_purge_truncate_rseg_history (trx0purge.cc:392), trx_purge_truncate_history (trx0purge.cc:527), trx_purge_truncate (trx0purge.cc:527), trx_purge (trx0purge.cc:527), srv_do_purge (srv0srv.cc:2589), srv_purge_coordinator_thread (srv0srv.cc:2589), start_thread (libpthread.so.0), clone (libc.so.6)

This thread got log_sys- > mutex, but was blocked when getting log_sys- > log_flush_order_muex because of a problem with memory barrier. For more information, please see http://dbaplus.cn/news-11-718-1.html

1 pthread_cond_wait,os_cond_wait (os0sync.cc:214), os_event_wait_low (os0sync.cc:214), sync_array_wait_event (sync0arr.cc:424), mutex_spin_wait (sync0sync.cc:580), mutex_enter_func (sync0sync.ic:218), pfs_mutex_enter_func (sync0sync.ic:218), log_reserve_and_write_fast (sync0sync.ic:218), mtr_log_reserve_and_write (sync0sync.ic:218) Mtr_commit (sync0sync.ic:218), trx_undo_report_row_operation (trx0rec.cc:1353), btr_cur_upd_lock_and_undo (btr0cur.cc:1706), btr_cur_update_in_place (btr0cur.cc:1706), btr_cur_optimistic_update (btr0cur.cc:2166), row_upd_clust_rec (row0upd.cc:2132), row_upd_clust_step (row0upd.cc:2435), row_upd (row0upd.cc:2521) Row_upd_step (row0upd.cc:2521), row_update_for_mysql (row0mysql.cc:1779), ha_innobase::update_row (ha_innodb.cc:7091), handler::ha_update_row (handler.cc:7305), Rpl_info_table::do_flush_info (rpl_info_table.cc:207), flush_info (rpl_info_handler.h:92), Master_info::flush_info (rpl_info_handler.h:92) Flush_master_info (rpl_slave.cc:871), handle_slave_io (rpl_slave.cc:4818), pfs_spawn_thread (pfs.cc:1860), start_thread (libpthread.so.0), clone (libc.so.6)

This thread got lock_log & mi- > data_lock, but blocked when getting log_sys- > mutex, causing other show slave status\ G to be blocked.

1 _ lll_lock_wait (libpthread.so.0), _ L_lock_791 (libpthread.so.0), pthread_mutex_lock (libpthread.so.0), inline_mysql_mutex_lock (mysql_thread.h:690), next_event (mysql_thread.h:690), exec_relay_log_event (mysql_thread.h:690), handle_slave_sql (mysql_thread.h:690), pfs_spawn_thread (pfs.cc:1860), start_thread (libpthread.so.0) Clone (libc.so.6)

Sql thread is blocked in getting lock_log because lock_log is obtained by IO thread thread

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.