How did the MySQL ZRM do the job?
Did I use the correct method to backup?
The mysql-zrm-reporter tool provides backup statistics about the MySQL backup run. It is a good idea to review the backup-time, read-locks-time, flush-logs-time and backup-size to determine whether the method used by MySQL ZRM is appropriate for the backup set. The backup-performance-info - backup performance report and backup-app-performance-info - application impact reports are useful backup reports to tune backup process or switch to a different backup method.
An example command displaying backup size and backup time for a backup set backupSet2:
# mysql-zrm-reporter --fields "backup-date,backup-level,backup-size,backup-time" \ --where backup-set=backupSet2 backup_set backup_date backup_level backup_size backup_time ------------------------------------------------------------------------------------------------------- backupSet2 Sat 09 Sep 2006 10:00:37 AM PDT 0 54.26 MB 00:00:33 backupSet2 Sat 09 Sep 2006 10:01:41 AM PDT 0 64.23 MB 00:00:50 backupSet2 Sat 09 Sep 2006 10:03:30 AM PDT 0 74.98 MB 00:01:03
Using mysql-zrm-reporter tool, you can figure out what backup method was used to do the backup (backup-method-info predefined report). You can override the backup method by setting backup parameters - replication, logical-backup, lvm-snapshot in MySQL ZRM configuration for the backup set.
Can I make backups more efficient?
The time taken to do a backup (backup window) and backup image size depends on various factors:
- Backup method used for each database - logical backups, raw backups, lvm snapshot, MySQL replication
- Backup level for the backup set
- Size of the database
- Backup compression
- Database activity - read-only, read/write ratio
- Database transaction rates
- Recovery requirements - how often do you recover data? What is the reason for data recovery?
There are three tools available to analyze backup configuration bottlenecks and the impact of backups on the MySQL application. Often, it takes multiple backup runs to arrive at good set of parameters for a backup set.
- Use mysql-zrm-reporter tool and mysql-zrm logs to analyze the backups. The backup-performance-info report is a good starting point to analyze the bottlenecks in the backup strategy.
Example: Backup performance report
# /usr/bin/mysql-zrm-reporter --show backup-performance-info --where backup-set=backupSet1 backup_set backup_date backup_level backup_siz backup_size_compressed backup_time ------------------------------------------------------------------------------------------- ------------------ backupSet1 Thu 28 Sep 2006 1 10.90 MB ---- 00:00:10 12:00:13 AM PDT backupSet1 Wed 27 Sep 2006 0 90.75 MB ---- 00:03:32 11:59:58 PM PDT backupSet1 Wed 27 Sep 2006 1 12.93 MB ---- 00:00:13 11:59:46 PM PDT
- The backup-app-performance-report reports on the impact of backup on MySQL applications using the backup set. This report can analyze the application performance problems due to backups.
Example: Backup Application performance report :
# /usr/bin/mysql-zrm-reporter --show backup-app-performance-info --where backup-set=backupSet1 backup_set backup_date backup_level backup_siz backup_time read_locks_time flush_logs_time -------------------------------------------------------------------------------------------------------------- backupSet1 Thu 28 Sep 2006 1 10:90 MB 00:00:10 00:00:00 00:00:04 12:00:13 AM PDT backupSet1 Wed 27 Sep 2006 0 90:75 MB 00:03:32 00:00:03 00:00:20 11:59:58 PM PDT backupSet1 Wed 27 Sep 2006 1 12:93 MB 00:00:13 00:00:00 00:00:05 11:59:46 PM PDT
- MySQL ZRM provides Pre Scheduler plugin so that the backup window can be adjusted to minimize the backup impact on application. You can create a plugin to delay the backup run or skip the backup run based on the application or server load.