PostgreSQL 9.6 の postgres_fdw について検証してみた

こんにちは。サイオステクノロジー OSS サポート担当 W です。

PostgreSQL 9.6 から postgres_fdw がリモートでの結合、ソート、UPDATE、DELETE に対応しました。
https://www.postgresql.jp/document/9.6/html/release-9-6.html

とあるようにリモートの postgres_fdw が JOIN, SORT, UPDATE, DELETE に対応しています。今回は PostgreSQL 9.6.1 の postgres_fdw を pgbench で試用してみます。

備考:postgres_fdw はリモートの PostgreSQL データベースのテーブルをローカルテーブルと同じように扱える拡張です。

PostgreSQL 9.6 と postgres_fdw のインストール

今回はソースコードから PostgreSQL 9.6 を Fedora 25 にインストールします。RHEL などでもほぼ同様の手順でインストール可能です。ビルドに必要なコンパイラなどのツール及びライブラリは既にインストール済みであるとします。

ダウンロード URL

https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.bz2

PostgreSQL のビルドとインストール手順

postgres_fdw のビルドとインストール手順

これでインストールは完了です。

テスト用の PostgreSQL データベース作成

新しくインストールした PostgreSQL 9.6 のコマンドが使えるように、/usr/local/pgsql-9.6/bin にパスを通します。

データベースファイルを作成する適当なディレクトリに移動します。ディレクトリはどこでも構いません。

local と remote ディレクトリがカレントディレクトリに作成されます。データベースが待機するデフォルトのポートは 5432 です。他の PostgreSQL と同じだと起動できないので local, remote 両方とも別のポートに変更します。筆者は

で待機させるように

local/postgresql.conf

remote/postgresql.conf

と編集しました。

これで 2つの PostgreSQL を起動できるようになりました。pg_ctl コマンドで起動します。

この手順でインストールすると現在のユーザが PostgreSQL のスーパーユーザとして登録されますが、スーパーユーザ用のデータベースは作成されません。必要なデータベースを local/remote に作成します。

local

remote

remote データベースで pgbench テーブルを初期化する

以下のようなコマンドで pgbench テーブルを初期化します。

local データベースに remote の pgbench テーブルを定義する

psql コマンドを使って local データベースに接続します。

リモートのデータベーステーブルをローカルで利用できるようにするには以下の手順を実行します。

https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

今回は 4. の手順でスキーマごと一度にインポートします。

psql のコマンドプロンプトから postgres_fdw 拡張をインストールします。

エラーとなる場合は postgres_fdw 拡張がインストールされていません。この場合、拡張のインストール手順を確認してください。

リモートサーバを定義します。

username には現在のログイン名(ユーザ名)を利用します。ここの手順ではパスワードの設定は必要ありませんが、実際の運用時の設定ではパスワードの設定も必要でしょう。

リモートユーザのマッピングを定義します。

先ほどと同様に username には現在のログイン名(ユーザ名)を利用します。パスワードが必要な場合はパスワードも設定してください。

remote のスキーマをインポートします。

remote の public スキーマを local の public スキーマにインポートできました。エラーが起きる場合は、local の public スキーマに既に同じ名前のテーブルがある、パスワードを利用している場合は間違っているなどが考えられます。

正しくインポートされると remote のテーブルが local から参照できるようになっています。

ローカルテーブルの場合、Type に “table” と表示されるところが “foreign table” と表示され、remote のテーブルだと判ります。

pgbench のベンチマーク

このベンチマークに利用しているシステムは以下の通りです。postgresql.conf のチューニングなどはありません。

まずテーブル本体を定義している remote データベースに直接 pgbench を実行したベンチマークを行います。

次に local データベースの外部テーブルに対して pgbench を実行します。

ベンチマーク結果の考察

外部テーブルを pgbench でベンチマークすると明らかな違いがあります。

  • 外部テーブルは VACUUM できないためエラーが発生している
  • pgbench_history がテーブルでないとエラーが発生している
  • SERIALIZABLE トランザクション分離レベルのエラーが発生している
  • 性能が 1/5 になっている

まずリモートテーブルに対して VACUUM は行えないのでこのエラーは正常です。

pgbench_history は外部テーブルとして定義されていない、というエラーはなぜ発生しているのかエラーメッセージからだけでは判りません。SERIALIZABLE トランザクション分離レベルのエラーはエラーメッセージ通りのエラーです。しかし、これは直接 pgbench を実行した場合にはエラーになりません。性能が大幅に低下しているのは仕組み上仕方ありません。

local データベースのログを確認してみたところ、記録されたエラーは SERIALIZABLE トランザクション分離レベルのエラーのみで他のエラーはありませんでした。念のため postgresql.conf で log_statement=’all’ に設定して確認したところ、クエリは受信され実行されていました。

SERIALIZABLE トランザクション分離レベルのエラーがリモートテーブルのみで発生する原因を調べる為、postgres_fdw のソースコードを参照したところ、以下のようなコードがありました。

IsolationIsSerializable() の実態はトランザクション分離レベルの値をチェックする PostgreSQL 本体のマクロでした。しかし、log_statement=’all’ にしても

がログに記録されておらず、ログに記録されていたのは

でした。

pgbench の実行方法をトランザクション数を指定する方法に変えてみます。

注目すべき箇所は

です。クエリエラーはこれほど多く記録されていませんが、実際に実行できたとするトランザクションは大幅に少ない値でした。この理由はまとめに記載します。

まとめ

postgres_fdw はトランザクションがない単純なクエリでは問題なく動作しますが、トランザクションを利用すると「ローカルテーブルと同様に利用できる」とまでは言えません。先に紹介した postgres_fdw のコメントにあるように

リモートテーブルに対するクエリは常に REPEATABLE READ になり、READ COMMITTED 分離レベルは使えない、と記載されています。リモートテーブルの場合、クエリに失敗するのは REPEATABLE READ と READ COMMITTED 分離レベルの違いが原因です。

参考:13.2. トランザクションの分離

http://www.postgresql.jp/document/9.6/html/transaction-iso.html

トランザクションエラーでロールバックしたトランザクションはクエリエラーとして記録されないので、PostgreSQL のログを見ただけでは判らなかったのです。

REPEATABLE READ では「反復不能読み取り」でトランザクションが失敗する可能性があります。ローカルと同じ(READ COMMITTEDの)ようには動作しない点に十分注意する必要があります。これは重要な違いですが、注意してマニュアルを読まないと見落としがちです。

https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

トランザクション利用時には十分注意が必要です。「ローカルと同じ」と過信せずに使えば postgres_fdw はとても便利な機能です。くれぐれも外部テーブルは常に REPEATABLE READ 分離レベル(少なくとも上記コメントの問題が解決するまでは)である点に十分注意して使いましょう。

>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

1人がこの投稿は役に立ったと言っています。

コメント投稿

メールアドレスは表示されません。


*