MySQL Shell转储和加载第3部分：加载转储

作者：Alfredo Kojima 译：徐轶韬

这是有关MySQL Shell转储和加载的博客文章系列的第3部分

MySQL Shell转储和加载实用程序是MySQL Shell 8.0.21提供的新工具，其主要目标是尽量减少创建和恢复大型数据集的逻辑转储所需的时间。

通过大量并行化技术及其他技术，我们能够将这些任务所需的时间减少一个数量级（与以前的转储实用程序相比较）。

实际上，在使用大型数据集的基准测试中，我们观察到转储速度接近3 GB / s，加载超过 200MB / s。可以在本系列的第2部分中了解有关此工具的更多信息以及与其他类似工具的性能比较。

在本文中，我将重点介绍加载程序的实现方法。您可以在第4部分中了解到在转储程序中所做的工作，转储程序的性能要比加载程序大得多。

禁用InnoDB重做日志

在许多情况下，数据加载期间的瓶颈是InnoDB的重做日志或二进制日志。在逻辑加载期间，通常禁用二进制日志（loadDump()has skipBinlog: true）。现在还 可以在MySQL Server 8.0.21中禁用InnoDB重做日志。

使用 ALTER INSTANCE DISABLE INNODB REDO_LOG，可以禁用重做日志和双写，这可以提高吞吐量并减少磁盘上的写入增加。请注意，MySQL Shell loadDump()不会禁用重做日志，必须在加载数据之前手动执行此操作。

并行转储和加载

尽快将数据移出和移回MySQL的关键是在多个并行会话/线程之间分配工作。

事实上 mysqlpump 已经做到了并行处理，但是它的粒度限制为每个表一个线程（仅适用于转储，加载是单线程的）。如果您的大多数表的大小都差不多，那会很好。但是，这种情况很少见，您经常会遇到一个线程转储1或2个巨大的表，而其他线程都已完成并处于空闲状态的情况。它还将转储到单个SQL文件，从而创建一个序列化点。单个SQL文件还使得并行加载所有数据变得更加困难，因为除了在脚本中找到合适的边界用于线程之间划分工作之外，还必须解析该文件。

Shell使用一种更具攻击性的方法，即在转储过程中将表分成小块，这些小块存储在单独的文件中。即使在单个表上工作时，我们也可以并行化，并且加载适，无需担心会拆分文件。

mydumper 也将表分解为较小的块，但是它每一次只加载同一表的块。尽管比在单个线程中加载整个表要快，但这种方法并不能像使用Shell一样扩展，我们通过谨慎地调度块来最大程度地提高摄取率，这将在本文后面的内容中进行解释。

在下面的图形中，我们表示每种方法的效率差异：

MySQL Shell具有的其他显着功能：

• 转储和加载步骤本身也可以同时完成。即使转储仍在执行，用户也可以开始加载它。通过利用这些优势，可以加快涉及跨服务器复制数据库的用例。
• 转储和加载都具有内置支持，可直接存储到OCI对象存储桶中或从中进行加载。
• 默认情况下，使用zstd 压缩表数据，不仅可以减少I / O或网络流量，而且可以有效地绕过I / O瓶颈，同时保持CPU使用率相对较低。zstd在压缩和解压缩方面比gzip / zlib快得多，但压缩的程度略有降低。
• Shell还通过支持恢复中断的加载来帮助节省时间。从外部跟踪加载进度，因此用户可以从它们离开的地方重试大型加载，而不必从头开始。

转储格式

与mysqldump，mysqlpump产生的转储不同，Shell转储将DDL，数据和元数据写入单独的文件。表也细分为大块，并写入多个类似CSV的文件中。

这可能会有一些缺点，因为转储不可以方便地复制的单个文件。但是，有几个优点：

• 加载转储不再是一个全有或全无的过程。由于DDL脚本，数据和元数据被写入单独的文件中，我们可以选择性地仅从转储中加载所需的内容，而不仅限于按原样加载已转储的所有内容。
• 在加载模式和数据之前，对其进行过滤和转换会更容易。
• 恢复中断的加载更为简单，因为我们可以跟踪已加载的内容，并在重试时跳过它们。
• 表数据以适合于LOAD DATA LOCAL INFILE 而不是普通SQL INSERT语句的格式转储。即使是单线程加载，减少的解析量也应意味着性能至少要好一些。
• 由于表已经预先分区在单独的文件中，因此并行加载表要容易得多，而且速度也快得多。importTable Shell工具支持加载单个CSV文件表并行转储，但它必须扫描文件加载它们，这可能需要一段时间才能找到块边界。

最大化摄取率

要最大化MySQL的加载性能，仅在客户端并行化工作是不够的。我们还需要通过最佳方式的调整和排序工作来帮助MySQL服务器，使其尽可能快地获取数据。

为此，加载程序执行以下操作：

• 首先加载较大的表/块。这样可以平衡所有线程之间的总工作量。我们最不希望看到的是小表都快速完成，而一个大表需要数小时才能加载，而线程却处于空闲状态。
• 优先并发加载不同的表。如果在任何时候，我们都可以在加载相同表的块或加载不同表的块之间进行选择，我们更喜欢后者。通过使用4个线程来加载4个不同的表所获得的总吞吐量要比通过加载同一表的块所获得的总吞吐量要高。这是因为4个加载会话中的每一个的服务器端线程都必须在存储引擎级别上争夺相同的锁。
• 优先加载较大表的块。如果线程多于要加载的表，则我们将线程中的块按与它们各自表中剩余数据量成比例的方式进行调度，同时仍要确保每个剩余表中至少有一个块被加载。这样，我们将最大限度地提高较大表的整体吞吐量和单个吞吐量，并尝试在大约同一时间更快地完成整个任务。
• 动态调度。每次线程完成一个块的加载后，我们都会计算下一个要加载的最佳块，从而确保调度保持接近理想状态，而不管由于索引，块大小，行大小等导致加载每个块需要多长时间。
• 预排序行。众所周知，InnoDB在以主键值顺序插入行的情况下工作得最好。但是这已经由转储程序处理了，因为它按照顺序查询和写入行。排序可能会使转储查询花费更长的时间，但会使数据为加载做好准备。

推迟还是不推迟（索引）

更快地加载表的一种常见做法是推迟创建二级索引。也就是说，在创建表时剥离二级索引，加载数据然后才创建索引。

在我们的测试中，我们发现，除了一种例外，推迟表索引通常无济于事，甚至可能适得其反。延迟索引是否有所帮助取决于您的具体情况，因此我们建议尝试使用deferTableIndexes选项。

设置deferTableIndexes为all的好处之一是辅助索引的碎片化程度降低，可能占用更少的磁盘空间。

但是，在推迟全文索引时，我们看到了加载时间的持续改进。因此，deferTableIndexes默认为fulltext。设置为all会推迟所有表的所有索引。

结论

通过重新设计逻辑转储，与以前的工具相比，我们能够获得显着的性能改进。在许多情况下，即使是功能最强大的硬件，过去耗时数小时甚至全天的转储现在都可以在不到一个小时甚至不到几分钟的时间内完成。

同样重要的是，通过加载这些转储还原服务器也要快得多。与加载等效的.sql转储文件相比，从Shell转储中还原大型数据库仅需花费一小部分时间。当需要紧急恢复时，这可以释放一些宝贵的时间！

我们希望您会尝试我们的新实用程序，并且发现它们不仅速度快，而且功能强大且易于使用。