<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MySQL / MariaDB and ZFS filesystem –  A good combination for backup and Disaster Recovery.

Published on
5,837 Points
2,837 Views
Last Modified:

Why snapshot as backup-strategy?

When working with large to huge databases it is critical to take a backup of the database for disaster recovery and data failures that application either cannot recover or regenerate corrected data. For a database of such size it is vital that backup time is as short as possible to minimize application downtime/disruption due to backups. Regular backups can take several hours or more to complete for very large to huge databases.
The fastest way today to take a database backup is using the snapshot technology (sometimes called FlashCopy) either on a filesystem level or if the database has such technology implemented.


ZFS filesystem and snapshots

ZFS uses copy-on-write transactional object model allowing old data to be retained as new data is written and allowing snapshot versions of the filesystem to be maintained.
Here I will demonstrate how you can use the Solaris zfs filesystem (ZFS on FUSE) to backup MySQL/MariaDB database in as short time as possible.


Preparing the VM

For this demonstration I'm using Centos 6.5 64 bit with zfs-fuse in a VirtualBox VM with 1 Core (2.3 MHz AMD A10 ) and 1GB RAM, even though recommended minimum for zfs-fuse is 2 GB.
The VM disks (VDI format) are as follows
  • 20 GB disk with the Centos 6.5 system installed.
  • Three empty 5 GB disks that will be under the zfs filesystem.  
First thing is to make sure the SELINUX is disabled and reboot the VM after editing the config file as root :
sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config; shutdown -r now

Open in new window


Then  install the zfs-fuse package from the epel repo.

yum install zfs-fuse

Open in new window


zfsinstall.jpgAfter that, start the service and configure it to start on boot

service zfs-fuse start
chkconfig zfs-fuse on

Open in new window


and configure it to use the three empty disks (here the disks are /dev/sdb, /dev/sdc and /dev/sdd )
zfsinstall2.jpgzfsinstall3.jpgI'm using the MariaDB version 10.1 as I find that version interesting in respect of performance and as the Centos 6.5 installs the MySQL v 5.1 you will need to remove the mysql-server and reinstall it using the MariaDB repos.
mariadbrepo.jpgTo do this you have follow these upgrade steps.

After you have done that, or if you still have the MySQL 5.1 version, your next step is to move the database from the original path to a new path on the zfs pool (make sure that the database is shutdown before).
movedatabase.jpgBefore moving the database to the zfs filesystem my.cnf needs this line in correct place (here I use MariaDB ).

[mariadb]
innodb_use_native_aio = 0

Open in new window

 

To test the creation of a database is successful on the zfs filesystem you simply create a database. createtestdatabase1.jpgYou should also look for it in the new path on the zfs filesystem.
createtestdatabase2.jpg


Snapshot demostration and comparison

Measuring the backup time of regular mysqldump vs zfs snapshot
And now for the interesting part : the comparison of the backup methods.
For this test I use the employee database from Launchpad.net  which installed  is roughly 188 MB in size. The whole database system (MySQL directory ) takes 347 MB of the zfs pool/filesystem.

testdatabasesize.jpgThe mysqldump takes 17.406 seconds while the zfs snapshot takes 0.388 seconds which is 45 times faster (98 % improvement). The transfer of the backup to another disk/location takes 21.271 seconds.

mysqldumptiming1.jpg zfssnapshot1.jpgzfssnapshot2.jpgAdding 1 GB RAM to the VM makes the snapshot even slight faster. The zfs snapshot takes 0.216 seconds which is 80 times faster (99 % improvement) than the mysqldump. And the transfer of the backup to another disk/location takes 13.098 seconds.

zfssnapshot3.jpg

Conclusion.

Here I have demonstrated how MySQL / MariaDB and ZFS is a good combination when it comes to backup large to huge databases (even though the sample database used was pretty small ) for disaster recovery as the backup time using zfs snapshot is many times faster than regular backups as these results shows. Keep in mind that even though the database grows the snapshot time increases a lot less than the mysqldump time. In fact, as the mysqldump backup times grow the snapshot time becomes a smaller fraction of it.
Be aware that both the database, VM and zfs filesystem needs little more tweaking for more and optimal performance which can only further improve those backup times.

Using ZFS you have also other nice features that include filesystem mirroring and fast filesystem/database cloning at any time which can come in handy.  Also note that you are able to use gzip to compress the backups on the fly (which requires some more memory to be fast) in both mysqldump and zfs snapshot commands.

For more info on ZFS you could look at this wiki as well as some other googling.
0
Comment
0 Comments

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month