| Bug #92274 | Question of mysql 8.0 sysbench oltp_point_select test | ||
|---|---|---|---|
| Submitted: | 3 Sep 2018 13:03 | Modified: | 15 Jun 2020 12:22 |
| Reporter: | victor zheng | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S7 (Test Cases) |
| Version: | 8.0.12 | OS: | Debian (8) |
| Assigned to: | Dimitri KRAVTCHUK | CPU Architecture: | x86 (40 core) |
[3 Sep 2018 13:03]
victor zheng
[3 Sep 2018 13:05]
victor zheng
show engine innodb status ouput
Attachment: show_engine_innodb_status.txt (text/plain), 10.67 KiB.
[3 Sep 2018 14:06]
victor zheng
in my opnion, the sephores in `show engine innodb status' is strange. file read io and buffer pool read/s
[4 Nov 2018 16:52]
Mark Callaghan
Can you add PMP output to show the thread stacks when the mutex contention occurs with 8.0? https://poormansprofiler.org/
[6 Nov 2018 8:14]
victor zheng
16 pthread_cond_wait@@GLIBC_2.3.2,native_cond_wait,<Per_thread_connection_handler::LOCK_thread_cache>,,<Per_thread_connection_handler::LOCK_thread_cache>,,at,handle_connection,pfs_spawn_thread,start_thread,clone
7 ??,??,??,free_root,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
6 ??,??,??,my_realloc,String::mem_realloc,String::replace,String::replace,Prepared_statement::insert_params,Prepared_statement::set_parameters,Prepared_statement::execute_loop,mysqld_stmt_execute,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
6 ??,??,??,mem_free,__in_chrg=<optimized,stmt_id=<optimized,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
6 ??,??,??,free_root,Prepared_statement::~Prepared_statement,Prepared_statement::~Prepared_statement,my_hash_delete,Prepared_statement_map::erase,Prepared_statement::deallocate,mysqld_stmt_close,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
5 ??,??,malloc,my_raw_malloc,size=size@entry=8160,,alloc_root,Item::operator,MYSQLparse,parse_sql,Prepared_statement::prepare,mysqld_stmt_prepare,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
4 ??,??,malloc,my_raw_malloc,size=size@entry=8208,,init_alloc_root,init_sql_alloc,Prepared_statement::Prepared_statement,mysqld_stmt_prepare,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
3 pthread_cond_wait@@GLIBC_2.3.2,wait,out>,,reset_sig_count=reset_sig_count@entry=0),srv_worker_thread,start_thread,clone
3 pthread_cond_wait@@GLIBC_2.3.2,wait,out>,,reset_sig_count=reset_sig_count@entry=0),buf_flush_page_cleaner_worker,start_thread,clone
3 ??,??,malloc,operator,mysqld_stmt_prepare,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
2 ??,??,malloc,my_raw_malloc,size=32,,get_lock_data,mysql_unlock_some_tables,JOIN::optimize,st_select_lex::optimize,handle_query,execute_sqlcom_select,mysql_execute_command,Prepared_statement::execute,Prepared_statement::execute_loop,mysqld_stmt_execute,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
1 st_select_lex::prepare,st_select_lex_unit::prepare,mysql_test_select,at,query_str=query_str@entry=0x7fb4b1b9c761,mysqld_stmt_prepare,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
1 str=0x7fb72f5531f0),Item::send,THD::send_result_set_row,Query_result_send::send_data,end_send,do_select,at,handle_query,execute_sqlcom_select,mysql_execute_command,Prepared_statement::execute,Prepared_statement::execute_loop,mysqld_stmt_execute,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone
1 ??,sigwaitinfo,timer_notify_thread_func,pfs_spawn_thread,start_thread,clone
[6 Nov 2018 17:24]
Mark Callaghan
That shows most threads doing some sort of malloc/free/realloc and nothing in InnoDB. Regardless, I appreciate all of the details you provided in this report.
[21 Nov 2019 16:21]
Dimitri KRAVTCHUK
I afraid the problem is common with other issues you logged here before, but which were finally related to SSL config..
so, to clarify this all, may you, please :
1) replay the same, but with ssl=0 on both sides ?
2) if the problem is still here, then which malloc lib do you use ? may you replay with jemalloc ?
3) if even after that the problem is still here, may you try to find who is involving this _raw_spin_lock ? ("perf record -g -a" will collect all the needed stacks)
side note: are your threads reconnecting after each point-select query, or connections are remaining persistent ?
Rgds,
-Dimitri
[15 Jun 2020 12:22]
MySQL Verification Team
We can't repeat it with latest 8.0.21.
