Today morning, we had a weird issue in one of the staging OLAP server (ETL); where all the InnoDB threads were locked and waiting on a signal condition for about 3-4 hours without performing any work related to InnoDB until we noticed it. Processlist indicates every thread is in ACTIVE state mode like Sending Data, update, Copying to tmp table etc; even queries that are acting on single row table are in Sending Data state for more than 3 hours; and slave thread is no exception as it was performing an update on InnoDB table and it got stuck in that query for 3+ hours.
Quick look at the stack trace as shown below indicates that, out of total 92 threads, none of them actually doing anything. Other than sleep, main and monitor threads, every other thread is waiting on a signal condition. The server was actually running with the following concurrency settings:
|
1 2 3 4 |
innodb_thread_concurrency=0 innodb_thread_sleep_delay=0 |
But looks like by accidentally thread_concurrency and concurrency_tickets count was toggled together; that forced currently executing threads to enter into this signal wait state (pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low). Resetting concurrency back to 0 or increasing tickets did not help; but that allowed me to execute new queries without any issues except that it couldn’t signal to wake rest of the threads.
One way to signal the event is by making a new thread to enter srv_conc_enter_innodb with no more concurrency tickets left; that means reset innodb_concurrency_tickets to a lower number along with setting innodb_thread_concurrency > 0 and by executing an InnoDB related query(create table, bunch of inserts, updates, selects and then drop); and by doing so, actually re-signaled the condition and all threads started working back.
This happened with 5.0.77 build with community patches and looks like a race condition that causes this in combination of innodb_thread_concurrency and innodb_concurrency_tickets when toggling back and forth on a long running active threads. I could not reproduce this again as its hard to simulate the load; but will try again on next weekend as week days we let production systems do their job
Stack trace:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
92 12 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,os_aio_simulated_handle,fil_aio_wait,io_handler_thread,start_thread,clone 8 pthread_cond_wait@@GLIBC_2.3.2,end_thread,handle_one_connection,start_thread,clone 1 select,os_thread_sleep,srv_lock_timeout_and_monitor_thread,start_thread,clone 1 select,os_thread_sleep,srv_error_monitor_thread,start_thread,clone 1 select,handle_connections_sockets,main 1 read,read,buf=0x2ab822169530,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7e0000030,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7dfda85c0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7dfda45b0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7d4b035e0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7a3d747e0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab7a0a1a970,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab78c5ab320,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab71686c1d0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6d649ad10,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6cca043e0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6bfd3bed0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6bbd3e620,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6af60dbe0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab6903fefd0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab690000010,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab68ddd4270,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab682eea0c0,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2ab681068d50,my_real_read,my_net_read,handle_one_connection,start_thread,clone 1 read,read,buf=0x2aaaac0010a0,vio_read_buff,my_real_read,my_net_read,cli_safe_read,read_event,at,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_master_thread,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,record=0x2ab74d2a9f00,write_record,mysql_insert,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,record=0x2ab68a89a5e0,write_record,mysql_insert,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab79032a2a0,join_read_key,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab78c4ac270,join_read_always_key,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab76e8e1ea0,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab752517498,write_record,read_sep_field,ex=<value ,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab74bc72430,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6e87bffb0,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6e3a77660,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6be153290,join_read_next_same,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6b7f893f0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6b6b103c0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6b4c1f570,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6af738490,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6a1268040,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab68746fcb0,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab686f64c40,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab685a3c340,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab683cef620,handler::read_range_first,handler::read_multi_range_first,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_delete,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab681b97eb0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab67af5eac0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab672f44fc0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66c88a778,handler::read_range_first,handler::read_multi_range_first,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66bc2f120,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66bbcefe0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66b054ef0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66960f7a0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6694a0620,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab668cd2230,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab667882de0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab660d71870,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,mysql_derived_filling,mysql_handle_derived,open_and_lock_tables,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab66073a070,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6603a8ab0,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6600964a0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65fe180a0,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65f18eed0,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65f1703d0,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,mysql_derived_filling,mysql_handle_derived,open_and_lock_tables,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65f145540,join_read_next_same,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65f1268e0,join_read_next_same,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab65da6d390,join_read_next_same,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab658caabe0,ha_innobase::index_first,ha_innobase::rnd_next,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,mysql_parse,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2ab6574905b0,join_read_always_key,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2aaaae28e998,handler::read_range_first,handler::read_multi_range_first,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,Query_log_event::exec_event,exec_relay_log_event,optimized,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2aaaac4fc010,rr_sequential,sub_select,do_select,JOIN::exec,mysql_select,mysql_derived_filling,mysql_handle_derived,open_and_lock_tables,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2aaaac03f000,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 pthread_cond_wait@@GLIBC_2.3.2,os_event_wait_low,srv_conc_enter_innodb,innodb_srv_conc_enter_innodb,buf=0x2aaaab42ab00,write_record,select_insert::send_data,end_send,evaluate_join_record,sub_select,do_select,JOIN::exec,mysql_select,handle_select,mysql_execute_command,Prepared_statement::execute,mysql_stmt_execute,dispatch_command,handle_one_connection,start_thread,clone 1 do_sigwait,sigwait,signal_hand,start_thread,clone |
[…] This post was mentioned on Twitter by Zuissi, Venu Anuganti. Venu Anuganti said: New blog post: http://tinyurl.com/35dsotu – InnoDB In a Complete Locked Mode […]
New blog post: http://tinyurl.com/35dsotu – InnoDB In a Complete Locked Mode
I have observed a very similar behavior with _exactly_ the same release but while the server was doing some other network related task. In order to reproduce I mount an NFS volume from which I then copy a big file (several GB) file back and forth while MySQL is loaded. The copy has to last several minutes, so the actual size to use will vary according to the specific network. This affected how we managed backups.
I believe it could be reproduced by using sysbench to run an I/O benchmark on the NFS volume while using sysbench again to run an InnoDB benchmark. Both would have to run for several minutes before MySQL locks up as described. I haven’t had time to look into it deeper. It affected our backups and we worked around it by scheduling the backups in such a way that the NFS traffic was kept to a minimum.
I worked w/ MySQL support and adjusting the innodb_thread_concurrency values helped a little bit. We couldn’t find a real culprit, so the problem is still unsolved and since we are moving to 5.1.47 with InnoDB plugin, we are hoping that the new InnoDB code will get rid of the problem.
My $.02
G
MySQL: InnoDB In a Complete Locked Mode: Today morning, we had a weird issue in one of the staging OLAP server (ET… http://bit.ly/bqPZ9H
Planet MySQL. #InnoDB In a Complete Locked Mode: Today morning, we had a weird issue in one of the staging OLAP se… http://bit.ly/bDtNat
InnoDB In a Complete Locked Mode http://bit.ly/dqFFC8
Thanks Gerry for sharing; but I doubt that this issue is resolved in 5.1 as that code path does not have any changes; as its a race condition and not that normally operating engine will get into this state; so not that important.
[…] Venu Anuganti Blog » InnoDB In a Complete Locked Mode […]
Venu,
This, I believe, was fixed in 5.1. http://bugs.mysql.com/bug.php?id=40760
Thanks Inaam for pointing me to the right bug.
That indeed fixes the error condition or makes thread(s) not to re-enter; but not very sure the actual source of problem (all threads waiting on signal) also gets addressed as it either needs to signal to wake any waiting threads when innodb_thread_concurrency is changed or make a timeout
I have observed a very similar behavior with _exactly_ the same release but while the server was doing some other network related task. In order to reproduce I mount an NFS volume from which I then copy a big file (several GB) file back and forth while MySQL is loaded. The copy has to last several minutes, so the actual size to use will vary according to the specific network. This affected how we managed backups.
+1
[…] month, I blogged about a case involving InnoDB, where all threads acting on InnoDB tables completely stuck for about few hours doing nothing; until we found a way to get around and make the threads to run […]