为 MariaDB 导入数据的注意事项 - Amazon Relational Database Service
二进制日志记录事务大小InnoDB数据导入格式使用 Amazon RDS 数据库快照提供数据库检查点减少数据库加载时间
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

为 MariaDB 导入数据的注意事项

以下内容包含与将数据加载到 MariaDB 中相关的技术信息。此内容面向熟悉 MariaDB 服务器架构的用户。

二进制日志记录

与禁用日志记录相比，启用二进制日志记录会降低数据加载性能，并且需要多达四倍的额外磁盘空间。用于加载数据的事务大小直接影响系统性能和磁盘空间需求，也即，事务越大，需要的资源越多。

事务大小

事务大小会影响 MariaDB 数据加载的以下方面：

  • 资源消耗

  • 磁盘空间利用率

  • 恢复进程

  • 恢复时间

  • 输入格式（平面文件或 SQL）

本部分介绍了事务大小如何影响二进制日志记录以及让案例在大数据加载过程中禁用二进制日志记录。可以通过设置 Amazon RDS 自动备份保留期来启用和禁用二进制日志记录。非零值会启用二进制日志记录，零会将其禁用。有关更多信息，请参阅 备份保留期。

本节还介绍大型事务对 InnoDB 的影响以及将事务大小保持为较小的重要性。

小型事务

对于小型事务，二进制日志记录会使加载数据所需的磁盘写入次数翻倍。这种影响会严重降低其他数据库会话的性能，并增加数据加载所需的时间。所经历的降级在一定程度上取决于以下因素：

  • 上传速率

  • 在加载期间发生的其它数据库活动

  • Amazon RDS 数据库实例的容量

此外，二进制日志占用的磁盘空间大致等于加载的数据量，直至备份和移除日志。Amazon RDS 通过频繁备份和移除二进制日志来尽量减少占用的磁盘空间量。

大型事务

对于大型事务，二进制日志记录会使 IOPS 和磁盘使用量增加两倍，原因如下：

  • 二进制日志缓存将事务数据临时存储在磁盘上。

  • 此缓存会随着事务大小而增长，这会占用磁盘空间。

  • 当事务（提交或回滚）完成时，系统会将缓存复制到二进制日志。

此过程会创建数据的三个副本：

  • 原始数据

  • 磁盘上的缓存

  • 最终二进制日志条目

每个写入操作都会产生额外的 IO，从而进一步影响性能。

因此，与禁用日志记录相比，二进制日志记录需要三倍的磁盘空间。例如，在单个事务中加载 10 GiB 的数据会创建三个副本：

  • 表数据为 10 GiB

  • 二进制日志缓存为 10 GiB

  • 二进制日志文件为 10 GiB

所需的临时磁盘空间总量为 30 GiB。

磁盘空间重要注意事项：

  • 缓存文件会一直存在，直到会话结束或新事务创建另一个缓存。

  • 二进制日志会保留直至对其进行备份，可能会在很长一段时间内占用 20 GiB（缓存和日志）。

如果您使用 LOAD DATA LOCAL INFILE 来加载数据，数据恢复会创建第四个副本，以防数据库必须从加载前进行的备份中恢复。在恢复期间，MariaDB 将二进制日志中的数据提取到平面文件中。然后，MariaDB 运行 LOAD DATA LOCAL INFILE。在前一个示例的基础上，此恢复需要的临时磁盘空间总量为 40 GiB，也即表、缓存、日志和本地文件各为 10 GiB。如果没有至少 40 GiB 的可用磁盘空间，则恢复将失败。

优化大数据加载

对于大数据加载，请禁用二进制日志记录，以减少开销和磁盘空间要求。可以通过将备份保留期设置为 0 来禁用二进制日志记录。完成加载后，将备份保留期还原为适当的非零值。有关更多信息，请参阅修改 Amazon RDS 数据库实例和设置表中的备份保留期。

注意

如果数据库实例是只读副本的源数据库实例，则您无法将备份保留期设置为 0。

在加载数据之前，我们建议您创建数据库快照。有关更多信息，请参阅 管理手动备份。

InnoDB

以下有关撤消日志记录和恢复选项的信息支持将 InnoDB 事务保持为较小，以优化数据库性能。

了解 InnoDB 撤消日志记录

撤消是一种日志记录机制，可启用事务回滚并支持多版本并发控制（MVCC）。

对于 MariaDB 10.11 及更低版本，撤消日志存储在 InnoDB 系统表空间（通常为 ibdata1）中，并保留至清除线程移除它们为止。因此，大数据加载事务可能会导致系统表空间变得相当大，并占用磁盘空间，除非重新创建数据库，否则无法回收这些磁盘空间。

