建站
咨詢
售后
建站咨詢
新聞中心
本文主要通過(guò)一個(gè)bug來(lái)記錄一下如何分析一個(gè)MySQL bug的崩潰信息。
版本:Percona 5.7.17-11
10年積累的網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì)經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問(wèn)題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站制作后付款的網(wǎng)站建設(shè)流程，更有烏當(dāng)免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。
一、數(shù)據(jù)庫(kù)重啟日志分析
terminate called after throwing an instance of 'std::out_of_range' what(): vector::_M_range_check 04:10:09 UTC - mysqld got signal 6 ; mysqld got signal 6 ; ...... 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 = 0 thread_stack 0x40000 /dbdata/mysql3306/bin/mysqld(my_print_stacktrace+0x35)[0xf3e175] /dbdata/mysql3306/bin/mysqld(handle_fatal_signal+0x4b4)[0x7c3b94] /lib64/libpthread.so.0(+0xf7e0)[0x7f79f28e87e0] /lib64/libc.so.6(gsignal+0x35)[0x7f79f137d495] /lib64/libc.so.6(abort+0x175)[0x7f79f137ec75] /usr/lib64/libstdc++.so.6(_ZN9__gnu_cxx27__verbose_terminate_handlerEv+0x12d)[0x7f79f1c37a8d] /usr/lib64/libstdc++.so.6(+0xbcbe6)[0x7f79f1c35be6] /usr/lib64/libstdc++.so.6(+0xbcc13)[0x7f79f1c35c13] /usr/lib64/libstdc++.so.6(+0xbcd32)[0x7f79f1c35d32] /usr/lib64/libstdc++.so.6(_ZSt20__throw_out_of_rangePKc+0x67)[0x7f79f1bdadb7] /dbdata/mysql3306/bin/mysqld[0x11d8f15] /dbdata/mysql3306/bin/mysqld[0x11d99d5] /dbdata/mysql3306/bin/mysqld(_Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t+0x9dc)[0x11de0cc] /dbdata/mysql3306/bin/mysqld(dict_stats_thread+0x4f2)[0x11e0512] /lib64/libpthread.so.0(+0x7aa1)[0x7f79f28e0aa1] /lib64/libc.so.6(clone+0x6d)[0x7f79f1433bcd] You may download the Percona Server operations manual by visiting http://www.percona.com/software/percona-server/. You may find information in the manual which will help you identify the cause of the crash.
這部分是數(shù)據(jù)庫(kù)崩潰的時(shí)候的棧幀，因?yàn)槭盏降氖切盘?hào)6 SIGABRT，只要捕獲信號(hào)后改變其行為即可。這部分在MySQL官方文檔中叫做Stack Trace，參考：
28.5.1.5 Using a Stack Trace
實(shí)際上在這里我們已經(jīng)可以看到大約是統(tǒng)計(jì)數(shù)據(jù)收集的問(wèn)題，因?yàn)槲覀兛吹搅薲ict_stats_thread，這是統(tǒng)計(jì)收集線程的回調(diào)函數(shù)。
二、生成更加可視化的Stack Trace
1、通過(guò)Stack Trace獲得內(nèi)存地址獲取如下：
[0xf3e175] [0x7c3b94] [0x7f79f28e87e0] [0x7f79f137d495] [0x7f79f137ec75] [0x7f79f1c37a8d] [0x7f79f1c35be6] [0x7f79f1c35c13] [0x7f79f1c35d32] [0x7f79f1bdadb7] [0x11d8f15] [0x11d99d5] [0x11de0cc] [0x11e0512] [0x7f79f28e0aa1] [0x7f79f1433bcd]2、將這些地址放入一個(gè)文件
如：vi /tmp/err0222.log放入即可
3、通nm命令獲取庫(kù)文件鏈接文件如：nm -D -n ./mysqld > /tmp/mysqld.sym
4、使用mysql工具resolve_stack_dump得到輸出如下：
[root@dyzsdb2 bin]# ./resolve_stack_dump -s /tmp/mysqld.sym -n /tmp/err0222.log | c++filt
0xf3e175 my_print_stacktrace + 53
0x7c3b94 handle_fatal_signal + 1204
0x7f79f28e87e0 _end + -258115144
0x7f79f137d495 _end + -280574355
0x7f79f137ec75 _end + -280568243
0x7f79f1c37a8d _end + -271422363
0x7f79f1c35be6 _end + -271430210
0x7f79f1c35c13 _end + -271430165
0x7f79f1c35d32 _end + -271429878
0x7f79f1bdadb7 _end + -271802481
0x11d8f15 dict_stats_analyze_index_for_n_prefix(dict_index_t*, unsigned long, std::vector> const*, n_diff_data_t*, mtr_t*) + 4949
0x11d99d5 dict_stats_analyze_index(dict_index_t*) + 2693
0x11de0cc dict_stats_update(dict_table_t*, dict_stats_upd_option_t) + 2524
0x11e0512 dict_stats_thread + 1266 0x7f79f28e0aa1 _end + -258147207
0x7f79f1433bcd _end + -279827035
實(shí)際上到這里通過(guò)函數(shù)的調(diào)用我們可以發(fā)現(xiàn)是統(tǒng)計(jì)數(shù)據(jù)收集出現(xiàn)了問(wèn)題。
三、通過(guò)官方網(wǎng)站查詢Bug- https://bugs.launchpad.net/percona-server
- https://bugs.mysql.com/
在報(bào)錯(cuò)信息中提起比較代表性的信息在官方網(wǎng)站中進(jìn)行搜索通過(guò)在percona中查看發(fā)現(xiàn)本bug由上游MySQL代碼造成BUG號(hào)：Bug #84940
并且發(fā)現(xiàn)在5.7.18中得到修復(fù)同時(shí)給出了內(nèi)部BUG號(hào)如下：
[10 Feb 2017 8:12] Shane Bester Oli, Umesh, this would be same as internal: Bug 24585978 - INNODB: ASSERTION TOTAL_RECS > 0 FAILURE IN FILE DICT0STATS.CC
四、查詢Bug到底修改了什么地方
這里請(qǐng)教了阿里的印風(fēng)兄才知道怎么查看，首先拿到了內(nèi)部bug號(hào)：24585978
然后在git的commit log中搜索得到
git --no-pager log >/root/commitlog
vi /root/commitlog 找到commit號(hào)為：
29acdaaaeef9afe32b42785f1da3d79d56ed7e59
如下是這個(gè)bug的修復(fù)地方：
commit 29acdaaaeef9afe32b42785f1da3d79d56ed7e59
Author: Thirunarayanan Balathandayuthapani
Date: Wed Feb 8 12:00:52 2017 +0530 Bug #24585978 INNODB: ASSERTION TOTAL_RECS > 0 FAILURE
IN FILE DICT0STATS.CC
Analysis:
========
There was missing bracket for IF conditon in dict_stats_analyze_index_level() and it leads to wrong result.
Fix:
====
Fix the IF condition in dict_stats_analyze_index_level() so that it satisfied
the if condtion only if level is zero.
Reviewed-by : Jimmy Yang
diff --git a/storage/innobase/dict/dict0stats.cc b/storage/innobase/dict/dict0stats.cc
index 3494070..55a2626 100644 --- a/storage/innobase/dict/dict0stats.cc
+++ b/storage/innobase/dict/dict0stats.cc
@@ -1099,10 +1099,10 @@ dict_stats_analyze_index_level(
leaf-level delete marks because delete marks on
non-leaf level do not make sense. */
- if (level == 0 && srv_stats_include_delete_marked? 0:
+ if (level == 0 && (srv_stats_include_delete_marked ? 0:
rec_get_deleted_flag(
rec,
- page_is_comp(btr_pcur_get_page(&pcur)))) {
+ page_is_comp(btr_pcur_get_page(&pcur))))) { if (rec_is_last_on_page
&& !prev_rec_is_copied
五、為什么這么修改
這里是我的淺顯的分析，不對(duì)的地方的還請(qǐng)見(jiàn)諒。
我們發(fā)現(xiàn)這里實(shí)際上修改就是多了一個(gè)括號(hào)而已，但是意義是相當(dāng)重要的。
if (level == 0 && srv_stats_include_delete_marked ? 0:
rec_get_deleted_flag(
rec,
page_is_comp(btr_pcur_get_page(&pcur))))
修改為了 if (level == 0 && (srv_stats_include_delete_marked ? 0:
rec_get_deleted_flag(
rec,
page_is_comp(btr_pcur_get_page(&pcur))))) 修改前：
如果level != 0 不管innodb_stats_include_delete_marked參數(shù)如何設(shè)置必然觸發(fā)判斷是否存在del_flag,然后通過(guò)設(shè)置偏移量的方式 跳過(guò)這行，但是隨后的(*total_recs)++; 將不會(huì)觸發(fā)，極端情況下可能為0。
而在上層代碼dict_stats_analyze_index中的found_level:地方實(shí)際上是需要非葉子結(jié)點(diǎn)行數(shù)不為0的如下：
/* if any of these is 0 then there is exactly one page in the
B-tree and it is empty and we should have done full scan and should not be here */
ut_ad(total_recs > 0);
ut_ad(n_diff_on_level[n_prefix - 1] > 0);
六、如何規(guī)避
在官網(wǎng)查看的時(shí)候有如下方式可以規(guī)避這個(gè)Bug
- 升級(jí)到5.7.18
- 設(shè)置參數(shù)
innodb-stats-persistent = 0
innodb-stats-transient-sample-pages = 20
innodb-stats-auto-recalc = 0
設(shè)置這些參數(shù)后實(shí)際上是使用的老的非固化的統(tǒng)計(jì)數(shù)據(jù)收集的方式，而不會(huì)通過(guò)線程dict_stats_thread收集下面是邏輯片段位于row_update_statistics_if_needed中如下：
if (dict_stats_is_persistent_enabled(table)) { //參數(shù)innodb-stats-persistent 作用 if (counter > n_rows / 10 /* 10% */ && dict_stats_auto_recalc_is_enabled(table)) {//參數(shù)innodb-stats-auto-recalc 作用 dict_stats_recalc_pool_add(table);
table->stat_modified_counter = 0;
} return;
} /* Calculate new statistics if 1 / 16 of table has been modified
since the last time a statistics batch was run.
We calculate statistics at most every 16th round, since we may have
a counter table which is very small and updated very often. */ if (counter > 16 + n_rows / 16 /* 6.25% */) {
ut_ad(!mutex_own(&dict_sys->mutex)); /* this will reset table->stat_modified_counter to 0 */ dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);
} 這樣做的話肯定不會(huì)調(diào)用到觸發(fā)bug的函數(shù)，有興趣的可以看看dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);的邏輯。實(shí)際上使用的是老的方式斷點(diǎn)可以打在btr_estimate_number_of_different_key_vals函數(shù)上。
作者微信：
新聞名稱：MySQLBug導(dǎo)致異常宕機(jī)的分析流程
文章地址：http://fisionsoft.com.cn/article/jggops.html
