Testing Oracle I/O

Starting with Oracle 11g there’s a new way to test I/O subsystems. The procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO tests your disks for different I/O workloads by issuing 1 MB random reads to your datafiles and reports the actual performance.

Test #1

oracle:~$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 5 10:25:19 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning option

Lets use the procedure with the script provided by the Oracle Performance Tuning Guide:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
  DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/
SQL>   2    3    4    5    6    7    8    9   10   11   12   13  
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: in "SYS.DBMS_RMIN", line 456
ORA-06512: in "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: in line 7

Setting Oracle parameters

It failed as asynchronous I/O is not enabled in my database instance:

SQL> show parameter io

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
[...]
disk_asynch_io                       boolean     TRUE
filesystemio_options                 string      none
[...]

I have to set FILESYSTEMIO_OPTIONS to SETALL to let Oracle choose between direct and async I/O:

SQL> alter system set filesystemio_options='setall';
alter system set filesystemio_options='setall'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

This parameter can only be changed in the init.ora or server parameter file:

SQL> alter system set filesystemio_options='setall' scope=spfile;

System altered.

If you use raw devices, set DISK_ASYNC_IO to TRUE:

SQL> alter system set disk_async_io=true scope=spfile;

System altered.

Bounce the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2220200 bytes
Variable Size             813698904 bytes
Database Buffers          247463936 bytes
Redo Buffers                5554176 bytes
Database mounted.
Database opened.

Check if your files support asynchronous I/O:

set linesize 132
col name format a70
select
    name
    , asynch_io
from
    v$datafile d
    inner join v$iostat_file f on d.file# = f.file_no
;

NAME                                                                   ASYNCH_IO
---------------------------------------------------------------------- ---------
/oracle/app/oradata/IRB1/datafile/o1_mf_system_5pts00dd_.dbf           ASYNC_ON
/oracle/app/oradata/IRB1/datafile/o1_mf_system_5pts00dd_.dbf           ASYNC_ON
/oracle/app/oradata/IRB1/datafile/o1_mf_sysaux_5pts00f8_.dbf           ASYNC_ON
/oracle/app/oradata/IRB1/datafile/o1_mf_undotbs1_5pts00fl_.dbf         ASYNC_ON
/oracle/app/oradata/IRB1/datafile/o1_mf_users_5pts00gg_.dbf            ASYNC_ON

Test #2

And try it again:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      2 /* # of disks */
      , 10 /* maximum tolerable latency in milliseconds */
      , iops /* I/O rate per second */
      , mbps /* throughput, MB per second */
      , lat  /* actual latency in milliseconds */
  );
  DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/
SQL>   2    3    4    5    6    7    8    9   10   11   12   13  

The procedure takes some time, you can monitor it using iostat. In my test case it’s a Debian GNU/Linux:

# iostat -xm 2
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.06    0.00    0.23   23.50    0.00   76.21

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00  136.00    1.00     1.08     0.02    16.35    21.01   22.04   7.30 100.00
sdb1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb3              0.00     0.00  136.00    1.00     1.08     0.02    16.35    21.01   22.04   7.30 100.00
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md1               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md2               0.00     0.00  135.50    1.00     1.08     0.02    16.41     0.00    0.00   0.00   0.00

Script output:

max_iops = 111
latency  = 8
max_mbps = 41

PL/SQL procedure successfully completed.

On my small system the storage for the tablespaces actually provides a maximum of 111 I/Os per second and a throughput of 41 MB per second. The actual latency is at 8 milliseconds.

Ooops…

This seems not to be correct as my server has two mirrored disks and this metrics looks like a single disk. Looking at the iostat above you can clearly see that only /dev/sdb is working when testing I/O performance with CALIBRATE_IO()!

By looking at /proc/mdstat I see that my mirror /dev/md2 got degraded:

# cat /proc/mdstat 
Personalities : [raid0] [raid1] [raid6] [raid5] [raid4] [raid10] 
md2 : active raid1 sdb3[1]
      726266432 blocks [2/1] [_U]

