MySQL Enterprise Backup: parallel config & backup n restore results.

In this post I go into some performance metrics and time spent on using MySQL Enterprise Backup instead of mysqldump, and seeing how far I could go with some parallel configuration.

Setup:

It’s on an old laptop:

–Ubuntu 12.04 LTS, 32bit Intel Pentium M 1.86Ghz, 2Gb
–Source disk:  internal 80Gb ATA ST9808211A
–Destination:  external 1Tb SAMSUNG HD103SI
–MySQL Enterprise Edition 5.6.15
–MySQL Enterprise Backup 3.9.0
Employees sample database duplicated via MySQL Utilities 1.3.6 (on Win7 PC) to generate a ~5Gb MySQL Server.
And to simulate data size, I used the MySQL Utilities:
mysqldbcopy --source=root:pass@host:3356 --destination=root:pass@host:3356 employees:employees1 \
employees:employees2 employees:employees3 employees:employees4 ... employees:employees18 \
employees:employees19 employees:employees20
But before we jump right in, how fast can I expect to go anyway. Simple, nothing fancy:
local disk:
dd if=/dev/zero of=/root/ddIOtest1 bs=1G count=1 oflag=direct
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.0825 s, 26.1 MB/s
external HD disk:
dd if=/dev/zero of=/media/1T_iomega/ddIOtest1 bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.214 s, 26.1 MB/s
To external HD disk via symbolic link:
dd if=/dev/zero of=/home/mysql/MEB/test/ddIOtest1 bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 41.3047 s, 26.0 MB/s

Other dd tests were run: bs=300M count=3 @ 26.8 MB/s, bs=200M count=5 @ 26.7 MB/s, bs=100M count=10 @ 26.7 MB/s, bs=300M count=5 @ 26.1 MB/s.

Ubuntu helped a little with some nice pretty graphs for the internal & external disk:

mysqldump:

So the times we’re looking at before we go Enterprise are:

Backup:

mysqldump -uroot -ppass -S /tmp/mysql.sock --all-databases --add-drop-database -r mysqldump_full.sql
start: Fri Jan  3 13:30:36 CET 2014
end:   Fri Jan  3 13:41:17 CET 2014
10:41 min.
$ du –sk     3453640 mysqldump_full.sql
5387 Kb/sec.

Restoring:

$ cp /usr/local/mysql/my.cnf mysqldump_full_test
$ cd mysqldump_full_test
$ vi my.cnf  
    (change all file paths, port numbers, change server-id (just in case) and add a new socket dir.)
$ cd /usr/local/mysql
$ scripts/mysql_install_db --defaults-file=/home/mysql/MEB/test/mysqldump_full_test/my.cnf \
--user=mysql --datadir=/home/mysql/MEB/test/mysqldump_full_test/datadir
$ mysqld_safe --defaults-file=/home/mysql/MEB/my.cnf --user=mysql \
--datadir=/home/mysql/MEB/test/mysqldump_full_test/datadir --socket=/tmp/mysql_mysqldump_restore.sock \
--port=13356
$ mysqladmin -uroot password ‘pass' -S /tmp/mysql_mysqldump_restore.sock
$ mysql -uroot -ppass -S /tmp/mysql_mysqldump_restore.sock
mysql> show databases;
(To avoid the GTID error, you might have to execute "reset master;" before trying the restore.)

