MySQL中Bug发现过程分析

2025-03-10 技术教程

本篇内容介绍了“MySQL中Bug发现过程分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

使用的是PXC环境如下:
MySQL:5.7.18-15
wsrep:29.20
os:Red Hat Enterprise Linux Server release 6.5

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:100T@2:|||||THD::enter_stage:'checkingpermissions'/root/mysql5.7.14/percona-server-5.7.14-7/sql/auth/sql_authorization.cc:843T@2:||||||THD::enter_stage:'Openingtables'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:5719T@2:|||||THD::enter_stage:'init'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_select.cc:121T@2:|||||||THD::enter_stage:'Systemlock'/root/mysql5.7.14/percona-server-5.7.14-7/sql/lock.cc:321T@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:386T@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:'Sendingdata'/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:199T@2:||||THD::enter_stage:'queryend'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5174T@2:||||THD::enter_stage:'closingtables'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5252T@2:|||THD::enter_stage:'freeingitems'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5855T@2:||THD::enter_stage:'cleaningup'/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1884三、详细的分析pstack

因为pstack日志太长了。我就不贴了。详细的分析pstack日志在开头给出的bug连接。其实要在冗长的pstack中找到有用的信息和合理的解释是一个困难的过程，因为源码能力非常有限，某些时候只能通过搜索临界区来确认问题。下面是我分析的结果，也是提交bug给出了的：

IusepstacktoreviewstackdiscoverDeadlockAnalyzepstackifindsomeproblem:Thread56:lock:trx_sys(whenparameterwsrep_log_conflicts=ONlock0lock.cc2281line)requisite:LOCK_wsrep_thdThread9:lock:LOCK_thd_list(mysql_thread_manager.cc339line)requisite:LOCK_thd_data(sql_parse.h175line)Thread26:lock:LOCK_thd_data(inPFS_status_variable_cache::do_materialize_allafterPFS_status_variable_cache::manifestreleaseLOCK_thd_data,buthang)requisite:trx_sys->mutex(srv0srv.cc1703line)alotofThreadwaitwhencallfunctiontrx_allocate_for_mysqlatmutextrx_sysalotofThreadwaitwhencallfunctionTHD::release_resourcesatmutexLOCK_thd_dataalotofThreadwaitwhencallfunctionGlobal_THD_manager::add_thdatmutexLOCK_thd_listandanyothermutexwait!!butInotfindwhichthreadholdLOCK_wsrep_thdmutex.Nowwedofollowthingshopetoresolvethisproblem:1、wsrep_log_conflicts=off2、SETglobaloptimizer_switch='materialization=off';3、athighloadtimenotexecutesql'show[global]status/select*fromperformance_schema.global_status'

简单的说我发现有多个线程获取mutex近乎出现环状，但是其中一环没有找到。最终percona恢复如下：

Yourproblemsoundsquitesimilartoonementionedhere:https://jira.percona.com/browse/PXC-877Saidreleasefixtheissuehttps://www.percona.com/blog/2018/01/26/percona-xtradb-cluster-5-7-20-29-24-is-now-available/Youmaywanttoconsideranupgradetolatestonethoughwhichhasmorefixes5.7.21.

“MySQL中Bug发现过程分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！