MySQL Clone Plugin Speed Test

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

  1. Clone-Plugin
  2. Xtrabackup
  3. mysqldump
  4. mydumper with myloader
  5. mysqlpump

Test cases:

Hardware Configuration:

Two standalone servers are picked up with identical configuration.

Server 1
   * IP: 172.23.26.127
   * CPU: 2 Cores
   * RAM: 4 GB
   * DISK: 200 GB SSD

Server 2
   * IP: 172.21.3.114
   * CPU: 2 Cores
   * RAM: 4 GB
   * DISK: 200 GB SSD

Workload Preparation:

  • On Server 1 (172.23.26.127), We have loaded approx 122.40 GB data.
  • Now, We want to restore the data from Server 1 (172.23.26.127) to Server 2 (172.21.3.114).
  • MySQL Setup
    • MySQL Version: 8.0.17
    • InnoDB Buffer Pool Size: 1 GB
    • InnoDB Log File Size: 16 MB
    • Binary Logging: On
  • Before starting every test, MySQL server is rebooted.
  • Sysbench is setup to generate active writes across 10 tables.
    # sysbench oltp_insert.lua --table-size=2000000 --num-threads=2 --rand-type=uniform --db-driver=mysql --mysql-db=sysbench --tables=10 --mysql-user=test --mysql-password=****** prepare
    
    Initializing worker threads...
    
    Creating table 'sbtest1'...
    Creating table 'sbtest2'...
    Inserting 2000000 records into 'sbtest1'
    Inserting 2000000 records into 'sbtest2'
    Creating a secondary index on 'sbtest1'...
    Creating a secondary index on 'sbtest2'...

 

Case 1: (MySQL Clone Plugin) 

  • MySQL Clone Plugin is the first in the queue for testing.
  • To read more on Clone Plugin Setup, Check out my Previous Blog
  • Cloning can be initiated by firing a simple SQL as below.
mysql> clone instance from mydbops_clone_user@172.23.26.127:3306 identified by 'XXXX';
Query OK, 0 rows affected (7 min 47.39 sec)

Restoration:

  • Once the clone is complete the plugin will be prepare the data and restart the mysql with in 1 minute.

Logs From Performance Schema:

Stage Status Start time End time
DROP DATA Completed 2019-10-24 14:16:19 2019-10-24 14:16:19
FILE COPY Completed 2019-10-24 14:16:19 2019-10-24 14:23:56
PAGE COPY Completed 2019-10-24 14:23:56 2019-10-24 14:23:57
REDO COPY Completed 2019-10-24 14:23:57 2019-10-24 14:23:57
FILE SYNC Completed 2019-10-24 14:23:57 2019-10-24 14:24:06
RESTART Completed 2019-10-24 14:24:06 2019-10-24 14:24:11
RECOVERY Completed 2019-10-24 14:24:11 2019-10-24 14:24:12

Overall Duration: 7 min 47 sec

Case 2: (Xtrabackup)

  • Next one on the queue is Xtrabackup 8.0. Streaming backup has been initiated from Server 1 (172.23.26.127) to Server 2 (172.21.3.114).
# xtrabackup --user=XXX --password='XXXX' --backup --no-timestamp --no-lock --stream=xbstream | ssh root@172.21.3.114 "xbstream -x -C /var/backup"
start_time = 2019-10-24 07:53:02
end_time   = 2019-10-24 08:10:08
  • It’s took around 16 min to complete.

Restoration:

  • Once the backup is complete the preparation and restoration taking around 8 min to complete.
xtrabackup --prepare --target-dir /var/lib/mysql
.
.
.
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 31714228528
191024 08:14:10 completed OK!

Overall Duration: 24 min

Case 3: (mysqldump)

  • Now it’s turn for mysqldump and backup took around 43 mins to complete.
# mysqldump -u mydbops -h XXXXX -p'XXXXX' -P3306 
--single-transaction --routines --events --triggers 
--master-data=2 --all-databases > /backup/fullbackup.sql

Restoration:

  • The backup restoration is taken around 52 min’s.

Overall Duration: 95 min

Case 4: (mydumper)

  • The mydumper took 39 min to complete. mydumper is setup to run on 2 threads as it’s dual core machine.
# mydumper --host=xxxx --user=mydbops --password='XXXXX' 
--triggers --events --routines -v 3 --outputdir=/backup/mydumper_backup

Restoration :

  • The backup restoration is taken around 46 mins to complete.
# myloader --user=mydbops --password='XXXXX' --host=xxxx 
--directory=/backup/mydumper_backup --queries-per-transaction=5000 
--threads=2 --verbose=3 -e 2> /backup/restore_sep_26.log

Overall Duration: 85 min

Case 5: (mysqlpump)

  • The mysqlpump is completed with in 37 mins to complete.
# mysqlpump --user=mydbops --password='XXXXX' 
--host=xxxx --default-parallelism=2 > pump.sql

Restoration:

  • The backup restoration is taken around 41 mins to complete
# mysql --user=mydbops --password='XXXX' < /home/vagrant/pump.sql

Overall Duration: 78 min

Test Observations:

Backup and Restore Time_new

Physical backups are faster as expected, Surprising to see clone plugin beats Xtrabackup. If you’re using MySQL 8.0, Clone plugin is worth a try, If you’re running older versions of MySQL, You can happily choose Xtrabackup.

MySQL Cloning and Xtrabackup Differences:

  • Both the Cloning and xtrabackup are physical backups (copying of data files), It can be used to perform hot backup and recovery (can be used on live databases) and the principle of backup recovery is also similar.
  • The permission of xtrabackup backup file is equal to the permission of the person who executes the command. When restoring the instance, it needs manual chown to return to the instance permission.
  • After cloning and backup, the permission is identical with the original data permission, and no manual chown is needed to facilitate recovery.
  • When restoring xtrabackup, reset master needs to be executed in mysql, then set global gtid_purged=”UUID:NUMBER”.
  • The specific value of UUID:NUMBER is the content of xtrabackup_info file in backup file ,But cloning does not need this operation step, and by default cloning can establish replication.
  • When the backup of xtrabackup is completed, scp is usually copied to another machine to recover. It takes port 22 and MySQL’s listening port is cloned.
  • So when the directory permissions are correct, you don’t even need the permissions to log on to the Linux server at all.
  • Do remember Clone is supported only from MySQL 8.0.17 and xtrabackup is a full fledge backup with more robust options.

Featured image by Pietro Mattia on Unsplash

One thought on “MySQL Clone Plugin Speed Test

  1. > xtrabackup –user=XXX –password=’XXXX’ –backup –no-timestamp –no-lock –stream=xbstream | ssh root@172.21.3.114 “xbstream -x -C /var/backup”

    1) Have you tried xtrabackup with the –parallel option?

    From https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/xbk_option_reference.html

    > –parallel=#
    >
    > This option specifies the number of threads to use to copy multiple data files concurrently when creating a backup. The default value is 1 (i.e., no concurrent transfer). In Percona XtraBackup 2.3.10 and newer, this option can be used with the –copy-back option to copy the user data files in parallel (redo logs and system tablespaces are copied in the main thread).

    Since you have only two tables, you can probably only go twice as fast, with –parallel=2, but that would get you quite close.

    2)Have you tried using some other protocol to transfer the files? ssh is not optmised for bulk throughput and with your 2 cores, the additional encryption overhead you are imposing by using ssh may play a role, so for this comparison you should probably use nc instead (you don’t seem to be using tls in your clone setup).

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s