关于编译:故障分析-MySQL-57-使用临时表导致数据库-Crash

58次阅读

共计 8754 个字符，预计需要花费 22 分钟才能阅读完成。

作者：雷文霆

爱可生华东交付服务部 DBA 成员，次要负责 Mysql 故障解决及相干技术支持。喜好看书，电影。座右铭，每一个未曾起舞的日子，都是对生命的辜负。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

目录

  • 背景信息
  • 故障剖析
  • 问题复现
  • 测试日志
  • 测试论断
  • 参考链接

背景信息

在 MySQL5.7.30 主从读写拆散环境下，从库在某天呈现了 MySQL crash.

零碎侧: 监控显示该从库主机的内存和 CPU 资源使用率在故障前后均失常，磁盘 IO 有 2% 的 iowait（读写 200M/s），阐明故障前磁盘存在压力。

服务侧：slow-log 中记录了服务重启前，存在应用了长期表和文件排序的慢 SQL 语句。

Error-log 中记录了服务调用到 btr0btr.cc 文件 的 L2165 行，呈现了 err == DB_SUCCESS 报错。

0x7f2dd49d0700  InnoDB: Assertion failure in thread 139834817316608 in file btr0btr.cc line 2165
InnoDB: Failing assertion: err == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

故障剖析

在零碎侧排除了磁盘空间有余和内存不足的因素，服务侧狐疑是慢查问和 BUG 的起因，之后通过 ” btr0btr.cc” 关键字查找到了一个相似的 BUG。链接如下:

https://bugs.mysql.com/bug.ph…

报告的意思是，MySQL 在执行 btr_insert_on_non_leaf_level_func()函数时，写入长期表会导致带有断言的服务解体。

通过查看 btr0btr.cc 文件结尾的正文理解到的起因是：

此文件的调用机制是：对 b 树行操作或记录所做的所有更改。

L2165 行操作内容是：在解决插入到非叶级别的内容时，会查看每个级别的可用空间 (需保留 2 倍索引数高度的页空间)，如果在操作之前，叶决裂曾经开始，就很难撤销，只能通过解体进行前滚。 该 BUG 只会在 MySQL5.7 呈现

代码查问：https://github.com/mysql/mysq… (通过 Tags 标签抉择对应版本)

代码内容：https://github.com/mysql/mysq…

nserts a data tuple to a tree on a non-leaf level. It is assumed
that mtr holds an x-latch on the tree. */
void
btr_insert_on_non_leaf_level_func()
    ---
    ut_a(err == DB_SUCCESS);
    ---
        
其中 ut_a()
https://dev.mysql.com/doc/dev/mysql-server/latest/ut0dbg_8h.html#ae7aed983dfe98ac872b5a9915fa778fa:

查看数据库对于长期表的参数：

innodb_temp_data_file_path ibtmp1:12M:autoextend:max:20G

tmp_table_size 64M 和 max_heap_table_size 64M

正文：理论限度取二者中的较小者。会话级别的参数，对于 innodb_buffer_pool_size 不大且没有用到长期大数据量查问的状况，不倡议设置的过大，可能会导致内存溢出的状况。连接数 800+，64M 为推荐值

internal_tmp_disk_storage_engine InnoDB

正文：用于定义磁盘长期表的存储引擎。超出 InnoDB 行或列限度的磁盘外部长期表的查问会返回 Row size too large 或 Too many columns 谬误。解决办法是设置 internal_tmp_disk_storage_engine 为 MYISAM，咱们的 error-log 中无相干报错。初步排查阶段不倡议批改

created_tmp_disk_tables 2987733

created_tmp_tables 11049848

正文：当在内存或磁盘上创立外部长期表时，服务器会减少该 Created_tmp_tables值。在磁盘上创立外部长期表时，服务器会减少该 Created_tmp_disk_tables值。如果在磁盘上创立了太多外部长期表，请思考减少 tmp_table_size 和max_heap_table_size设置。从早上 10 点 36 分到 17 点产生较多长期表，联合业务忙碌状况，属于失常景象

小结: 通过下面的剖析，联合利用架构 (无奈降级到 MySQL8.0)。初步阶段是倡议 先优化 SQL 语句，缩小对长期表的应用，升高再次发生的概率。

问题复现

上文提到的 BUG 报告中，有个应用 MySQL Test Run 简称 MTR(MySQL 官网的自动化测试框架) 的测试用例。

