作者: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=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
没有内置压缩功能,转储输出已通过管道传递到zstd
。mysqldump
,转储和加载都是单线程的。转储:
$ mysqldump --single-transaction \
--databases <db> \
| zstd -z <file>
加载:
$ zstd -d --stdout <file> \
| mysql
mysqlpump
内置的压缩功能,既zlib
和lz4
,lz4是
目前最快的选择,zlib
非常缓慢。mysqlpump
可以在多个线程中转储数据,但仅限于表级别。如果有一个很大的表,它将仅使用1个线程转储。mysqlpump
生成一个类似于的SQL文件,并且加载数据是单线程的。转储:
$ mysqlpump --default-parallelism=88 \
--compress-output lz4 \
--databases <db> \
> <file>
加载:
$ lz4cat <file> | mysql
mydumper
能够并行转储数据,并且在使用--rows
选项时还可以并行转储单个表。 myloader
还提供并行加载数据。转储:
$ mydumper --rows 250000 \
-c \
--trx-consistency-only \
-t 88 \
-B <db> \
-o <directory>
加载:
$ myloader -t 88 \
-d <directory>
转储:
mysqlsh-js> util.dumpSchemas(["<db>"],
"<directory>",
{threads: 88,
bytesPerChunk: "256M"})
加载:
mysqlsh-js> util.loadDump("<directory>",
{threads: 88})
每个数据集均可下载。
1987年至2020年4月的航空公司航班准点统计。
stackoverflow.com数据
EN维基百科备份,始于20200620
binary
数据类型合并以上所有数据集
是时候显示一些结果了……
mysqldump
与mysqlpump
缺乏或受限于并行转储。mysqlpump
,所有数据集中只有足够的大表才能提高转储吞吐量。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
文件,加载数据是单线程的,因此它们慢得多。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能够快速转储数据,最高可达3GB / s的速度,并以200MB / s以上的速度加载数据(禁用InnoDB重做日志时)。下一篇博客文章将介绍Shell如何实现这种转储和加载性能。
本文分享自 MySQL解决方案工程师 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!