MySQL Shell转储和加载第2部分：基准测试
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> \
| mysqlmysqlpump
mysqlpump内置的压缩功能，既zlib和lz4，lz4是目前最快的选择，zlib非常缓慢。mysqlpump可以在多个线程中转储数据，但仅限于表级别。如果有一个很大的表，它将仅使用1个线程转储。mysqlpump生成一个类似于的SQL文件，并且加载数据是单线程的。
转储：
$ mysqlpump --default-parallelism=88 \
--compress-output lz4 \
--databases <db> \
> <file>加载：
$ lz4cat <file> | mysqlmydumper
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如何实现这种转储和加载性能。