Deadlock causes MySQL Hang Analysis (transfer)

Shulou Source: shulou.com Published: 2022-06-01 04:54:47 09月26日 Update

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

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

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

Deadlock causes MySQL Hang Analysis (transfer)

Related Articles

Oracle 10g rac modifies public ip and vip

Simple misoperation recovery

2018 OCP 052 up-to-date question bank and answers-35

Ocp latest question bank 052 new question tape answer collation-36 questions

Analysis of 052 new questions in OCP latest question bank-with answers-question 37

CentOS 7.5 1804 yum install Postgre