Java + PostgreSQL で BLOB を扱う その 3

◆ テーブル定義

PostgreSQL の OID 型を使う場合は、バイナリ型というより Oracle や DB2 の BLOB 型と同じように考えたほうが良いのだろう。とりあえずテスト用のテーブルを作ってみよう。

stmt = con.prepareStatement(     "CREATE TABLE DUMMY(INTEGER ID PRIMARY KEY, CONTENT OID)"); stmt.executeUpdate(); stmt.close();

単純明快。見ての通りデータ型に OID を指定するだけである。もちろん通常のテーブルと同じように他のカラムが混在していても問題はない。

◆ レコード作成

次に新規のレコードを作成したいのだが、Java で LOB を扱う互換性の高い手順は以下の 2 ステップが必要だ。

1. ID だけ割り当てて空の LOB を作成する
2. 作成した空の LOB を取り出して更新する

PostgreSQL を始めとするいくつかのデータベースは BLOB 型カラムを単なる整数としか扱っていないため、直接バイナリ値を挿入するとエラーになってしまうのだ。もちろん他のデータベースや PostgreSQL の将来のバージョンで BLOB/OID 型カラムに直接 setBinaryStream() 出来るなるならそのほうが得策なのは言うまでもない。

こういった INSERT & SELECT を行う場合はちょっと注意。プログラム側であらかじめユニークキーを持っておく必要があるので SERIAL 型に頼った設計はできないし、DB2 とか検索キーにインデックス張り忘れてるとデッドロックの原因になったりもする。いずれにせよあまり良い設計ではない事は認識しておく必要がある。

そんなわけで、まずは実体が空のレコード作成してみよう。

stmt = con.prepareStatement(     "INSERT INTO DUMMY(ID,CONTENT) VALUES(?,lo_create(0))"); stmt.setInt(1, 0); stmt.executeUpdate(); stmt.close();

上記では lo_create() 関数を使って CONTENT に新規の OID を割り当てている。ドキュメントの関数と演算子ではなくサーバ側の関数に載っているので見落としがちなのだが、どうやら C で LOB を扱うための関数と同等の関数が SQL にも用意されているようだ (なので詳細はそちらを参照)。

lo_create() は OID 型カラムのデフォルト値にも使用できるので、NOT NULL 制約を付けたい場合はそちらが良いだろう。

7 以前の PostgreSQL では lo_create() が定義されていないので、代わりに lo_creat() を使う必要がある (Unix の creat() から来ていると思われるが紛らわしい名前だ…)。C のチュートリアルを見る限り、値は INV_READ と INV_WRITE のビット論理和で良いとの事。PostgreSQL 本体ソースの include/libpq/libpq-fs.h を参照すると:

#define INV_WRITE  0x00020000 #define INV_READ   0x00040000

と定義されているので、これは lo_creat(393216) と記述すれば良いだろう。もちろん 8 以降でも下位互換性を持たせたい場合にはこちらを使用する必要がある。

◆ BLOBの更新

さて、まだ LOB の実体は空っぽである。LOB に値を設定するには、先ほど挿入したレコードを検索して java.sql.Blob を参照し、setBinaryStream() で出力ストリームを取り出す（何て違和感のあるネーミングだろう…) 。

stmt = con.prepareStatement(     "SELECT CONTENT FROM DUMMY WHERE ID=?"); stmt.setInt(1, 0); rs = stmt.executeQuery(); if(rs.next()){     Blob content = rs.getBlob("CONTENT");     InputStream in = /* ファイル入力ストリームなど */;     OutputStream out = content.setBinaryStream(1);     byte[] buffer = new byte[4096];     while(true){         int len = in.read(buffer);         if(len < 0) break;         out.write(buffer, 0, len);     }     out.close();     in.close(); } rs.close(); stmt.close();

ソースを見てみると PostgreSQL の Blob インスタンスは LargeObject API のラッパーになっているようだ。取り出した出力ストリームにバイナリを出力してやればそれが LOB の実体となって保存される。

既存の内容を入れ替える場合もこの方法で行うことができる。LOB を 0 バイトにしたければ、何も出力せずに close() してやれば良い。だが PostgreSQL の内部では 2kB のページ単位で管理しているので、あまり更新が頻発するようなら注意が必要だろう。

ところで Blob#setBinaryStream() のパラメータは更新対象バイナリブロックの先頭オフセットを示すものなのだが、この値の JDBC 仕様って 1 起源なんだっけ? 先頭から更新する気で 0 を指定したら例外になってしまった。他のデータベースはどうなのかな? 

◆ BLOBの参照

さて、次は BLOB に設定されている内容を取り出してみよう。更新時と同様に該当レコードを検索し、Blob インスタンスから InputStream を参照する。

stmt = con.prepareStatement(     "SELECT CONTENT FROM DUMMY WHERE ID=?"); stmt.setInt(1, 0); rs = stmt.executeQuery(); while(rs.next()){     Blob content = rs.getBlob("CONTENT");     InputStream in = content.getBinaryStream();     OutputStream out = /* ファイル出力ストリームなど */;     byte[] buffer = new byte[4096];     while(true){         int len = in.read(buffer);         if(len < 0) break;         out.write(buffer, 0, len);     }     in.close();     out.close();    } rs.close(); stmt.close();

