前の3件 | -

補助インスタンスを用いた表単位のリカバリ [アーキテクチャ]

1.はじめに


 最近関わっている案件で、DBは遠隔の災対環境へ常時同期し、罹災時に災対環境に切り替えする、ただし特定の表(複数)だけ、前日の特定リストアポイントに復旧したい、という要件があった。いくつか検討した方式が以下であった。
 (a)災対同期をGoldenGate等論理レプリケーションとし表毎に同期を制御できるようにする
 (b)災対同期はDataGuardで物理レプリケーションとし、フェールオーバー後に
  (b-1)前日断面のエクスポートを取得しておき、それをインポートする
  (b-2)フラッシュバックテーブルで特定表を復旧
  (b-3)補助インスタンスを用いた表単位のリカバリ

 このうち、(a)は災対側の維持運用の負荷が高いので、(b)の方向性が望ましい。ActiveDataGuardなら(b-1)方式で、災対側でエクスポートしておくのがシンプルだ。しかし、表数やサイズが大きいと罹災時のRTOが許容できるか、ダンプファイルの置き場所やダンプ処理にかかる時間も実運用上許容されるかといった懸念がある。
 (b-2)はDBオープン後にフラッシュバックテーブルで特定断面に戻す方法である。フラッシュバックテーブルは内部的にはUNDOを使ってリストアポイントまでリカバリする。母体の表サイズが大きくても更新が少なければ高速に処理が完了する。リストアポイントまでのUNDOが上書きされないよう、十分にUNDOリテンションを確保する必要がある。リストアポイントまでの間にalter table等のDDLが実行された場合はフラッシュバックテーブルは使えない(エラーとなる)という制約があり、プライマリ側で定義変更後に罹災するなど刹那なタイミングで復旧できないリスクがあるため、これだけに頼るのもリスクがある。
 そこで(b-3)補助インスタンスを用いた表単位のリカバリである。これはRMANバックアップとアーカイブログを利用し、仮のインスタンスを立てて特定の(複数の)表をリカバリする機能である。災対側でもRMANのバックアップがとられていれば、そこから表の特定時点の断面を取り出すことができる。
 今回、この補助インスタンスを用いた表単位のリカバリを実際に行い、補助インスタンスを利用した表リカバリについて考察してみたい。

2.検証モデル


 ここでは、補助インスタンスを使った表単位のリカバリで、PDB上のSCOTT.EMPを特定のリストアポイント(=SCN)にリカバリしてみる(その他のDBは最新状態のまま)。テスト環境は手元のVirtual BoxのOracle19.11を使う。テスト準備として、アーカイブログモードとし、RMANフルバックアップを取得する。補助インスタンスリストア用に/mntをC:\Users\kazuhiro\Downloadsにマウントしておく(VirtualBoxの共有フォルダの設定)。
20240316_RMANrecoverTable.jpg

 テストシナリオは以下の通り:
  • PDB名ORCLのSCOTT.EMP/DEPTを作成、初期データを挿入
  • リストアポイント取得
  • EMPにINSERT 1件
  • EMPをリストアポイントにリカバリした内容をEMP_RECOVER表に復旧

 テストのコマンドは以下の通り。ポイントは★の部分のRMANコマンドで、recover tableで、EMP表をEMP_RECOVERED表に復旧するようにしている。なお、@empはemp表の作成スクリプトである。EMP表には初期データとして14件、リストアポイント作成後にempno=9999を1行を追加している。したがって、復旧した際には15件ではなく、14件となることが期待である。
rman target /
backup as compressed backupset database;

--EMP表作成
sqlplus scott/oracle@orcl
@emp
select count(*) from dept;
select count(*) from emp;

--リストアポイント(zenjitu)を作成
sqlplus sys/oracle@orcl as sysdba
create restore point zenjitu;

--リストアポイント後の更新を実行
sqlplus scott/oracle@orcl
insert into emp
values(  
 9999, 'KAZUHIRO', 'TAKAHASHI', 7000,  
 to_date('08-03-2024','dd-mm-yyyy'),  
 1000, null, 10  
);
commit;
select count(*) from emp;
select * from emp where empno=9999;
exit

--★表のリカバリ(EMP表をEMP_RECOVERED表に復旧する)
rman target /
recover table scott.emp of pluggable database orcl until restore point ZENJITU auxiliary destination '/mnt' REMAP TABLE 'SCOTT'.'EMP':'EMP_RECOVERED' ;

