[Oracle] RMAN (backup) performance with synchronou...

stefan_koehler · ‎05-21-2013

Introduction

Have you ever switched from an "Oracle flat file database backup" to RMAN and the read I/O throughput for the backup decreased drastically? Have you ever migrated an Oracle database from a platform that supports asynchronous I/O (like Linux or AIX) to a platform that supports synchronous I/O (like HP-UX) only and the read I/O throughput for the backup decreased drastically? .. or have you ever disabled asynchronous I/O due to performance issues and the read I/O throughput for the backup decreased drastically?

If you answer one of the questions with "Yes", then this blog is worth to read and get some more insights into synchronous I/O and RMAN backups.

I came across this issue recently as one of my clients had to disable asynchronous I/O on Solaris with ZFS due to I/O performance issues and high CPU usage. Basically the root cause for the issue looked like this (by running truss on the specific Oracle processes):

14051/1:     kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF)      Err#22 EINVAL

14051/1:     kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF)      Err#22 EINVAL

14051/1:     kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF)      Err#22 EINVAL

My client set the initialization parameter "DISK_ASYNCH_IO" to FALSE to avoid such aio errors and perform pread / pwrites only. In consequence the backup performance dropped drastically and the backup time windows were not sufficient anymore.

Let's start with an explanation and some SAP information about the DISK_ASYNCH_IO, before we go on with researching and performance measures.

Official Oracle 11g R2 documentation

Parameter DISK_ASYNCH_IO

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.

1.3.4 DISK_ASYNCH_IO Initialization Parameter (HP-UX)

The DISK_ASYNCH_IO initialization parameter determines whether the database files reside on raw disks or file systems. Asynchronous I/O is available only with Automatic Storage Management disk group which uses raw partitions as the storage option for database files. The DISK_ASYNCH_IO parameter can be set to TRUE or FALSE depending on where the files reside. By default, the value is set to TRUE.

Note:

The DISK_ASYNCH_IO parameter must be set to FALSE when the database files reside on file system. This parameter must be set to TRUE only when the database files reside on raw partitions.

SAP Information / documentation

SAPnote #1431798 - Oracle 11.2.0: Database Parameter Settings

DISK_ASYNCH_IO FALSE (only on HP-UX, only for standard file systems, not for OnlineJFS(VxFS 5.x), not for ASM, not for raw devices, see SAP note 798194)

Performance researching

The following tests were performed on Solaris x86 with an attached enterprise SAN storage and an Oracle 11.2.0.3 database. RMAN VALIDATE was used for simulating the read I/O load by a RMAN database backup (basically the write phase of a RMAN backup is missing - for details check the graphic below). The backup was performed with one channel only (no parallel backup).

Let's start with some basic information about how RMAN performs a backup and how we can influence the behavior with different buffer sizes (without hidden parameters) or throughput. In our case we talk about the read and copy phase only as we perform a RMAN VALIDATE of the whole database.

Phases of a RMAN backup

Each channel (in our test case we have only one channel) reads the data into the input buffers, processes the data while copying it from the input buffers to the output buffers, and then writes the data from the output buffers to tape (the write phase is missing by a RMAN VALIDATE). Channel 1 writes the data to a locally attached tape drive, whereas channel 2 sends the data over the network to a remote media server.

Disk I/O Slaves

You can control disk I/O slaves by setting the DBWR_IO_SLAVES initialization parameter, which is not dynamic. The parameter specifies the number of I/O server processes used by the database writer process (DBWR). By default, the value is 0 and I/O server processes are not used. If asynchronous I/O is disabled, then RMAN allocates four backup disk I/O slaves for any nonzero value of DBWR_IO_SLAVES.

Allocation of Input Disk Buffers / Level of Multiplexing

Less than or equal to 4

The RMAN channel allocates 16 buffers of size 1 megabyte (MB) so that the total buffer size for all the input files is 16 MB.

Greater than 4 but less than or equal to 8

The RMAN channel allocates a variable number of disk buffers of size 512 kilobytes (KB) so that the total buffer size for all the input files is less than 16 MB.

