Category Archives: DB関連

JDBCでResultSet.nextを使って逐次処理しているからといって安心しない。setFetchSizeを忘れずに!

JDBCを直接使っている人向けの話。いねえよとか言わない!おれがいる!

try (ResultSet result = statement.executeQuery();) {// このクエリは数百万件とかすごい量のデータを返すとする

とかしたとして、ブロックの中で

while (result.next()) {
    // ここで順次的に処理をする
}

とか書いて置いて、ほらちゃんとresult.nextで順次的に処理してるし、try with resourceでcloseもバッチリ!といっても、俺偉いとか思わない。executeQueryを実行した直後のヒープダンプがコレ。

ResultSetのインスタンスが60%以上取っている

まだresult.next()していない。そんな!ResultSetはデータそのものじゃなくて、接続の管理クラスみたいなやつだから、実際はnextして順次的にDBから値をもらうんだって教わった。なのにどうもメモリを食ってる。

ヒープダンプの中身を解析してみる。

rowsというVectorに全部のデータが格納されている

rowsというVectorがデカイ。

_人人人人人_
> Vector <
 ̄Y^Y^Y^Y ̄

WeakReferenceでもSoftReferenceでもないタダのVectorなので、メモリにガッツリ確保しております。そう、ResultSetはパフォーマンスのために、結果のうち数行をキャッシュするのだ!

そのキャッシュのサイズを制御するのがこのメソッド

https://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setFetchSize(int)

setFetchSize

void setFetchSize(int rows)
                  throws SQLException
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

なるほど。フェッチする行サイズを(あくまでヒントとして)指定できるらしい。デフォルトでは0、すなわち無指定状態のようだ。

じゃあ、ヒント無しの場合はどうなるのだろう。

http://grepcode.com/file/repo1.maven.org/maven2/postgresql/postgresql/8.4-702.jdbc3/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java#AbstractJdbc2ResultSet.next%28%29

row_offset += rows.size(); // We are discarding some data.
//
// 省略
//
int fetchRows = fetchSize;
if (maxRows != 0)
{
    if (fetchRows == 0 || row_offset + fetchRows > maxRows) // Fetch would exceed maxRows, limit it.
        fetchRows = maxRows - row_offset;
}

rowsが件のVectorであるので、つまり全部のデータをfetchRowsしようと試みてるってこと??

なので、大きめのクエリを流そうとしている場合は、setFetchSizeはほぼ確実に呼び出すべき。ただ、大きめにするとメモリを食うし、小さめだとパフォーマンスが不利になるのでその辺りは統計を取って最適値を模索するしかないかと。

あと、あくまで行のキャッシュであるので、一行にtext型を格納したドデカイカラムがあったりするとやばそうです。

PostgreSQLを使っていて、カラムの中にカンマ区切りデータを入れてしまった時のノウハウあれこれ

一つのカラムのなかに、カンマ区切りでデータを入れてしまったとします。そんな時に役立つテクニックをここにまとめます。

言ってみれば第一正規形ですらないテーブルデータと言えるので、いかがなものかとも思いますが、それはそれとして。))前提として、値に","は含まないこととし、エスケープとかは考えないことにします。

カンマ区切りを分離してテーブルとしてselectする(regexp_split_to_table)

select regexp_split_to_table('one,two,three', ',');

結果

regexp_split_to_table
---------------------
one
two
three
(3 行)

参考:http://blog.cloudpack.jp/2011/10/server-news-postgres-comma-separate.html

複数行(1列)のテーブルを結合してカンマ区切りにする(array_to_string)

select c1 from t1

の結果が

c1
-----
one
two
three
(3 行)

のとき、

select array_to_string(ARRAY(select c1 from t1), \',\')

結果

array_to_string
---------------
one,two,three

参考:http://postgresql.g.hatena.ne.jp/iakio/20080427/1209276667

カンマ区切りの中にある要素が含まれているかどうかを調べる(',' || column_name || ',' like '%,' || keyword || ',%')

一個目の方法でexistsとかやってもいいのですが、いかにも遅そう。そこで、この方法を使うと、インデックスこそ使われませんが程々高速にできる(はず)

select \',\' || \'hoge,fuga,piyo\' || \',\' like \'%,\' || \'hoge\' || \',%\'

結果

?column?
--------
t
(1 行)
select \',\' || \'fuga,piyo\' || \',\' like \'%,\' || \'hoge\' || \',%\'

結果

?column?
--------
f
(1 行)

PostgreSQL で LANケーブルが抜けた場合にConnectionを破棄する方法

TomcatでWebアプリを開発していてDBとの接続はコネクションプーリングを行なっています。コネクションプーリングをしているとはいえ、何らかの原因でConnectionが使い物にならない場合は、再接続をするために、Connection取得時に以下のようなSQLを投げて死活チェックを行なっています。

select 1

ところが、例えばDBサーバへのLANケーブルが抜けた場合、このSQLをいつまでも実行してしまい、結果的にConnectionPoolからの取得待ち行列がたまり、Tomcatのスレッドを消費してDBが不要なサービスにまで影響を及ぼしてしまうという問題があります。

その問題が発生しないように、クエリにタイムアウトを施したいと思い、まずは以下のようにしてみました。

Statement stmt = connection.createStatement();
stmt.setQueryTimeout(1);

ところがこの方法は上手くいきません。

Method setQueryTimeout(int) is not yet implemented.

などのように言われてしまいます。実装されていないのか?(なぜ?)それでは、タイマーを用意して横からcancelをさせることを考えました。

final Statement stmt = connection.createStatement();
Runnable canceler = new Runnable() {
    public void run() {
        try {
            stmt.cancel();
        } catch (SQLException e) {
            // I can do nothing :)
        }
    }
};
ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor();
executor.schedule(canceler, 500, TimeUnit.MILLISECONDS);

この方法は上手くいきませんでした。なにせLANケーブルが抜けているのでcancelすら到達しないようです。

それでは、逆の発想で、別スレッドでテストをさせて、メインのスレッドはjoinをして、制限時間を過ぎればスルーして終了する実装にしてみましょう。

final Statement stmt = connection.createStatement();
class Checker implements Runnable {
    boolean succeeded = false;
    public void run() {
        try {
            try {
                stmt.execute("SELECT 1");
                succeeded = true;
            } finally {
                stmt.close();
            }
        } catch (SQLException e) {
            // I can do nothing :)
        }
    }
}
Checker checker = new Checker();
Thread thread = new Thread(checker);
thread.start();
try {
    thread.join(1000);
} catch (InterruptedException e) {
   // i can do nothing :)
}
return !checker.succeeded;

この方法でとりあえず何とかしましたが、これでもなんだかcheckerのスレッドを消費しそうなのと毎回スレッド生成するので遅くなりそうですね……。背に腹は変えられないということか?