MySQL:5.6 大事务show engine innodb status故障一例

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL:5.6 大事务show engine innodb status故障一例

今天遇到一个朋友的线上问题,大概意思就是说,我有一个线上的大事务大概100G左右,正在做回滚,当前看起来似乎影响了线上的业务,并且回滚很慢,是否可以减轻对线上业务的影响。并且朋友已经取消了双1设置,但是没有任何改观。版本MySQL 5.6首先我们需要知道的是,MySQL并不适合大事务,大概列举一些MySQL中大事务的影响:

  • binlog文件作为一次写入,会在sync阶段消耗大量的IO,会导致全库hang主,状态大多为query end。
  • 大事务会造成导致主从延迟。
  • 大事务可能导致某些需要备份挂起,原因在于flush table with read lock,拿不到MDL GLOBAL 级别的锁,等待状态为 Waiting for global read lock。
  • 大事务可能导致更大Innodb row锁加锁范围,导致row锁等待问题。
  • 回滚困难。

基于如上一些不完全的列举,我们应该在线上尽可能的避免大事务。好了我们下面来进行问题讨论。


一、问题前面已经说了,我们已经取消了双1设置,所谓的双1就是 sync_binlog=1和 innodb_flush_log_at_trx_commit=1。这两个参数线上要保证为1,前者保证binlog的安全,后者保证redo的安全,它们在数据库crash recovery的时候起到了关键做用,不设置为双1可能导致数据丢失。具体的参数含义不做过多讨论。但是这里的问题是即便取消了双1,没有任何改观,因此似乎说明IO问题不是主要瓶颈呢?下面我们来看几个截图:

  • vmstat 截图

image.png

  • iostat 截图


image.png

image.png

  • top -Hu截图

image.png

我们重点观察vmstat的r 和 b列发现,IO队列没有有什么问题 并且wa%并不大。我们观察iostat中的%util和读写数据大小来看问题不大,并且tps远没达到极限(SSD盘)。我们top -Hu 可以观察到 %us不小,并且有线程已经打满了(99.4%CPU)一个CPU核。

因此我们可以将方向转为研究CPU瓶颈的产生,希望能够对问题有帮助,然后从提供的perf top中我们有如下发现:

image.png

好了我们将问题先锁定到lock_number_of_rows_locked这个函数上。


二、函数lock_number_of_rows_locked的作用朋友用的5.6,但是我这里以5.7.26的版本进行描述。然后下一节描述5.6和5.7算法上的关键差异。不知道大家是否注意过show engine innodb status中的这样一个标志:

image.png