下文将援用此测试用例，进行复现测试。

参数解释：

innodb_limit_optimistic_insert_debug 参数能够限度每个 B 树页面的记录数，在 MySQL 运行过程中动静设置此参数能够导致页决裂。

innodb_limit_optimistic_insert_debug
限度每个 B 树页面的记录数。默认值 0 示意不施加限度。仅当应用 CMake 选项编译调试反对时，需开启 DEBUG 选项。# 依赖
yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison openssl openssl-devel
tar -xvf mysql-boost-5.7.30.tar.gz

-- 编译装置 MySQL，因为须要设置 innodb_limit_optimistic_insert_debug 参数 -------
tar -xvf mysql-boost-5.7.30.tar.gz

# 非 BOOST 版本的 Mysql 源码包，须要指定 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost
cd mysql-5.7.30
cmake . -DCMAKE_INSTALL_PREFIX=/tools/mysql-test5.7.30 -DMYSQL_DATADIR=/tools/mysql-test5.7.30/data -DMYSQL_UNIX_ADDR=/tools/mysql-test5.7.30/mysql5.7.30.sock -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_SSL=system  -DWITH_BOOST=boost -DWITH_DEBUG=1 

make
make install

# Cmake 编译之后会在 DCMAKE_INSTALL_PREFIX 目录中生成 mysql-test 测试框架目录
/tools/mysql-test5.7.30/mysql-test/t
vim my0420.test 
cat my0420.test 
--source include/have_innodb.inc

--let $restart_parameters = "restart: --innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G --big-tables=1"
--source include/restart_mysqld.inc

SELECT @@innodb_temp_data_file_path;

drop function if exists func1;
delimiter |;
create function func1(x int) returns int deterministic
        begin
                declare z1, z2 int;
                set z1 = x;
                set z2 = z1 + 2;
                return z2;
        end|
delimiter ;|

create table t1 (a int, b varchar(20));
insert into t1 values(1, 'a'), (2, 'b'), (3, 'c'), (3, 'c'), (4, 'c');

SET GLOBAL innodb_limit_optimistic_insert_debug=4;
--let $i=1
while ($i <= 15) {
INSERT INTO t1 SELECT * FROM t1;
--inc $i
}

SELECT COUNT(*) FROM t1;

SET GLOBAL innodb_limit_optimistic_insert_debug=2;
select * from t1 order by func1(a);

进行测试,
/tools/mysql-test5.7.30/mysql-test
./mtr my0420.test

其中 select * from t1 order by func1(a); 会应用 Using temporary; Using filesort 和 业务 SQL 的执行打算统一

将 my0420.test 第二行新增 –internal_tmp_disk_storage_engine=MYISAM 参数后，服务不解体。

–let $restart_parameters = “restart: –innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:1G –internal_tmp_disk_storage_engine=MYISAM –big-tables=1″

将 my0420.test 第二行增大为 —innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:6G 参数后，服务不解体。

–let $restart_parameters = “restart: –innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:6G –big-tables=1”

big_tables如果启用，服务器将所有长期表存储在磁盘上而不是内存中。这能够避免须要大型长期表的操作的大多数谬误，但也会减慢内存表就足够的查问。most The table *tbl_name* is full errors forSELECToperations，如果 error-log 中呈现此报错，阐明 select 操作应用了大的磁盘长期表，不举荐启用。

