共计 3633 个字符，预计需要花费 10 分钟才能阅读完成。
本篇内容介绍了“MySQL 中 Bug 发现过程分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
使用的是 PXC 环境如下:
MySQL:5.7.18-15
wsrep:29.20
os:Red Hat Enterprise Linux Server release 6.5
image.png
操作系统层面基本看不出来任何负载：
image.png
image.png
所以 show processlist 的 state 只是一个状态值，它代表是代码某一段到某一段的执行阶段，下面是一个典型的
select 的状态切换流程。但是要确认问题，有时候光靠这个是不够的。
T@2: | THD::enter_stage: starting /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/socket_connection.cc:100
T@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:121
T@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:151
T@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:494
T@2: | | | | | | THD::enter_stage: executing /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:119
T@2: | | | | | | THD::enter_stage: Sending data /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_executor.cc:195
T@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:5855
T@2: | | THD::enter_stage: cleaning up /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1884三、详细的分析 pstack
因为 pstack 日志太长了。我就不贴了。详细的分析 pstack 日志在开头给出的 bug 连接。其实要在冗长的 pstack 中找到有用的信息和合理的解释是一个困难的过程，因为源码能力非常有限，某些时候只能通过搜索临界区来确认问题。下面是我分析的结果，也是提交 bug 给出了的：
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_thd
Thread 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_sys
a lot of Thread wait when call function THD::release_resources at mutex LOCK_thd_data
a 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简单的说我发现有多个线程获取 mutex 近乎出现环状，但是其中一环没有找到。最终 percona 恢复如下：
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.“MySQL 中 Bug 发现过程分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