select count(*) from dept;
select count(*) from emp;
select * from emp where empno=9999;
select segment_name, bytes, blocks from user_segments;
desc emp
desc emp_recovered

3.検証結果


 補助インスタンスを用いた表のリカバリは、内部的に以下の動作を行っていることがわかった(検証結果のログは参考[1]を参照されたい)。ポイントとなる部分に★を付けたので、ここだけ追えば概ね流れは理解できるだろう。
SID='gicq'でインスタンスを起動

SCNをリストアポイントに設定
制御ファイルをリストア ★①補助インスタンス用制御ファイル
クローンデータベースをマウント
ログスイッチ

SCNをリストアポイントに設定
クローンデータファイルの1,9,4,11,3,10とtempファイルの1,3に新しい名前を付与
全てのtempfileをスイッチ
クローンデータファイルをリストア(1,9,4,11,3,10) ★②CDBリストア
全てのクローンデータファイルをスイッチ

SCNをリストアポイントに設定
クローンデータファイルをオンライン(1,9,4,11,3,10)
クローンデータベースの表領域をリカバリ ★③CDBメディアリカバリ
"SYSTEM", "ORCL":"SYSTEM", "UNDOTBS1", "ORCL":"UNDOTBS1", "SYSAUX", "ORCL":"SYSAUX"
クローンデータベースをリードオンリーでオープン

クローンのPDBをオープン(ORCL)

クローンでspfileをメモリから生成
クローンをシャットダウン
クローンをnomountで起動
制御ファイルを設定(①でリストアしたファイル)
クローンをシャットダウン
クローンをnomountで起動
クローンをマウント

SCNをリストアポイントに設定
クローンデータファイルの12に新しい名前を付与
クローンデータファイルをリストア(12) ★④PDBリストア
全てのクローンデータファイルをスイッチ

SCNをリストアポイントに設定
クローンORCLのデータファイルをオンライン(12)
クローンデータベースの表領域をリカバリ ★⑤PDBメディアリカバリ
"ORCL":"USERS", "SYSTEM", "ORCL":"SYSTEM", "UNDOTBS1", "ORCL":"UNDOTBS1", "SYSAUX", "ORCL":"SYSAUX" delete archivelog
クローンデータベースをリセットログでオープン

クローンのPDBをオープン(ORCL)

ORCLにディレクトリオブジェクトを作成する(TSPITR_DIROBJ_DPDIR as /mnt)

クローンのORCLにディレクトリオブジェクトを作成する(TSPITR_DIROBJ_DPDIR as /mnt)

表のエクスポート(/mnt/tspitr_gicq_28603.dmp) ★⑥表エクスポート

クローンデータベースをシャットダウン(abort)

表のインポート ★⑦表インポート
自動インスタンスの削除

 復旧後の結果確認をしたところ、想定通りEMP_RECOVEREDが作成されている。件数は14件で、INSERTの更新前のリストアポイントに復旧していることがわかる。
[oracle@localhost ~]$ sqlplus scott/oracle@orcl
...
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
DEPT
EMP
EMP_RECOVERED ★

SQL>
SQL> select count(*) from EMP_RECOVERED;

  COUNT(*)
----------
        14

SQL>
SQL> select * from emp where empno=9999;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      9999 KAZUHIRO   TAKAHASHI       7000 08-MAR-24       1000
        10


SQL> select * from EMP_RECOVERED where empno=9999;

no rows selected

 セグメントの状態を確認すると、EMP_RECOVEREDにはPKの索引がない。表の定義を確認すると、EMPNOのNOT NULL制約がなくなっていた。FK制約もない。データは復旧できるが、表の制約は外れてしまうようだ。
SQL> select segment_name, bytes, blocks from user_segments;

SEGMENT_NAME        BYTES    BLOCKS
________________ ________ _________
DEPT                65536         8
EMP                 65536         8
EMP_RECOVERED       65536         8 ★対応するPK索引がない
PK_DEPT             65536         8
PK_EMP              65536         8

SQL>
SQL> desc emp

Name        Null?       Type
___________ ___________ _______________
EMPNO       NOT NULL    NUMBER(4)
ENAME                   VARCHAR2(10)
JOB                     VARCHAR2(9)
MGR                     NUMBER(4)
HIREDATE                DATE
SAL                     NUMBER(7,2)
COMM                    NUMBER(7,2)
DEPTNO                  NUMBER(2)
SQL> desc emp_recovered