对于所有 MariaDB 版本，清除线程必须等待以移除所有撤消日志，直到最旧的活动事务提交或回滚。如果数据库在加载过程中正在处理其它事务，则其撤消日志也会累积并且无法移除，即使事务提交并且没有其它事务需要 MVCC 的撤消日志，也是如此。在这种情况下，所有事务（包括只读事务）都会变慢。之所以发生这种变慢过程，是因为所有事务都访问任何事务（而不仅仅是加载事务）更改的所有行。实际上，事务必须扫描一些撤消日志，在撤消日志清理期间，长时间运行的加载事务已阻止清除这些撤消日志。这会影响访问已修改行的任何操作的性能。

InnoDB 事务恢复选项

尽管 InnoDB 优化了提交操作，但大型事务回滚速度很慢。为了加快恢复速度，请执行时间点故障恢复或还原数据库快照。有关更多信息，请参阅时间点故障恢复和还原到数据库实例。

数据导入格式

MariaDB 支持两种数据导入格式：平面文件和 SQL。查看有关每种格式的信息，以确定最适合您需求的选项。

平面文件

对于小型事务，请使用 LOAD DATA LOCAL INFILE 加载平面文件。与使用 SQL 相比，这种数据导入格式具有以下优势：

  • 减少网络流量

  • 降低数据传输成本

  • 减少了数据库处理开销

  • 处理速度更快

LOAD DATA LOCAL INFILE 会将整个平面文件作为一个事务进行加载。将各个文件的大小保持为较小可获得以下优势：

  • 恢复功能：可以保持跟踪哪些文件已加载。如果在加载期间出现问题，可以从停止处继续加载。可能需要将一些数据重新传输到 Amazon RDS，但使用小文件，就可以将重新传输的量减少至最低水平。

  • 并行数据加载：如果 IOPS 和网络带宽足以支持单个文件加载，那么并行加载就可以节省时间。

  • 加载速率控制：如果数据加载对其它进程产生负面影响，则可以通过增加文件之间的间隔来控制加载速率。

大型事务会降低使用 LOAD DATA LOCAL INFILE 导入数据的优势。当您无法将大量数据分解成较小的文件时，可以考虑使用 SQL。

SQL

与平面文件相比，SQL 的一个主要优势为：您可以轻松地将事务大小保持为较小。但是，SQL 的加载时间可能比平面文件长得多。此外，在发生故障后，可能难以确定从哪里恢复，而您无法重新启动 mariadb-dump 文件。如果加载 mariadb-dump 文件时发生故障，则必须在加载可以恢复前修改或替换该文件。或者，在纠正故障的原因后，您可以恢复到加载前的时间点，并重新发送该文件。有关更多信息，请参阅 时间点故障恢复。

使用 Amazon RDS 数据库快照提供数据库检查点

如果您在没有二进制日志记录的情况下长时间（如数小时或数天）加载数据，请使用数据库快照提供定期检查点来实现数据安全。每个数据库快照都会创建数据库实例的一致副本，该副本在系统故障或数据损坏事件期间用作恢复点。由于数据库快照速度很快，因此频繁的检查点对加载性能的影响微乎其微。您可以在不影响数据库持久性或恢复能力的情况下，删除以前的数据库快照。有关数据库快照的更多信息，请参阅管理手动备份。

减少数据库加载时间

以下各项是减少加载时间的其它技巧：

  • 在将数据加载到 MariaDB 数据库之前，请创建所有二级索引。与其它数据库系统不同，MariaDB 在添加或修改二级索引时会重建整个表。此过程会创建一个包含索引更改的新表，复制所有数据，然后删除原始表。

  • 按主键顺序加载数据。对于 InnoDB 表，这可以将加载时间缩短 75% 至 80%，并将数据文件大小减少 50%。

  • 通过将 foreign_key_checks 设置为 0 来禁用外键约束。对于使用 LOAD DATA LOCAL INFILE 加载的平面文件，这通常是必需的。对于任何加载，禁用外键检查会加快数据加载速度。加载完成后，通过将 foreign_key_checks 设置为 1 来重新启用约束并验证数据。

  • 并行加载数据，除非接近资源限制。要实现跨多个表段的并行加载，请在适当时使用分区表。

  • 要减少 SQL 执行开销，请将多条 INSERT 语句合并为单个多值 INSERT 操作。mariadb-dump 自动实现此优化。

  • 通过将 innodb_flush_log_at_trx_commit 设置为 0 来减少 InnoDB 日志 IO 操作。加载完成后，将 innodb_flush_log_at_trx_commit 还原为 1。

    警告

    将 innodb_flush_log_at_trx_commit 设置为 0 会导致 InnoDB 每秒都刷新其日志，而不是在每次提交时刷新。此设置可以提高性能，但在系统故障期间可能会有事务丢失的风险。

  • 如果您要将数据加载到没有只读副本的数据库实例，请将 sync_binlog 设置为 0。加载完成后，将 sync_binlog parameter 还原为 1。

  • 在将数据库实例转换为多可用区部署之前，将数据加载到单可用区实例中。如果数据库实例已经使用多可用区部署，我们建议您不要切换为单可用区部署来加载数据。这样做所带来的改进微不足道