TOP>企業情報>コラム>技術情報>PGEConsのドキュメントからPostgreSQLの「今」を知る! Vol.3

PGEConsのドキュメントからPostgreSQLの「今」を知る! Vol.3

エンタープライズ領域でのPostgreSQLの普及促進を目指して設立されたPostgreSQLエンタープライズ・コンソーシアム(略称:PGECons)。本連載では、3年目となった2014年度の活動を通じて新たに提示された性能、移行、設計運用に関する数百ページにわたるドキュメントを項目ごとに分かりやすく要約していきます。最終回となる今回は移行時のテスト工程に焦点を当てご紹介します。

Vol.3 PostgreSQL移行の90%を占めるテスト工程を攻略!移行ワーキンググループの活動成果

最も時間を要する移行後のテスト工程


近年、PostgreSQLが採用される機会が増えていますが、既存システムからの置きかえの場合、異種データベースからPostgreSQLへの「移行手順が確立されていない」ことが重要な課題の1つとして挙げられ、実際の移行は過去の経験則や点在するノウハウに依存しているのが実状です。

今回ご紹介する移行ワーキンググループ(移行WG)は、PGEConsが設立された2012年から活動するグループで、異種データベースからPostgreSQLへの移行を検討する際のガイドラインの提示を目標に、移行検討時の留意事項や、難易度判断、調査方法など14種の項目を策定し、2012年度はそのうちの11種の情報を整理しました。ドキュメントは手に取りやすいように項目ごとに個々のファイルとなっており、例えば、PostgreSQLへの移行に必要な一連の作業は「DB移行フレームワーク編 」に、また移行対象となる「スキーマ」「データ」「アプリケーション」のデータベース間の相違点や具体的な移行手順はそれぞれ以下のドキュメントに詳細情報が記載されています。

スキーマ移行調査編
データ移行調査および実践編
アプリケーション移行実践編

2013年度は新たにマルチバイトを含む文字コード変換を伴うデータの移行、商用データベースのストアドプロシージャ移行の手順やチューニング手法などを整備しました。それぞれ以下のドキュメントに詳細情報がまとめられています。

データ移行・文字コード変換編
ストアドプロシージャ移行調査編
チューニング編

そして3年目となった2014年度はデータベース移行後のテスト工程に焦点を当て、「スキーマ」「データ」「アプリケーション」が正常に移行できたかどうかを確認する手法や注意点を整理しました。データベース移行後はすべてのアプリケーションを対象に稼働テストを実施することが通常であり、移行WGが2012年度に実施したアプリケーション移行検証においてもテスト工程が移行の90%を占める結果になったことを受け、テスト工程の情報整備が重要という認識に基づいています。

図1:APP移行検証の各フェーズ所要時間比率

図1:APP移行検証の各フェーズ所要時間比率

今回、「InfoScoop」と呼ばれるオープンソースの企業ポータルツールを使って、Oracle Database 11gからPostgreSQL 9.4へのテスト移行を実施し、その結果を元にドキュメントを整備しています。次項から移行対象となる「スキーマ」「データ」「アプリケーション」を順に見ていきます。

スキーマ移行の結果を確認


オブジェクト定義情報を含むスキーマは、図2のように移行元の異種データベースと移行先のPostgreSQLからスキーマの情報をそれぞれ抽出し、それらのオブジェクト定義情報の差分を元に結果が同一または仕様変更を行った部分が正しく変更されていることを確認します。

今回の対象は「スキーマ」とそれに紐付く「テーブル」「列」「インデックス」「ビュー」「制約」など基本的なオブジェクトを前提としており、成果ドキュメントではOracle Database、Microsoft SQL Server、PostgreSQLから定義情報を抽出するためのサンプルSQLが紹介されています。

※各RDBMSから定義情報を抽出するサンプルSQLは「試験編 異種RDBMSからPostgreSQLへの移行」p14-p16をご参照ください。
https://www.pgecons.org/wp-content/uploads/PGECons/2014/WG2/13_Test.pdf

図2:スキーマ移行の結果確認

図2:スキーマ移行の結果確認

定義情報はテキストで出力して比較することができますが、比較する前に出力形式の統一などいくつか加工を行う必要があります。今回は、迅速かつ容易に確認するために、図3にあるように抽出データをPostgreSQLのデータベースへ挿入し、出力形式の変換を行った上で差分比較を行いました。今回の比較では、Oracle DatabaseからPostgreSQLへのデータ移行ツールである「Ora2Pg」で定義された属性変換ルールに基づいて差分を抽出していますが、実際の移行時はシステムごとに定義された変換ルールに則って実施してください。

