Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Bug Discovery Process Analysis in MySQL". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

The PXC environment used is as follows:MySQL:5.7.18-15wsrep:29.20os:Red Hat Enterprise Linux Server release 6.5

image.png

At the operating system level, there is basically no load to see:

image.png

image.png

So the state of show processlist is just a state value, which represents the execution stage from a certain section of code to a certain section. Here is a typical state transition flow of select. But to identify problems, sometimes that alone is not enough.

T@2: | THD::enter_stage: 'starting' /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:100T@2: ||||| THD::enter_stage: 'checking permissions' /root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authorization.cc:843 T@2: |||||| THD::enter_stage: 'Opening tables' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5719 T@2: ||||| THD::enter_stage: 'init' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:121T@2: ||||||| THD::enter_stage: 'System lock' /root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:321 T@2: ||||||| THD::enter_stage: 'optimizing' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:151T@2: ||||||| THD::enter_stage: 'statistics' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:386 T@2: ||||||| THD::enter_stage: 'preparing' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_optimizer.cc:494T@2: |||||| THD::enter_stage: 'executing' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:119T@2: |||||| THD::enter_stage: 'Sending data' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:195T@2: ||||| THD::enter_stage: 'end' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:199 T@2: |||| THD::enter_stage: 'query end' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5174 T@2: |||| THD::enter_stage: 'closing tables' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5252 T@2: ||| THD::enter_stage: 'freeing items' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5855T@2: ||THD::enter_stage: 'cleaning up' /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1884 III. Detailed analysis of pstack

Because pstack logs are too long. I'm not gonna post it. Detailed analysis of the bug links given at the beginning of the pstack log. Finding useful information and reasonable explanations in lengthy pstacks is a difficult process because source code capabilities are very limited and sometimes problems can only be identified by searching critical areas. Here are the results of my analysis, which is also given by submitting bugs:

I use pstack to review stack discover Dead lock Analyze pstack i find some problem: Thread 56: lock:trx_sys (when parameter wsrep_log_conflicts=ON lock0lock.cc 2281 line) requisite:LOCK_wsrep_thdThread 9: lock: LOCK_thd_list (mysql_thread_manager.cc 339 line) requisite:LOCK_thd_data (sql_parse.h 175 line)Thread 26: lock: LOCK_thd_data (in PFS_status_variable_cache::do_materialize_all after PFS_status_variable_cache::manifest release LOCK_thd_data ,but hang) requisite:trx_sys->mutex (srv0srv.cc 1703 line)a lot of Thread wait when call function trx_allocate_for_mysql at mutex trx_sysa lot of Thread wait when call function THD::release_resources at mutex LOCK_thd_dataa lot of Thread wait when call function Global_THD_manager::add_thd at mutex LOCK_thd_list and any other mutex wait!! but I not find which thread hold LOCK_wsrep_thd mutex.Now we do follow things hope to resolve this problem: 1、wsrep_log_conflicts=off 2、SET global optimizer_switch = 'materialization=off'; 3、at high load time not execute sql 'show [global] status/select * from performance_schema.global_status'

To put it simply, I found multiple threads getting mutexes that appeared almost circular, but one of the rings was not found. The final percona recovery is as follows:

Your problem sounds quite similar to one mentioned here: https://jira.percona.com/browse/PXC-877 Said release fix the issue https://www.percona.com/blog/2018/01/26/percona-xtradb-cluster-5-7-20-29-24-is-now-available/ You may want to consider an upgrade to latest one though which has more fixes 5.7.21. "Bug discovery process analysis in MySQL" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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