[MDEV-17063] server locks up and is killed after innodb_fatal_semaphore_wait_threshold is reached Created: 2018-08-24  Updated: 2023-06-26  Resolved: 2023-06-26

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.2.17
Fix Version/s: 10.2.24, 10.3.14, 10.4.4

Type: Bug Priority: Major
Reporter: Rick Pizzi Assignee: Julius Goryavsky
Resolution: Fixed Votes: 2
Labels: None

Attachments: Text File crash.log     File sema.log.gz    
Issue Links:
Blocks
Relates
relates to MDEV-17190 MariaDB Server deadlock Closed
relates to MDEV-18464 Port kill_one_trx fixes from 10.4 to ... Closed

 Description   

Happening at a customer of ours.
This issue repeats itself every saturday at approx the same time - looks like something in the workload is triggering it.
The server locks up and does not process any more data (connections are accepted but not handled). After the timeout set in innodb_fatal_semaphore_wait_threshold the InnoDB kernel commits suicide.

I was able to capture a stack trace right before the fatal wait was reached.

    367 __lll_lock_wait,__GI___pthread_mutex_lock,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    347 poll,vio_io_wait,vio_socket_io_wait,vio_read,::??,my_net_read_packet_reallen,my_net_read_packet,do_command,do_handle_one_connection,handle_one_connection,start_thr
 
ead,clone
 
    251 pthread_cond_wait,::??,::??,::??,::??,::??,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_ha
 
ndle_one_connection,handle_one_connection,start_thread,clone
 
    157 nanosleep,::??,::??,::??,handler::ha_index_read_map,::??,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_command,mysql_parse,:
 
:??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    123 nanosleep,::??,::??,::??,handler::index_read_idx_map,handler::ha_index_read_idx_map,::??,::??,::??,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,:
 
:??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     67 pthread_cond_wait,::??,::??,::??,::??,::??,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,Ds
 
Mrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,::??,mysql_update,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_
 
connection,start_thread,clone
 
     52 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::ha_open,open_table_from_share,open_table,open_tables,open_and_lock_tables,::??,mysql_execute_command,mysql
 
_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     30 nanosleep,::??,::??,::??,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_ne
 
xt,QUICK_RANGE_SELECT::get_next,QUICK_ROR_UNION_SELECT::reset,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,JOIN::exec_inner,JOIN::
 
exec,mysql_select,handle_select,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     18 nanosleep,::??,::??,::??,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connectio
 
n,handle_one_connection,start_thread,clone
 
     17 __lll_lock_wait,__GI___pthread_mutex_lock,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     15 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::ha_open,open_table_from_share,open_table,open_tables,open_and_lock_tables,::??,mysql_execute_command,sp_in
 
str_stmt::exec_core,sp_lex_keeper::reset_lex_and_exec_core,sp_instr_stmt::execute,sp_head::execute,sp_head::execute_procedure,::??,mysql_execute_command,mysql_parse,::??,d
 
ispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     13 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::ha_open,open_table_from_share,open_table,open_tables,mysql_update,mysql_execute_command,mysql_parse,::??,d
 
ispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     10 libaio::??,::??,::??,::??,::??,::??,start_thread,clone
 
      9 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::index_read_idx_map,handler::ha_index_read_idx_map,::??,::??,::??,JOIN::optimize_inner,JOIN::optimize,mysql
 
_select,handle_select,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      9 nanosleep,::??,::??,::??,::??,handler::ha_rnd_next,rr_sequential,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_command,mysql
 
_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      8 pthread_cond_wait,::??,::??,start_thread,clone
 
      7 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::ha_open,open_table_from_share,open_table,open_tables,open_and_lock_tables,mysql_insert,mysql_execute_comma
 
nd,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      6 pthread_cond_wait,::??,::??,::??,::??,::??,::??,handler::ha_index_read_map,::??,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execut
 
e_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      4 pthread_cond_wait,fifo_lock_get,gu_fifo_get_head,gcs_recv,galera::Gcs::recv,galera::GcsActionSource::process,galera::ReplicatorSMM::async_recv,galera_recv,::??,sta
 
rt_wsrep_THD,start_thread,clone
 
      4 nanosleep,::??,::??,::??,::??,handler::ha_rnd_next,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,JOIN::exec_inner,JOIN::exe
 
c,mysql_select,handle_select,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,::??,::??,::??,::??,open_tables,open_and_lock_tables,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connec
 
tion,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,::??,::??,::??,::??,::??,handler::ha_update_row,mysql_update,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_con
 
nection,handle_one_connection,start_thread,clone
 
      2 pthread_cond_wait,::??,::??,::??,start_thread,clone
 
      2 pthread_cond_timedwait,::??,::??,::??,start_thread,clone
 
      2 nanosleep,::??,::??,start_thread,clone
 
      2 nanosleep,::??,::??,::??,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_ne
 
xt,QUICK_RANGE_SELECT::get_next,::??,mysql_update,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_t
 
hread,clone
 
      2 nanosleep,::??,::??,::??,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_ne
 
xt,QUICK_RANGE_SELECT::get_next,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_selec
 
t,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,::??,start_wsrep_THD,start_thread,clone
 
      1 pthread_cond_wait,::??,start_thread,clone
 
      1 pthread_cond_wait,::??,::??,::??,::??,::??,start_thread,clone
 
      1 pthread_cond_wait,::??,::??,::??,::??,::??,plugin_foreach_with_mask,ha_kill_query,THD::awake,kill_one_thread,::??,mysql_execute_command,mysql_parse,::??,dispatch_c
 
ommand,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,::??,::??,::??,::??,::??,::??,ha_show_status,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_o
 
ne_connection,start_thread,clone
 
      1 pthread_cond_wait,handle_slave_background,start_thread,clone
 
      1 pthread_cond_wait,::??,::??,::??,::??,::??,::??,::??,handler::ha_rnd_next,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,JOI
 
N::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,
 