md1 : active raid1 sda2[0] sdb2[1]
      2104448 blocks [2/2] [UU]

md0 : active raid1 sda1[0] sdb1[1]
      4200896 blocks [2/2] [UU]

unused devices: <none>

Time to repair… first re-add the failed disk into the RAID-1:

# mdadm --manage /dev/md2 --add /dev/sda3
mdadm: re-added /dev/sda3

Monitor the recovery process via /proc/mdstat:

# cat /proc/mdstat 
Personalities : [raid0] [raid1] [raid6] [raid5] [raid4] [raid10] 
md2 : active raid1 sda3[2] sdb3[1]
      726266432 blocks [2/1] [_U]
      [&gt;....................]  recovery =  0.3% (2701248/726266432) finish=224.7min speed=53648K/sec

md1 : active raid1 sda2[0] sdb2[1]
      2104448 blocks [2/2] [UU]

md0 : active raid1 sda1[0] sdb1[1]
      4200896 blocks [2/2] [UU]

unused devices: <none>

For a full description of recovering failed disks in a software RAID with Linux, see for example HowtoForge.

Once again

After my RAID has rebuilt the values differ:

max_iops = 93 (previous test: 111)
latency  = 10 (previous test: 8)
max_mbps = 78 (previous test: 41)

PL/SQL procedure successfully completed.

iostat also shows both disks working:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.39    0.00    0.26   13.04    0.00   86.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00   99.00    0.00     0.77     0.00    16.00     3.40   34.30  10.10 100.00
sda1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda3              0.00     0.00   99.00    0.00     0.77     0.00    16.00     3.40   34.30  10.10 100.00
sdb               0.00     0.00  138.00    0.00     1.07     0.00    15.94     2.59   18.74   7.23  99.80
sdb1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb3              0.00     0.00  138.00    0.00     1.07     0.00    15.94     2.59   18.74   7.23  99.80
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md1               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
md2               0.00     0.00  237.00    0.00     1.85     0.00    15.97     0.00    0.00   0.00   0.00

Data dictionary views

There’s also a data dictionary view DBA_RSRC_IO_CALIBRATE where results are stored:

set linesize 132
col start_time format a25
col end_time format a25
select * from dba_rsrc_io_calibrate;

START_TIME                END_TIME                    MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
------------------------- ------------------------- ---------- ---------- ---------- ---------- ------------------
05.01.11 17:41:04,050934  05.01.11 17:45:05,105168          87         78         18         10                  2

The value in MAX_PMBPS describes the maximum MB/sec throughput for a single process.

Another view V$IOSTAT_FILE provides file level statistics:

set linesize 132
col name format a35
select
   d.name
   , f.file_no
   , f.small_read_megabytes
   , f.small_read_reqs
   , f.large_read_megabytes
   , f.large_read_reqs
from
   v$iostat_file f
   inner join v$datafile d on f.file_no = d.file#
;

NAME                                   FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
----------------------------------- ---------- -------------------- --------------- -------------------- ---------------
/oracle/app/oradata/IRB1/datafile/o          1                  602           76464                4677             4693
1_mf_system_5pts00dd_.dbf                                                                                           

/oracle/app/oradata/IRB1/datafile/o          1                    0           0                       0                0
1_mf_system_5pts00dd_.dbf                                                                                           

/oracle/app/oradata/IRB1/datafile/o          2                  677           84775                5705             5708
1_mf_sysaux_5pts00f8_.dbf                                                                                           

/oracle/app/oradata/IRB1/datafile/o          3                  272           34851                2410             2410
1_mf_undotbs1_5pts00fl_.dbf                                                                                         

/oracle/app/oradata/IRB1/datafile/o          4                    0           4                       0                0
1_mf_users_5pts00gg_.dbf

15 rows selected.

Resources

This entry was posted in Databases, System Administration and tagged , . Bookmark the permalink.