mysql -uroot –ppass -S /tmp/mysql_mysqldump_restore.sock < mysqldump_full.sql
start: Fri Jan  3 15:16:45 CET 2014
end:   Fri Jan  3 16:26:26 CET 2014
1:09:41 min. (1hr 9 mins 41 sec.) 4181 sec.
$ du –sk   4974081 (21x employee schemas dir's)
1189.68 Kb / sec.

MySQL Enterprise Backup:

Now to use ‘mysqlbackup’:

mysqlbackup --user=root --socket=/tmp/mysql.sock --backup-dir=/home/mysql/MEB/test --with-timestamp \
 backup
And the results:
start: Tue Dec 31 13:09:05 CET 2013
end:   Tue Dec 31 13:13:56 CET 2013
5054864 2013-12-31_13-09-05
4:51 min.
17370.66666666667 Kb/sec.

Configuration and Performance Times:

Parallel Backup
By using parallelization configuration we can improve backup performance significantly. The following is the default setting for MEB 3.9 (for more information see parallel backup description):
--read-threads=1 --process-threads=6 --write-threads=1 --limit-memory=300

And again, the results:

read threads

process threads

write threads

limit memory

number

of buffers

(16M each)

Backup

size

(Kb)

Duration (hh:mm:ss)

Kb/sec

1

6

1

300

14

5054864

00:04:50

17430.57

2

3

2

300

10

5054872

00:04:32

18584.09

3

6

3

600

18

5054872

00:04:11

20138.93

3

3

3

300

12

5054868

00:03:57

21328.56

4

4

4

300

16

5054872

00:03:51

21882.56

4

4

4

320

16

5054876

00:03:37

23294.35

4

4

4

640

16

5054876

00:03:37

23294.35

4

8

4

640

20

5054876

00:03:37

23294.35

(apologies for the formatting)

Restoring:

Scenario specifics:

We want to restore a single table, Partial Recovery, not the whole server.

  • The table to be restored is on a running server and can’t be stopped.
  • No compression is being used.

What about having specified –user-tts at backup time?

  • Backups taken with this option are meant to transport the table to another, new environment, i.e. tables can’t be restored selectively from these types of backups.
  • The tables to be restored can’t exist on the destination server.
  • mysqlbackup is already flexible enough to be able to single out a table and restore it via the transportable tablespace definition, i.e. discard / import tablespace.

Converting the on-disk backup to image.

First convert to image, so that we can then extract the table, or apply-logs from incremental backups taken post-Full backup:

mysqlbackup --user=root --socket=/tmp/mysql.sock \ 
--backup-image=/home/mysql/MEB/test/2014-01-02_13-57-17.mbi \
--backup-dir=/home/mysql/MEB/test/2014-01-02_13-57-17 --read-threads=4 \
--write-threads=4 --process-threads=8 --limit-memory=320 backup-dir-to-image
Start: 140102 15:57:34
End:   140102 16:04:50
7:16 min.
5054052 /home/mysql/MEB/test/2014-01-02_13-57-17.mbi
11591.86 Kb/sec.

Or…

… have backed up directly to the single image file :

mysqlbackup --user=root --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi –backup-dir=/home/mysql/MEB/test/full \
--with-timestamp --read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 \
backup-to-image
Start: 140102 14:43:01
End:   140102 14:47:43
4:42 min.
5054124 full.mbi
16735.50 Kb/sec

Now to restore…

Native Transportable tablespace

Knowing we’re going to use transportable tablespace from the image backup file, and that the table has just lost some rows (as if it had been deleted, we’d have to recover in another env and then mysqldump it out).

First check the image file and list the table location:

mysqlbackup --user=root --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi --read-threads=4 --write-threads=4 \
--process-threads=8 --limit-memory=320 --sleep=0 list-image

Restore:

mysql> lock tables salaries write;
mysql> alter table salaries discard tablespace;
mysqlbackup --user=root --socket=/tmp/mysql.sock --backup-image=/home/mysql/MEB/test/full.mbi \
--read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 --sleep=0 \
--src-entry=datadir/employees/salaries.ibd --dst-entry=/opt/mysql/5615/data/employees/salaries.ibd \
extract
Start: 140102 16:48:04
End:   140102 16:48:09
0:05 min.
143360 /opt/mysql/5615/data/employees1/salaries.ibd
28672.00 Kb/sec.
mysql> alter table salaries import tablespace;
mysql> desc salaries;

A Full Restore

Consider that the table has been deleted and we need to restore the full backup.

  • Restore the image backup to a new backup / datadir.
  • Start the restored environment with a modified my.cnf.
  • Export & import the desired data for the affected table into the working environment.
  • Carry on working with some minor disruption to the affected table.

The details:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql.sock \
--backup-image=/home/mysql/MEB/test/full.mbi –backup-dir=/home/mysql/MEB/test/full-img-restore \
--read-threads=4 --write-threads=4 --process-threads=8 --limit-memory=320 --sleep=0 \
image-to-backup-dir
start: Thu Jan 2 16:58:28 CET 2014
end:   Thu Jan 2 17:04:46 CET 2014
6:18 min.
5054972 full-img-restore
13372.94 Kb/sec.

Now, we can start up the restored env to use and abuse the salaries table we’re worried about:

$ cd /hoe/mysql/MEB/test/full-img-restore
$ vi server-my.cnf
  change all paths (datadir, socket, pid_file, log_bin, log_bin_index, log_error, port & report 
  port if using replication.
$ mysqld_safe --defaults-file=/home/mysql/MEB/test/full-img-restore/server-my.cnf --user=mysql \
--datadir=/home/mysql/MEB/test/full-img-restore/datadir -P13356 --socket=/tmp/mysql_restore.sock &
$ mysql -uroot –ppass -S /tmp/mysql_restore.sock
mysql> use employees
mysql> show table like salaries;

Now to double check the instance where we’re missing the salaries table:

$ mysql -uroot –ppass -S /tmp/mysql.sock
mysql> use employees
mysql> show table status;
mysql> show create table salaries;
ERROR 1146 (42S02): Table 'employees.salaries' doesn't exist

So now to import / recreate the previously dropped ‘employees.salaries’ table, and check times, after all, we are interested:

mysqldump –uroot –ppass -S /tmp/mysql_restore.sock --tables employees salaries | mysql -uroot –ppass \
-S /tmp/mysql.sock employees
Start: Fri Jan 3 13:16:38 CET 2014
End:   Fri Jan 3 13:19:37 CET 2014
2:59 min
143364 /opt/mysql/5615/data/employees/salaries.ibd
800.91 Kb/sec.

Checking & Alternatives

MySQL Utilities 1.3.6

Once the backup has been restored in another environment, use MySQL Utilities ‘mysqldiff’ to see if it’s missing:

mysqldiff --server1=root:oracle@141.144.12.45:3356 --server2=root:oracle@141.144.12.45:13356  \
employees.salaries:employees.salaries

If the row number is different, mysqldiff doesn’t detect this, it’s only for object and structure differences.

# server1 on 141.144.12.45: ... connected.
# server2 on 141.144.12.45: ... connected.
# Comparing employees.salaries to employees.salaries         [PASS]
Success. All objects are the same.

Use Utilities mysqldbcompare to get exact changes and data consistency checking. Saves having to restore the whole table:

mysqldbcompare --server1=root:oracle@141.144.12.45:3356 --server2=root:oracle@141.144.12.45:13356 \
employees:employees -a -d differ –vvv

mysqlfrm could be used, however be wary of auto-increment sequences & Foreign Keys.

Also restore the .frm from the backup image fileset, to change the datadir for that specific tablespace and then use mysqldump | mysql to import the row data.

 Conclusion

mysqlbackup restores the single transportable tablespace at 28672.00 Kb/sec where mysqldump is done at 800.91 Kb/sec: 35.79 times faster.

(In this scenario, a 4 column table with 2844047 rows).

About these ads

Tags: , , , , , , , , , ,

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: