mysql - 为什么 MySQL 写入磁盘的数据比提交到数据库的数据多 5 倍？

Question

我在 Ubuntu 20.04 之上安装了 MySQL 8.0.25，在 C5.2xlarge 实例上运行。

然后我运行了一个用数据填充 10 个表的脚本。测试只用了 2 个小时，在此期间创建了 123146.5MB 的数据：

这意味着平均有 17.1MB/s 的速度写入数据库。然而，atop 报告了一些奇怪的事情：虽然它显示磁盘活动在 18-19MB/s 左右，但它还显示进程 mysqld 在 10 秒样本中写入了 864MB - 转换为 86.4MB/s，大约是实际提交到数据库的数据量：

为什么会有这样的差异？

iotop 通常还显示 MySQL 正在写入 5x：

与 pidstat 相同：

我还尝试使用 Percona 工具包中的 pt-diskstats，但它没有显示任何内容......

我还在 RDS 上重现了这个问题。在这两种情况下（EC2 和 RDS），Cloudwatch 统计数据还显示 5x 写入...

该数据库有 10 个已填充的表。其中5个有这个定义：

CREATE TABLE `shark` (
  `height` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  `name` mediumtext,
  `shark_id` bigint NOT NULL,
  `owner_id` bigint DEFAULT NULL,
  PRIMARY KEY (`shark_id`),
  KEY `owner_id` (`owner_id`),
  CONSTRAINT `shark_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `shark_owners` (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

另外 5 个表有这个定义：

CREATE TABLE `shark_owners` (
  `name` mediumtext,
  `owner_id` bigint NOT NULL,
  PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我可以理解差异是否约为 2 倍——数据首先写入事务日志，然后提交到数据库，但是 5 倍？这是 MySQL 的正常行为，还是我的表中的某些东西触发了这个？为什么会有这么多“取消写入”——大约 12%？

标签： mysqlamazon-web-servicesperformanceamazon-rdsinnodb

Answer 1

• LOAD DATA运行速度非常快，I/O 最少
• 每个查询至少 100 行的批量INSERT运行速度是单行插入的 10 倍。
• autocommit在每个 SQL 之后导致至少一个额外的 I/O（为了事务完整性）。
• 50 1-line Inserts，然后 aCOMMIT是一种折衷方案。
• FOREIGN KEY需要检查另一个表。
• 如果innodb_buffer_pool_size太小，就会出现磁盘搅动。
• owner_id是“二级索引”。它以半优化的方式完成，但可能涉及读取和写入，具体取决于各种事情。
• 如果您可以使用更小的数据类型，表会更小。（例如，BIGINT占用 8 个字节并且通常是多余的。）较小会导致较少的 I/O。
• 有多大name？使用什么ROW_FORMAT？它们合谋导致或多或少的“非记录”存储，从而导致磁盘 I/O。
• 您在进行插入时是否使用了多个线程？

换句话说，需要更多细节来分析您的问题。