MySQL · 特性分析 · InnoDB transaction history

背景

在写压力负载比较重的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快速增长的情况，如下图所示:

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

显示在并发 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增长最常见的两种场景：

这里介绍一下如何查看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_lag ， innodb_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);  }

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

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

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增加带来的问题呢？

</div>

来自： http://mysql.taobao.org/monthly/2016/02/03/