Greater than 8

The RMAN channel allocates 4 disk buffers of 128 KB for each file, so that the total buffer size for each input file is 512 KB

MOS - RMAN Backup Performance [ID 360443.1]

Disk IO slaves should always be used to simulate asynchronous IO when native asynchronous IO is disabled. There are always 4 slaves spawned initially per channel but slaves will die if idle > 60 secs.

....

RMAN is designed to take advantage of asynchronous io. You cannot expect good performance of any kind if synchronous io is used in which case stop - implement slaves (Note 73354.1: RMAN: I/O Slaves and Memory Usage) or enable native async io and re-assess the situation after doing the backup again.

Let's test the various settings and check the corresponding system calls (to understand how it works internally) after we got all that detailed information.

RMAN VALIDATE with DISK_ASYNCH_IO = FALSE, MAXOPENFILES = 8 (Default Setting) and DBWR_IO_SLAVES = 0

RMAN> validate database;

shell> truss -p <PID> (<PID> of oracle shadow process for specific RMAN channel)

pread(279, "\0A2\0\0 @E0C101\0\0\0\0".., 524288, 0x3C080000) = 524288

pread(280, "06A2\0\0 @E0C102 ?\nBB01".., 524288, 0x3C080000) = 524288

pread(273, "06A2\0\080E00101 ' {\n\0".., 524288, 0x3C100000) = 524288

pread(274, "  A2\0\080E0 A02 9D39B\0".., 524288, 0x3C100000) = 524288

pread(275, "  A2\0\080E0 A01BA81EF0E".., 524288, 0x3C100000) = 524288

pread(276, "06A2\0\080E08101 dA9\t\0".., 524288, 0x3C100000) = 524288

pread(277, "06A2\0\080E08102 l BD2\0".., 524288, 0x3C100000) = 524288

pread(278, " EA2\0\080E00103 t Q 901".., 524288, 0x3C100000) = 524288

pread(279, "\0A2\0\080E0C101\0\0\0\0".., 524288, 0x3C100000) = 524288

pread(280, "06A2\0\080E0C102FEF8BA01".., 524288, 0x3C100000) = 524288

pread(273, "06A2\0\0C0E00101 J {\n\0".., 524288, 0x3C180000) = 524288

pread(274, "06A2\0\0C0E0 A0215B296\0".., 524288, 0x3C180000) = 524288

pread(275, "06A2\0\0C0E0 A01 p98 i\r".., 524288, 0x3C180000) = 524288

pread(276, "06A2\0\0C0E08101CDAA\t\0".., 524288, 0x3C180000) = 524288

pread(277, "06A2\0\0C0E08102 jD4BE\v".., 524288, 0x3C180000) = 524288

....

8 database files are read simultaneously with system call pread and a request / read size of 512 kb, if you look closely at the open file handles (273 to 280) and the corresponding read size (524288).

RMAN VALIDATE with DISK_ASYNCH_IO = FALSE, MAXOPENFILES = 4 and DBWR_IO_SLAVES = 0

RMAN> run

{

allocate channel ch1 type disk maxopenfiles 4;

validate database;

}

shell> truss -p <PID> (<PID> of oracle shadow process for specific RMAN channel)

pread(275, "\0A2\0\080A7 I01\0\0\0\0".., 1048576, 0x134F00000) = 1048576

pread(276, "06A2\0\080A78901B69F A\v".., 1048576, 0x134F00000) = 1048576

pread(273, "06A2\0\0\0A8\t01B584 ,\0".., 1048576, 0x135000000) = 1048576

pread(274, "\0A2\0\0\0A8 I02\0\0\0\0".., 1048576, 0x135000000) = 1048576

pread(275, "\0A2\0\0\0A8 I01\0\0\0\0".., 1048576, 0x135000000) = 1048576

pread(276, "06A2\0\0\0A88901B69F A\v".., 1048576, 0x135000000) = 1048576

