MySQL · 特性分析 · InnoDB transaction history

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介: 背景 在写压力负载比较重的MySQL实例上,InnoDB可能积累了较长的没有被purge掉的transaction history,导致实例性能的衰减,或者空闲空间被耗尽,下面就来看看它是怎么产生的,或者有没有什么方法来减轻,避免这样的问题出现。 InnoDB purge 概要 InnoDB是

背景

在写压力负载比较重的MySQL实例上,InnoDB可能积累了较长的没有被purge掉的transaction history,导致实例性能的衰减,或者空闲空间被耗尽,下面就来看看它是怎么产生的,或者有没有什么方法来减轻,避免这样的问题出现。

InnoDB purge 概要

InnoDB是一个事务引擎,实现了MVCC特性,也就是在存储引擎里对行数据保存了多个版本。在对行数据进行delete或者update更改时,行数据的前映像会保留一段时间,直到可以被删除的时候。

在大部分OLTP负载情况下,前映像会在数据操作完成后的数秒钟内被删除掉,但在一些情况下,假设存在一些持续很长时间的事务需要看到数据的前映像,那么老版本的数据就会被保留相当长一段时间。

虽然MySQL 5.6版本增加了多个purge threads来加快完成老版本数据的清理工作,但在write-intensive workload情况下,不一定完全凑效。

测试案例

Peter Zaitsev 使用sysbench的update进行的测试,无论是 innodb_purge_threads=1 还是8的时候,显示的transaction history快速增长的情况,如下图所示:

transaction history增长情况

transaction history增长情况

下面看一下同步测试过程中purge的速度(可以通过I_S.innodb_metrics进行查询):

InnoDB purge 情况

InnoDB purge 情况

显示在并发 process 的过程中,purge thread 其实处在饥饿状态,待sysbench结束,purge线程满载运行清理工作。

对于这个测试结果,这里需要说明下:

  1. 对于Peter Zaitsev的测试,其实主要是为了说明transaction history的情况,如果是用sysbench进行小事务的OLTP测试,并不会产生这么明显的transaction history增长而purge thread 跟不上的情况,或者他在测试的时候,对sbtest表进行了全表查询吧,或者设置了RR级别,不过这只是猜测。
  2. 对于undo page大部分被cache在buffer pool的情况下,purge thread还是比较快的,但如果因为buffer pool的不足而导致undo page被淘汰到disk上的情况,purge操作就会被受限IO情况, 而导致跟不上。

问题分析

我们来看下出现transaction history增长最常见的两种场景:

大查询
如果你在一张大表上发起一个长时间运行的查询,比如mysqldump,那么purge线程必须停下来等待查询结束,这个时候transaction undo就会累积。如果buffer pool中 free page紧张,undo page 还会被置换到disk上,加剧purge的代价。

MySQL重启
即使transaction history并没有急剧增加,但MySQL重启操作,buffer pool的重新预热,还是导致purge变成IO密集型操作。不过MySQL 5.6提供了InnoDB buffer pool的dump和reload方法,可以显著减轻purge的IO压力。

这里介绍一下如何查看buffer pool中undo page的cache情况,percona的版本上提供了I_S.innodb_rseg记录undo的分配和使用情况:

mysql> select sum(curr_size)*16/1024 undo_space_MB from innodb_rseg;
+---------------+
| undo_space_MB |
+---------------+
|     1688.4531 |
+---------------+
1 row in set (0.00 sec)
mysql> select count(*) cnt, count(*)*16/1024 size_MB, page_type from innodb_buffer_page group by page_type;
+--------+-----------+-------------------+
| cnt    | size_MB   | page_type         |
+--------+-----------+-------------------+
|     55 |    0.8594 | EXTENT_DESCRIPTOR |
|      2 |    0.0313 | FILE_SPACE_HEADER |
|    108 |    1.6875 | IBUF_BITMAP       |
|  17186 |  268.5313 | IBUF_INDEX        |
| 352671 | 5510.4844 | INDEX             |
|     69 |    1.0781 | INODE             |
|    128 |    2.0000 | SYSTEM            |
|      1 |    0.0156 | TRX_SYSTEM        |
|   6029 |   94.2031 | UNDO_LOG          |
|  16959 |  264.9844 | UNKNOWN           |
+--------+-----------+-------------------+
10 rows in set (1.65 sec)

从这两个information_schema下的两张表可以看到:undo space使用的总大小是1.7G,而buffer pool中cached不足100M。

InnoDB 优化方法

在一定的写压力情况下,并发进行一些大查询,transaction history就会因为undo log无法purge而一直增加。

