Backup/Restore Performance Conclusion: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

MySQL Restore Backup ComparisonSlightly bit in the past, I launched a weblog put up evaluating the backup efficiency of various MySQL instruments akin to mysqldump, the MySQL Shell function referred to as Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You could find the primary evaluation right here:

Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup

Nonetheless, we all know the backups are simply the primary a part of the story. What concerning the restore time? And which instrument performs higher for the whole operation (backup+restore)?

Let’s see the outcomes and the conclusion within the following sections.

Benchmark Outcomes

I ran the benchmark on an m5dn.8xlarge occasion, with 128GB RAM, 32 vCPU, and two io1 disks of 600GB (one for backup and the opposite one for MySQL information) with 5000 provisioned IOPS. The MySQL model was 8.0.26 and configured with 89Gb of the buffer pool, 20Gb of redo log, and a pattern database of 96 GB (extra particulars under).

Once we sum the backup time and the restore time, we are able to observe the leads to the chart under:

MySQL Backup and Restore

And if we analyze the chart with out mysqldump to have a greater concept of how the opposite instruments carried out:


The backup measurement created by every instrument:

MySQL Backup Size

Word that the backup measurement of XtraBackup (with out compression) is the dimensions of the datadir with out the binary logs. Subsequent, we are able to see the backup time:

Time to execute MySQL backup

And the restore time:

Time to restore MySQL

Analyzing The Outcomes

Once we sum backup and restore occasions, we observe that the quickest instrument is Percona XtraBackup. The principle level of XtraBackup just isn’t even the velocity however its capability to carry out PITR backups. Additionally, the instrument helps compression and encryption.

We will additionally observe that mydumper/myloader and MySQL Shell utilities produce good leads to each phases. The distinction from Xtrabackup is that each instruments carry out logical backups, which signifies that these instruments connect with MySQL and extract the info to dump information. As a result of they need to extract information from MySQL, these instruments are extra delicate for the MySQL configuration and backup/restore parametrization. For instance, MyDumper/MyLoader has some further choices that may enhance the backup and restore efficiency, akin to --rows, --chunk-filesize, and --innodb-optimize-keys.

Word that  XtraBackup, MyDumper, and mysqldump assist stream restore, decreasing total timing to carry out the backup and restore operation.

The instrument that has probably the most inconsistent conduct is mysqlpump the place the instrument could make speedy backups, however the restore efficiency is horrible since it’s single-threaded the identical method as mysqldump.

Primarily based on the exams, we are able to observe that compression, TLS, socket, or TCP/IP don’t considerably influence the time wanted to carry out the entire operation. As a result of there isn’t any vital influence, instruments that may carry out compression and use TLS like MySQL Shell, mydumper/myloader, and XtraBackup have an excellent benefit since their backups are safer and use much less disk house (much less disk house = fewer prices). The trade-off between the options of those instruments and the time spent to backup and restore the database is one thing that every one DBAs ought to consider.

And to reply some questions/feedback about this matter:

The distinction you see between MySQL Shell and mydumper might be defined by means of SSL in a single and clear switch within the different. Encryption has a value, sadly. 

A: Certainly, SSL has a value. Nonetheless, once we put the safety advantages of the SSL and take into account the entire course of, it’s a small value (in the identical method as compression).

Does XtraBackup assist ZSTD? 

A: At this second, no. Nonetheless, there’s a function request for this (you possibly can observe the JIRA ticket to obtain updates about it):

https://jira.percona.com/browse/PXB-2669

Is there any distinction substituting mysqldump | gzip with a unique compression instrument?

A: The distinction is neglectable piping with gzip or sending the uncompressed dump to the disk. The mysqldump instrument is probably the most inefficient choice on account of its single-thread nature, severely impacting efficiency. Due to its single-thread nature, the instrument can not extract most efficiency from {hardware} assets (particularly I/O).

How is the efficiency influence on MySQL when operating the backups?

A: Sadly, I didn’t measure this. Primarily based on my expertise, there’s a devoted reproduction server for backup more often than not. If the MySQL group is on this take a look at, I can write one other put up about this (go away within the feedback your opinion).

It’s doable to squeeze extra juice from MySQL within the restore section. We will take some actions like disabling the binary log and making asynchronous writes. You may test the recommendation (professionals and cons) in these two weblog posts:

https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/

https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/

To conclude, this weblog put up is meant to provide an total concept of how these instruments carry out. I attempted to stay with the default choices of every instrument (besides the variety of threads) to maintain the take a look at as truthful as doable. Additionally, time just isn’t the one factor that corporations take into account to undertake a backup methodology (safety, encryption, and information safety are essential). In my each day duties, I exploit mydumper/myloader and XtraBackup as a result of I’m extra accustomed to the instructions, and I’ve used them for a very long time. Nonetheless, I might advise keeping track of the MySQL Shell utilities since it’s turning into a captivating instrument to carry out many duties (backup and restore have wonderful outcomes).

{Hardware} and Software program Specs

These are the specs of the benchmark:

  • 32 CPUs
  • 128GB recommended
  • 2x io1 disks 600 GB with 5000 IOPS
  • Centos 7.9
  • MySQL 8.0.26
  • MySQL shell 8.0.26
  • mydumper 0.11.5 – gzip
  • mydumper 0.11.5 – zstd
  • Xtrabackup 8.0.26

Leave a Reply

Your email address will not be published.