pread(273, "  A2\0\080A8\t018984 ,\0".., 1048576, 0x135100000) = 1048576

pread(274, "\0A2\0\080A8 I02\0\0\0\0".., 1048576, 0x135100000) = 1048576

....

4 database files are read simultaneously with system call pread and a request / read size of 1024 kb, if you look closely at the open file handles (273 to 276) and the corresponding read size (1048576).

RMAN VALIDATE with DISK_ASYNCH_IO = FALSE, MAXOPENFILES = 8 (Default Setting) and DBWR_IO_SLAVES = 4

RMAN> validate database;

shell> truss -p <PID> (<PID> of oracle shadow process for specific RMAN channel)

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 43, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 43, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 43, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 44, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 44, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDF7718, 1, 0xFFFFFD7FFFDF7720) = 0

semctl(352321614, 44, SETVAL, 1)                = 0

....

shell> truss -p <PID> (<PID> of one I/O slave process)

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(256, "06A2\0\080 > H02 N9A G\v".., 524288, 0x107D00000) = 524288

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(260, "06A2\0\080 >\b03 \938D01".., 524288, 0x107D00000) = 524288

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(263, "\0A2\0\0C0 >\b01\0\0\0\0".., 524288, 0x107D80000) = 524288

semctl(352321614, 38, SETVAL, 1)                = 0

....

No database files are read by the Oracle (RMAN) shadow process itself anymore. The I/O requests are "swapped out" to the I/O slaves and exchanged by memory operations (semctl, semtimedop). The I/O slaves perform the same pread system call as the "RMAN channel shadow process" before (check request / read size for example).

RMAN VALIDATE with DISK_ASYNCH_IO = FALSE, MAXOPENFILES = 4 and DBWR_IO_SLAVES = 4

RMAN> run

{

allocate channel ch1 type disk maxopenfiles 4;

validate database;

}

shell> truss -p <PID> (<PID> of one I/O slave process)

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

times(0xFFFFFD7FFFDFE2D0)                       = 233253482

times(0xFFFFFD7FFFDFE2D0)                       = 233253482

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(259, "06A2\0\0\0 /9601\v bEB02".., 1048576, 0x2C5E00000) = 1048576

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(257, "06A2\0\080 / V02 01998\0".., 1048576, 0x2C5F00000) = 1048576

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(256, "06A2\0\0\0 01601 %B0 w\t".., 1048576, 0x2C6000000) = 1048576

semctl(352321614, 38, SETVAL, 1)                = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

semtimedop(352321614, 0xFFFFFD7FFFDFDCB8, 1, 0xFFFFFD7FFFDFDCC0) = 0

pread(259, "06A2\0\0\0 0960119 yEB02".., 1048576, 0x2C6000000) = 1048576

semctl(352321614, 38, SETVAL, 1)                = 0

....

No database files are read by the Oracle (RMAN) shadow process itself anymore. The I/O requests are "swapped out" to the I/O slaves and exchanged by memory operations (semctl, semtimedop). The I/O slaves perform the same pread system call as the "RMAN channel shadow process" before (check request / read size for example).

We have checked how Oracle handles the I/O requests internally with various settings, but how does this affect the I/O read throughput for a classic RMAN database backup?

The following table represents the results of the I/O read throughput with one channel only and DBWR_IO_SLAVES set to 4 (last column).

You can scale up the I/O read throughput to the enterprise storage maximum by parallel RMAN backup of course, but the main focus was on increasing the I/O throughput for each channel.

MAXOPENFILES / Input Buffer Size	Average synchronous I/O throughput without I/O slaves	Average synchronous I/O throughput with I/O slaves
8 / 512 k	49 MB per second	125 MB per second
4 / 1024 k	47 MB per second	206 MB per second

Summary

Using I/O slaves (for "simulating asynchronous I/O") can improve the I/O throughput drastically, if you are running an Oracle database on an OS platform, that does not support asynchronous I/O at all or if you need to disable asynchronous I/O due to various reasons (like bugs or file system designs).

If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database performance issues.