Name        Null?    Type
___________ ________ _______________
EMPNO                NUMBER(4) ★NOT NULLがない
ENAME                VARCHAR2(10)
JOB                  VARCHAR2(9)
MGR                  NUMBER(4)
HIREDATE             DATE
SAL                  NUMBER(7,2)
COMM                 NUMBER(7,2)
DEPTNO               NUMBER(2)
SQL>
SQL> select index_name from user_indexes;

INDEX_NAME
_____________
PK_DEPT
PK_EMP

SQL> select constraint_name,constraint_type,table_name,status from user_constraints;

CONSTRAINT_NAME    CONSTRAINT_TYPE    TABLE_NAME    STATUS
__________________ __________________ _____________ __________
FK_DEPTNO          R                  EMP           ENABLED
PK_DEPT            P                  DEPT          ENABLED
PK_EMP             P                  EMP           ENABLED ★EMP_RECOVEREDに対応するPK制約がない
SQL>

4.考察


 今回の検証を通して分かったことは以下の通り
  • recover table scott.emp of pluggable database orcl until restore pointで特定の表の断面を復旧させることが可能
  • 補助インスタンスに必要なメモリ(SGAのサイズ)は元のDBと同じ
  • 補助インスタンスに必要な領域はSYSTEM、SYSAUX、UNDO(CDBとPDB)+復旧したい表の格納されている表領域を構成するデータファイルの合計サイズ+TEMP(CDBとPDB)+REDO+リストアが必要なアーカイブログ
  • 復旧時間は上記ファイルのリストアおよびSCNまでのリカバリ時間+エクスポート+インポートの時間
  • 復旧した表は制約が外れる

 今回の検証結果を踏まえると、データの内容は復旧できるが、表の制約や関連する索引は復旧できない。元表をドロップして、recover tableで戻したとしても、その状態から制約や索引を付与する対処をすることは、運用上結構厳しいかもしれない。それならば、今回のように別名で一度復旧させて、元表をトランケートし、insert into selectでコピー、リストアした表をドロップが良いだろう。一時的にこの表の領域が2倍必要となるが、表毎の個別対応をするよりはシンプルに復旧できる。
SQL> truncate table emp;

Table EMP truncated.

SQL> insert /*+ append */ into emp select * from emp_recovered;

14 rows inserted.

SQL> commit;

Commit complete.

SQL> drop table emp_recovered;

Table EMP_RECOVERED dropped.

SQL>

 もう一つ考えられるのは、RESTOREコマンドにNOTABLEIMPORT句をつけて、ダンプの出力で留めておく方法である。インポートする際にトランケートモード、DATA_ONLYでインポートすれば、索引を維持したままデータを復旧できるし、DB領域は余計にかからないだろう。

22.1.3.4 リカバリされた表および表パーティションのターゲット・データベースへのインポートについて デフォルトでは、RMANは、リカバリされた表または表パーティション(エクスポート・ダンプ・ファイルに格納される)をターゲット・データベースにインポートします。ただし、RESTOREコマンドのNOTABLEIMPORT句を使用すると、リカバリされた表または表パーティションをインポートしないように選択できます。


 もしリストアポイント後に定義変更がされていた場合、元表と復旧した表とで定義が異なることになる。この場合はやはり表は再作成(必要な索引も含め)した上で、上記いずれかの対応を取ることになるのだろう。データやオブジェクトの依存関係を考えなければならないという点で、オブジェクトレベルのリカバリは本質的に難易度が高くなるのは仕方がない。

5.まとめ


 本稿では補助インスタンスを用いた表のリカバリについて、実機検証した結果を踏まえ活用方法について考察した。総じて言えるのは、バックアップから特定の表の特定の状態のダンプファイルを生成することができる、という点は極めて有用である。本質的には複雑な処理をRMANの1つのコマンドで実現できてしまうのは非常に有難い。
 ただ、使いどころという観点では、あまり大量の表に対して実行するケースには向いておらず、特定、あるいはいくつかの表について復旧したい場合に限られるだろう。補助インスタンスは復旧に関連する表領域(を構成するデータファイル)をリストアするため、復旧範囲が広がれば広がるほど、元のデータベースと同じ領域が補助インスタンスに必要となってしまうし、ダンプファイルも大きくなってしまう。
 今回の結果を踏まえると、フラッシュバックテーブルが使えるなら、多くの場合でその方が復旧はシンプルで速いだろう。UNDOを大きくとる必要はあるが、バックアップ運用が回れば副作用はないように思う。それでもUNDOが不足した場合(ギャランティでない場合は上書きされてしまう)は復旧できないというリスクがあるし、定義変更の影響もあるので、そういった表についてはこの方法で個別にリカバリすればよいだろう。
 今回調べている中で、TSPITRを使った表領域のリカバリもできることが気が付いた。リカバリしたい表(+関連する索引)を表領域にまとめられれば、有効な復旧方法になるかもしれない。この点については、別途検証してみたい。Oracleは様々なリカバリ方法が用意されているので、ユーザ側が要件に対してどこまで使い方をイマジネーションできるかが重要である。