start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,RecvBuf::front,gcomm_recv,core_msg_recv,gcs_core_recv,gcs_recv_thread,start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,galera::ServiceThd::thd_func,start_thread,clone
 
      1 pthread_cond_wait,::??,::??,::??,::??,::??,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_command,m
 
ysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,binlog_background_thread,start_thread,clone
 
      1 pthread_cond_timedwait,::??,start_thread,clone
 
      1 pthread_cond_timedwait,::??,::??,start_thread,clone
 
      1 pthread_cond_timedwait,::??,::??,::??,::??,start_thread,clone
 
      1 nanosleep,::??,::??,::??,start_thread,clone
 
      1 nanosleep,::??,::??,::??,handler::ha_index_next_same,::??,sub_select,::??,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_comm
 
and,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,::??,::??,::??,::??,handler::ha_index_first,::??,sub_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_command,mysql_parse
 
,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,THD::~THD,THD::~THD,CONNECT::close_with_error,handle_connections_sockets,mysqld_main,__libc_start_main,_start
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,thd_query_safe,::??,::??,::??,::??,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,:
 
:??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,mysqld_list_processes,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,ha
 
ndle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,st_select_lex_unit::exec,mysql_union,handle_select,
 
::??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,::??,mysql_execute_comma
 
nd,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,::??,calc_sum_of_all_status,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,:
 
:??,mysql_execute_command,mysql_parse,::??,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 epoll_wait,asio::detail::epoll_reactor::run(epoll_reactor.ipp:391),asio::detail::task_io_service::do_run_one(task_io_service.ipp:355),asio::detail::task_io_service
 
::run(task_io_service.ipp:355),asio::io_service::run(task_io_service.ipp:355),gcomm::AsioProtonet::event_loop(task_io_service.ipp:355),GCommConn::run,GCommConn::run_fn,sta
 
rt_thread,clone
 
      1 do_sigwait,__sigwait,signal_hand,start_thread,clone



 Comments   
Comment by Marko Mäkelä [ 2018-08-28 ]

There are too many ?? in the stack traces, apparently exactly for the InnoDB functions. I see that a handler::ha_open() is blocked. This could be because something is holding dict_operation_lock exclusively while waiting for something else. Is there any server error log available?

Comment by Marko Mäkelä [ 2018-08-28 ]

Thinking aloud: 10.2.17 contains the fix of MDEV-15855 but not MDEV-17043.
Were there any virtual columns?

Comment by Todd Coker (Inactive) [ 2018-08-28 ]

No virtual columns. Here's a snip from the error log when the problem occurred.

2018-08-10 22:50:30 139558212155136 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 60 seconds. We intentionally crash the server because it appears to be hung.
180810 22:50:30 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.2.16-MariaDB-10.2.16+maria~stretch-log
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1393
max_threads=1502
thread_count=1562
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3431484 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x49000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55ae15c43a8e]
/usr/sbin/mysqld(handle_fatal_signal+0x41d)[0x55ae156e662d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7f026b2900c0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcf)[0x7f026972afff]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f026972c42a]
2018-08-10 22:50:33 139648192849664 [Warning] WSREP: last inactive check more than PT1.5S ago (PT2.87574S), skipping check
/usr/sbin/mysqld(+0x93f6c1)[0x55ae159cb6c1]
/usr/sbin/mysqld(+0x8e88a6)[0x55ae159748a6]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7494)[0x7f026b286494]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f02697e0acf]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Comment by Rick Pizzi [ 2018-08-28 ]

Hi Marko,
no I don't think they're using virtual columns.
Server error log attached.

Thanks
Rick

Comment by Marko Mäkelä [ 2018-10-30 ]

According to crash.log, 2 threads are holding dict_operation_lock in S mode. These threads are essentially causing the other rw-lock and mutex waits.

This looks somewhat similar to MDEV-17190, but not exactly the same, because I see no reference to any buf_block_t::lock.

Would it be possible to get proper stack traces with InnoDB function names instead of ??, so that we can try to identify the 2 threads that are holding the latch, and see what they are waiting for.

Also, would it be possible to get the server log messages between the latest startup to the first "long semaphore wait" message? Had there been any abnormal events, such as page corruption?

Comment by Rick Pizzi [ 2018-10-31 ]