見ての通り、特に言及することもない率直なコードだ。

◆ BLOBの削除

一つ、問題が残っている。lo_create() で作成した LOB は、レコードの DELETE やテーブルの DROP で自動的に消えないのだ。LOB の実体は OID を lo_unlink() に指定して明示的に削除してやる必要がある。

stmt = con.prepareStatement("SELECT lo_unlink(?)"); stmt.setInt(1, oid); stmt.executeQuery().close(); stmt.close();

これは事前に OID を数値として保持している場合。サブクエリーで該当オブジェクトの OID を引っ張ってきても大丈夫なようである。

stmt = con.prepareStatement(     "SELECT lo_unlink((SELECT CONTENT FROM DUMMY WHERE ID=?))"); stmt.setInt(1, 0); stmt.executeQuery().close(); stmt.close();

こんな RDBMS 依存のオマジナイ SQL 投げるのは嫌だ、と感じる人は多分私と気が合う。DELETE 文のサブクエリーに仕込んで 1 発で済ますという手がある。lo_unlink() は成功時に 1 を返すので以下のように書けるだろう。

DELETE FROM DUMMY WHERE ID=0 AND (SELECT lo_unlink(     (SELECT CONTENT FROM DUMMY WHERE ID=0) ))=1

ただし該当する LOB が存在しなければレコードも削除されないので注意。LOB が先に消えてる可能性がある場合は IN でも何でも使ってください。

一つずつ lo_unlink() するのは面倒だ、バッチでまとめて消しこみたい、という人はシステムカタログの pg_largeobject を参照。LOB の実体がページ分割されたレコードとして保存されているので、別のテーブルと結合させて削除対象の OID を見つけることが出来る (一応、手作業でシステムカタログを更新するなというお達しがあるので直接削除は NG)。例えば全ての LOB を消したい場合は以下のような SQL を発行すれば良いだろう。

SELECT COUNT(lo_unlink(loid))     FROM pg_largeobject GROUP BY loid

これは SELECT 文だが、executeUpdate() で実行したければ前述のように適当な更新系 SQL のサブクエリーにしてやれば良い。やりようはいろいろはありそうだ。

ここまでのところ JDBC の標準 API のみを使用して何とかなった。まぁ参照と作成、更新、削除が出来ればとりあえずの用途は大丈夫そうかな。

◆ パフォーマンスの評価

java.sql.Blob 型を使用した場合のパフォーマンスについて調べてみよう。まずは、今まで BYTEA 型で問題なったヒープの使用についてである。

左のグラフはプロファイラで計測した Java VM のヒープ消費量である。このページのサンプルコードを用いて、20MB のデータの更新、参照をセットで 1 回行った (これは BYTEA 型だと確実に OutOfMemoryError が発生するサイズである)。

最大ヒープサイズは 4.4MB から拡張していないので、巨大なメモリブロックの割り当ては行われていないようだ。ただ何かしらのヒープの割り当てと開放が繰り返されることから、残念ながら内部は完全なストリーム処理ではないようだ。

続いて処理にかかる時間を計測してみよう (マシンスペックは後述、数値は参考、相対的に見るべし)。まずは同一筐体 (Win) 上で上記サンプルコードを用いて BLOB を扱った場合である。データサイズは 20MB、それぞれ 3 回計測した平均値。

ローカル処理

BLOB更新	28,695 [msec]
BLOB参照	2,667 [msec]
BLOB削除	4,706 [msec]
ファイル出力	3,511 [msec]

最後の 「ファイル出力」 とは、データベースのインスタンスと同一パーティションに直接 20MB のデータを書き出した時間である。とりわけ更新時のオーバーヘッドが目立つようだが、1MB あたり 1.4 秒が気になるかどうかは用途しだいと言ったところか (マシンスペックや状況にも依存するが)。

他、BLOB の削除 (lo_unlink() の実行) が意外に遅い。なんだかやたらとディスクアクセスをしているようだ。このテストはラージオブジェクトのテーブル (psql の ¥lo_list で表示されるもの) に一つも入っていない状態で行ったのだが、どうもこれが大きくなるとやたら遅くなるような感じがする。

続いてネットワーク越しの処理。クライアント(Win) - サーバ(Linux) 間を無線 LAN 接続。データサイズは 20MB の 3 回計測平均。

リモート処理

BLOB更新	50,125 [msec]
BLOB参照	22,652 [msec]
BLOB削除	454 [msec]
ファイル転送	46,169 [msec]

最後の計測値は Windows ファイル共有で同サイズのデータをサーバに転送した結果だ。これをスループットとして見れば、同サイズのファイルをファイル共有で送るより 10% 程度のオーバーヘッドがかかるようだ (データサイズなどにも依存するだろう)。しかし参照の方がエラく速いのだが、更新直後のためヒープにでも乗っかっていたのだろうか?