図3:定義情報の比較手法

図3:定義情報の比較手法

定義に差異があったオブジェクトについては、移行時の変更仕様に基づくものかどうかを確認し、該当しない差異を不具合と判断します。不具合には図4のようなケースが考えられ、内容に応じて対処を実施します。

図4:不具合の内容と対処

図4:不具合の内容と対処

データ移行の結果を確認


移行で最も重要な要素であるデータの確認項目は「テーブル数」「データ件数」「データサイズ」「データ内容」など多岐にわたります。成果ドキュメントではOracle Database、Microsoft SQL ServerとPostgreSQLそれぞれの確認方法に加え、各項目の考慮点が詳細にまとめられています。

例えば、テーブル数を確認する場合、パーティションを利用する環境では確認方法によって個数が同一にならない可能性があります。それは、PostgreSQLでは親テーブルの定義を継承する形でパーティションの数だけ子テーブルが存在するためです。子テーブルを排除したテーブル数は、以下のSQLで確認できます。


データ件数の確認には、テーブル名とデータ件数を取得するSQLを生成し、そのSQLを実行することで比較がしやすくなります。PostgreSQLでは、以下のSQLによりテーブル名とデータ件数を取得するSQLが生成できます。


上記SQLを実行すると次のような結果が得られます。このSQLを実行してテーブル名とデータ件数を取得しますが、すべてのテーブルに対してシーケンシャルスキャンが行われるため、ディスクI/Oの負荷に注意が必要です。


データサイズは、移行元のデータベースに格納されたデータ件数とデータの種別などから、まずPostgreSQLへ移行後のデータサイズの想定値を算出しておき、PostgreSQLの実際のデータサイズと想定値が近似であることを確認していきます。

PostgreSQLのデータサイズの想定値は、1データブロック(デフォルト8192バイト)あたりに格納可能な行データの数を確認し、全ての行データに必要なブロック数を算出します。1行あたりのデータサイズは以下の公式で算出できます。各要素のサイズや計算方法は、成果ドキュメントをご参照ください。

レコードサイズ =
ブロック内部ポインタ + レコードヘッダ + NULLビットマップ + パディング + OID + フィールド領域


上記で算出された値を元に、すべての行データに必要なブロック数とデータサイズを算出します。

全レコードを格納するのに必要なブロック数 =
全レコード数 / 1ブロックに格納できる行数
テーブルサイズ =
全レコードを格納するのに必要なブロック数 * ブロックサイズ


PostgreSQLでは、1ブロックサイズの大きさを超える行データを複数ブロックにまたぐ形で格納することができないため、TOAST(The Oversized-Attribute Storage Technology)と呼ばれる仕組みでデータを分割、圧縮して外部テーブルへ格納します。TOASTはテーブルの列定義に応じて自動的に有効になるため、データサイズを確認する際は、実テーブルとあわせてTOASTの外部テーブルのサイズも考慮します。

※移行ツールのログからデータ件数やサイズが確認できるPostgres Plus

弊社アシストの取扱製品である「Postgres Plus Enterprise Edition」(以下、PPEE)はPostgreSQLをエンジンとし、パフォーマンス、セキュリティ、GUIツールなどPostgreSQLに未実装のエンタープライズ向け機能を補完したRDBMSで、標準で提供される移行ツール「Migration Tool Kit」(以下、MTK)により、Oracle Databaseのテーブル(パーティションテーブルを含む)、ビュー、インデックス、制約など基本オブジェクトを簡単に移行できます。さらにMTK実行時のログファイルにはオブジェクト名、テーブルデータの件数やサイズが出力されるため、移行元の情報と比較することでデータ移行の妥当性の確認に役立ちます。MTKはMicrosoft SQL Server、Sybase、MySQLにも対応しています。

PPEEのすべての機能を60日間無償で利用できるトライアルをご用意しています。
詳細URL:https://www.ashisuto.co.jp/product/category/database/postgresql/download/#tab


データそのものの確認は最も難易度が高い作業の1つです。移行元と移行先が同種のデータベースであれば、テーブル数やデータ件数の確認のみで完了とするケースもありますが、異種RDBMSの場合は、図5のように各データベースからデータを抽出し突き合わせる必要があります。

図5:データ移行の結果確認

図5:データ移行の結果確認

