作者:Kenny Gryp 译: 徐轶韬
这是有关MySQL Shell Dump&Load的博客文章系列的第2部分:
关于新的MySQL Shell Dump&Load实用程序的第二部分旨在演示性能,同时还将其与其他各种逻辑转储和加载工具进行比较:mysqldump
,mysqlpump
&mydumper
。
为了使数字更有意义,我使用了一些在线提供的真实的生产数据集:stackoverflow.com,en.wikipedia.org,准时飞行数据以及这3种数据的组合。
数据库环境
测试是在具有大量CPU,RAM和存储性能的高端服务器上执行的。转储/加载工具和服务器都位于同一主机上。
ALTER INSTANCE DISABLE INNODB REDO_LOG;
MySQL配置(其他设置只是默认设置):
[mysqld]innodb_dedicated_server=oninnodb_numa_interleave=oninnodb_adaptive_hash_index=0innodb_change_buffering=nonelocal_infile=1skip_log_bininnodb_buffer_pool_instances=16innodb_read_io_threads=16innodb_write_io_threads=16innodb_io_capacity=40000innodb_io_capacity_max=60000innodb_max_dirty_pages_pct=10
转储和加载工具-用法
每种工具的转储和加载方式不同,如下所述。
mysqldump
转储:
$ mysqldump --single-transaction \ --databases \ | zstd -z
加载:
$ zstd -d --stdout \ | mysql
mysqlpump
mysqlpump
内置的压缩功能,既zlib
和lz4
,lz4
是
目前最快的选择,zlib
非常缓慢。
mysqlpump
可以在多个线程中转储数据,但仅限于表级别。如果有一个很大的表,它将仅使用1个线程转储。
mysqlpump
生成一个类似于的SQL文件,并且加载数据是单线程的。
转储:
$ mysqlpump --default-parallelism=88 \ --compress-output lz4 \ --databases \ >
加载:
$ lz4cat | mysql
mydumper
转储:
$ mydumper --rows 250000 \ -c \ --trx-consistency-only \ -t 88 \ -B \ -o
加载:
$ myloader -t 88 \ -d
MySQL Shell转储/加载
转储:
mysqlsh-js> util.dumpSchemas([""], "", {threads: 88, bytesPerChunk: "256M"})
加载:
mysqlsh-js> util.loadDump("", {threads: 88})
数据集
每个数据集均可下载。
准时飞行数据
1987年至2020年4月的航空公司航班准点统计。
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)
加载
除了禁用InnoDB重做日志,MySQL Server 8.0.21还包括对锁管理器的改进,它通过替换锁系统互斥体(lock_sys->mutex
)来提高InnoDB的写可伸缩性。这样可以提高写入性能,尤其是在NUMA节点距离较远的环境中(例如AMD Epyc Naples)。
警告:禁用InnoDB重做日志仅用于将数据加载到新的MySQL实例中。不要在生产系统上禁用重做日志记录,禁用重做日志记录时 服务器意外停止可能/将导致数据丢失和实例损坏。
二级索引影响写入吞吐量
使用stackoverflow和Wikipedia数据集的情况下,存在的二级索引越多,则预期的写吞吐量就越慢。
MySQL Shell能够在加载数据后(带有deferTableIndexes
选项)创建二级索引,但事实上,加载后添加索引会使数据集的整个过程变慢,因此通常不是最佳选择。mysqlpump
默认情况下会执行此操作,但与我们使用myloader和
MySQL Shell 获得的结果相比,性能提升可忽略不计。
分区可以帮助大表
那么为什么加载准时飞行数据表比其他数据集慢呢?
原因是准时数据集由单个表组成。瓶颈在InnoDB中表级别(索引锁定和页面锁定)。当我们对ontime表进行分区(在这种情况下为128个KEY()
分区)时,写入吞吐量显着增加,尤其是在使用MySQL Shell时:
当MySQL Shell跟踪加载进度时,首先可以通过仅加载DDL,进行更改,然后加载其余的转储来添加分区:
mysqlsh-js> util.loadDump("", {threads: 88 loadData: false})mysqlsh-js> \sql ALTER TABLE ontime.ontime PARTITION BY KEY() PARTITIONS 128;mysqlsh-js> util.loadDump("", {threads: 88})
MySQL Shell转储和加载:快!
如这些基准测试所示,MySQL Shell能够快速转储数据,最高可达3GB / s的速度,并以200MB / s以上的速度加载数据(禁用InnoDB重做日志时)。下一篇博客文章将介绍Shell如何实现这种转储和加载性能。
感谢您关注“MySQL解决方案工程师”!