How do I tune MySQL backup performance?

Goal of the MySQL system administrator is to have regular consistent backups of the databases with minimal impact on the application using the database. The MySQL 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 for databases, number of MySQL servers, MySQL configuration, level of security required.

ZRM for MySQL provides 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 the 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 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, backup size was obtained from 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 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 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.

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 gzip program (default compression method). To do backup compression, 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 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. 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 the configuration 3 were removed. To do "raw" backups with backup compression, add the following to mysql-zrm.conf to the mysql-zrm configuration file from the 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

Eventhough, 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.