AFAIK to get function names we need a mysqld executable with debugging symbols....
Right now, only way to do have that is to compile MariaDB from source with proper options.
Since this is a critical customer I don't feel like installing a binary I have built myself on their production systems....
We should really provide a debugging package like Percona does (why don't we??)

To answer your other question - no, nothing interesting in error log...

Comment by Marko Mäkelä [ 2018-11-02 ]

rpizzi, what is the platform in question? Is it still 10.2.17? If we cannot provide debugging symbols for that platform, I think that we should recompile the version for the platform, with debugging symbols enabled, so that we can get proper stack traces.

Comment by Rick Pizzi [ 2018-11-02 ]

still 10.2.17

Comment by Rick Pizzi [ 2018-11-02 ]

I have installed the debugging symbols package , looks like the stack dump is proper now.
All we have to do now is to wait for the next occurrence of the issue.

Comment by Elena Stepanova [ 2018-11-30 ]

rpizzi, have you gotten an occurrence with debug symbols?

Comment by Rick Pizzi [ 2018-11-30 ]

We did; but were unable to capture the stack trace. We will update ticket as soon as we get one.

Comment by Rick Pizzi [ 2018-12-03 ]

Here's a complete stack trace at the time of the incident!
This is happening almost daily currently.

    891 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,add_to_active_threads,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    436 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_insert_for_mysql,ha_innobase::write_row,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    271 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,THD::add_status_to_global,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    233 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,join_read_always_key,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    214 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     84 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     40 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     18 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::write_row,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     14 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,QUICK_ROR_UNION_SELECT::reset,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     10 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      9 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,ha_innobase::index_first,ha_innobase::rnd_next,handler::ha_rnd_next,rr_sequential,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      8 libaio::??,LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_linux_handler,os_aio_handler,fil_aio_wait,io_handler_thread,start_thread,clone
 
      5 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,open_and_lock_for_insert_delayed,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      5 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_update_for_mysql,ha_innobase::update_row,handler::ha_update_row,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      5 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,ha_innobase::index_first,ha_innobase::rnd_next,handler::ha_rnd_next,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      4 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,Rows_log_event::do_apply_event,Log_event::apply_event,wsrep_apply_events,wsrep_apply_cb,galera::TrxHandle::apply,apply_trx_ws,galera::ReplicatorSMM::apply_trx,galera::ReplicatorSMM::process_trx,galera::GcsActionSource::dispatch,galera::GcsActionSource::process,galera::ReplicatorSMM::async_recv,galera_recv,wsrep_replication_process,start_wsrep_THD,start_thread,clone
 
      4 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,unlink_not_visible_thd,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_tables,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_allocate_for_mysql,innobase_trx_allocate,check_trx_exists,ha_innobase::update_thd,ha_innobase::info_low,ha_innobase::info,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_allocate_for_mysql,innobase_trx_allocate,check_trx_exists,ha_innobase::extra,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_trx_handle_wait,kill_handlerton,plugin_foreach_with_mask,ha_kill_query,THD::awake,kill_one_thread,sql_kill,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,srv_resume_thread,srv_worker_thread,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_flush_page_cleaner_worker,start_thread,clone
 
      3 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,mysqld_list_processes,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,execute_sqlcom_select,mysql_execute_command,sp_instr_stmt::exec_core,sp_lex_keeper::reset_lex_and_exec_core,sp_instr_stmt::execute,sp_head::execute,sp_head::execute_procedure,do_execute_sp,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_allocate_for_mysql,innobase_trx_allocate,check_trx_exists,ha_innobase::update_thd,ha_innobase::info_low,ha_innobase::info,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_tables,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_allocate_for_mysql,innobase_trx_allocate,check_trx_exists,ha_innobase::update_thd,ha_innobase::info_low,ha_innobase::info,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,open_and_lock_for_insert_delayed,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_derived_optimize,mysql_handle_single_derived,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,join_read_last_key,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,join_read_always_key,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,ha_innobase::index_first,ha_innobase::rnd_next,handler::ha_rnd_next,rr_sequential,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::size,srv_printf_innodb_monitor,srv_monitor_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::clone_oldest_view,trx_purge,srv_do_purge,srv_purge_coordinator_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_wait_check_and_cancel,lock_wait_timeout_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_update_delete,btr_cur_optimistic_delete_func,row_undo_ins_remove_clust_rec,row_undo_ins,row_undo,row_undo_step,que_thr_step,que_run_threads_low,que_run_threads,trx_rollback_to_savepoint_low,trx_rollback_for_mysql_low,trx_rollback_for_mysql,innobase_rollback,ha_rollback_trans,trans_rollback,THD::cleanup,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_update_delete,btr_cur_optimistic_delete_func,ibuf_delete_rec,ibuf_merge_or_delete_for_page,buf_page_io_complete,fil_aio_wait,io_handler_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,rw_lock_x_lock_func,pfs_rw_lock_x_lock_func,trx_i_s_cache_start_write,trx_i_s_common_fill_table,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,rw_lock_sx_lock_func,pfs_rw_lock_sx_lock_func,buf_flush_page,buf_flush_try_neighbors,buf_flush_page_and_try_neighbors,buf_do_flush_list_batch,buf_flush_batch,buf_flush_do_batch,buf_flush_lists,buf_flush_page_cleaner_coordinator,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,rw_lock_s_lock_func,pfs_rw_lock_s_lock_func,buf_page_get_gen,btr_cur_search_to_nth_level,btr_pcur_open_low,btr_pcur_open_on_user_rec_func,ibuf_merge_or_delete_for_page,buf_page_io_complete,fil_aio_wait,io_handler_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,rw_lock_s_lock_func,pfs_rw_lock_s_lock_func,buf_page_get_gen,btr_cur_open_at_rnd_pos_func,btr_pcur_open_at_rnd_pos_func,ibuf_merge_pages,ibuf_merge,ibuf_merge_in_background,srv_master_do_active_tasks,srv_master_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_resize_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_dump_thread,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,wsrep_rollback_process,start_wsrep_THD,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,thd_destructor_proxy,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,handle_slave_background,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,binlog_background_thread,start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,RecvBuf::front,gcomm_recv,core_msg_recv,gcs_core_recv,gcs_recv_thread,start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,galera::ServiceThd::thd_func,start_thread,clone
 
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,ib_wqueue_timedwait,fts_optimize_thread,start_thread,clone
 
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,dict_stats_thread,start_thread,clone
 
      1 pthread_cond_timedwait,inline_mysql_cond_timedwait,timer_handler,start_thread,clone
 
      1 pthread_cond_timedwait,inline_mysql_cond_timedwait,my_service_thread_sleep,ma_checkpoint_background,start_thread,clone
 
      1 nanosleep,os_thread_sleep,sync_array_print_long_waits,srv_error_monitor_thread,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_delete,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,QUICK_ROR_UNION_SELECT::reset,join_init_read_record,sub_select,evaluate_null_complemented_join_record,sub_select,evaluate_join_record,sub_select,evaluate_join_record,sub_select,evaluate_join_record,sub_select,evaluate_join_record,sub_select,evaluate_join_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,ha_innobase::index_first,handler::ha_index_first,join_read_first,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,btr_defragment_thread,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,thd_query_safe,innobase_get_stmt_safe,fill_trx_row,fetch_data_into_cache_low,fetch_data_into_cache,trx_i_s_possibly_fetch_data_into_cache,trx_i_s_common_fill_table,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,THD::add_status_to_global,THD::~THD,THD::~THD,CONNECT::close_with_error,create_new_thread,handle_connections_sockets,mysqld_main,__libc_start_main,_start
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,execute_show_status,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,execute_show_status,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,calc_sum_of_all_status,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 epoll_wait,asio::detail::epoll_reactor::run(epoll_reactor.ipp:391),asio::detail::task_io_service::do_run_one(task_io_service.ipp:355),asio::detail::task_io_service::run(task_io_service.ipp:355),asio::io_service::run(task_io_service.ipp:355),gcomm::AsioProtonet::event_loop(task_io_service.ipp:355),GCommConn::run,GCommConn::run_fn,start_thread,clone
 
      1 do_sigwait,__sigwait,signal_hand,start_thread,clone

Comment by Rick Pizzi [ 2018-12-04 ]

Relevant info

 
MariaDB [(none)]> show global variables like 'innodb%';
 
+---------------------------------------------+------------------------+
 
| Variable_name                               | Value                  |
 
+---------------------------------------------+------------------------+
 
| innodb_adaptive_flushing                    | ON                     |
 
| innodb_adaptive_flushing_lwm                | 10.000000              |
 
| innodb_adaptive_hash_index                  | ON                     |
 
| innodb_adaptive_hash_index_partitions       | 8                      |
 
| innodb_adaptive_hash_index_parts            | 8                      |
 
| innodb_adaptive_max_sleep_delay             | 150000                 |
 
| innodb_autoextend_increment                 | 64                     |
 
| innodb_autoinc_lock_mode                    | 2                      |
 
| innodb_background_scrub_data_check_interval | 3600                   |
 
| innodb_background_scrub_data_compressed     | OFF                    |
 
| innodb_background_scrub_data_interval       | 604800                 |
 
| innodb_background_scrub_data_uncompressed   | OFF                    |
 
| innodb_buf_dump_status_frequency            | 0                      |
 
| innodb_buffer_pool_chunk_size               | 134217728              |
 
| innodb_buffer_pool_dump_at_shutdown         | ON                     |
 
| innodb_buffer_pool_dump_now                 | OFF                    |
 
| innodb_buffer_pool_dump_pct                 | 25                     |
 
| innodb_buffer_pool_filename                 | ib_buffer_pool         |
 
| innodb_buffer_pool_instances                | 16                     |
 
| innodb_buffer_pool_load_abort               | OFF                    |
 
| innodb_buffer_pool_load_at_startup          | ON                     |
 
| innodb_buffer_pool_load_now                 | OFF                    |
 
| innodb_buffer_pool_populate                 | OFF                    |
 
| innodb_buffer_pool_size                     | 75161927680            |
 
| innodb_change_buffer_max_size               | 25                     |
 
| innodb_change_buffering                     | all                    |
 
| innodb_checksum_algorithm                   | crc32                  |
 
| innodb_checksums                            | ON                     |
 
| innodb_cleaner_lsn_age_factor               | DEPRECATED             |
 
| innodb_cmp_per_index_enabled                | OFF                    |
 
| innodb_commit_concurrency                   | 0                      |
 
| innodb_compression_algorithm                | zlib                   |
 
| innodb_compression_default                  | OFF                    |
 
| innodb_compression_failure_threshold_pct    | 5                      |
 
| innodb_compression_level                    | 6                      |
 
| innodb_compression_pad_pct_max              | 50                     |
 
| innodb_concurrency_tickets                  | 15000                  |
 
| innodb_corrupt_table_action                 | deprecated             |
 
| innodb_data_file_path                       | ibdata1:12M:autoextend |
 
| innodb_data_home_dir                        |                        |
 
| innodb_deadlock_detect                      | ON                     |
 
| innodb_default_encryption_key_id            | 1                      |
 
| innodb_default_row_format                   | dynamic                |
 
| innodb_defragment                           | OFF                    |
 
| innodb_defragment_fill_factor               | 0.900000               |
 
| innodb_defragment_fill_factor_n_recs        | 20                     |
 
| innodb_defragment_frequency                 | 40                     |
 
| innodb_defragment_n_pages                   | 7                      |
 
| innodb_defragment_stats_accuracy            | 0                      |
 
| innodb_disable_sort_file_cache              | OFF                    |
 
| innodb_disallow_writes                      | OFF                    |
 
| innodb_doublewrite                          | ON                     |
 
| innodb_empty_free_list_algorithm            | DEPRECATED             |
 
| innodb_encrypt_log                          | OFF                    |
 
| innodb_encrypt_tables                       | OFF                    |
 
| innodb_encryption_rotate_key_age            | 1                      |
 
| innodb_encryption_rotation_iops             | 100                    |
 
| innodb_encryption_threads                   | 0                      |
 
| innodb_fake_changes                         | OFF                    |
 
| innodb_fast_shutdown                        | 1                      |
 
| innodb_fatal_semaphore_wait_threshold       | 300                    |
 
| innodb_file_format                          | Barracuda              |
 
| innodb_file_format_check                    | ON                     |
 
| innodb_file_format_max                      | Barracuda              |
 
| innodb_file_per_table                       | ON                     |
 
| innodb_fill_factor                          | 100                    |
 
| innodb_flush_log_at_timeout                 | 1                      |
 
| innodb_flush_log_at_trx_commit              | 0                      |
 
| innodb_flush_method                         | O_DIRECT               |
 
| innodb_flush_neighbors                      | 1                      |
 
| innodb_flush_sync                           | ON                     |
 
| innodb_flushing_avg_loops                   | 30                     |
 
| innodb_force_load_corrupted                 | OFF                    |
 
| innodb_force_primary_key                    | OFF                    |
 
| innodb_force_recovery                       | 0                      |
 
| innodb_foreground_preflush                  | DEPRECATED             |
 
| innodb_ft_aux_table                         |                        |
 
| innodb_ft_cache_size                        | 8000000                |
 
| innodb_ft_enable_diag_print                 | OFF                    |
 
| innodb_ft_enable_stopword                   | ON                     |
 
| innodb_ft_max_token_size                    | 84                     |
 
| innodb_ft_min_token_size                    | 3                      |
 
| innodb_ft_num_word_optimize                 | 2000                   |
 
| innodb_ft_result_cache_limit                | 2000000000             |
 
| innodb_ft_server_stopword_table             |                        |
 
| innodb_ft_sort_pll_degree                   | 2                      |
 
| innodb_ft_total_cache_size                  | 640000000              |
 
| innodb_ft_user_stopword_table               |                        |
 
| innodb_idle_flush_pct                       | 100                    |
 
| innodb_immediate_scrub_data_uncompressed    | OFF                    |
 
| innodb_instrument_semaphores                | OFF                    |
 
| innodb_io_capacity                          | 40000                  |
 
| innodb_io_capacity_max                      | 80000                  |
 
| innodb_kill_idle_transaction                | 0                      |
 
| innodb_large_prefix                         | ON                     |
 
| innodb_lock_schedule_algorithm              | fcfs                   |
 
| innodb_lock_wait_timeout                    | 500                    |
 
| innodb_locking_fake_changes                 | OFF                    |
 
| innodb_locks_unsafe_for_binlog              | OFF                    |
 
| innodb_log_arch_dir                         |                        |
 
| innodb_log_arch_expire_sec                  | 0                      |
 
| innodb_log_archive                          | OFF                    |
 
| innodb_log_block_size                       | 0                      |
 
| innodb_log_buffer_size                      | 67108864               |
 
| innodb_log_checksum_algorithm               | DEPRECATED             |
 
| innodb_log_checksums                        | ON                     |
 
| innodb_log_compressed_pages                 | ON                     |
 
| innodb_log_file_size                        | 1073741824             |
 
| innodb_log_files_in_group                   | 2                      |
 
| innodb_log_group_home_dir                   | ./                     |
 
| innodb_log_optimize_ddl                     | ON                     |
 
| innodb_log_write_ahead_size                 | 8192                   |
 
| innodb_lru_scan_depth                       | 1024                   |
 
| innodb_max_bitmap_file_size                 | 0                      |
 
| innodb_max_changed_pages                    | 0                      |
 
| innodb_max_dirty_pages_pct                  | 50.000000              |
 
| innodb_max_dirty_pages_pct_lwm              | 0.000000               |
 
| innodb_max_purge_lag                        | 0                      |
 
| innodb_max_purge_lag_delay                  | 0                      |
 
| innodb_max_undo_log_size                    | 10485760               |
 
| innodb_mirrored_log_groups                  | 0                      |
 
| innodb_monitor_disable                      |                        |
 
| innodb_monitor_enable                       |                        |
 
| innodb_monitor_reset                        |                        |
 
| innodb_monitor_reset_all                    |                        |
 
| innodb_mtflush_threads                      | 8                      |
 
| innodb_numa_interleave                      | OFF                    |
 
| innodb_old_blocks_pct                       | 37                     |
 
| innodb_old_blocks_time                      | 1000                   |
 
| innodb_online_alter_log_max_size            | 134217728              |
 
| innodb_open_files                           | 1024                   |
 
| innodb_optimize_fulltext_only               | OFF                    |
 
| innodb_page_cleaners                        | 4                      |
 
| innodb_page_size                            | 16384                  |
 
| innodb_prefix_index_cluster_optimization    | OFF                    |
 
| innodb_print_all_deadlocks                  | OFF                    |
 
| innodb_purge_batch_size                     | 300                    |
 
| innodb_purge_rseg_truncate_frequency        | 128                    |
 
| innodb_purge_threads                        | 4                      |
 
| innodb_random_read_ahead                    | OFF                    |
 
| innodb_read_ahead_threshold                 | 56                     |
 
| innodb_read_io_threads                      | 4                      |
 
| innodb_read_only                            | OFF                    |
 
| innodb_replication_delay                    | 0                      |
 
| innodb_rollback_on_timeout                  | ON                     |
 
| innodb_rollback_segments                    | 128                    |
 
| innodb_sched_priority_cleaner               | 0                      |
 
| innodb_scrub_log                            | OFF                    |
 
| innodb_scrub_log_speed                      | 256                    |
 
| innodb_show_locks_held                      | 0                      |
 
| innodb_show_verbose_locks                   | 0                      |
 
| innodb_sort_buffer_size                     | 1048576                |
 
| innodb_spin_wait_delay                      | 6                      |
 
| innodb_stats_auto_recalc                    | ON                     |
 
| innodb_stats_include_delete_marked          | OFF                    |
 
| innodb_stats_method                         | nulls_equal            |
 
| innodb_stats_modified_counter               | 0                      |
 
| innodb_stats_on_metadata                    | OFF                    |
 
| innodb_stats_persistent                     | ON                     |
 
| innodb_stats_persistent_sample_pages        | 40                     |
 
| innodb_stats_sample_pages                   | 32                     |
 
| innodb_stats_traditional                    | ON                     |
 
| innodb_stats_transient_sample_pages         | 32                     |
 
| innodb_status_output                        | OFF                    |
 
| innodb_status_output_locks                  | OFF                    |
 
| innodb_strict_mode                          | ON                     |
 
| innodb_support_xa                           | ON                     |
 
| innodb_sync_array_size                      | 1                      |
 
| innodb_sync_spin_loops                      | 30                     |
 
| innodb_table_locks                          | ON                     |
 
| innodb_temp_data_file_path                  | ibtmp1:12M:autoextend  |
 
| innodb_thread_concurrency                   | 16                     |
 
| innodb_thread_sleep_delay                   | 0                      |
 
| innodb_tmpdir                               |                        |
 
| innodb_track_changed_pages                  | OFF                    |
 
| innodb_track_redo_log_now                   | OFF                    |
 
| innodb_undo_directory                       | ./                     |
 
| innodb_undo_log_truncate                    | OFF                    |
 
| innodb_undo_logs                            | 128                    |
 
| innodb_undo_tablespaces                     | 0                      |
 
| innodb_use_atomic_writes                    | ON                     |
 
| innodb_use_fallocate                        | OFF                    |
 
| innodb_use_global_flush_log_at_trx_commit   | OFF                    |
 
| innodb_use_mtflush                          | OFF                    |
 
| innodb_use_native_aio                       | ON                     |
 
| innodb_use_stacktrace                       | OFF                    |
 
| innodb_use_trim                             | ON                     |
 
| innodb_version                              | 5.7.23                 |
 
| innodb_write_io_threads                     | 4                      |
 
+---------------------------------------------+------------------------+
 
189 rows in set (0.00 sec)

Comment by Rick Pizzi [ 2018-12-04 ]

Added relevant log, sema.log.gz

Comment by Rick Pizzi [ 2018-12-07 ]

Another occurrence from 12 hours ago.
Stack dump follows.

 
 
   1565 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,add_to_active_threads,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    177 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,THD::add_status_to_global,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    146 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_insert_for_mysql,ha_innobase::write_row,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
    113 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,join_read_always_key,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     94 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     25 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
     10 libaio::??,LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_linux_handler,os_aio_handler,fil_aio_wait,io_handler_thread,start_thread,clone
 
      9 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,add_to_active_threads,cache_thread,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      8 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      7 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::write_row,handler::ha_write_row,write_record,mysql_insert,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      6 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,join_read_always_key,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      4 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,Rows_log_event::do_apply_event,Log_event::apply_event,wsrep_apply_events,wsrep_apply_cb,galera::TrxHandle::apply,apply_trx_ws,galera::ReplicatorSMM::apply_trx,galera::ReplicatorSMM::process_trx,galera::GcsActionSource::dispatch,galera::GcsActionSource::process,galera::ReplicatorSMM::async_recv,galera_recv,wsrep_replication_process,start_wsrep_THD,start_thread,clone
 
      4 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,srv_resume_thread,srv_worker_thread,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,lock_wait_suspend_thread,row_mysql_handle_errors,row_update_for_mysql,ha_innobase::update_row,handler::ha_update_row,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      3 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_flush_page_cleaner_worker,start_thread,clone
 
      3 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,QUICK_ROR_UNION_SELECT::reset,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::index_read_idx_map,handler::ha_index_read_idx_map,join_read_,join_read__table,make_join_statistics,JOIN::optimize_inner,JOIN::optimize,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,sp_instr_stmt::exec_core,sp_lex_keeper::reset_lex_and_exec_core,sp_instr_stmt::execute,sp_head::execute,sp_head::execute_procedure,do_execute_sp,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,ha_innobase::index_first,ha_innobase::rnd_next,handler::ha_rnd_next,rr_sequential,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      2 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,mysqld_list_processes,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,trx_start_low,trx_start_internal_low,dict_stats_fetch_from_ps,dict_stats_update,dict_stats_init,ha_innobase::open,handler::ha_open,open_table_from_share,open_table,open_and_process_table,open_tables,open_and_lock_tables,open_and_lock_tables,mysql_delete,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::view_open,trx_assign_read_view,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,MVCC::size,srv_printf_innodb_monitor,srv_monitor_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_wait_check_and_cancel,lock_wait_timeout_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_trx_handle_wait,kill_handlerton,plugin_foreach_with_mask,ha_kill_query,THD::awake,kill_one_thread,sql_kill,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,sync_array_wait_event,TTASEventMutex::enter,PolicyMutex<TTASEventMutex<GenericPolicy>,lock_table,row_search_mvcc,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,srv_resume_thread,srv_purge_coordinator_suspend,srv_purge_coordinator_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_resize_thread,start_thread,clone
 
      1 pthread_cond_wait,os_event::wait,os_event::wait_low,os_event_wait_low,buf_dump_thread,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,wsrep_rollback_process,start_wsrep_THD,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,thd_destructor_proxy,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,handle_slave_background,start_thread,clone
 
      1 pthread_cond_wait,inline_mysql_cond_wait,binlog_background_thread,start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,RecvBuf::front,gcomm_recv,core_msg_recv,gcs_core_recv,gcs_recv_thread,start_thread,clone
 
      1 pthread_cond_wait,gu::Lock::wait,galera::ServiceThd::thd_func,start_thread,clone
 
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,pc_sleep_if_needed,buf_flush_page_cleaner_coordinator,start_thread,clone
 
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,ib_wqueue_timedwait,fts_optimize_thread,start_thread,clone
 
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,dict_stats_thread,start_thread,clone
 
      1 pthread_cond_timedwait,inline_mysql_cond_timedwait,timer_handler,start_thread,clone
 
      1 pthread_cond_timedwait,inline_mysql_cond_timedwait,my_service_thread_sleep,ma_checkpoint_background,start_thread,clone
 
      1 poll,poll(poll2.h:46),vio_io_wait(poll2.h:46),vio_socket_io_wait,vio_read,my_real_read,my_net_read_packet_reallen,my_net_read_packet,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,sync_array_print_long_waits,srv_error_monitor_thread,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_master_sleep,srv_master_thread,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,handler::ha_index_read_map,handler::read_range_first,handler::multi_range_read_next,Mrr_simple_index_reader::get_next,DsMrr_impl::dsmrr_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,srv_conc_enter_innodb_with_atomics,ha_innobase::index_read,ha_innobase::index_first,ha_innobase::rnd_next,handler::ha_rnd_next,find_all_keys,filesort,create_sort_index,st_join_table::sort_table,join_init_read_record,sub_select,do_select,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 nanosleep,os_thread_sleep,btr_defragment_thread,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,unlink_not_visible_thd,unlink_thd,one_thread_per_connection_end,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,thd_query_safe,innobase_get_stmt_safe,fill_trx_row,fetch_data_into_cache_low,fetch_data_into_cache,trx_i_s_possibly_fetch_data_into_cache,trx_i_s_common_fill_table,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,THD::add_status_to_global,THD::~THD,THD::~THD,CONNECT::close_with_error,create_new_thread,handle_connections_sockets,mysqld_main,__libc_start_main,_start
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,st_select_lex_unit::exec,mysql_union,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,execute_show_status,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,execute_show_status,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 __lll_lock_wait,__GI___pthread_mutex_lock,inline_mysql_mutex_lock,calc_sum_of_all_status,fill_status,get_schema_tables_result,JOIN::exec_inner,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,wsrep_mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection,start_thread,clone
 
      1 epoll_wait,asio::detail::epoll_reactor::run(epoll_reactor.ipp:391),asio::detail::task_io_service::do_run_one(task_io_service.ipp:355),asio::detail::task_io_service::run(task_io_service.ipp:355),asio::io_service::run(task_io_service.ipp:355),gcomm::AsioProtonet::event_loop(task_io_service.ipp:355),GCommConn::run,GCommConn::run_fn,start_thread,clone
 
      1 do_sigwait,__sigwait,signal_hand,start_thread,clone

Comment by Marko Mäkelä [ 2018-12-11 ]

wlad and me made some analysis of the stack traces. I do not see anything obviously wrong in InnoDB, and he did not see anything obviously wrong on the SQL layer.
Given that the deployment involves Galera, and based on the fact that many ./mysql-test-run --suite=galera tests occasionally fail due to mutex violations (especially in the forced rollback of transactions), I would tend to believe that the hang could be caused by the wsrep patches or the Galera library.

Comment by Marko Mäkelä [ 2018-12-14 ]

Here is one regular (but non-deterministic) test crash that leads to me believe that the problem could be in Galera:

10.4 67e3d1ee93505e320bdb4343a8a8be827183a58e

 
CURRENT_TEST: galera.MW-286
 
mysqltest: At line 26: query 'ALTER TABLE t1 ADD PRIMARY KEY (f1)' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...
 
 
 
The result from queries just before the failure was:
 
connection node_1;
 
CREATE TABLE ten (f1 INTEGER) Engine=InnoDB;
 
INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB;
 
INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;
 
INSERT INTO t1 (f1) SELECT 100000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;;
 
connection node_2;
 
SET GLOBAL wsrep_desync = TRUE;
 
SET wsrep_on = FALSE;
 
ALTER TABLE t1 ADD PRIMARY KEY (f1);
 
…
 
Version: '10.4.1-MariaDB-debug-log'  socket: '/dev/shm/10.4c/mysql-test/var/tmp/63/mysqld.2.sock'  port: 17261  Source distribution
 
2018-12-14 10:52:58 0 [Note] WSREP: Member 1.0 (jyty) desyncs itself from group
 
2018-12-14 10:52:58 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 4)
 
