MySQL案例-mysqld got signal 11(补充)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: -------------------------------------------------------------------------------------------------正文-----------------------------------...
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

背景:
MySQL-5.7.12, debian 8核16G虚拟机, 业务方反馈在某一个时间点, 出现了大量的数据库报错, 之后恢复正常;

场景:
开发查看日志后, 发现在某个时间点, 应用断开了所有与数据库的连接, 几秒钟以后就恢复了;
同时监控系统的内存使用率出现了异常的骤降;


3min之后收到了报警系统的信息, 内存使用率82%;

分析:
第一时间的判断是网络的问题造成了应用层的连接断开了, 但是这种内存使用率骤降的现象不会是网络造成的;
查看MySQL的日志, 发现MySQL实例发生了crash, 相关的报错信息如下:

点击(此处)折叠或打开

  1. 07:42:44 UTC - mysqld got signal 11 ;
  2. This could be because you hit a bug. It is also possible that this binary
  3. or one of the libraries it was linked against is corrupt, improperly built,
  4. or misconfigured. This error can also be caused by malfunctioning hardware.
  5. Attempting to collect some information that could help diagnose the problem.
  6. As this is a crash and something is definitely wrong, the information
  7. collection process might fail.
  8. key_buffer_size=8388608
  9. read_buffer_size=16777216
  10. max_used_connections=29
  11. max_threads=5000
  12. thread_count=32
  13. connection_count=22
  14. It is possible that mysqld could use up to
  15. key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 245834871 K bytes of memory
  16. Hope that is ok; if not, decrease some variables in the equation.
  17. Thread pointer: 0x7f607c0072c0
  18. Attempting backtrace. You can use the following information to find out
  19. where mysqld died. If you see no messages after this, something went
  20. terribly wrong...
  21. stack_bottom = 7f6141b36e80 thread_stack 0x40000
  22. /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0xe77fec]
  23. /usr/sbin/mysqld(handle_fatal_signal+0x459)[0x7a7019]
  24. /lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7f643257a8d0]
  25. /usr/sbin/mysqld(_ZN16Partition_helper25handle_ordered_index_scanEPh+0x5c)[0xbbabec]
  26. /usr/sbin/mysqld(_ZN7handler13ha_index_lastEPh+0x1b0)[0x7f4410]
  27. /usr/sbin/mysqld(_Z14join_read_lastP7QEP_TAB+0x65)[0xc1f605]
  28. /usr/sbin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x11b)[0xc25e4b]
  29. /usr/sbin/mysqld(_ZN4JOIN4execEv+0x3b8)[0xc1ea78]
  30. /usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x238)[0xc8e408]
  31. /usr/sbin/mysqld[0x770ccf]
  32. /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x3403)[0xc51103]
  33. /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xc531bd]
  34. /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x817)[0xc53a47]
  35. /usr/sbin/mysqld(_Z10do_commandP3THD+0x18f)[0xc54faf]
  36. /usr/sbin/mysqld(handle_connection+0x278)[0xd108d8]
  37. /usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xe90784]
  38. /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f64325730a4]
  39. /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f6430e1b87d]
  40. Trying to get some variables.
  41. Some pointers may be invalid and cause the dump to abort.
  42. Query (7f607c015ad0): select * from test where time>='2016-07-29 00:00:00' and time<='2016-07-29 23:59:59' and tag in (2,3,6) order by id desc limit 2000
  43. Connection ID (thread ID): 138760
  44. Status: NOT_KILLED
  45. The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  46. information that should help you find out what is causing the crash.
  47. 2016-07-29T07:42:45.661724Z mysqld_safe Number of processes running now: 0
  48. 2016-07-29T07:42:45.664516Z mysqld_safe mysqld restarted
  49. 2016-07-29T15:42:45.991109+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.12-log) starting as process 8367 ...

首先是第一部分的信息:

点击(此处)折叠或打开

  1. mysqld got signal 11 ;
通过perror命令(感谢@杨奇龙的场外援助..._(:з」∠)_... )看到ErrorCode的信息:

点击(此处)折叠或打开

  1. Resource temporarily unavailable
代表MySQL发现某一项资源临时不可用, 应该是资源耗尽 or 申请失败等情况;

然后是第二部分信息:

点击(此处)折叠或打开

  1. It is possible that mysqld could use up to
  2. key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 245834871 K bytes of memory
这一段计算了当前配置下, 需要的最大内存数, 大概换 算了一下, 是234G;

这么高, 明显是有问题的, 联想到82%内存使用率的报警信息, 推测是内存耗尽造成的;

max_used_connections来算一下使用的内存的话,有约1.5G;

加上BP的9.6G, 有11G了, 算上MySQL本身占用的 一部分内存, 确实达到了比较高的程度;

但是看了一下kernel和message, 都没有发现系统出现OOM的日志, 应该不是系统kill的;


再看看堆栈相关的信息, 在里面记录了MySQL crash时的状态;