心配していた削除に関しては、こちらはやたらと速いな。ローカルで遅かったのは単に実行環境のせいだったのだろうか?

ちなみに、更新に用いたバイナリと参照できたバイナリが完全に一致することは確認済みだ (バイナリの内容は乱数で作成)。

データ量に対する推移も調べてみよう。どうも Win 機は Norton やら Google デスクトップやらのせいで結果が怪しいので、今度は Linux のサーバ上で 1MB のデータを 100 個、前述と同様のコードで更新 (作成)・参照・削除を行ってみた (右のグラフ)。

更新・参照はデータ量が増えても目に見える変化は無いようだ。心配していた削除に変なばらつきが見られるのは、どうも突発的に最適化か何かをやってるようだった。あまり頻繁に削除が繰り返されるなら要注意といったところだろうか。

これはせいぜい 100MB 程度のデータ量なので、1GB、10GB と増加した場合にどうなるかは分からない。まぁ、商用データベースの抜き差しならぬ速さには及ばないかもしれないが、総じて常識的なレベルにあると言えるのではないだろうか。少なくとも同サイズの BYTEA 型をメモリに展開する動きよりは遥かにマシである。

◆ 結論

• Java + PostgreSQL での getBinaryStream()/setBinaryStream() は getBytes()/setBytes() と等価と思え。巨大データには絶対使うべからず。
• より大きなバイナリは OID 型を java.sql.Blob で扱え。

ちなみに今回使ったのは全て 8.1 の JDBC ドライバ。7.4 ドライバは使っていない。将来のバージョンではまたソースを確認する必要があるだろう。

◇ 諸々

最後に、いろいろ試していて気付いた点や懸念点など。

1. 自動コミットを false にしておくのは必須のようだ。まぁ当然と言えば当然か。
2. BYTEA 型カラムに getBlob() はできない。逆に OID 型カラムには setBinaryStream(), setBytes() はできない。
3. OID は PostgreSQL 管理のシーケンス。initdb で作成するインスタンスごとに管理される。32 ビット幅で、一巡したら重複が発生する。
4. OID に割り当てられる数値は WITH OIDS (デフォルト) で付く数値と同じシーケンスが使われているようだ。消費を抑えたければ全テーブルに WITHOUT OIDS した方が良さそう (こっちをデフォルトにして欲しい)。 
5. テーブルスペースを変更したい場合は ALTER TABLE pg_largeobject SET TABLESPACE XXX で良いのだろうか?
6. クライアントとデータベースが同一マシン上なら lo_import() や lo_export() 関数を用いてファイル経由でデータの受け渡しも行えそうな感じ。
7. なんか大きいデータは BYTEA 型に setBinaryStream() で、と紹介してるサイトがやたら多いのな…

◆ おまけ

LOB の参照が複数のテーブルに分散しており、どこにも所属してない LOB をまとめて消したい場合は一時テーブルを使うと楽。

BEGIN;
CREATE TEMPORARY TABLE TMP_RMLOB(LOID OID NOT NULL)
  WITHOUT OIDS ON COMMIT DROP;
INSERT INTO TMP_RMLOB(LOID) SELECT LOID FROM pg_largeobject
  GROUP BY LOID;
DELETE FROM TMP_RMLOB WHERE LOID IN (SELECT LOB_A FROM TBL_A);
DELETE FROM TMP_RMLOB WHERE LOID IN (SELECT LOB_B FROM TBL_B);
...
SELECT COUNT(*) FROM (SELECT LO_UNLINK(LOID) FROM TMP_RMLOB
  GROUP BY LOID) AS FOO;
COMMIT;

◇ 検証環境

JDK 1.4 + WinXP + IA32 　java version "1.4.2_09"      Java™ 2 Runtime Environment, Standard Edition (build 1.4.2_09-b05)      Java HotSpot™ Client VM (build 1.4.2_09-b05, mixed mode)     PostgreSQL 8.1.3      i686-pc-mingw32,      compiled by GCC gcc.exe (GCC) 3.4.4 (mingw special) 　Microsoft Windows XP Professional SP2  　IBM ThinkPad X31 2672LJ7 　　Intel Pentium M Processor 1.7GHz 　　1GM PC-2700 DDR SDRAM, 80GB 4,200rpm 9.5mm HD

Linux+ IA32 　Fecora Core 3 　　2.6.12-1.1381_FC3    PostgreSQL 7.4.11      i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC)      3.4.4 20050721 (Red Hat 3.4.4-2) 　IBM ThinkCentre A50 NN85T55 　　Intel® Celeron®  D Processor 335 2.80GHz 　　640MB PC-2700 DDR SDRAM 　　hda - 40GB 7,200rpm Barracuda ST340014A Ultra IDE/ATA100 　　hdd - 120GB 7,200rpm Maxtor 6Y120L0 Ultra IDE/ATA133 　　sda - 160GB 7,200rpm Maxtor OneTouch USB/2.0,FireWire 　　sdb - 250GB 7,200rpm Maxtor OneTouch USB/2.0,FireWire