mysqld: /mariadb/10.4c/storage/innobase/include/sync0policy.ic:64: void MutexDebug<TTASEventMutex<GenericPolicy> >::enter(const Mutex *, const char *, unsigned int) [Mutex = TTASEventMutex<GenericPolicy>]: Assertion `!is_owned()' failed.
 
…
 
#10 PolicyMutex<TTASEventMutex<GenericPolicy> >::enter (n_spins=30, n_delay=4, name=0x121cc79 "/mariadb/10.4c/storage/innobase/lock/lock0lock.cc", line=6398, this=<optimized out>) at /mariadb/10.4c/storage/innobase/include/ib0mutex.h:590
 
#11 lock_trx_handle_wait (trx=0x7f2316024208) at /mariadb/10.4c/storage/innobase/lock/lock0lock.cc:6398
 
#12 0x0000000000a990c2 in innobase_kill_query (thd=0x7f2298000cf8) at /mariadb/10.4c/storage/innobase/handler/ha_innodb.cc:5133
 
#13 0x00000000008c0eb7 in kill_handlerton (thd=0x7f2298000cf8, plugin=<optimized out>, level=0x7f2324103d8c) at /mariadb/10.4c/sql/handler.cc:813
 
#14 0x00000000006a62cf in plugin_foreach_with_mask (thd=0x7f2298000cf8, func=0x8c0e80 <kill_handlerton(THD*, st_plugin_int**, void*)>, type=<optimized out>, state_mask=8, arg=0x7f2324103d8c) at /mariadb/10.4c/sql/sql_plugin.cc:2432
 
#15 0x00000000008c0e47 in ha_kill_query (thd=0x7f2298000cf8, level=<optimized out>) at /mariadb/10.4c/sql/handler.cc:820
 
#16 0x000000000064481e in THD::awake_no_mutex (this=0x7f2298000cf8, state_to_set=<optimized out>) at /mariadb/10.4c/sql/sql_class.cc:1836
 
#17 0x000000000081bfda in THD::awake (this=0x7f2298000cf8, state_to_set=KILL_QUERY) at /mariadb/10.4c/sql/sql_class.h:3291
 
#18 wsrep_thd_awake (thd=0x7f2298000cf8, signal=<optimized out>) at /mariadb/10.4c/sql/wsrep_mysqld.cc:2635
 
#19 0x0000000000a95064 in wsrep_innobase_kill_one_trx (bf_thd_ptr=0x7f2308000cf8, bf_trx=<optimized out>, victim_trx=<optimized out>, signal=<optimized out>) at /mariadb/10.4c/storage/innobase/handler/ha_innodb.cc:18711
 
#20 0x0000000000a9b3f2 in wsrep_abort_transaction (bf_thd=<optimized out>, victim_thd=<optimized out>, signal=1 '\001') at /mariadb/10.4c/storage/innobase/handler/ha_innodb.cc:18787
 
#21 0x00000000008cd3fa in ha_abort_transaction (bf_thd=0x7f2308000cf8, victim_thd=0x7f2298000cf8, signal=1 '\001') at /mariadb/10.4c/sql/handler.cc:6564
 
#22 0x000000000082527b in wsrep_abort_thd (bf_thd_ptr=0x7f2308000cf8, victim_thd_ptr=0x7f2298000cf8, signal=1 '\001') at /mariadb/10.4c/sql/wsrep_thd.cc:655
 
#23 0x000000000081b4b1 in wsrep_grant_mdl_exception (requestor_ctx=<optimized out>, ticket=<optimized out>, key=<optimized out>) at /mariadb/10.4c/sql/wsrep_mysqld.cc:2006
 
#24 0x000000000079abcb in MDL_lock::can_grant_lock (this=0x7f2298107dd8, type_arg=<optimized out>, requestor_ctx=0x7f2308000e18, ignore_lock_priority=<optimized out>) at /mariadb/10.4c/sql/mdl.cc:1765
 
#25 0x000000000079b428 in MDL_context::try_acquire_lock_impl (this=0x7f2308000e18, mdl_request=0x7f23088d5da0, out_ticket=0x7f2324104180) at /mariadb/10.4c/sql/mdl.cc:2116
 
#26 0x000000000079b7c7 in MDL_context::acquire_lock (this=0x7f2308000e18, mdl_request=0x7f23088d5da0, lock_wait_timeout=86400) at /mariadb/10.4c/sql/mdl.cc:2263
 
#27 0x00000000006274eb in open_table_get_mdl_lock (thd=0x7f2308000cf8, ot_ctx=0x7f2324104ab0, mdl_request=0x7f23088d5da0, flags=0, mdl_ticket=0x7f2324104478) at /mariadb/10.4c/sql/sql_base.cc:1592
 
#28 0x0000000000626676 in open_table (thd=0x7f2308000cf8, table_list=0x7f23088d5958, ot_ctx=0x7f2324104ab0) at /mariadb/10.4c/sql/sql_base.cc:1852
 
#29 0x00000000006297df in open_and_process_table (thd=<optimized out>, lex=<optimized out>, tables=<optimized out>, counter=<optimized out>, flags=<optimized out>, prelocking_strategy=<optimized out>, ot_ctx=<optimized out>, has_prelocking_list=<optimized out>) at /mariadb/10.4c/sql/sql_base.cc:3724
 
#30 open_tables (thd=0x7f2308000cf8, options=..., start=0x7f2324104b48, counter=0x7f2324104b44, flags=0, prelocking_strategy=0x7f2324104c00) at /mariadb/10.4c/sql/sql_base.cc:4255
 
#31 0x000000000062be3b in open_and_lock_tables (thd=0x7f2308000cf8, options=..., tables=<optimized out>, derived=false, flags=0, prelocking_strategy=0x7f2324104c00) at /mariadb/10.4c/sql/sql_base.cc:5134
 
#32 0x00000000009d7d0f in open_and_lock_tables (thd=0x2, tables=0x0, derived=false, flags=0) at /mariadb/10.4c/sql/sql_base.h:507
 
#33 Rows_log_event::do_apply_event (this=0x7f230802c8a8, rgi=<optimized out>) at /mariadb/10.4c/sql/log_event.cc:11286
 
#34 0x00000000005eddfa in Log_event::apply_event (this=0x7f230802c8a8, rgi=0x7f23080229e0) at /mariadb/10.4c/sql/log_event.h:1481
 
#35 0x00000000008234e1 in wsrep_apply_events (thd=<optimized out>, events_buf=<optimized out>, buf_len=493589) at /mariadb/10.4c/sql/wsrep_applier.cc:166
 
#36 wsrep_apply_cb (ctx=<optimized out>, buf=0x7f23180c3128, buf_len=<optimized out>, flags=<optimized out>, meta=<optimized out>) at /mariadb/10.4c/sql/wsrep_applier.cc:269
 
#37 0x00007f232688323b in galera::TrxHandle::apply(void*, wsrep_cb_status (*)(void*, void const*, unsigned long, unsigned int, wsrep_trx_meta const*), wsrep_trx_meta const&) const () from /usr/lib/galera/libgalera_smm.so
 
#38 0x00007f23268c6203 in ?? () from /usr/lib/galera/libgalera_smm.so
 
#39 0x00007f23268c8ba8 in galera::ReplicatorSMM::apply_trx(void*, galera::TrxHandle*) () from /usr/lib/galera/libgalera_smm.so
 
#40 0x00007f23268cbede in galera::ReplicatorSMM::process_trx(void*, galera::TrxHandle*) () from /usr/lib/galera/libgalera_smm.so
 
#41 0x00007f23268a8288 in galera::GcsActionSource::dispatch(void*, gcs_action const&, bool&) () from /usr/lib/galera/libgalera_smm.so
 
#42 0x00007f23268a8ea8 in galera::GcsActionSource::process(void*, bool&) () from /usr/lib/galera/libgalera_smm.so
 
#43 0x00007f23268cc52d in galera::ReplicatorSMM::async_recv(void*) () from /usr/lib/galera/libgalera_smm.so
 
#44 0x00007f23268e284b in galera_recv () from /usr/lib/galera/libgalera_smm.so
 
#45 0x0000000000824904 in wsrep_replication_process (thd=0x7f2308000cf8) at /mariadb/10.4c/sql/wsrep_thd.cc:362
 
#46 0x000000000081b7fa in start_wsrep_THD (arg=0x8248b0 <wsrep_replication_process(THD*)>) at /mariadb/10.4c/sql/wsrep_mysqld.cc:2101
 
#47 0x00007f2328650fa3 in start_thread (arg=<optimized out>) at pthread_create.c:486
 
#48 0x00007f23274f188f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

This same problem should exist in 10.3 and likely also in 10.2, maybe even earlier major versions.

Comment by Roel Van de Paar [ 2020-08-14 ]

rpizzi are BACKUP LOCKs being used?

Comment by Rick Pizzi [ 2020-08-14 ]

No backup involved in this scenario

Comment by Julius Goryavsky [ 2023-06-26 ]

This issue has never reproduced since the MDEV-18464 fix

Generated at Thu Feb 08 08:33:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.