这个标记就来自函数lock_number_of_rows_locked,含义为当前事务加行锁的行数。而这个函数包裹在函数lock_print_info_all_transactions下面,lock_print_info_all_transactions函数是打印我们通常看到show engine innodb status中事务部分的核心参数。我们来看一下简单的流程:

  1. PrintNotStarted print_not_started(file);//建立一个结构体,目的是做not start 事务的打印
  2. ut_list_map(trx_sys->mysql_trx_list, print_not_started); //这个地方打印出那些事务状态是no start的事务。mysql_trx_list是全事务。

  3. const trx_t* trx;
  4. TrxListIterator trx_iter; //这个迭代器是trx_sys->rw_trx_list 这个链表的迭代器
  5. const trx_t* prev_trx = 0;

  6. /* Control whether a block should be fetched from the buffer pool. */
  7. bool load_block = true;
  8. bool monitor = srv_print_innodb_lock_monitor && (srv_show_locks_held != 0);

  9. while ((trx = trx_iter.current()) != 0) { //通过迭代器进行迭代 ,显然这里不会有只读事务的信息,全部是读写事务。

  10. ...
  11. /* If we need to print the locked record contents then we
  12. need to fetch the containing block from the buffer pool. */
  13. if (monitor) {

  14. /* Print the locks owned by the current transaction. */
  15. TrxLockIterator& lock_iter = trx_iter.lock_iter();

  16. if (!lock_trx_print_locks( //打印出锁的详细信息
  17. file, trx, lock_iter, load_block))

简单的说就是先打印哪些处于not start的事务,然后打印那些读写事务的信息,当然我们的回滚事务肯定也包含在其中了,需要注意的是只读事务show engine不会打印。对于处于回滚状态的事务我们可以在show engine中观察到如下信息:

image.png

函数trx_print_low可以看到大部分的信息,这里就不详细解释了。既然如此我们需要明白lock_number_of_rows_locked是如何计算的,下面进行讨论。

三、函数lock_number_of_rows_locked的算法变化上面我们说了函数lock_number_of_rows_locked函数会打印出当前事务加行锁的行数。那么我们来看一下5.6和5.7算法的不同。

  • 5.7.26

实际上只有如下一句话:

  1. return(trx_lock->n_rec_locks);

我们可以看到这是返回了一个计数器,而这个计数器的递增就是在每行记录加锁后完成的,在函数lock_rec_set_nth_bit的末尾可以看到 ++lock->trx->lock.nreclocks ,因此这是一种预先计算的机制。因此这样的计算代价很低,也不会由于某个事务持有了大量的锁,而导致计算代价过高。

  • 5.6.22

随后我翻了一下5.6.22的代码,发现完全不同如下:

  1. for (lock = UT_LIST_GET_FIRST(trx_lock->trx_locks); //使用for循环每个获取的锁结构
  2. lock != NULL;
  3. lock = UT_LIST_GET_NEXT(trx_locks, lock)) {

  4. if (lock_get_type_low(lock) == LOCK_REC) { //过滤为行锁
  5. ulint n_bit;
  6. ulint n_bits = lock_rec_get_n_bits(lock);

  7. for (n_bit = 0; n_bit < n_bits; n_bit++) { //开始循环每一个锁结构的每一个bit位进行统计
  8. if (lock_rec_get_nth_bit(lock, n_bit)) {
  9. n_records++;
  10. }
  11. }
  12. }
  13. }

  14. return(n_records);

我们知道循环本身是一种CPU密集型的操作,这里使用了嵌套循环实现。因此如果在5.6中如果出现大事务操作了大量的行,那么获取行锁记录的个数的时候,将会出现高耗CPU的情况。

四、原因总结和解决有了上面的分析我们很清楚了,触发的原因有如下几点:

  • MySQL 5.6版本
  • 有大事务的存在,大概100G左右的数据加行锁了
  • 使用了show engine innodb status

这样当在统计这个大事务行锁个数的时候,就会进行大量的循环操作。从现象上看就是线程消耗了大量的CPU资源,并且处于perf top的第一位。

知道了原因就很简单了,找出为频繁使用show engine innodb status的监控工具,随后业务全部恢复正常,IO利用率也上升了如下:

image.png

当然如果能够使用更新的版本比如5.7及8.0 版本将不会出现这个问题,可以考虑使用更高版本。分析性能问题需要首先找到性能的瓶颈然后进行集中突破,比如本例中CPU资源消耗更加严重。也许解决问题就在一瞬间。

五、其他最后通过朋友后面查询的bug如下:https://bugs.mysql.com/bug.php?id=68647 发现印风(翟卫翔)已经在多年前提出过了这个问题,并且做出了修改意见,并且这个修改意见官方采纳了,也就是上面我们分析的算法改变。经过印风(翟卫翔)的测试有bug中有如下描述:

  • From perf top, function locknumberofrowslocked may occupy more than 20% of CPU sometimes

也就是CPU消耗会高达20%。

下面是5.7.26调用栈帧:

image.png

            </div>
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
33 7
|
12天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
65 7
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
142 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
18天前
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
49 9
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
2月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
170 0
|
4月前
|
监控 关系型数据库 MySQL
在Linux中,mysql的innodb如何定位锁问题?
在Linux中,mysql的innodb如何定位锁问题?