主页 / dba / 问题 / 54626
Accepted
Franck Dernoncourt
Asked: 2013-12-07 20:37:10 +0800 CST

为什么导入 MySQL 转储会导致如此多的 I/O？

  • 772

我导入了 100 个 SQL 格式的转储文件。总大小为 20 GB。导入后生成的 .ibd 文件的总大小为 40 GB。mysqld.exe 在磁盘上写入 1.1 TB 并读取 120 GB。为什么有这么多 I/O？

我使用 MySQL Workbench 的默认选项来创建 SQL 格式的转储文件，即：

在此处输入图像描述

它似乎与 MySQL 参考手册的 InnoDB 表的批量数据加载部分的建议非常匹配（除了autocommit=0，它不存在于我的转储中）。转储中生成的代码标头如下所示：

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `askbot_activityauditstatus`
--
LOCK TABLES `askbot_activityauditstatus` WRITE;
/*!40000 ALTER TABLE `askbot_activityauditstatus` DISABLE KEYS */;
INSERT INTO `askbot_activityauditstatus` VALUES 
etc

我知道我可以尝试使用其他转储工具来加快速度，但我很好奇。写入次数给我留下了特别深刻的印象。

我使用 Windows 7 SP1 x64、MySQL 5.6 和 MySQL Workbench 6.0。

mysql mysqldump

1 个回答

  1. Best Answer

    从根本上说，恢复转储文件涉及插入所有行并为所有数据构建所有索引。鉴于此，转储文件的生成方式、您使用的是一个文件还是多个文件，或者您选择了哪些可用选项都不是特别相关（尽管其中一些选项extended-insert可能会使不选择的情况变得更糟）。

    当您恢复文件时，您还：

    • 将数据写入事务日志
    • 将数据写入双写缓冲区
    • 将数据写入表空间文件
    • 将索引写入双写缓冲区
    • 将索引写入表空间文件
    • 将数据写入二进制日志（如果已启用）...并且如果您具有binlog_format“语句”的默认设置，那么您实际上是在写入转储文件中的几乎每个字节加上开销...但是如果您binlog_format设置为“行”，您正在为二进制日志编写一个更紧凑的版本……如果您binlog_format设置为“混合”，那么二进制日志中实际使用的格式取决于您的默认事务隔离级别。

    设置innodb_flush_log_at_trx_commit从非常昂贵但符合 ACID 的默认值 1 到稍微不太安全的值 2 或明显不太安全的值 0 肯定会加速您的插入，尽管它不太可能减少实际 I/O，因为该值不会更改写入事务日志的内容，它只会更改 InnoDB 坚持从操作系统确认日志内容已保存到磁盘的频率。我使用“安全”是为了防止在该值设置为 2 或 0 期间发生崩溃而丢失最近的交易；1 如果 MySQL 或系统崩溃，则防止数据丢失；如果 MySQL 崩溃但系统不崩溃，2 可防止数据丢失，而 0 则两者都不会。一旦你把它放回去，它就没有任何后遗症。

    快速旁注，显示的一些看起来像评论的东西不是评论。/*!mnnrr 格式是一个 MySQL 向后兼容扩展，它告诉服务器“如果您的 MySQL 版本为 m.nn.rr 或更高版本，请执行此语句，否则请忽略”。 

    /*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;

    每个表都被转储文件中的这些包围。这些对于 MyISAM 比对于 InnoDB 更有用，因为DISABLE KEYS指示存储引擎在发布之前不要更新任何非唯一索引ENABLE KEYS，允许写入所有行数据，然后批量索引。使用 InnoDB，索引是在处理插入时构建的……因此在构建索引树和四处移动时有很多 I/O 潜力。

    InnoDB 缓冲池的大小将在磁盘 I/O 量中发挥作用——如果它相对较小，则可能是一个重要的——因为任何不能留在内存中的东西都必须迅速刷新到磁盘，只有在再次需要时才被读回，这对于索引尤其如此，对于实际行则不然，因为在 InnoDB 中，行在物理上以主键顺序存储，并以主键mysqldump将它们写入文件顺序...所以它们按存储顺序插入...但是随着对给定表的插入操作的进行，随着索引页的更新，二级索引必须来回穿梭到磁盘.

    因此，由于所有不同的日志记录、安全性和 ACID 机制，每个发生的插入都有一个“额外”活动的基线……而且索引似乎是可能创建大量额外 I/O 的通配符候选者。

    • 3

相关问题