(小提示，客户环境中时常会收到某张长期表 #sql_tbl_name is full 的告警邮件，须要思考是否能够优化 SQL 了)

测试日志

MTR 的执行逻辑为启动一个长期 MySQL 服务，并执行 t 目录中 my0420.test 文件的内容，执行后果默认会和 r 目录中的 result 同名文件 (也叫规范执行后果文件，个别会在正确的版本中生成) 进行比照，用于判断测试文件是否正确。

在 mysql-test 目录下：./mtr my0420.test  
-- 执行到以下语句时报错 --
SET GLOBAL innodb_limit_optimistic_insert_debug=2;
[100%] main.my0420                              [fail]
        Test ended at 2022-04-20 20:05:39

CURRENT_TEST: main.my0420
mysqltest: At line 32: query 'select * from t1 order by func1(a)' failed: 2013: Lost connection to MySQL server during query
safe_process[7080]: Child process: 7081, exit: 1

Server [mysqld.1 - pid: 7089, winpid: 7089, exit: 256] failed during test run
Server log from this test:

Lost connection，MySQL 服务已进行。之后打印了 error 信息。谬误同样呈现在 btr0btr.cc line 2165

Server log from this test:
----------SERVER LOG START-----------
2022-04-20T12:02:42.082135Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld (mysqld 5.7.30-debug-log) starting as process 7049 ...
2022-04-20T12:02:43.698812Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld: Shutdown complete
2022-04-20T12:02:45.051667Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld (mysqld 5.7.30-debug-log) starting as process 7090 ...
2022-04-20T12:02:45.262573Z 0 [Note] /tools/mysql-test5.7.30/bin/mysqld: ready for connections.
Version: '5.7.30-debug-log'  socket: '/tools/mysql-test5.7.30/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
2022-04-20 15:05:37 0x7fc298bc4700  InnoDB: Assertion failure in thread 140473762858752 in file btr0btr.cc line 2165
InnoDB: Failing assertion: err == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:05:37 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61093 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fc234005890): select * from t1 order by func1(a)
Connection ID (thread ID): 2
Status: NOT_KILLED

和客户环境的区别之处:

生产中的报错为 Query(7f3be00479d0) is an invalid pointer. 有效指针，相似磁盘空间有余的报错.

测试中的报错为 Query (7fc234005890): select * from t1 order by func1(a).

测试环境的堆栈信息:

https://github.com/mysql/mysq…

（上图中文是翻译的代码正文，可能会有些偏差谬误）

查看测试日志文件 error-log，默认会在当前目录生成 var 目录，其中蕴含 my.cnf 文件

/tools/mysql-test5.7.30/mysql-test/var/log/mysqld.1.err

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fc234005890): select * from t1 order by func1(a)
Connection ID (thread ID): 2
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file
safe_process[7089]: Child process: 7090, killed by signal: 6

对于编译装置 MySQL 的补充：

Cmake 编译之后会在 DCMAKE_INSTALL_PREFIX 目录中生成 mysql-test 测试框架目录，

不须要以下步骤（以下步骤在不须要 DEBUG 调试时应用）

1. 此包是 mysql-test-5.7.30-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.30-linux-glibc2.12-x86_64/ /tools/mysql-test

2. 将 mysql 安装包目录下的文件与 mtr 目录合并，mysql 安装包目录下为 basedir

cp -r /data/mysql/base/5.7.30 /tools/mysql-test

(插播生存日记，make 命令大概须要执行 1 个小时，刚好 18:27，又恰逢隔离，起身洗锅炸厨房了嗷)

测试论断

此 BUG 可能会呈现在 MySQL5.7 版本中

1. 测试中验证了数据库参数 innodb_temp_data_file_path 增大 max_file_size 后不会产生服务解体，如果业务 SQL 无奈进行优化时，能够增大此参数，可升高触发解体的概率。

2. 测试中验证了数据库参数 internal_tmp_disk_storage_engine=MYISAM 时不会产生服务解体，默认 INNODB

如果业务无奈降级到 8.0 时，能够动静调整此参数。

咱们倡议的变更程序是:

优化 SQL 语句 -> 增大 innodb_temp_data_file_path 参数的 max_file_size 值 -> 降级到 MySQL 8.0（应用会话长期表空间）-> 批改 internal_tmp_disk_storage_engine 参数。

其中 internal_tmp_disk_storage_engine 参数，集体不是很了解，是否真的要将默认值 INNODB 更改为 MYISAM。之后求教共事理解到，” 外部长期表不会被复制，不会有并发拜访，是能够思考应用 MYISAM 的 ”

再次感激嗷。

参考链接

BUG 报告:

https://bugs.mysql.com/bug.ph…
https://jira.percona.com/brow…

编译装置及参数阐明:

https://blog.csdn.net/iteye_6…
https://baijiahao.baidu.com/s…

https://dev.mysql.com/doc/ref…

MTR 文档:

https://dev.mysql.com/doc/dev…

数据库参数:

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…

https://dev.mysql.com/doc/ref…

正文完
编译
发表至： 编译
2022-05-13
 0
关于编译:第39问如何编译-MySQL-的调试版本
关于编译:手撸golang-行为型设计模式-解释器模式
关于编译:龙智宣布与Incredibuild建立战略合作伙伴关系
关于centos7:20200323-Centos7下当前最高版本python392源码编译安装方法