DB SELECT COUNT JAVA実行でout of memoryになってしまう原因について

Question

下記のようなテーブルの1000万件のデータをカウントするプログラムをjavaで作成したのですが、resultsetのところでoutofmemoryが発生してしまいました。

住所 テーブル　（テーブル名・カラム名は仮）
名前　　　id 　　住所
佐藤 1111　　　東京
佐藤 2222　　　東京
田中 3333　　　東京
清水 4444　　　東京
山田　　5555　　　埼玉

SELECT 住所,COUNT(DISTINCT 名前) AS カウントFROM 住所テーブル　GROUP BY 住所

試したこととして、fetchsizeを指定（100~10000）したり、postgreなのでautocommitをfalseにしなくてはいけない等を試しましたが、解消されませんでした。

おそらく原因はSELECT結果を格納するメモリが足りないのではなくて、SELECT処理自体がメモリを使うのかなと思っているのですが、
もしDBのデータが100億件とか増えた場合、SELECT処理自体のメモリ使用量が変わるなどはあるのでしょうか？
（これだとDB使う意味が薄れるのですが....）

fetchsize指定の仕方が間違っているとかは調査していていつか気づくと思っているのですが、根本的にSELECT処理自体のメモリ使用量が増える場合全く気づけないのでこの場で質問させていください

Comments

[m.ts10806]

Javaからの実行ではなくDBに直に実行して、実行計画もとってみましたか？

[tututu_0507]

実行計画はoutofmemoryの調査に関係ないと思っていたので取っていませんでした。
実行計画をどのように使えばoutofmemoryの調査になりますか？

[sousuke]

resultset周りのコードも提示されたほうがよろしいかと

[Orlofsky]

住所テーブルの表示を全角空白を入れるのは止めて、CREATE TABLEとINSERTを https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正しては？

[tututu_0507]

@Orlofsky
申し訳ありませんテーブル名・カラム名は質問用に書き換えていたものです。明記しておくべきでした。

[neko_the_shadow]

ConnectionのautoCommitをFalseにした後、fetchsizeを適切に指定してもOut of Memoryが解消しないという認識で正しいでしょうか?

[tututu_0507]

@neko_the_shadow
はい、そうなります。

Answer 1

out of memoryはjavaのヒープサイズが不足する事によるものです。

もしDBのデータが100億件とか増えた場合、SELECT処理自体のメモリ使用量が変わるなどはあるのでしょうか？
（これだとDB使う意味が薄れるのですが....）

メモリーは有限であるのに、サイズを考慮せずに取得する方が問題です。
オンラインである場合は、件数制限を設けるのが普通ですし、バッチなどの場合は分割コミットなどで対応します。

取り敢えずは、select文 に limitを付けて、状況を確認してみて下さい。

追記

fetchsizeを指定している場合でも元のDBの件数が増えた場合はメモリ使用量は増えるものなのでしょうか？

該当しているかどうかは分かりませんが、以下参考
JDBC setFetchSize() ではまった話

PostgreSQLの場合、setFetchSize() を使用することで暗黙的にカーソルが作成され、ResultSet#next()時に適宜、作成したカーソルを使用してデータがロードされます。

ただし、トランザクション内でのみ上記挙動となります。トランザクション外の場合、とくにエラーなどなくクエリが実行されますが、全件ロードされてしまいます。
件数、搭載メモリ量によってOutOfMemoryErrorとなる可能性があります。

Comments

[tututu_0507]

ご回答ありがとうございます。
fetchsizeを10000などに設定した場合、10000万件取得→メモリ格納→処理→次の10000万件目取得→メモリ格納→処理　というプロセスなので10000万件分のデータを格納する分しかメモリを使わないと思っていたのですが、メモリサイズの考慮はfetchsizeだけでは足りないのでしょうか？

limitというキーワードをいただけたので調べてみます。
ありがとうございます。

[sazi]

そもそもヒープ領域のサイズが足りていないんでしょう。

[tututu_0507]

そもそものヒープ領域が足りないという原因であれば問題ないのですが、
もしヒープ領域を増やしたとしてもDBの件数が1000万件→10億件などになった場合にoutofmemoryになるのは困るので、仕組みを知りたいなと思いました。
fetchsizeを指定している場合でも元のDBの件数が増えた場合はメモリ使用量は増えるものなのでしょうか？

[tututu_0507]

sazi
追記ありがとうございます。そもそもfetchsizeが効いてない可能性が高いですね...

メソッドの上に@Transactionalつけてみましたがうまくいきませんでした。
fetchsizeがいくつで動いてるか確認する方法ってありませんでしょうか？

[tututu_0507]

原因が分かりました。
ステートメント作成時ResultSet.TYPE_FORWARD_ONLYを指定する必要があったようです。

皆さんにお答えいただいたおかげでfetchsizeが効いていないことがわかり根気よく調査できました。ありがとうございます。

[sazi]

fetchsizeではなく、SQLレベルでorder by と limitの組み合わせで行う方が、チューニングの観点では効率的だと思います。