成果ドキュメントでは、基本的なデータ抽出方法として、データベースごとにspool(Oracle Database)、bcp(Microsoft SQL Server)、copy(PostgreSQL)の各コマンドを利用したCSVファイルへの出力手順が紹介されています。ファイルへ出力する際、コマンドによって注意すべき点がいくつかあります。例えば、Oracle Databaseのspoolコマンドは「SELECT * FROM…」で出力すると固定長のCSVが生成されてしまうため、セパレータ(||','||など)をクエリ内に組み込む必要があります。また、PostgreSQLのcopyコマンドでは、出力データにダブルコーテーションが自動で付与されないようにFORMAT CSVオプションは外しておきます。

データの出力順序や文字コードなどがデータベースごとに異なるため、抽出時の対処に加えて、生成されたCSVファイルの整形が必要なケースがあります。この場合、移行先のデータベースであるPostgreSQLのCSVファイルは基本的に整形せず、可能な限り移行元のデータベースのCSVファイルを整形していきます。成果ドキュメントでは、Oracle DatabaseとPostgreSQL、Microsoft SQL ServerとPostgreSQLの組み合わせで、CSVファイルの整形方法をそれぞれ整理しています。

例えば、NULLは、Oracle Databaseのspoolコマンドでは空文字としてCSVファイルに出力されます。そのため、Oracle DatabaseとPostgreSQLのCSVファイルを比較する場合はOracle DatabaseのCSVファイルの空文字を全てNULL(\N)に置換します。

データの出力順序はデータベース内の物理的な格納に依存します。データ抽出時にORDER BY句などであらかじめ並び替えることもできますが、どの列で並び替えるのかを考慮する必要があり、SQLの生成にかかる時間が大きくなる可能性があります。成果ドキュメントでは、LinuxやWindowsのsortコマンドを利用して全体を並び替える基本的な方法が紹介されていますが、データサイズによっては、弊社の取扱製品「Syncsort DMExpress」など、データの抽出と並び替えを高速に行うことができるETLツールの利用を検討します。

ファイルの整形が完了した後、データの比較を行います。成果ドキュメントでは基本的な手法として、LinuxのdiffコマンドとWindowsのfcコマンドによる比較手順が紹介されています。ただし、実際のシステムでは移行対象のデータが数百ギガバイト、テラバイトといった大規模のケースもあり、その場合、全件データを出力したCSVファイルの比較は難しいのが実状です。一部の最も重要なテーブルのみCSVファイルで比較を行い、それ以外は特定の数値項目の合計(SUM)値を比較する、移行元と移行先で同一処理を行った結果を比較する、ランダムに抜き出した行データのみ比較する、キー列の値と非キー列のハッシュ値をデータベースから取得し比較するなど、移行にかけられる時間やデータの重要性などから最適な手法を検討します。

※データ移行結果の確認方法は「試験編 異種RDBMSからPostgreSQLへの移行」p26-p49をご参照ください。
https://www.pgecons.org/wp-content/uploads/PGECons/2014/WG2/13_Test.pdf

アプリケーション移行の結果を確認


アプリケーションの確認項目は「SQL」「業務バッチ」「画面操作」「性能」の4つに分けられます。尚、InfoScoopを前提とした移行では、主に画面操作を中心に確認を行っています。

SQLは、図6にあるように異種データベースとPostgreSQLでそれぞれSQLを実行し、その結果を突き合わせることで同一の動作か否かを確認します。実行結果が異なる場合は、移行方針に従った変更(SQLの修正など)、または移行時のミスによる問題のいずれかが考えられ、仕様に含まれないSQLの変更や移行漏れを「不具合」と定義します。

図6:SQLの確認の流れ

図6:SQLの確認の流れ

SQLの実行結果を突き合わせた時点で、テスト結果は図7にあるようなパターンに分類できます。

図7:SQLの確認の結果パターン

図7:SQLの確認の結果パターン

予期しないSQL実行結果の不一致が発生する箇所として、ソート順序や精度などいくつかの項目が考えられます。例えば、NULLと空文字のソート順序やDECIMAL、NUMERICなど数値型の精度を省略した場合の桁数の取り扱いがデータベースごとに異なるため、アプリケーション側でどのように吸収するかを検討します。SQL実行エラーはPostgreSQLのサーバログでエラー内容を確認します。主に一意制約違反やデッドロック、データ型の不一致などのエラーが発生することが考えられ、エラーの内容に応じて対処を実施します。成果ドキュメントに発生しうるエラーの詳細や確認すべきポイントが記載されていますので、ご参照ください。


