Oracle Databaseの I/O測定は、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャを使用します。
このプロシージャは、I/Oを連続的に発行する外部のI/O測定ツールとは異なり、Oracleデータファイルを使用してI/Oをランダムに発行し、ストレージ・サブシステムが耐えられる最大IOPS(1秒当たりのI/Oリクエスト)と MBPS(1秒当たりのI/OのMB)を判定するため、よりデータベースの実際のパフォーマンスに近い結果を得ることができます。
I/Oについて、CPUタイムの大部分をI/Oアクティビティが完了するまでの待機に使用するアプリケーションはI/Oバウンドと呼ばれます。 許容時間内にI/Oリクエストに対応できない場合は、パーティショニング、圧縮、In-Memory、Smart Scan等の I/Oチューニングを行うと、アプリケーションのパフォーマンスを向上できます。ただし、アプリケーションがI/Oバウンドではない場合(たとえば、CPUが制限要因である場合)、I/Oをチューニングしてもパフォーマンスを改善できません。
ということで、データベースとしての I/O値を知るために DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャを使用して、I/O測定をしてみてみます。
■ I/O測定の前提条件
前提条件は次になります。
・ SYSDBA権限でログイン
・ timed_statistics=TRUE
・ 非同期I/O有効化
● SYSDBA権限でログイン
[oracle@basedb ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 4 15:57:03 2023
Version 19.19.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
● timed_statistics = TRUE 設定
timed_statisticsが TRUEであることを確認
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
● 非同期I/O有効化確認
1) filesystemio_options パラメータ確認
ilesystemio_options が ASYNCH または SETALL のいずれかであることを確認
SQL> show parameter filesystemio_options
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL
2) Datafaile ASYNC_ON確認
次のSQL文を使用して、asynch_ioステータスであることを確認
SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I
WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File'
ORDER BY 1;
NAME ASYNCH_IO
---------------------------------------------------------------------------------------------------- ---------
+DATA/CDB_TOKYO/DATAFILE/sysaux.268.1138430843 ASYNC_ON
+DATA/CDB_TOKYO/DATAFILE/system.261.1138430857 ASYNC_ON
+DATA/CDB_TOKYO/DATAFILE/undotbs1.260.1138430879 ASYNC_ON
+DATA/CDB_TOKYO/DATAFILE/users.270.1138431375 ASYNC_ON
+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/sysaux.265.1138430601 ASYNC_ON
+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/system.264.1138430601 ASYNC_ON
+DATA/CDB_TOKYO/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/undotbs1.266.1138430601 ASYNC_ON
+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/sysaux.271.1138431203 ASYNC_ON
+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/system.273.1138431193 ASYNC_ON
+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/undotbs1.275.1138431385 ASYNC_ON
+DATA/CDB_TOKYO/FD210074E2916B27E053B5010A0A0DE2/DATAFILE/users.274.1138431375 ASYNC_ON
11 rows selected.
■ BaseDB の最大IOPS
Oracle Base Database Service(BaseDB)の最大IOPSスペックは、作成時に確認できます。
今回、使用可能なデータ・ストレージ(GB)を 256GB で作成すると、データ・ストレージの理論上の最大IOPSは 19.2K となります。
■ I/O測定実行
入力パラメータnum_physical_disksはオプションです。
num_physical_disksパラメータにデータベースのストレージ・システム内にある物理ディスクの概数を設定すると、より高速かつ正確に測定できます。
今回ASMなので、ASMDISK数を確認します。
I/O設計計画を作成する場合は、Oracle Automatic Storage Management(Oracle ASM)の使用を検討します。Oracle ASMは、記憶域の管理を管理者に任せるのではなく、データベースで管理するという方針に基づいた、高いパフォーマンスを実現する統合データベース・ファイル・システムおよびディスク・マネージャです。
データベース・ファイル記憶域には、RAWデバイスやオペレーティング・システムのファイル・システムではなく、Oracle ASMを使用することをお薦めします。Oracle ASMの主な利点を次に示します。
・ ストライプ化
・ ミラー化
・ オンラインでの記憶域の再構成と動的リバランス
・ 管理対象ファイルの作成と削除
1) ASM DISK数確認
次のSQLから。データ・ストレージの DATA DISKは、4つで構成されていることが確認できます。
SQL> set lin 100
SQL> col ASMDISK format a20
SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state, dg.name AS diskgroup
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
WHERE dg.group_number = d.group_number order by 1;
ASMDISK MOUNT_S STATE DISKGROUP
-------------------- ------- -------- ------------------------------
DATA_0000 CACHED NORMAL DATA
DATA_0001 CACHED NORMAL DATA
DATA_0002 CACHED NORMAL DATA
DATA_0003 CACHED NORMAL DATA
RECODISK1 CACHED NORMAL RECO
RECODISK2 CACHED NORMAL RECO
RECODISK3 CACHED NORMAL RECO
RECODISK4 CACHED NORMAL RECO
8 rows selected.
2) 測定実行
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
lat NUMBER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (4, 10, iops, mbps, lat);
end;
/
max_iops = 19398
latency = 6.558
max_mbps = 152
I/O測定は、次の2つのステップで行われます。
最初のステップでは、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャにより、すべてのデータベース・インスタンスのすべてのデータファイルに対してデータベース・ブロック・サイズのランダムな読取り(デフォルトは8KB)を発行します。このステップでは、出力パラメータmax_iopsに、データベースが維持できる最大IOPSが示されます。max_iopsの値は、OLTPデータベースに重要なメトリックです。出力パラメータactual_latencyには、このワークロードの平均待機時間が示されます。特定の目標待機時間が必要な場合は、入力パラメータmax_latencyを使用して目標待機時間を指定できます(データベース・ブロック・サイズのIOリクエストの最大許容待機時間をミリ秒単位で指定)。
2番目の測定のステップでは、DBMS_RESOURCE_MANAGER.CALIBRATE_IOプロシージャにより、すべてのデータベース・インスタンスのすべてのデータファイルに対して1MBのランダムな読取りを発行します。2番目のステップでは、出力パラメータmax_mbpsに、データベースが維持できるI/Oの最大MBPSが示されます。このステップは、データ・ウェアハウスに重要なメトリックを提供します。
3) 測定結果参照
I/O測定プロセス中は、いつでもV$IO_CALIBRATION_STATUSビューで測定ステータスを問い合せることができます。I/O測定が正常に完了したら、DBA_RSRC_IO_CALIBRATE表でその結果を参照できます。
SQL> set lin 200
SQL> col ADDITIONAL_INFO format a30
SQL> col START_TIME format a30
SQL> col END_TIME format a30
SQL> select * from DBA_RSRC_IO_CALIBRATE;
START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS ADDITIONAL_INFO
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------------------ ------------------------------
04-JUN-23 03.59.24.797641 PM 04-JUN-23 04.09.00.228331 PM 19398 152 150 6.558 4
4) 最大IOPSスペック値確認
Oracle Base Database Service(BaseDB)の最大IOPSスペックは、OCIコンソールから確認できます。
表示された最大IOPS値が 19.2K なので、測定値と同じことが確認できました。
測定結果が異なる場合 BaseDBは、OCPUの数に応じてIOPSは比例してスケーリングされます。OCPU数とIOPSの関係は次を参考にします。
・参考: Create a DB System Using the Console: Theoretical max IOPS
■ I/Oチューニング
Oracle DatabaseのI/Oチューニングは次を参考にします。
・実践！！ パフォーマンス・チューニング ～DISKI/Oボトルネック解決編～
■ 参考
・パフォーマンス・チューニングの概要
・データベース・パフォーマンス・チューニング・ガイド: I/O構成および設計
・データベース内部のI/O測定
・Oracle Orion測定ツールによるI/O測定
・管理者ガイド: ビューを使用したOracle ASM情報の表示
・CALIBRATE_IOプロシージャ
・TIMED_STATISTICS
・Create a DB System Using the Console: Theoretical max IOPS
・実践！！ パフォーマンス・チューニング ～DISKI/Oボトルネック解決編～