Optimizations: Difference between revisions
No edit summary |
No edit summary |
||
(One intermediate revision by one other user not shown) | |||
Line 1: | Line 1: | ||
Regular consistent backups of | 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 extensive list of [[Backup_Set_Parameters|parameters]] to tune the backup process for the user environment. It also tracks lots of [[Backup_Set_Parameters#Backup_parameters|backup parameters]] that are available to the administrator to | ''ZRM for MySQL'' provides an extensive list of [[Backup_Set_Parameters|parameters]] to tune the backup process for the user environment. It also tracks lots of [[Backup_Set_Parameters#Backup_parameters|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 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. | 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 | 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-scheduler --now --backup-set dailyrun1 | ||
# mysql-zrm-reporter --show backup-performance-info --where 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. | 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 | # 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. | 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 \ | # mysql-zrm --action restore --backup-set dailyrun1 \ | ||
--source-directory /var/lib/mysql-zrm/dailyrun1/20061115154319 | --source-directory /var/lib/mysql-zrm/dailyrun1/20061115154319 | ||
Line 19: | Line 19: | ||
MySQL server has been shutdown. Please restart after verification. | 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) === | === Configuration 1 (default parameters; logical backup) === | ||
Line 29: | Line 30: | ||
=== Configuration 2 (backup compression) === | === 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]]. | 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 | compress=1 | ||
Line 35: | Line 36: | ||
=== Configuration 3 (backup compression and encryption) === | === Configuration 3 (backup compression and encryption) === | ||
To improve security of the backup images, the backup image is encrypted using ''gpg'' program - [http://www.gnupg.org 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. | To improve security of the backup images, the backup image is encrypted using the ''gpg'' program - [http://www.gnupg.org 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=1 | ||
Line 43: | Line 44: | ||
=== Configuration 4 (raw backups using lvm snapshot) === | === 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 | 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 | backup-mode=raw | ||
Line 65: | Line 66: | ||
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 [[Pre-defined_Reports|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. | |||
<br> | <br> | ||
<table class="main" border="2" bordercolor="blue" > | <table class="main" border="2" bordercolor="blue" > |
Latest revision as of 18:51, 3 April 2008
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.