業務バッチとは参照・更新処理を含む複数のSQLをデータベースに対して実行するプログラムを指します。埋め込みSQLなどのバッチ処理が存在する場合、SQL単体テストではなく、バッチ処理ごとにテストの実施が必要です。日次バッチ、月次バッチなどを実施する際には、バッチ処理が対象とするデータの範囲、データ量そのものを可能な限り実際の業務に合わせるようにします。


画面操作の確認は、画面テストツールなどを利用して移行元と移行先それぞれのアプリケーションに対して画面操作を実施し、画面の遷移結果や画面の出力内容が同一であることを確認します。今回は、Webアプリケーション用のテストツール「Selenium」から移行検証の対象としたInfoScoop向けのシナリオが提供されていたため、そちらを利用してテストを実施しています。PostgreSQLでは、176個のシナリオのうち、エラー発生が18、失敗が11と成功率は約8割という結果でしたが、原因を調査したところ、利用した仮想環境のスペック不足によるタイムアウトやPostgreSQL以外のRDBMSでも同様のエラーまたは失敗となることが判明し、PostgreSQLに起因する事象は発生していませんでした。


性能は、スキーマやデータ、アプリケーションの移行完了後、図8にある流れに沿って移行元のデータベースで求められた性能要件をPostgreSQLが満たすかどうかを確認します。性能要件は、要件定義書や設計書から実現すべき性能目標を抽出し、テストシナリオは移行元のデータベースで使用したものを流用するなどして事前に準備しておきます。テストは処理の「応答時間」「単位時間あたりのトランザクション処理件数」「消費するハードウェアリソース」の3つの要素を基本に結果を確認します。

図8:性能の確認手順

図8:性能の確認手順

要件を満たしていない処理が存在する場合は、チューニングを実施します。成果ドキュメントではCHECKPOINTやWALの書き込み競合、VACUUMなど性能に影響を与える可能性があるPostgreSQLの内部動作について、確認とチューニング方法が紹介されています。例えば、複数の更新処理が並列に実行されると、直列で行われるWALへの書き込みが競合し、トランザクション処理量やCPU使用率が向上しないケースがあります。この場合、時間あたりのWAL書き込み量を算出し、算出値とWALの格納ディスクへの単体書き込み性能の限界点とを比較します。値が近い場合はWALの書き込み競合がボトルネックになっていると判断できます。この場合、WALの領域を異なるディスクへ配置するなどの検討が必要です。


※アプリケーション移行の結果確認の詳細は「試験編 異種DBMSからPostgreSQLへの移行」p50-p68をご参照ください。
https://www.pgecons.org/wp-content/uploads/PGECons/2014/WG2/13_Test.pdf

また、性能チューニングについては過去の成果ドキュメント「チューニング編 」全般もあわせてご参照ください。

3回にわたり、PGEConsの2014年度成果ドキュメントを項目ごとにご紹介してきましたが、いかがでしたか。実際の成果ドキュメントには、本記事でご紹介できなかった詳細な検証手順やスクリプトが満載です。PostgreSQLの評価や採用を検討される際は、ぜひ手に取っていただければと思います。

PostgreSQLエンタープライズ・コンソーシアム 2014年度活動報告
https://www.pgecons.org/download/works_2014/

PostgreSQLは毎年メジャーバージョンがリリースされており、次バージョン9.5のリリースは2015年11月頃が予定されています。DWHシステム向けのインデックスや行単位のアクセス制御などエンタープライズで必要な多くの機能が新たに組み込まれる予定です。PostgreSQLの進化に引き続きご注目ください。

  • Postgres Plus は、EDB Postgres の旧製品名です。


執筆者紹介

高瀬 洋子(Youko Takase)

株式会社アシスト データベース技術本部

アシスト入社後、Oracle Databaseのサポート業務を経て、2009年よりPostgreSQL、EDB Postgresのサービス立ち上げに参画。「PostgreSQLなら高瀬に聞こう」と社内外から言ってもらえる存在となることを目標に日々活動。2014年4月よりイギリスに拠点を移し、PostgreSQL、EDB Postgresの啓蒙活動と顧客対応の後方支援を担当。

高瀬の紹介記事はこちら

連載記事一覧

関連製品/サービス


Facebookで情報をお届けしています

Facebookでは、アシストの「今」を週3回のペースでお届けしています。「めげない、逃げない、あまり儲けない」を合言葉に日々頑張っておりますので、応援よろしくお願いします。



ページの先頭へ戻る