点击(此处)折叠或打开

  1. stack_bottom = 7f6141b36e80 thread_stack 0x40000
  2. /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0xe77fec]
  3. /usr/sbin/mysqld(handle_fatal_signal+0x459)[0x7a7019]
  4. /lib/x86_64-linux-gnu/libpthread.so.0(+0xf8d0)[0x7f643257a8d0]
  5. /usr/sbin/mysqld(_ZN16Partition_helper25handle_ordered_index_scanEPh+0x5c)[0xbbabec]
  6. /usr/sbin/mysqld(_ZN7handler13ha_index_lastEPh+0x1b0)[0x7f4410]
  7. /usr/sbin/mysqld(_Z14join_read_lastP7QEP_TAB+0x65)[0xc1f605]
  8. /usr/sbin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x11b)[0xc25e4b]
  9. /usr/sbin/mysqld(_ZN4JOIN4execEv+0x3b8)[0xc1ea78]
  10. /usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x238)[0xc8e408]
  11. /usr/sbin/mysqld[0x770ccf]
  12. /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x3403)[0xc51103]
  13. /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3ad)[0xc531bd]
  14. /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x817)[0xc53a47]
  15. /usr/sbin/mysqld(_Z10do_commandP3THD+0x18f)[0xc54faf]
  16. /usr/sbin/mysqld(handle_connection+0x278)[0xd108d8]
  17. /usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0xe90784]
  18. /lib/x86_64-linux-gnu/libpthread.so.0(+0x80a4)[0x7f64325730a4]
  19. /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f6430e1b87d]
从红字等地方的信息, 可以推断出当时MySQL是正在执行查询, 这些查询中 有join, 也有subquery , 且查询的表包含了分区表;

可以预料到在crash的时候, MySQL执行这些语句时 肯定 需要申请一部分join用的buffer, 同时子查询也会建立临时表, 都需要占用内存空间 ;

同时还有分区表的使用, 看了一下当时候分区表的大小:


发现当天的数据超过了BP的大小, 且用到分区表的查询走的全表扫描, 并且还有order by, 会用到sort的buffer, 且由于全表扫描的数据很多, 这个buffer有可能是需要申请满的 ;

综合这些信息,  基本确认是内存耗尽造成了MySQL crash;

那么根据堆栈的信息尝试 还原crash时的场景:

在内存占用率很高的情况下, MySQL thread在执行较大表的查询时, 无法再申请到足够的内存(sort的buffer, join的buffer等), 因此发生了crash;

处理方式:
最终把BP从9.6G调整为9G, 并把sort, read等buffer的数值调整到了4M, 其他的buffer也调低了;

PS: 算是疏忽吧, 因为说在生产环境已经用这么一套配置很久了, 没出过问题, 所以也没有仔细的排查配置文件中的设置;
PSS: sort的buffer原来是多少? 32M...sort的buffer还是per thread的...失职了..._(:з」∠)_

-------------------------------------------------------------------------------------------------后续---------------------------------------------------------------------------------------------------------------

峰回路转.....在调整了buffer的数量以后, 不可能再出现内存不够的现象了, 然后crash的现象重现了;

而且是主库和备库在非常短的时间内都发生了crash;

报错信息除了pointer不同以外, 堆栈的信息也是完全一致;

包括那个语句;
在之前出问题的时候, 记录了一条语句:

点击(此处)折叠或打开

  1. select * from test where time>='2016-07-29 00:00:00' and time<='2016-07-29 23:59:59' and tag in (2,3,6) order by id desc limit 2000
在后来重现的时候, 两次crash的语句中, 记录的 是同样的语句, (而且堆栈的输出信息也是完全一样) , 仅仅只是时间不一样:

点击(此处)折叠或打开

  1. select * from test where time>='2016-08-09 00:00:00' and time<='2016-08-09 23:59:59' and tag in (2,3,6) order by id desc limit 2000
如果是因为内存or系统资源的不可用导致了crash的话, 不可能有这么巧合的事情, 都是这个语句;

so,  在被拉起来的备库上跑了一下这个语句, 结果MySQL马上就 crash了...


那么明显就是这个语句的问题了, order by desc + limit, 看上去并没什么问题, 看看explain的结果


虽然好久没做开发了, 但是filtered在100%的情况下, rows只有1还是挺奇怪的, 一整张表只有一行数据, 但是 还有这种查询一整天的语句;

看看表的结构;
隐去生产库上的一部分信息, 留下关键的部分;


分区表的分区有问题....

问过业务以后, 原来是这个功能还没做完, 所以表相关的操作并没有一直执行;
但是这个功能的页面没有屏蔽, 所以对应的那条语句是有可能被触发的;

考虑到用那条语句可以必现这个crash, 且输出的堆栈信息和之前完全一致,
所以 确定是这个分区表的分区缺失的前提下, 触发那个查询语句的时导致了MySQL的Crash;

处理方式:
虽然最后还是找到了问题所在, 但是 最开始的时候还是被buffer和内存使用率的现象误导了, too young......
PS:本来还是觉得分区表在5.7改进了一点以后, 应该还挺好用的.....恩, 现在持保留意见....._(:з」∠)_  
PPS:应该不会再有后续了, 嗯嗯....

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
Mysql数据恢复—Mysql数据库delete删除后数据恢复案例
本地服务器,操作系统为windows server。服务器上部署mysql单实例,innodb引擎,独立表空间。未进行数据库备份,未开启binlog。 人为误操作使用Delete命令删除数据时未添加where子句,导致全表数据被删除。删除后未对该表进行任何操作。需要恢复误删除的数据。 在本案例中的mysql数据库未进行备份,也未开启binlog日志,无法直接还原数据库。
|
5月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
11月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
120 3
|
11月前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
744 0
案例剖析:MySQL唯一索引并发插入导致死锁!
|
11月前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
141 0
|
11月前
|
消息中间件 关系型数据库 MySQL
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
663 0
|
7月前
|
存储 SQL 关系型数据库
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
184 25
|
7月前
|
SQL 关系型数据库 MySQL
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
|
9月前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好,我是V哥。上周与朋友讨论数据库子查询问题,深受启发。为此,我整理了10个案例,详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例,帮助大家在实际开发中选择更高效的查询方式,提升系统性能。关注V哥,一起探讨技术,欢迎点赞支持!
498 5
|
9月前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。

推荐镜像

更多