RMAN Performance Tuning Using Buffer Memory Parameters
Introduction
The purpose of RMAN performance tuning is to identify the bottlenecks for a given backup or restore job and use RMAN commands, initialization parameters, or adjustments to physical media to improve overall performance.
As database sizes continue to grow unabated, with tens to hundreds of terabytes prevalent in customer’s environments, server and storage systems have also dramatically increased in resource, network, and I/O capacity to support required database backup and restore windows. Against this backdrop, RMAN performance tuning becomes more important than ever.
This document details RMAN performance tuning guidelines as they relate to buffer memory usage during backup and restore operations. These guidelines are targeted to systems where the available I/O exceeds 100 MB/s, while memory, CPU, network consumption, and tape drive throughput do not constrain overall RMAN performance. This is especially true in very high performance configurations, such as Exadata. In these environments, the default RMAN buffer sizes are usually inadequate and should be increased.
It is assumed that the reader has a basic understanding of RMAN performance tuning concepts. See this doc for an overview of RMAN tuning.
RMAN Buffer Usage
During backup, datafile blocks are read into a set of input buffers, where they are validated/compressed/encrypted and copied to a set of output buffers. The output buffers are then written to backup pieces on either disk or tape (DEVICE TYPE DISK or SBT). This process is depicted in the below diagram.
Conversely, on restore, blocks are read from the backup piece into a set of input buffers, where they are validated/uncompressed/decrypted and copied to a set of output buffers. The output buffers are then written back to data files on disk.
By default, buffers are allocated from the PGA, unless DISK or TAPE I/O slaves are used, in which case buffers are allocated from the SGA or large pool (if LARGE_POOL_SIZE is set).
The number and size of the buffers used for each RMAN job can be found in the views V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO.
The following guidelines for setting RMAN memory usage are organized by Oracle release.
Tuning guideline:
• When increasing buffer size using any of the following underscore parameters, it is recommended to increase both input and output buffer sizes simultaneously for a given operation
Oracle Database 11g Release 11.1.0.7 and prior releases
When creating backup sets:
Input buffers are allocated based on the level of multiplexing (i.e. number of files read per channel), according to the below chart:
Multiplexing = Min (MAXOPENFILES, FILESPERSET)
Defaults: MAXOPENFILES=8, FILESPERSET=64
For example, if MAXOPENFILES=1, each channel uses 16 buffers, each of size 1MB, for a total of 16 MB allocated per channel.
Tuning guideline:
• The number and size of input buffers can be changed by using the undocumented parameters _backup_ksfq_bufcnt and _backup_ksfq_bufsz.
• When using these parameters to increase backup I/O throughput, it is recommended that _backup_ksfq_bufsz be first set to the stripe size of the files being read and to test the effect that setting has on performance.
• If further tuning is needed, then set _backup_ksfq_bufcnt to the number of disks which the files are striped over. In the case of massively striped systems, such as ASM, this parameter should be used judiciously to achieve an optimal balance between performance and memory usage.
o For example, if there are 500 disks in your ASM diskgroup, setting _ backup_ksfq_bufcnt to 500 would use an excessive amount of memory for each RMAN channel. A more reasonable setting, such as 32 or 64, provides a better balance between performance and memory usage
When creating or restoring from image copies:
The size and number of input buffers default to 1 MB/buffer and 4 buffers.
Tuning guideline:
• Input buffer sizes for image copies can be increased using _db_file_direct_io_count.
When creating backup sets or image copies:
Output buffers are allocated based on device type:
Tuning guideline:
• For DISK backup, the output buffer size can be increased using _db_file_direct_io_count.
• Note that _db_file_direct_io_count controls other database I/O operations, not just RMAN, and therefore its adjustment should be carefully tested.
• For SBT backup, the output buffer size can be increased using BLKSIZE channel parameter.
• Note that BLKSIZE does not apply to Oracle Secure Backup. In this case, buffer sizes are automatically calculated for optimal transfer speeds.
When restoring from backup sets:
The default size and number of output buffers is 128 KB/buffer and 4 buffers.
Tuning guideline:
• The number of output buffers can be increased using _backup_ksfq_bufcnt.
• The output buffer size can be increased using _backup_ksfq_bufsz.
• Note that the default buffer sizes for restoring files are smaller than what are used when backing up those same files. These buffers should be increased to match the corresponding backup if restore times are significantly slower than the corresponding backups.
When restoring from image copies:
The default size and number of output buffers is 1 MB/buffer and 4 buffers.
Tuning guideline:
• Output buffer size can be increased using _db_file_direct_io_count.
Oracle Database 11g Release 11.1.0.7 + Patch 8369105 (also in Exadata V1 Bundle Patch 2)
Input and output buffers are allocated and tuned largely the same as in the preceding section, with the exception of the following changes:
• When restoring from backup sets, the default output buffer size is increased from 128 KB to 1 MB.
• _db_file_direct_io_count is not used to adjust any RMAN buffer sizes. This means that RMAN buffer sizes can be adjusted without affecting any other database I/O.
• _backup_ksfq_bufcnt can be used to adjust the number of input and output buffers for all operations using DISK channels.
Oracle Database 11g Release 2 -
All RMAN I/O operations with ASM files select an optimal buffer size and count based on the allocation unit (AU) size and disk count of the ASM disk group.
Additional parameters are introduced to give more fine-grained control over RMAN buffer allocations. Although these parameters are supported for all RMAN operations, they are primarily intended for use in non-ASM systems, since RMAN operations in ASM will automatically select an optimal buffer configuration.
Tuning guideline:
######################################################################################################################################
RMAN Performance Troubleshooting [ID 1326686.1]
RMAN command processing comprises two phases that we call compilation and execution.
During the compilation phase, RMAN identifies the files that will participate in the command and constructs one or more job steps. Each job step consists of a set of instructions from the RMAN client to the target RDBMS server, to perform specific data movement operations on one or more files. For example, the RMAN command "backup database" might be compiled into the following two job steps:
The compilation phase is where most of RMAN's query activity against the backup repository (which is either the recovery catalog or target database controlfile) takes place.
During the execution phase, RMAN submits each job step to an available channel and monitors the running channels. RMAN channels process work in parallel. After the completion of each job step, RMAN submits the next job step to that channel. When all job steps have completed, the command is complete.
Each RMAN channel contains a separate OCI connection to the target database. So each channel also represents one Oracle foreground process that performs whatever data movement tasks it is told to do by the RMAN client.
The RMAN client does not read or write datafiles or backup sets during backup. It simply compiles and executes an RMAN command, and during the execution phase, instructs the target database instance to perform the appropriate data movement tasks.
We also want to know which operation is the bottleneck. These are some of the things we have found helpful in the past.
create tablespace testrman datafile '/path/testrman1.f' size 10g;
and see how long it takes to write the file. If this command shows the same poor I/O rates as the RMAN operation, that would indicate that the issue lies with general Oracle I/O performance to this device, rather than RMAN in particular.
p0 => 6
p1 => buffer size in bytes (specify NULL or 0 to use default)
p2 => number of buffers (specify NULL or 0 to use default)
p3 => number of blocks to write
p4 => block size in bytes (must be specified, 8192 is a good choice)
p5 => file name to write
p6 => 1
Once we've identified where in the pipeline the problem is, there may be additional things that can be done to narrow the problem down further.
To measure the O/S I/O speed of an input device, we can use dd where the input file is located on the device in question and the output device is/dev/null.
To measure the O/S I/O speed of an output device, we can use dd where the input file is /dev/zero and the output file is written to the device in question.
Measuring disk performance using O/S utilities is more difficult for ASM files. With ASM it is sometimes possible to use the ORION utility to measure the performance of the disks that underly ASM.
When trying to isolate RMAN performance issues, it is important to narrow down the RMAN job as much as possible. Use only enough channels, and back up the fewest number of files, as are required to demonstrate the problem. For example, it is much easier to debug a performance issue with a backup of a single datafile than a whole database.
The best source for this information is the backup performance views, v$backup_sync_io and v$backup_async_io. These views contain various I/O statistics about every file that is read or written during any RMAN data movement operation. v$backup_sync_io contains information about files that are accessed using synchronous interfaces. v$backup_async_io contains information about files that are accessed using asynchronous interfaces. Under normal circumstances, we would expect to see all RMAN I/O appear in the async table. The only I/O that should appear in the sync table is for SBT channels whenbackup_tape_io_slaves=FALSE.
Attached to this note is a query that combines the sync and async tables and shows information that is usually helpful for performance debugging.
With ASM files, in 11.2+, the buffer configuration is automatically tuned to match the physical characteristics of the ASM diskgroup. With all other files a default buffer configuration is used. The default buffer configuration may not utilize enough I/O buffers to perform well with very high-performance disk systems.
The best way to determine whether your buffer configuration is the cause of a performance problem is to alter it and observe the effect that has on throughput. Document 1072545.1 shows the parameters that can be used to alter the buffer configuration. While experimenting with different buffer configurations, it is important to continuously monitor the backup performance views, so that you know what buffer configuration is used for each run, and that your parameter changes are changing the buffers as desired.
If your database has a smaller number of very large files, and you want to run RMAN work on a large number of channels, you should use the Multi-Section feature (available in 11g+) to allow individual files to be processed concurrently by multiple channels.
DBGRPC: krmxr - sleeping for 10 seconds
These periods of RMAN client idle time do not indicate any particular problem. This is the normal behavior of the RMAN client when it has given every available channel a job step to run. After every channel is running a job step, the RMAN client must wait for one of the steps to complete. It sleeps for intervals of up to 10 seconds, and after each sleep interval RMAN queries the target database to find out if any channel has completed its work. If not, then RMAN sleeps for another interval and repeats. Because typical production backups can take hours, it is normal to see many such sleeps in the RMAN debug output.
RMAN IO_PerformanceQuery -
set linesize 999
set pagesize 999
set numwidth 14
set numformat 999G999G999G990
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
column inst format a4 with subq as
(select 'ASYNC',
to_char(inst_id) inst,
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_async_io where type<>'AGGREGATE'
union all
select 'SYNC',
to_char(inst_id),
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_sync_io where type<>'AGGREGATE')
select subq.*,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400+agg)*1 rate_with_create,
decode(buffer_mem,0,null,rate/buffer_mem)*1000 efficiency
from subq order by open_time;
Introduction
The purpose of RMAN performance tuning is to identify the bottlenecks for a given backup or restore job and use RMAN commands, initialization parameters, or adjustments to physical media to improve overall performance.
As database sizes continue to grow unabated, with tens to hundreds of terabytes prevalent in customer’s environments, server and storage systems have also dramatically increased in resource, network, and I/O capacity to support required database backup and restore windows. Against this backdrop, RMAN performance tuning becomes more important than ever.
This document details RMAN performance tuning guidelines as they relate to buffer memory usage during backup and restore operations. These guidelines are targeted to systems where the available I/O exceeds 100 MB/s, while memory, CPU, network consumption, and tape drive throughput do not constrain overall RMAN performance. This is especially true in very high performance configurations, such as Exadata. In these environments, the default RMAN buffer sizes are usually inadequate and should be increased.
It is assumed that the reader has a basic understanding of RMAN performance tuning concepts. See this doc for an overview of RMAN tuning.
RMAN Buffer Usage
During backup, datafile blocks are read into a set of input buffers, where they are validated/compressed/encrypted and copied to a set of output buffers. The output buffers are then written to backup pieces on either disk or tape (DEVICE TYPE DISK or SBT). This process is depicted in the below diagram.
Conversely, on restore, blocks are read from the backup piece into a set of input buffers, where they are validated/uncompressed/decrypted and copied to a set of output buffers. The output buffers are then written back to data files on disk.
By default, buffers are allocated from the PGA, unless DISK or TAPE I/O slaves are used, in which case buffers are allocated from the SGA or large pool (if LARGE_POOL_SIZE is set).
The number and size of the buffers used for each RMAN job can be found in the views V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO.
The following guidelines for setting RMAN memory usage are organized by Oracle release.
Tuning guideline:
• When increasing buffer size using any of the following underscore parameters, it is recommended to increase both input and output buffer sizes simultaneously for a given operation
Oracle Database 11g Release 11.1.0.7 and prior releases
When creating backup sets:
Input buffers are allocated based on the level of multiplexing (i.e. number of files read per channel), according to the below chart:
Multiplexing = Min (MAXOPENFILES, FILESPERSET)
Defaults: MAXOPENFILES=8, FILESPERSET=64
| Number of Files Read Per Channel ........................................................................................... | Buffer SIZE .................................................................................................................. |
| Multiplexing ≤ 4 | Each buffer = 1MB, total buffer size for channel is up to 16MB. Number of buffers per file will depend on the number of files. |
| 4 >Multiplexing ≤ 8 | Each buffer = 512KB, total buffer size for channel is up to 16MB. Numbers of buffers per file will depend on number of files. |
| Multiplexing > 8 | Each file will have 4 512KB buffers. |
Tuning guideline:
• The number and size of input buffers can be changed by using the undocumented parameters _backup_ksfq_bufcnt and _backup_ksfq_bufsz.
• When using these parameters to increase backup I/O throughput, it is recommended that _backup_ksfq_bufsz be first set to the stripe size of the files being read and to test the effect that setting has on performance.
• If further tuning is needed, then set _backup_ksfq_bufcnt to the number of disks which the files are striped over. In the case of massively striped systems, such as ASM, this parameter should be used judiciously to achieve an optimal balance between performance and memory usage.
o For example, if there are 500 disks in your ASM diskgroup, setting _ backup_ksfq_bufcnt to 500 would use an excessive amount of memory for each RMAN channel. A more reasonable setting, such as 32 or 64, provides a better balance between performance and memory usage
When creating or restoring from image copies:
The size and number of input buffers default to 1 MB/buffer and 4 buffers.
Tuning guideline:
• Input buffer sizes for image copies can be increased using _db_file_direct_io_count.
When creating backup sets or image copies:
Output buffers are allocated based on device type:
| DEVICETYPE | NUMBEROFBUFFERCHANNEL | BUFFERSIZE | TOTALOFBUFFERALLOCATED |
| DISK | 4 | 1MB | 4MB |
| SBT | 4 | 256KB | 1MB |
Tuning guideline:
• For DISK backup, the output buffer size can be increased using _db_file_direct_io_count.
• Note that _db_file_direct_io_count controls other database I/O operations, not just RMAN, and therefore its adjustment should be carefully tested.
• For SBT backup, the output buffer size can be increased using BLKSIZE channel parameter.
• Note that BLKSIZE does not apply to Oracle Secure Backup. In this case, buffer sizes are automatically calculated for optimal transfer speeds.
When restoring from backup sets:
The default size and number of output buffers is 128 KB/buffer and 4 buffers.
Tuning guideline:
• The number of output buffers can be increased using _backup_ksfq_bufcnt.
• The output buffer size can be increased using _backup_ksfq_bufsz.
• Note that the default buffer sizes for restoring files are smaller than what are used when backing up those same files. These buffers should be increased to match the corresponding backup if restore times are significantly slower than the corresponding backups.
When restoring from image copies:
The default size and number of output buffers is 1 MB/buffer and 4 buffers.
Tuning guideline:
• Output buffer size can be increased using _db_file_direct_io_count.
Oracle Database 11g Release 11.1.0.7 + Patch 8369105 (also in Exadata V1 Bundle Patch 2)
Input and output buffers are allocated and tuned largely the same as in the preceding section, with the exception of the following changes:
• When restoring from backup sets, the default output buffer size is increased from 128 KB to 1 MB.
• _db_file_direct_io_count is not used to adjust any RMAN buffer sizes. This means that RMAN buffer sizes can be adjusted without affecting any other database I/O.
• _backup_ksfq_bufcnt can be used to adjust the number of input and output buffers for all operations using DISK channels.
Oracle Database 11g Release 2 -
All RMAN I/O operations with ASM files select an optimal buffer size and count based on the allocation unit (AU) size and disk count of the ASM disk group.
Additional parameters are introduced to give more fine-grained control over RMAN buffer allocations. Although these parameters are supported for all RMAN operations, they are primarily intended for use in non-ASM systems, since RMAN operations in ASM will automatically select an optimal buffer configuration.
- _backup_seq_bufsz/_backup_seq_bufcnt - These parameters set the buffer size and count for both creating and restoring backup pieces using SBT channels with third-party media managers. Note that these parameters are not needed when using Oracle Secure Backup, as the optimal buffer size is automatically selected.
- _backup_disk_bufsz/_backup_disk_bufcnt - These parameters set the buffer size and count for both creating and restoring backup pieces using DISK channels.
- _backup_file_bufsz/_backup_file_bufcnt - These parameters set the input buffer size and count for all backup operations, and the output buffer size and count for all restore operations.
- Note that _backup_ksfq_bufsz and _backup_ksfq_bufcnt are still supported in Oracle Database 11g Release 2 for compatibility reasons, but it is recommended that you tune the RMAN buffer sizes instead using the above parameters.
Tuning guideline:
- Output buffer size can be increased using _db_file_direct_io_count.
- _backup_disk_bufsz/_backup_disk_bufcnt
- These parameters set the buffer size and count for both creating and restoring backup pieces using DISK channels.
- _backup_file_bufsz/_backup_file_bufcnt
- These parameters set the input buffer size and count for all backup operations, and the output buffer size and count for all restore operations.
- Note that _backup_ksfq_bufsz and _backup_ksfq_bufcnt are still supported in Oracle Database 11g Release 2 for compatibility reasons, but it is recommended that you tune the RMAN buffer sizes instead using the above parameters.
######################################################################################################################################
RMAN Performance Troubleshooting [ID 1326686.1]
Background
First let's briefly review how RMAN processes a command.RMAN command processing comprises two phases that we call compilation and execution.
During the compilation phase, RMAN identifies the files that will participate in the command and constructs one or more job steps. Each job step consists of a set of instructions from the RMAN client to the target RDBMS server, to perform specific data movement operations on one or more files. For example, the RMAN command "backup database" might be compiled into the following two job steps:
- Step 1: backup datafiles 1,2,3,4 into backup piece /recoveryarea/bp1
The compilation phase is where most of RMAN's query activity against the backup repository (which is either the recovery catalog or target database controlfile) takes place.
During the execution phase, RMAN submits each job step to an available channel and monitors the running channels. RMAN channels process work in parallel. After the completion of each job step, RMAN submits the next job step to that channel. When all job steps have completed, the command is complete.
Each RMAN channel contains a separate OCI connection to the target database. So each channel also represents one Oracle foreground process that performs whatever data movement tasks it is told to do by the RMAN client.
The RMAN client does not read or write datafiles or backup sets during backup. It simply compiles and executes an RMAN command, and during the execution phase, instructs the target database instance to perform the appropriate data movement tasks.
Check to see if there really is a problem
Be sure there really is a problem before spending a lot of time tuning RMAN. If, for example, the issue is with the speed of a tape backup, and we see that the data rate during the backup is about equal to the tape drive's documented maximum speed, we would conclude that the backup is performing as well as can be expected. In other words, we should always start by doing a sanity check on the current rate of the backup, compared to the maximum speeds of the input and output storage that is being used.Isolating the Problem
RMAN copy steps are a pipelined data flow consisting of three operations:- read (do I/O to read data from disk or tape)
- process (block validation, encryption, compression, buffer copy)
- write (do I/O to write data to disk or tape)
We also want to know which operation is the bottleneck. These are some of the things we have found helpful in the past.
Backup Validate
Adding the VALIDATE option to any RMAN BACKUP command causes RMAN to perform all steps of the backup, up to the point where it is about to write the data to the output device, then the data is discarded and nothing is written. This is useful to diagnose problems in the read and processing operations of a BACKUP command. Note that VALIDATE does perform compression if specified on the backup command, but not encryption. If compression was specified on the backup command, then the BACKUP VALIDATE should be first run without compression, to measure the speed at which RMAN can read the input files, then again with compression to see the effects of the compression itself.Restore Validate
Adding the VALIDATE option to any RMAN RESTORE command causes RMAN to perform all the steps of a restore, up to the point where it is about to write the restored data to disk, then the data is discarded and nothing is written. This is often used to validate the integrity of backup media, but is also useful to diagnose performance problems with the read and processing operations of a RESTORE command. RESTORE VALIDATE performs both decryption and decompression if the backup was created with those options.Create Datafile
Creating a datafile is a good way to determine the performance of an output DISK device that is being used during an RMAN backup or restore. Just issue a sql statement such as:create tablespace testrman datafile '/path/testrman1.f' size 10g;
and see how long it takes to write the file. If this command shows the same poor I/O rates as the RMAN operation, that would indicate that the issue lies with general Oracle I/O performance to this device, rather than RMAN in particular.
Write I/O driver
Although we recommend creating a datafile as the easiest way to isolate write performance on DISK channels, that technique cannot always be used, such as when the database is not mounted, or when it is not feasible to create an additional tablespace. For those situations, there is a low-level write I/O driver that can be used. The write I/O driver is also helpful if you want to quickly make a series of runs with varying buffer sizes and counts to try to tune DISK channel output throughput. The write driver is invoked by calling the DBMS_BACKUP_RESTORE.SETPARMS function with parameters as follows:p0 => 6
p1 => buffer size in bytes (specify NULL or 0 to use default)
p2 => number of buffers (specify NULL or 0 to use default)
p3 => number of blocks to write
p4 => block size in bytes (must be specified, 8192 is a good choice)
p5 => file name to write
p6 => 1
SBT test program
The sbttest program is not normally used in performance situations, but it can be used as a write driver for determining the throughput of a single SBT stream. Its usage is documented.SBT wait events
All calls to media manager functions in the SBT library are recorded in wait events so that we can identify how much time in a given job was spent in the SBT library vs. the Oracle code. The SBT wait events all begin with "Backup:" and are displayed in the usual performance views, such asV$SYSTEM_EVENT.Once we've identified where in the pipeline the problem is, there may be additional things that can be done to narrow the problem down further.
Measure O/S I/O performance
If we find that the read or write phase using a disk-based device is slow, we can perform I/O to that device using O/S utilities such as cp or dd.To measure the O/S I/O speed of an input device, we can use dd where the input file is located on the device in question and the output device is/dev/null.
To measure the O/S I/O speed of an output device, we can use dd where the input file is /dev/zero and the output file is written to the device in question.
Measuring disk performance using O/S utilities is more difficult for ASM files. With ASM it is sometimes possible to use the ORION utility to measure the performance of the disks that underly ASM.
Measure CPU Usage
Ordinarily we expect RMAN channel processes to use very little CPU, because RMAN jobs are typically I/O bound. The exception to this rule is when compression or encryption are being used. A channel that is performing compression or encryption can be expected to use much more CPU. Often each such channel will use 100% of a single CPU. We can see this effect by using O/S tools such as SAR to monitor CPU usage during an RMAN job.When trying to isolate RMAN performance issues, it is important to narrow down the RMAN job as much as possible. Use only enough channels, and back up the fewest number of files, as are required to demonstrate the problem. For example, it is much easier to debug a performance issue with a backup of a single datafile than a whole database.
Measuring RMAN Performance
While using these techniques to isolate the bottleneck in an RMAN job, it is helpful to know the actual I/O rates that any given job is getting.The best source for this information is the backup performance views, v$backup_sync_io and v$backup_async_io. These views contain various I/O statistics about every file that is read or written during any RMAN data movement operation. v$backup_sync_io contains information about files that are accessed using synchronous interfaces. v$backup_async_io contains information about files that are accessed using asynchronous interfaces. Under normal circumstances, we would expect to see all RMAN I/O appear in the async table. The only I/O that should appear in the sync table is for SBT channels whenbackup_tape_io_slaves=FALSE.
Attached to this note is a query that combines the sync and async tables and shows information that is usually helpful for performance debugging.
Tuning
Once a poorly performing component has been isolated, it can sometimes be tuned to perform better. These are some of the areas where we've had good results tuning RMAN jobs.Buffer Tuning
The size and number of I/O buffers used to read or write a file can have a significant effect on performance, and is usually the first area to look at when tuning. We want to use a buffer size that is large enough so that RMAN issues fewer, but larger, I/O requests, which is more efficient than issuing many smaller I/O requests. Also we want to use a sufficient number of buffers so that there are always asynchronous I/O requests in the pipeline while other buffers are being processed.With ASM files, in 11.2+, the buffer configuration is automatically tuned to match the physical characteristics of the ASM diskgroup. With all other files a default buffer configuration is used. The default buffer configuration may not utilize enough I/O buffers to perform well with very high-performance disk systems.
The best way to determine whether your buffer configuration is the cause of a performance problem is to alter it and observe the effect that has on throughput. Document 1072545.1 shows the parameters that can be used to alter the buffer configuration. While experimenting with different buffer configurations, it is important to continuously monitor the backup performance views, so that you know what buffer configuration is used for each run, and that your parameter changes are changing the buffers as desired.
Output file space allocation
Depending on the type of file being written, and the type of output device, RMAN might be required to pre-allocate the space that it is writing to. The most common situation where pre-allocation is required is when writing datafile backup pieces into ASM. By default, when allocation is required, RMAN allocates space in 64MB increments as the file is written. In high-performance environments such as Exadata, the default allocation increment should be increased. In Exadata environments we have gotten good results with values of 1-2 GB. This is controlled with parameter_file_size_increase_increment.Channel Parallelism
When backing up to tape, choosing the correct number of channels is very straightforward: you simply allocate one channel per available tape drive. When backing up to disk, we recommend using the smallest number of channels needed to get acceptable performance. Adding additional channels beyond that does not further improve performance, and in extreme cases too many channels can hurt performance by causing resource contention and process thrashing.Node Parallelism
When the target database uses RAC, you should consider spreading the RMAN workload across multiple nodes. The best way to implement node parallelism in RAC is to connect to the RMAN TARGET database using a SCAN connect string. RMAN will connect to one of the available nodes, and each channel will connect to either the same, or another available node, depending on current resource availability in the cluster. This method is preferred over using the CONNECT option to direct each RMAN channel to a specific node, because it is more flexible when some nodes are unavailable.Asynchronous I/O
RMAN supports asynchronous I/O on most platforms, and we always recommend that it be used when available. All disk I/O is asynchronous by default, unless initialization parameters have been used to disable it. The most significant exception to that rule is SBT I/O, which is always synchronous and in most cases requires that I/O slaves be used to obtain asynchronous processing.I/O Slaves
I/O slaves can be used to obtain asynchronous I/O processing when that is not natively available. DISK devices rarely need I/O slaves because they almost always support native asynchronous I/O. SBT devices do not support asynchronous I/O, and we recommend that I/O slaves be used with SBT devices. The exception to this is Oracle Secure Backup, which uses a different kind of buffer management and is able to provide the benefits of asynchronous I/O without using I/O slaves. However, although we do recommend that I/O slaves be used with non-OSB SBT devices, we have found in practice that they do not always give a large benefit, and we have seen cases where using I/O slaves reduces performance. So it is important when implementing I/O slaves to measure the performance difference in a controlled environment.Compression and Encryption
Compression and encryption will often cause each RMAN channel to bottleneck on CPU usage, with each channel running at 100% CPU. There are a few different things we can do to alleviate this.Tape Drive Compression/Encryption
When using compression and/or encryption for backups that are being written to tape, consider whether those operations can be offloaded to the tape device. Offloading compression and encryption to the tape device almost always results in much better throughput than performing them using the target database CPUs.Change Compression Algorithm
If you have licensed the Advanced Compression Option, we recommend switching from the default BASIC compression algorithm to either the MEDIUM or LOW algorithm to improve CPU usage. Because compression algorithm performance is highly variable depending on the actual data, the only way to determine which of RMAN's compression algorithms gives the best trade-off between CPU utilization and compression ratio is to try each different algorithm on the actual data.Spread the Workload
To spread the CPU workload of compression and encryption among more CPUs, we can run the job with more RMAN channels. Allocate one channel per CPU that you want to use for RMAN. This technique generally applies only to DISK backups, because with tape backups we can compress and encrypt on the drive, and we should never allocate more than one channel per tape drive.If your database has a smaller number of very large files, and you want to run RMAN work on a large number of channels, you should use the Multi-Section feature (available in 11g+) to allow individual files to be processed concurrently by multiple channels.
Tuning in Exadata
Document 1275894.1 has a number of Exadata-specific diagnostic and tuning recommendations for RMAN performance issues.Common Issues
Here are some other issues that we have encountered.NFS
Oracle requires that filesystems accessed via NFS be mounted with particular options, as specified in Document 359515.1. In particular, it is documented that Oracle requires using the noac (or actimeo=0) option to turn off NFS attribute caching. Turning off attribute caching can significantly reduce the performance of RMAN backups, and is not needed when writing RMAN backup pieces. We recommend that NFS filesystems where RMAN backup pieces are being written not be mounted with the noac or actimeo=0 options. This should be checked whenever debugging a performance issue where RMAN is writing backups to NFS filesystems.Filesystem Fragmentation
We have seen situations where the filesystem that RMAN was writing backups to was heavily fragmented, which caused very poor RMAN write performance. De-fragmenting the filesystem can correct this problem. Note that ASM does not suffer from this issue because it uses much larger allocation units than traditional filesystems.Tape Multiplexing
Poor restore performance when restoring from tape can occur when tape multiplexing was used during backup. Tape multiplexing occurs when the media manager is configured to allow more than one RMAN channel to write concurrently to a single tape drive. Although multiplexing can improve backup performance, it can hurt restore performance far more. The problem is that RMAN does not know that two or more backup pieces are commingled together on the same physical tape. In 10g+, RMAN is media aware, and always binds each media volume used during a restore to one channel for the entirety of that restore. So a set of two or more backup pieces that were written concurrently to the same tape during a backup will now be restored sequentially, each one requiring that the entire tape be read.RMAN Client Polling
It is normal during most RMAN operations for the RMAN client program to eventually reach a state where all channels are running some work and the RMAN client is doing nothing more than monitoring each channel for completion. This state will be evident in an RMAN DEBUG trace as long periods of time when the RMAN client is idle, and outputting nothing into the trace file except a repeating sequence of messages which include the following:DBGRPC: krmxr - sleeping for 10 seconds
These periods of RMAN client idle time do not indicate any particular problem. This is the normal behavior of the RMAN client when it has given every available channel a job step to run. After every channel is running a job step, the RMAN client must wait for one of the steps to complete. It sleeps for intervals of up to 10 seconds, and after each sleep interval RMAN queries the target database to find out if any channel has completed its work. If not, then RMAN sleeps for another interval and repeats. Because typical production backups can take hours, it is normal to see many such sleeps in the RMAN debug output.
RMAN IO_PerformanceQuery -
set linesize 999
set pagesize 999
set numwidth 14
set numformat 999G999G999G990
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
column inst format a4 with subq as
(select 'ASYNC',
to_char(inst_id) inst,
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_async_io where type<>'AGGREGATE'
union all
select 'SYNC',
to_char(inst_id),
substr(filename,1,60),
open_time,
close_time,
elapsed_time/100,
substr(device_type,1,10) devtype,
set_count,
set_stamp,
maxopenfiles agg,
buffer_size,
buffer_count,
buffer_size*buffer_count buffer_mem,
io_count,
total_bytes,
bytes,
decode(nvl(close_time,sysdate),
open_time,
null,io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400))*1 rate,
effective_bytes_per_second eff
from gv$backup_sync_io where type<>'AGGREGATE')
select subq.*,
io_count*buffer_size/((nvl(close_time,sysdate)-open_time)*86400+agg)*1 rate_with_create,
decode(buffer_mem,0,null,rate/buffer_mem)*1000 efficiency
from subq order by open_time;
No comments:
Post a Comment