前の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の共有フォルダの設定)。
テストシナリオは以下の通り:
- 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 表および表パーティションのリカバリ
2024-03-16 20:58
OCIでPostgreSQLインスタンスの作成 [OCI]
検証のためOCIのPostgreSQLインスタンスを作成したので備忘までメモを残しておく。細かな手順は別として、実際に構築してみてわかったのは以下の点である。
実施した内容は以下の通り。詳細な手順は参考[1]のマニュアルを参照されたい。
前提として、パブリックネットワークとプライベートネットワークから構成されるVCNがあるとする。PostgreSQLへ接続するコンピュートVMはパブリックネットワーク、PostgreSQLインスタンスはプライベートネットワークに配置するものとする。このため、プライベートネットワークのセキュリティリストにPostgreSQLの通信用に5432ポートの通信許可を追加する。
パブリックネットワークのクライアントVMには、PostgreSQLのクライアントを構成しておく。今回実施した手順は以下の通り(参考[2])。結果、psqlが使えることを確認する。
OCIコンソールのPostgreSQLメニューから、Create Database systemを選択し、以下を入力する。
レビュー画面で内容を確認しSubmitボタンを押下すると10分程度でインスタンス作成が完了しACTIVE状態になる。Connection detailから、EndpointのFQDNを確認しておく(今回は検証のためIPアドレスを直接指定する)。バックアップを取得しておく。
設定(postgresql.conf)はシェイプ毎にOracleお勧めの設定がすでに用意されているので、普通に使うだけなら特に変更の必要はない。configurationメニューから設定されているパラメータセットの内容を確認することができる。ただ、ここで直接パラメータの変更はできない。パラメータセットを新規作成すると値を入力できるようだが、1つずつ入れていくのは現実的ではないので、何か良い方法があるのだろう。参考[4]に今回作成したpostgreSQLのパラメータを載せておく。
クライアントのコンピュートVMから、psqlで以下の通り接続する。
必要に応じて.pgpassを設定しておくと、パスワードを聞かれずに済むので楽になる。
検証用のDBを作成する。タイムゾーンがデフォルトUTCのため、Asia/Tokyoに変更する。postgresql.confを直接変更したいところだが、コンソールから直接変更することができないため、alter databaseで変更した。
本稿ではOCIでPostgreSQLインスタンスを作成しクライアントから接続する手順を確認した。今回は単純なシングルインスタンスであったが、簡単にリードレプリカのノードを追加もできるようなので、時間があれば検証してみたい。さすがにOracleのBaseDBと比較すると、バージョンが固定であったり、CPUのスケーリングや停止ができないことなど制約があると感じた。postgresql.confの設定変更については便利な方法が望まれる。
[1]OCI Database with PostgreSQL
[2]PostgreSQL Linux downloads (Red Hat family)
[3]PostgreSQLクライアントの構成例
[4]PostgreSQLパラメータ一覧:20240303_postgresqlconf.txt
- OSにログインはできない。psql等クライアントからの接続となる
- バージョンは14.9(現時点で他に選択肢はない)
- リードレプリカを作成可能(未検証)
- postgresql.confは直接編集不可
- ノード停止できない(起動したままとなる)
- シェイプの変更はできない
実施した内容は以下の通り。詳細な手順は参考[1]のマニュアルを参照されたい。
- 1.準備
- 2.PostgreSQLインスタンス作成
- 3.クライアントからの接続
- 4.設定変更(timezone)
1.準備
前提として、パブリックネットワークとプライベートネットワークから構成されるVCNがあるとする。PostgreSQLへ接続するコンピュートVMはパブリックネットワーク、PostgreSQLインスタンスはプライベートネットワークに配置するものとする。このため、プライベートネットワークのセキュリティリストにPostgreSQLの通信用に5432ポートの通信許可を追加する。
パブリックネットワークのクライアント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 を指定(シェイプで決定される)
レビュー画面で内容を確認しSubmitボタンを押下すると10分程度でインスタンス作成が完了しACTIVE状態になる。Connection detailから、EndpointのFQDNを確認しておく(今回は検証のためIPアドレスを直接指定する)。バックアップを取得しておく。
設定(postgresql.conf)はシェイプ毎にOracleお勧めの設定がすでに用意されているので、普通に使うだけなら特に変更の必要はない。configurationメニューから設定されているパラメータセットの内容を確認することができる。ただ、ここで直接パラメータの変更はできない。パラメータセットを新規作成すると値を入力できるようだが、1つずつ入れていくのは現実的ではないので、何か良い方法があるのだろう。参考[4]に今回作成したpostgreSQLのパラメータを載せておく。
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
2024-03-03 10:15
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バイト挿入する。
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.測定結果
測定結果のサマリは下表の通り。
上記結果から、平均行長とロード性能の関係をグラフにすると、下図の通りとなった。行長が大きくなると線形に処理時間が伸びているように見えるため、ここから任意の平均行長について10,000,000件をロードする近似式を作ることができる。
上記近似式で得られた結果から、単位行数あたりのロード時間が求まるので、下図のように任意の行数を時間を見積もる式が得られる。例えば、平均行長223バイト、5,000,000件のb200tblの場合は、コンベンショナルは57秒、ダイレクトパスロードは23秒と見積もることができる。
答え合わせのため、実際に平均行長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
2024-02-20 06:47
前の3件 | -