A little fun with InnoDB multi-versioning（14.有关InnoDB多版本中的一个小问题）

冬天里的懒猫

发布于 2020-09-02 19:17:18

3730

发布于 2020-09-02 19:17:18

执行以下命令，在MySQL CLI中，在一个新的连接上执行，不需要做特别的准备(并且要特别注意执行时间):

ysql> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from t;
Empty set (5.20 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (5.22 sec)

mysql> select * from t where a = 10;
Empty set (0.00 sec)

mysql> select * from t where a < 10;
Empty set (5.35 sec)

mysql> select * from t where a > 10;
Empty set (5.41 sec)

mysql> select * from t where a in (10, 20, 30);
Empty set (0.00 sec)

mysql> select * from t where a > 1000000;
Empty set (0.00 sec)

mysql> select * from t where a > 500000;
Empty set (2.60 sec)

发生了什么?为什么这么慢?为什么有些东西慢而有些不慢?

寻找罪魁祸首

SHOW PROCESSLIST没有显示任何异常:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  6 | root | localhost:34840 | test | Query   |    0 | init  | show processlist |
|  8 | root | localhost:34842 | test | Sleep   | 2116 |       | NULL             |
+----+------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

如果我们看一下显示引擎INNODB的状态，就会发现一条线索:一个忙于作恶的事务:

---TRANSACTION 1481, ACTIVE 2183 sec
3487 lock struct(s), heap size 570920, 2001743 row lock(s), undo log entries 11000000
MySQL thread id 8, OS thread handle 0x7fca27ba2700, query id 5000144 localhost 127.0.0.1 root cleaning up

它还显示在information_schema.innodb_trx:

mysql> select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
                    trx_id: 1481
                 trx_state: RUNNING
               trx_started: 2014-04-17 01:38:27
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 11003487
       trx_mysql_thread_id: 8
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 3487
     trx_lock_memory_bytes: 570920
           trx_rows_locked: 2001743
         trx_rows_modified: 11000000
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)

事务怎么会是邪恶的?

我使用下面的脚本插入一百万行，然后每一行更新十次，保持事务打开和未提交:

#!/usr/bin/env ruby

require "mysql"

m = Mysql.new("127.0.0.1", "root", "", "test", 13000)

m.query("DROP TABLE IF EXISTS t")
m.query("CREATE TABLE t (a INT UNSIGNED NOT NULL, b INT UNSIGNED NOT NULL, PRIMARY KEY (a)) ENGINE=InnoDB")

m.query("START TRANSACTION")

(1..1000000).each do |i|
  m.query("INSERT INTO t (a, b) VALUES (#{i}, 0)")
  puts "Inserted #{i} rows..." if i % 10000 == 0
end

(1..10).each do |i|
  m.query("UPDATE t SET b=#{i}")
  puts "Updated #{i} times..."
end

sleep 1000000

正如InnoDB撤销日志记录和历史系统的基础中所描述的，这些修改都是对数据库进行的，未提交的写事务和我的读事务都使用单一的索引结构。尽管我的读事务不能看到写入事务插入和随后修改的任何行，但为了弄清楚这一点，它需要将所有的undo记录应用到遇到的每一行(每行10条)。当然，这需要时间。

漏洞的可见性

您可以看到不同操作所花费的时间中，飞行中数据“泄漏”的一些隐藏可见性。例如，扫描所有行的时间超过5秒，但是使用条件a > 1000000可以立即计算。令人怀疑的是，> 500000的条件大约只需要扫描所有行的一半时间(因为它只需要做一半的工作)。当然，所有这些查询都返回一个空集。

对现实世界的适用性

虽然几乎没有人会故意做我的脚本所做的事情，但我以前在生产系统中确实看到过这样的结果:一个非常大的事务多次更新一行会导致试图访问该表的许多慢速查询。有些查询会很快，有些则很慢。

对系统管理员和dba有严重的影响

undo历史记录的形式对用户可以使用的空间没有限制。我已经提交了MySQL Bug #72362:“用户可以无限制地使用undo space”，建议添加配置选项来限制用户可能消耗的undo space。我写了以下内容: 由于InnoDB目前已经实现，用户可以通过以下任意一种方式使用无限的空间来撤消历史记录:

1.创建一个或多个大型事务，多次写入一行，直接积累undo历史记录。
2.使用read视图打开一个事务(同时最低限度地保持其活动)，通过防止清除间接积累undo历史。此外，用户可以将这两种方法结合使用，在打开事务的同时使用read视图同时生成大量的小事务，这些事务各自只有少量的undo历史记录。这样可以更好地防止对罪魁祸首的检测。这两种情况都允许没有特殊权限的普通用户消耗system表空间中的大量磁盘空间，这可能会导致system表空间被扩展，消耗所有的文件系统空间，而系统管理员没有简单的资源。我建议为以下内容添加新的配置选项:
模拟单个事务消耗的撤销空间。
限制给定用户使用的聚合undo空间。
限制给定用户的事务读取视图的年龄。

本文参与?腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-08-31 ，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server