InnoDB提供了两个参数innodb_max_purge_laginnodb_max_purge_lag_delay 来调整,即当trx_sys->rseg_history_len超过了设置的innodb_max_purge_lag,就影响DML操作最大delay不超过innodb_max_purge_lag_delay设置的时间,以microseconds来计算。

其核心计算代码如下:

/*******************************************************************//**
Calculate the DML delay required.
@return delay in microseconds or ULINT_MAX */
static
ulint
trx_purge_dml_delay(void)
/*=====================*/
{
     /* Determine how much data manipulation language (DML) statements
     need to be delayed in order to reduce the lagging of the purge
     thread. */
     ulint     delay = 0; /* in microseconds; default: no delay */

     /* If purge lag is set (ie. > 0) then calculate the new DML delay.
     Note: we do a dirty read of the trx_sys_t data structure here,
     without holding trx_sys->mutex. */

     if (srv_max_purge_lag > 0) {
          float     ratio;

          ratio = float(trx_sys->rseg_history_len) / srv_max_purge_lag;

          if (ratio > 1.0) {
               /* If the history list length exceeds the
               srv_max_purge_lag, the data manipulation
               statements are delayed by at least 5000
               microseconds. */
               delay = (ulint) ((ratio - .5) * 10000);
          }

          if (delay > srv_max_purge_lag_delay) {
               delay = srv_max_purge_lag_delay;
          }

          MONITOR_SET(MONITOR_DML_PURGE_DELAY, delay);
     }

     return(delay);
}

但这两个参数设计有明显的两个缺陷:

缺陷1:针对total history length
假设transaction history中保留两类records,一类是是马上可以被purge的,一类是因为active transaction而不能purge的。但大多数时间,我们期望的是purgable history比较小,而不是整个history。

缺陷2:针对大小而非变化
trx_sys->rseg_history_len是一个当前history的长度,而不是一个interval时间段内undo的增长和减少的变化情况,导致trx_sys->rseg_history_len一旦超过innodb_max_purge_lag这个设定的值,就对DML产生不超过innodb_max_purge_lag_delay的时间delay,一旦低于这个值马上delay 时间就又恢复成 0。

在对系统的吞吐监控的时候,会发现系统抖动非常厉害,而不是一个平滑的曲线。类似于下图:

Purge 造成系统抖动

Purge 造成系统抖动

InnoDB purge 设计思路

针对InnoDB的purge功能,可以从以下几个因素来综合考虑:

  1. 增加默认 purge thread 的个数;
  2. 测量 purgable history 长度而不是总的长度;
  3. 针对变化进行调整 delay 数值,以应对 shrinking;
  4. 基于 undo space 的大小,而不是事务的个数;
  5. 调整 undo page 在 buffer pool 中的缓存策略,类似 insert buffer;
  6. 针对 undo page 使用和 index page 不同的预读策略。

以上6条可以针对purge线程进行一些改良。

当前调优方法

在当前的 MySQL 5.6 版本上,我们能做哪些调整或者调优方法,以减少transaction history增加带来的问题呢?

监控
监控trx_sysinnodb_history_list_length,为它设置报警值,及时关注和处理。

调整参数
如果你的实例是写压力比较大的话,调整innodb_purge_threads=8,增加并发purge线程数。
谨慎调整innodb_max_purge_laginnodb_max_purge_lag_delay参数,依据现在的设计,可能你的实例的吞吐量会急剧的下降。

purge完之后再shutdown
大部分的case下,MySQL实例重启后,会发现purge的性能更差,因为undo page未命中的原因,并且是random IO请求。
如果是正常shutdown,就等purge完成再shutdown;如果是crash,就启动后等purge完成再接受业务请求。

预热
使用MySQL 5.6 提供的innodb_buffer_pool_dump_at_shutdown=on 和 innodb_buffer_pool_load_at_startup=on进行预热,把undo space page预热到buffer pool中。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
22天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1585 14
|
8天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则,包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限,后者又优先于表级别权限。在权限冲突时,更严格的权限将被优先执行,确保数据库的安全性与资源合理分配。
|
2月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
124 10
MySQL 8.0 新特性
|
2月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
329 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
23天前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性?
【10月更文挑战第3天】MySQL8.2有哪些新特性?
29 2
|
25天前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
54 4
|
24天前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
124 0
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
198 2
|
2月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
86 6
|
26天前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析?
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析,帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划,其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议,帮助你理解和应用`EXPLAIN`,提升数据库查询效率。
48 0

相关产品

  • 云数据库 RDS MySQL 版