参考


[1]補助インスタンスを用いた表のリカバリ検証のログ20240311_recovertable.txt
[2]バックアップおよびリカバリ・ユーザーズ・ガイド 19c, 22 表および表パーティションのリカバリ

OCIでPostgreSQLインスタンスの作成 [OCI]

 検証のためOCIのPostgreSQLインスタンスを作成したので備忘までメモを残しておく。細かな手順は別として、実際に構築してみてわかったのは以下の点である。
  • OSにログインはできない。psql等クライアントからの接続となる
  • バージョンは14.9(現時点で他に選択肢はない)
  • リードレプリカを作成可能(未検証)
  • postgresql.confは直接編集不可
  • ノード停止できない(起動したままとなる)
  • シェイプの変更はできない

実施した内容は以下の通り。詳細な手順は参考[1]のマニュアルを参照されたい。
  • 1.準備
  • 2.PostgreSQLインスタンス作成
  • 3.クライアントからの接続
  • 4.設定変更(timezone)

1.準備


 前提として、パブリックネットワークとプライベートネットワークから構成されるVCNがあるとする。PostgreSQLへ接続するコンピュートVMはパブリックネットワーク、PostgreSQLインスタンスはプライベートネットワークに配置するものとする。このため、プライベートネットワークのセキュリティリストにPostgreSQLの通信用に5432ポートの通信許可を追加する。
20240303_OCIPostgreSQL.jpg

 パブリックネットワークのクライアントVMには、PostgreSQLのクライアントを構成しておく。今回実施した手順は以下の通り(参考[2])。結果、psqlが使えることを確認する。
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server

2.PostgreSQLインスタンス作成


 OCIコンソールのPostgreSQLメニューから、Create Database systemを選択し、以下を入力する。
  • Database system name: pg14
  • PostgreSQL major version: 14 (固定)
  • Node count: 1 (リードレプリカなし)
  • Performance tier: 300K IOPS (固定)
  • OCPU count: 2 (2,4,8,16,32,64から選択)
  • Network configuration: VCNとプライベートネットワーク、IPアドレスを指定
  • administrator credentials: pgadmin/xxxを指定 (OCI Vaultが利用可能)
  • Automatic backups: Enable/Daily/retention 1 dayを選択
  • Maintenance: set by oracleを指定(schdule your own maintenanceも選択可能)
  • Configuration: PostgreSQL.VM.Standard.E4.Flex.2.32GB-14-0_34 を指定(シェイプで決定される)

20240303_confdbsystem1.jpg

 レビュー画面で内容を確認しSubmitボタンを押下すると10分程度でインスタンス作成が完了しACTIVE状態になる。Connection detailから、EndpointのFQDNを確認しておく(今回は検証のためIPアドレスを直接指定する)。バックアップを取得しておく。
20240303_confdbsystem2.jpg

 設定(postgresql.conf)はシェイプ毎にOracleお勧めの設定がすでに用意されているので、普通に使うだけなら特に変更の必要はない。configurationメニューから設定されているパラメータセットの内容を確認することができる。ただ、ここで直接パラメータの変更はできない。パラメータセットを新規作成すると値を入力できるようだが、1つずつ入れていくのは現実的ではないので、何か良い方法があるのだろう。参考[4]に今回作成したpostgreSQLのパラメータを載せておく。
20240303_confdbsystem3.jpg

3.クライアントからの接続


 クライアントのコンピュートVMから、psqlで以下の通り接続する。
