MySQL Shell转储和加载第2部分：基准测试

作者：Kenny Gryp 译: 徐轶韬

这是有关MySQL Shell Dump＆Load的博客文章系列的第2部分

关于新的MySQL Shell Dump＆Load实用程序的第二部分旨在演示性能，同时还将其与其他各种逻辑转储和加载工具进行比较：mysqldump
，mysqlpump
＆mydumper
。

为了使数字更有意义，我使用了一些在线提供的真实的生产数据集：stackoverflow.com，en.wikipedia.org，准时飞行数据以及这3种数据的组合。

数据库环境

测试是在具有大量CPU，RAM和存储性能的高端服务器上执行的。转储/加载工具和服务器都位于同一主机上。

• Oracle Linux 7.8

• OCI裸机实例BM.Standard.B1.44

• 44x Intel Xeon E5-2699 v4。基本频率2.2 GHz，最大加速频率3.6 GHz，启用超线程（总共88个可见核心）

• 512 GB内存

• 25 Gbps网络（同时供块存储使用）

• 3.2 TB 存储：8个块卷，RAID-0条带化MD，每个400GB

• MySQL社区版服务器8.0.21

• InnoDB重做日志已禁用（同时禁用双写）：

ALTER INSTANCE DISABLE INNODB REDO_LOG;

MySQL配置（其他设置只是默认设置）：

[mysqld]
innodb_dedicated_server=on
innodb_numa_interleave=on
innodb_adaptive_hash_index=0
innodb_change_buffering=none
local_infile=1
skip_log_bin
innodb_buffer_pool_instances=16
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=40000
innodb_io_capacity_max=60000
innodb_max_dirty_pages_pct=10

转储和加载工具-用法

每种工具的转储和加载方式不同，如下所述。

mysqldump

• 由于mysqldump
  没有内置压缩功能，转储输出已通过管道传递到zstd
  。

• 使用mysqldump
  ，转储和加载都是单线程的。

转储：

$ mysqldump --single-transaction \
            --databases <db> \
            | zstd -z <file>

加载：

$ zstd -d --stdout <file> \
          | mysql

mysqlpump

• mysqlpump
  内置的压缩功能，既zlib
  和lz4
  ，lz4
是
  目前最快的选择，zlib
  非常缓慢。

• mysqlpump
  可以在多个线程中转储数据，但仅限于表级别。如果有一个很大的表，它将仅使用1个线程转储。

• mysqlpump
  生成一个类似于的SQL文件，并且加载数据是单线程的。

转储：

$ mysqlpump --default-parallelism=88 \
            --compress-output lz4 \
            --databases <db> \
            > <file>

加载：

$ lz4cat <file> | mysql

mydumper

• mydumper
  能够并行转储数据，并且在使用--rows
  选项时还可以并行转储单个表。 myloader
  还提供并行加载数据。

• 表分成约250.000行的大块，并且还启用了zlib压缩。

转储：

$ mydumper --rows 250000 \
           -c \
           --trx-consistency-only \
           -t 88 \
           -B <db> \
           -o <directory>

加载：

$ myloader -t 88 \
           -d <directory>

MySQL Shell转储/加载

• 数据以约256 MB的块的形式转储

转储：

mysqlsh-js> util.dumpSchemas(["<db>"], 
                             "<directory>",
                             {threads: 88, 
                             bytesPerChunk: "256M"})

加载：

mysqlsh-js> util.loadDump("<directory>", 
                          {threads: 88})

数据集

每个数据集均可下载。

准时飞行数据

1987年至2020年4月的航空公司航班准点统计。

• 可在transtats.bts.gov上获得

• 一个单独的MySQL表，一个主键，没有辅助键

• 未压缩的TSV大小：64 GB

• 194,699,018行

Stackoverflow.com

stackoverflow.com数据

• 在archive.org上可下载

• 未压缩的TSV大小：216 GB

• 502,676,396行

EN维基百科

EN维基百科备份，始于20200620

• 下载的enwiki备份

• 未压缩的TSV大小：130 GB

• 1,673,892,597行

• 频繁使用  binary
  数据类型

所有

合并以上所有数据集

• 数据集不适合内存

• 未压缩的TSV大小：410 GB

• 2,371,268,011行

基准测试结果

是时候显示一些结果了……

转储

• 正如所料，mysqldump
  与mysqlpump
  缺乏或受限于并行转储。

• 对于mysqlpump
  ，所有数据集中只有足够的大表才能提高转储吞吐量。

• 使用mydumper和MySQL Shell与其他工具相比较，有很大的不同

• 除1种情况外，MySQL Shell是最快的，有时甚至快得多（stackoverflow）

• mydumper
  慢的一个原因可能是它使用的zlib
  压缩比zstd要慢
  

• mydumper
  转存维基百科的速度比MySQL Shell快，这可能是因为Wikipedia数据集包含许多二进制列，MySQL Shell将其转换为base64格式， mydumper
  不进行转换。MySQL Shell 8.0.22将包含进一步的改进。

加载

除了禁用InnoDB重做日志，MySQL Server 8.0.21还包括对锁管理器的改进，它通过替换锁系统互斥体（lock_sys->mutex
）来提高InnoDB的写可伸缩性。这样可以提高写入性能，尤其是在NUMA节点距离较远的环境中（例如AMD Epyc Naples）。

警告：禁用InnoDB重做日志仅用于将数据加载到新的MySQL实例中。不要在生产系统上禁用重做日志记录，禁用重做日志记录时  服务器意外停止可能/将导致数据丢失和实例损坏。

• mysqldump
和
mysqlpump
  生成单个.sql
  文件，加载数据是单线程的，因此它们慢得多。

• MySQL Shell的速度比myloader
   所有情况都要快，这是由于其调度算法所致，本博文系列的第3部分对此进行了解释。

二级索引影响写入吞吐量

使用stackoverflow和Wikipedia数据集的情况下，存在的二级索引越多，则预期的写吞吐量就越慢。

MySQL Shell能够在加载数据后（带有deferTableIndexes
选项）创建二级索引，但事实上，加载后添加索引会使数据集的整个过程变慢，因此通常不是最佳选择。mysqlpump
默认情况下会执行此操作，但与我们使用myloader和
MySQL Shell 获得的结果相比，性能提升可忽略不计。

分区可以帮助大表

那么为什么加载准时飞行数据表比其他数据集慢呢？

原因是准时数据集由单个表组成。瓶颈在InnoDB中表级别（索引锁定和页面锁定）。当我们对ontime表进行分区（在这种情况下为128个KEY()
分区）时，写入吞吐量显着增加，尤其是在使用MySQL Shell时：

当MySQL Shell跟踪加载进度时，首先可以通过仅加载DDL，进行更改，然后加载其余的转储来添加分区：

mysqlsh-js> util.loadDump("<directory>", {threads: 88
                                          loadData: false})
mysqlsh-js> \sql ALTER TABLE ontime.ontime
                             PARTITION BY KEY()
                             PARTITIONS 128;
mysqlsh-js> util.loadDump("<directory>", {threads: 88})

MySQL Shell转储和加载：快！

如这些基准测试所示，MySQL Shell能够快速转储数据，最高可达3GB s的速度，并以200MB s以上的速度加载数据（禁用InnoDB重做日志时）。下一篇博客文章将介绍Shell如何实现这种转储和加载性能。

感谢您关注“MySQL解决方案工程师”！