Optimizations

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Jump to navigationJump to search

Regular consistent backups of databases with minimal impact on the application using the database is the goal of all database adminstrators. The backup process should also minimise the use of CPU, memory and network resources on the MySQL server. Optimal backup method and backup parameters must be choosen to keep the backup window as small as possible. The backup parameters depends on various factors - number of databases, number of MySQL servers, MySQL configuration, level of security required.

ZRM for MySQL provides an extensive list of parameters to tune the backup process for the user environment. It also tracks lots of backup parameters that are available to the administrator to tune the backup process.

This section shows an example of doing full backup of a local InnoDB database of size 6GB using various backup parameters. We are considering four measurements - backup time, restoration time, verification time and the backup size for different combination of parameters. Backup and verification of backups are regular activities performed by a database administrator. Backup verification does not impact the MySQL database or the application using it. When there is a failure, it is important to restore the database as soon as possible. The size of backup helps in determining the amount of backup storage required.

Backup was done using mysql-zrm-scheduler. The backup time and backup size were obtained from the backup-performance-info report. A read-only application was using the database when the backups were being done.

# mysql-zrm-scheduler --now --backup-set dailyrun1
# mysql-zrm-reporter --show backup-performance-info --where backup-set=dailyrun1

The verification time was obtained by verifying backup images using the mysql-zrm command verify-backup action.

# time mysql-zrm --action verify-backup --backup-set dailyrun1

Restoration time is the time taken to restore the complete database. Time taken to restore can be obtained from the output of the mysql-zrm command and is also logged to the mysql-zrm log (/var/log/mysql-zrm/mysql-zrm.log) file.

# mysql-zrm --action restore --backup-set dailyrun1  \
  --source-directory  /var/lib/mysql-zrm/dailyrun1/20061115154319
....
INFO: Restore done in 276 seconds.
MySQL server has been shutdown. Please restart after verification.

Computing checksums asynchronously can result in considerable reduction in the backup time for large databases. Checksum computation for multi-terabyte databases can take couple of hours.

Configuration 1 (default parameters; logical backup)

The database is backed up locally using logical backup method with no backup compression or encryption (default configuration for InnoDB database). MySQL server parameters are not shown.

backup-level=0
backup-mode=logical

Configuration 2 (backup compression)

To reduce the size of backup, we can use backup compression using the gzip program (default compression method). To do backup compression, the following parameter was added to mysql-zrm.conf.

compress=1

Configuration 3 (backup compression and encryption)

To improve security of the backup images, the backup image is encrypted using the gpg program - Gnu Privacy Guard. The default encryption plugin supports data encryption using gpg. It may not be necessary to encrypt the database backup if the database contents are encrypted using MySQL. The following additions should be made to mysql-zrm.conf to do backup encryption:

encrypt=1
encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt-plugin.pl"
decrypt-plugin="-d"

Configuration 4 (raw backups using lvm snapshot)

Since the databases are stored in LVM2 logical volumes, it is possible to do backups in "raw" mode using lvm snapshots. In this configuration, encryption parameters added in configuration 3 were removed. To do "raw" backups with backup compression, add the following to mysql-zrm.conf from configuration 2:

backup-mode=raw
lvm-snapshot=100M

Results

The following table shows the comparison of backup measurements for various configurations:

Configuration Backup size Backup size(compressed) Backup time Verification time Restoration time
1 (default) 2.99GB - 1260s 90s 3551s
2 (compression) 2.99GB 681.15MB 1345s 285s 3723s
3 (compression + encryption) 2.99GB 682.90MB 1296s 328s 3713s
4 (compression + raw backups using lvm snapshot) 4.84GB 1.71GB 1442s 236s 276s


Even though the backups using LVM snapshots (Configuration 4) took lot of time, the time taken by the actual backup was not high. Most time was spent in doing the backup compression. This information can be obtained from the backup-performance-info backup report. The backup compression took 1221 seconds (this time is included in the backup time). The actual backup took 221 seconds.

backup_set backup_date backup_level backup_size backup_size_compressed backup_time compress_encrypt_time
dailyrun1 Wed 15 Nov 2006 03:43:19 PM PST 0 4.84 GB 1.71 GB 00:24:02 00:20:21


The backup application performance report for LVM snapshot raw backup shows that the application impact was minimal. Read locks are not obtained during "raw" InnoDB storage engine backups using LVM snapshots.

backup_set backup_date backup_level backup_size backup_time read_locks_time flush_logs_time
dailyrun1 Wed 15 Nov 2006 03:43:19 PM PST 0 4.84 GB 00:24:02 00:00:00 00:00:00


The same set of backup parameters will not yield optimal backup performance results for 
all MySQL configurations. It will be useful to tune the backup parameters before putting
ZRM for MySQL to production use and watch these parameters over time. The application  
characteristics can also change over time.