[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d postgres
Password for user pgadmin:
psql (14.11, server 14.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \l
                                         List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Access privileges
-----------+---------------+----------+-------------+-------------+---------------------------------
 postgres  | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | oci_superuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser               +
           |               |          |             |             | oci_superuser=CTc/oci_superuser
 template1 | oci_superuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/oci_superuser               +
           |               |          |             |             | oci_superuser=CTc/oci_superuser
(3 rows)

postgres=>

必要に応じて.pgpassを設定しておくと、パスワードを聞かれずに済むので楽になる。
[opc@bastvm1 ~]$ cat .pgpass
10.0.1.XX:5432:postgres:pgadmin:password
[opc@bastvm1 ~]$ ll .pgpass
-rw-------. 1 opc opc 45 Mar  3 05:13 .pgpass

4.設定変更


 検証用のDBを作成する。タイムゾーンがデフォルトUTCのため、Asia/Tokyoに変更する。postgresql.confを直接変更したいところだが、コンソールから直接変更することができないため、alter databaseで変更した。
[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d postgres
Password for user pgadmin:
...
postgres=> create database pgdb;
CREATE DATABASE
postgres=> \l
                                         List of databases
   Name    |     Owner     | Encoding |   Collate   |    Ctype    |        Acces
s privileges        
-----------+---------------+----------+-------------+-------------+-------------
--------------------
 pgdb★    | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | pgadmin       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
...(4 rows)

pgdb=> alter database pgdb set timezone='Asia/Tokyo';
ALTER DATABASE
postgres=> exit
[opc@bastvm1 ~]$ psql -h 10.0.1.XX -U pgadmin -d pgdb
Password for user pgadmin:
...
pgdb=> select now();
              now
-------------------------------
 2024-03-03 07:17:27.088856+09
(1 row)

pgdb=> show timezone;
  TimeZone
------------
 Asia/Tokyo
(1 row)

5.まとめ


 本稿ではOCIでPostgreSQLインスタンスを作成しクライアントから接続する手順を確認した。今回は単純なシングルインスタンスであったが、簡単にリードレプリカのノードを追加もできるようなので、時間があれば検証してみたい。さすがにOracleのBaseDBと比較すると、バージョンが固定であったり、CPUのスケーリングや停止ができないことなど制約があると感じた。postgresql.confの設定変更については便利な方法が望まれる。

参考


[1]OCI Database with PostgreSQL
[2]PostgreSQL Linux downloads (Red Hat family)
[3]PostgreSQLクライアントの構成例
[root@bastvm1 opc]# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:06:25 ago on Sun 03 Mar 2024 04:32:27 AM JST.
pgdg-redhat-repo-latest.noarch.rpm               11 kB/s |  14 kB     00:01
Dependencies resolved.
================================================================================
 Package                Architecture Version           Repository          Size
================================================================================
Installing:
 pgdg-redhat-repo       noarch       42.0-38PGDG       @commandline        14 k

Transaction Summary
================================================================================
Install  1 Package

Total size: 14 k
Installed size: 15 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1
  Installing       : pgdg-redhat-repo-42.0-38PGDG.noarch                    1/1
  Verifying        : pgdg-redhat-repo-42.0-38PGDG.noarch                    1/1

Installed:
  pgdg-redhat-repo-42.0-38PGDG.noarch

Complete!
[root@bastvm1 opc]#
[root@bastvm1 opc]# sudo dnf -qy module disable postgresql
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
[root@bastvm1 opc]#
[root@bastvm1 opc]# sudo dnf install -y postgresql14-server
Last metadata expiration check: 0:03:35 ago on Sun 03 Mar 2024 04:40:25 AM JST.
Dependencies resolved.
================================================================================
 Package               Arch     Version               Repository           Size
================================================================================
Installing:
 postgresql14-server   x86_64   14.11-1PGDG.rhel8     pgdg14              5.7 M
Installing dependencies:
 lz4                   x86_64   1.8.3-3.el8_4         ol8_baseos_latest   103 k
 postgresql14          x86_64   14.11-1PGDG.rhel8     pgdg14              1.5 M
 postgresql14-libs     x86_64   14.11-1PGDG.rhel8     pgdg14              281 k

Transaction Summary
================================================================================
Install  4 Packages

Total download size: 7.6 M
Installed size: 32 M
Downloading Packages:
(1/4): postgresql14-libs-14.11-1PGDG.rhel8.x86_ 111 kB/s | 281 kB     00:02
(2/4): postgresql14-14.11-1PGDG.rhel8.x86_64.rp 604 kB/s | 1.5 MB     00:02
(3/4): lz4-1.8.3-3.el8_4.x86_64.rpm              32 kB/s | 103 kB     00:03
(4/4): postgresql14-server-14.11-1PGDG.rhel8.x8 2.6 MB/s | 5.7 MB     00:02
--------------------------------------------------------------------------------
Total                                           1.6 MB/s | 7.6 MB     00:04
PostgreSQL 14 for RHEL / Rocky / AlmaLinux 8 -  2.4 MB/s | 2.4 kB     00:00
Importing GPG key 0x08B40D20:
 Userid     : "PostgreSQL RPM Repository "
 Fingerprint: D4BF 08AE 67A0 B4C7 A1DB CCD2 40BC A2B4 08B4 0D20
 From       : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1
  Installing       : postgresql14-libs-14.11-1PGDG.rhel8.x86_64             1/4
  Running scriptlet: postgresql14-libs-14.11-1PGDG.rhel8.x86_64             1/4
  Installing       : lz4-1.8.3-3.el8_4.x86_64                               2/4
  Installing       : postgresql14-14.11-1PGDG.rhel8.x86_64                  3/4
  Running scriptlet: postgresql14-14.11-1PGDG.rhel8.x86_64                  3/4
  Running scriptlet: postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Installing       : postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Running scriptlet: postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4
  Verifying        : lz4-1.8.3-3.el8_4.x86_64                               1/4
  Verifying        : postgresql14-14.11-1PGDG.rhel8.x86_64                  2/4
  Verifying        : postgresql14-libs-14.11-1PGDG.rhel8.x86_64             3/4
  Verifying        : postgresql14-server-14.11-1PGDG.rhel8.x86_64           4/4

Installed:
  lz4-1.8.3-3.el8_4.x86_64
  postgresql14-14.11-1PGDG.rhel8.x86_64
  postgresql14-libs-14.11-1PGDG.rhel8.x86_64
  postgresql14-server-14.11-1PGDG.rhel8.x86_64

Complete!
[root@bastvm1 opc]#
[root@bastvm1 opc]# which psql
/bin/psql

[4]PostgreSQLパラメータ一覧:20240303_postgresqlconf.txt

SQL*Loaderの性能見積もりの考え方 [アーキテクチャ]

はじめに


 データ移行等でcsvをSQL*LoaderでOracleにロードする際、どの程度の性能を期待できるのかを見積もる必要がある。特定の表についてであれば、実機の基礎性能をベースに件数で比例させれば簡単に見積もりができる。しかし、実際は様々な表があり、それぞれに対して測定する訳にもいかない。ここでは、簡単なテストケースでLoaderの基礎性能を測定し、平均行長と行数からロード性能を見積もる方法について考察してみたい。

1.性能モデル


 やりたいことは、特定の環境において、入力となるcsvの平均行長と行数から、SQL*Loaderの性能を見積もりすることである。
 方法として考えたのは、平均行長の異なる3つの表、b10tbl, b50tbl, b100tblについて、一定の件数(10,000,000件)のロード性能を基礎数値として取得し、それをもとに性能を見積もる方法である。
 モデルとした表は、idカラムをnumber型の主キーとし、1からの連番を振る。dtカラムはdate型で、更新日時を入れる。col1~colnはvarchar2(10)のカラムで、b10tblは1個、b50tblは5個、b100tblは10個とし、ランダムな文字列を10バイト挿入する。
20240220_sqlldrPerfModel.jpg

 Loaderの測定パタンとしては、一般的なにコンベンショナルとダイレクトパスロードを考える。前提として、データは主キー順に並んでいることは保証されず、主キーは作成しつつ挿入することとする。NOLOGGINGも使わない。表のPCTFREEはデフォルト10%、記憶域(初期エクステントサイズ)もデフォルト(64KB)とする。

2.表(データ)の準備


 表を作成する。
drop table b10tbl cascade constraints;
create table b10tbl(
id number(9),
dt date,
col1 varchar2(10),
constraint pk_b10tbl primary key (id)
);
drop table b50tbl cascade constraints;
create table b50tbl(
id number(9),
dt date,
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10),
col5 varchar2(10),
constraint pk_b50tbl primary key (id)
);
drop table b100tbl cascade constraints;
create table b100tbl(
id number(9),
dt date,
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10),
col5 varchar2(10),
col6 varchar2(10),
col7 varchar2(10),
col8 varchar2(10),
col9 varchar2(10),
col10 varchar2(10),
constraint pk_b100tbl primary key (id)
);

 SQL*Loader用のcsvを生成するための初期データを挿入する。
begin
 for i in 1..10 loop
  insert into b10tbl select
   1000000*(i-1)+rownum id,
   sysdate dt,
   dbms_random.string('x',10) col1
   from dual connect by level <=1000000;
  commit;
 end loop;
end;
/
begin
 for i in 1..10 loop
  insert into b50tbl select
   1000000*(i-1)+rownum id,
   sysdate dt,
   dbms_random.string('x',10) col1,
   dbms_random.string('x',10) col2,
   dbms_random.string('x',10) col3,
   dbms_random.string('x',10) col4,
   dbms_random.string('x',10) col5
  from dual connect by level <=1000000;
  commit;
 end loop;
end;
/
begin
 for i in 1..10 loop
  insert into b100tbl select
   1000000*(i-1)+rownum id,
   sysdate dt,
   dbms_random.string('x',10) col1,
   dbms_random.string('x',10) col2,
   dbms_random.string('x',10) col3,
   dbms_random.string('x',10) col4,
   dbms_random.string('x',10) col5,
   dbms_random.string('x',10) col6,
   dbms_random.string('x',10) col7,
   dbms_random.string('x',10) col8,
   dbms_random.string('x',10) col9,
   dbms_random.string('x',10) col10
  from dual connect by level <=1000000;
  commit;
 end loop;
end;
/

 統計情報を取得しておく(_optimizer_gather_stats_on_loadがデフォルト(TRUE)のため、空だと自動で取得されてしまうかもしれないため)。
exec dbms_stats.gather_table_stats('scott','b10tbl');
exec dbms_stats.gather_table_stats('scott','b50tbl');
exec dbms_stats.gather_table_stats('scott','b100tbl');

 この時点でセグメントサイズを確認しておく。
col segment_name for a20
select segment_name, sum(bytes)/1024/1024 mb from user_segments
where segment_name like '%B%TBL' group by segment_name order by 1;

SEGMENT_NAME                 MB
-------------------- ----------
B100TBL                    1415
B10TBL                      327
B50TBL                      804
PK_B100TBL                  160
PK_B10TBL                   164
PK_B50TBL                   163

6 rows selected.

 ここから、csvファイルを出力するためのSQLファイルを作成する。ここで主キーでのソートはしない。csvの出力先を/u01とする(OCIのBaseDBは/u01のファイルシステムに196Gが割り当てられている。/homeは1GBもないので、大きなファイルを置いてはいけない)。
vi b10tbl.sql
spool /u01/app/oracle/dump/scott/b10tbl.csv
select * from b10tbl;
spool off

vi b50tbl.sql
spool /u01/app/oracle/dump/scott/b50tbl.csv
select * from b50tbl;
spool off

vi b100tbl.sql
spool /u01/app/oracle/dump/scott/b100tbl.csv
select * from b100tbl;
spool off

 上記sqlファイルをsqlplusから実行して、csvファイルを作成する。
sqlplus scott/xxx@oradb
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';
set feedback off heading off termout off
set markup csv on delimiter '|'
@b10tbl.sql
@b50tbl.sql
@b100tbl.sql
set markup csv off

 csvのサイズは以下の通り、最大のB100TBLで1.47GBであった。
[oracle@oradbvm1 ~]$ cd /u01/app/oracle/dump/scott/
[oracle@oradbvm1 scott]$ ll *.csv
-rw-r--r-- 1 oracle oinstall 1578888897 Feb 18 16:19 b100tbl.csv
-rw-r--r-- 1 oracle oinstall  408888897 Feb 18 16:17 b10tbl.csv
-rw-r--r-- 1 oracle oinstall  928888897 Feb 18 16:18 b50tbl.csv
[oracle@oradbvm1 scott]$

3.SQL*Loaderの準備


 SQL*Loaderの制御ファイルを用意する。トランケートモードとして、表にデータが入っていれば切り捨てられる。
vi b10tbl.ctl
load data characterset UTF8
infile '/u01/app/oracle/dump/scott/b10tbl.csv'
badfile 'b10tbl.bad'
discardfile 'b10tbl.dsc'
truncate into table b10tbl
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(
id integer external,
dt date 'YYYYMMDD HH24:MI:SS',
col1 char(10)
)

vi b50tbl.ctl
load data characterset UTF8
infile '/u01/app/oracle/dump/scott/b50tbl.csv'
badfile 'b50tbl.bad'
discardfile 'b50tbl.dsc'
truncate into table b50tbl
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(
id integer external,
dt date 'YYYYMMDD HH24:MI:SS',
col1 char(10),
col2 char(10),
col3 char(10),
col4 char(10),
col5 char(10)
)

vi b100tbl.ctl
load data characterset UTF8
infile '/u01/app/oracle/dump/scott/b100tbl.csv'
badfile 'b100tbl.bad'
discardfile 'b100tbl.dsc'
truncate into table b100tbl
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(
id integer external,
dt date 'YYYYMMDD HH24:MI:SS',
col1 char(10),
col2 char(10),
col3 char(10),
col4 char(10),
col5 char(10),
col6 char(10),
col7 char(10),
col8 char(10),
col9 char(10),
col10 char(10)
)

4.性能測定


 上記のモデルを実際にOCIのBaseDBを利用して測定した。環境はこちらに記載の環境と同じため割愛する。
 SQL*Loaderでロードを行う。まずは、コンベンショナルモードを使う。1000件毎にコミットとなるよう、rowsとbindsizeを設定する。
sqlldr scott/xxx@oradb control=b10tbl.ctl log=b10tblc.log rows=1000,bindsize=1000000,silent=errors,feedback
sqlldr scott/xxx@oradb control=b50tbl.ctl log=b50tblc.log rows=1000,bindsize=1000000,silent=errors,feedback
sqlldr scott/xxx@oradb control=b100tbl.ctl log=b100tblc.log rows=1000,bindsize=1000000,silent=errors,feedback

 続いて、ダイレクトパス(direct=true)でデータロードを行う。1000件毎の処理となるよう、columnarrayrowsを設定する。
sqlldr scott/xxx@oradb control=b10tbl.ctl log=b10tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback
sqlldr scott/xxx@oradb control=b50tbl.ctl log=b50tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback
sqlldr scott/xxx@oradb control=b100tbl.ctl log=b100tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback

5.測定結果


 測定結果のサマリは下表の通り。
20240219_Result_1.jpg

 上記結果から、平均行長とロード性能の関係をグラフにすると、下図の通りとなった。行長が大きくなると線形に処理時間が伸びているように見えるため、ここから任意の平均行長について10,000,000件をロードする近似式を作ることができる。
20240219_Result_5.jpg

 上記近似式で得られた結果から、単位行数あたりのロード時間が求まるので、下図のように任意の行数を時間を見積もる式が得られる。例えば、平均行長223バイト、5,000,000件のb200tblの場合は、コンベンショナルは57秒、ダイレクトパスロードは23秒と見積もることができる。
20240220_LoaderEstimation.jpg

 答え合わせのため、実際に平均行長223バイト、5,000,000件で測定したところ、コンベンショナルの見積もり57秒に対し54秒、ダイレクトパスの見積もり23秒に対し28秒となった。若干ブレはあるものの、まずまずの値が見積もれることがわかった。
SQL> !ls -l /u01/app/oracle/dump/scott/b200tbl.csv
-rw-r--r-- 1 oracle oinstall 1113888896 Feb 20 07:36 /u01/app/oracle/dump/scott/b200tbl.csv
SQL> select 1113888896/5000000 from dual;
        222.777779 →平均カラム長223バイト
SQL> !wc -l /u01/app/oracle/dump/scott/b200tbl.csv
5000000 /u01/app/oracle/dump/scott/b200tbl.csv →5,000,000件
[oracle@oradbvm1 ldrtest]$ grep Elapsed b200tbld.log
Elapsed time was:     00:00:28.24 →ダイレクトパス
[oracle@oradbvm1 ldrtest]$ grep Elapsed b200tblc.log
Elapsed time was:     00:00:54.42 →コンベンショナル
[oracle@oradbvm1 ldrtest]$

7.まとめ


 本稿では、SQL*Loaderの性能を、実機の基礎性能を用いて、任意の行長、行数のロード性能(コンベンショナル、ダイレクト)を見積もる方法について考察した。性能の結果はあくまで一例で、実際は入力ファイルの要件に応じてLoaderのパラメータは実際の利用シーンにより適宜変更が必要だろう。なお、平均行長が伸びると、線形に処理時間も伸びる、という仮定は一定の範囲を超えると誤差が大きくなるかもしれない。ブロックサイズ(今回の環境では8KB、PCTFREE10%)に対して、平均行長が極端に大きい場合は誤差が大きくなる可能性があるので、大きな行長を扱う場合は基礎性能モデルの作り方に注意が必要だろう。

◆参考


[1]測定結果詳細:20240218_sqlldrPerfTest_v2.xlsx
前の3件 | -