JDBCのsetFetchSizeメソッドはご存知でしょうか？
通常、クエリの結果はResultSetにすべてロードされます。このため、大量のレコードを取得するようなクエリではOutOfMemoryErrorが発生してしまいます。
このような場合に有効なのがsetFetchSizeです。
たとえば、クエリ発行の前にsetFetchSize(1000)とすると、1000件ずつResultSetにとりこまれるようになり、OutOfMemoryErrorを回避することが可能です。
（もちろん、搭載メモリ量、１レコードあたりのサイズによります！）
しかし、ここに罠がありました。。。
setFetchSizeを使用しているにもかかわらず、全件がResultSetにロードされてしまう場合があるのです。
JDBCドライバごとにsetFetchSizeの挙動がかなり違っているので、はまりどころです。
今回はPostgreSQL/MySQL/MariaDBの各ドライバでの挙動について紹介していきたいと思います。
ちなみに似たような名前で setMaxRows() というメソッドがありますが、これはクエリの結果件数の上限を設定するもの（LIMIT句を指定するようなもの）で、全く違う用途なので間違わないようにしましょう。
これは地味にやってしまいがち。。。
PostgreSQLの場合
（以下は PostgreSQLドライバ バージョン 42.2.5 で動作確認しています）
PostgreSQLの場合、setFetchSize() を使用することで暗黙的にカーソルが作成され、ResultSet#next()時に適宜、作成したカーソルを使用してデータがロードされます。
ただし、トランザクション内でのみ上記挙動となります。トランザクション外の場合、とくにエラーなどなくクエリが実行されますが、全件ロードされてしまいます。
件数、搭載メモリ量によってOutOfMemoryErrorとなる可能性があります。
コード例（Spring JDBCを利用）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@Autowired private JdbcTemplate jdbcTemplate; @Transactional public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(1000); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
@Transactionalを外した場合、全件ロードとなり、OutOfMemoryErrorとなってしまいました。
そもそもですが、PostgreSQLはトランザクション外ではカーソルを定義できません。
|
1 2 |
foobar=# DECLARE cursor_foo CURSOR FOR SELECT * FROM foobar; ERROR: DECLARE CURSOR can only be used in transaction blocks |
MySQL Connector/J の場合
（以下は mysql-connectorバージョン 8.0.15 で動作確認しています）
PostgreSQLのドライバでは setFetchSize() はカーソルで実装されていましたが、MySQLのドライバでは カーソル or ストリーミングのどちらかを選択して利用可能です。
カーソルモード
JDBCの接続パラメータに useCursorFetch=true を設定することで利用可能となります。
|
1 |
jdbc:mysql://127.0.0.1:3306/foobar?useCursorFetch=true |
setFetchSize() を使用することでPostgreSQLと同様に暗黙的にカーソルを利用してデータを取得しますが、PostgreSQLとは違い、トランザクション内外を問わずカーソルが有効に動作します。いいですね！
useCursorFetch=true が設定されていない場合、とくにエラーなどなく全件がResultSetにロードされてしまいますので注意してください。
ストリーミングモード
ストリーミングモードとは、カーソルのようなDBMSの機能を使用せず、データベースサーバからのレスポンスを一度に受信しきらず、ゆるやかに受信していくことで setFetchSize() を実現するものです。
ストリーミングモードはJDBC接続パラメータの設定は不要で、setFetchSize(Integer.MIN_VALUE) とすることで利用可能です。
（JDBC接続パラメータに useCursorFetch=true を設定している場合でもこちらが優先されてストリーミングモードとなります。）
コード例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@Autowired private JdbcTemplate jdbcTemplate; @Transactional public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(Integer.MIN_VALUE); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
こちらもトランザクション内外問わず有効に動作しますが、カーソルと違っていくつか注意点があります。
１．並行してクエリを実行できない。
クエリの実行結果を受信中というステータスなので、別のクエリを実行することはできません。
新たにコネクションを開いたうえで実行する必要があります。
以下のコードでは "SELECT 1" の発行時に例外が発生します。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@Autowired private JdbcTemplate jdbcTemplate; @Transactional public void execute() throws Exception { jdbcTemplate.setFetchSize(Integer.MIN_VALUE); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { jdbcTemplate.execute("SELECT 1"); System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
|
1 2 |
Caused by: java.sql.SQLException: Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@157632c9 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.15.jar!/:8.0.15] |
２．サーバタイムアウトが存在する
MySQL/MariaDBには net_write_timeout という設定値があります。サーバが最後にレスポンスを書き込んでから net_write_timeout 秒が経過するとコネクションが切られます。
ですので、ResultSetからの読み込み中に時間のかかる処理がある場合に注意が必要です。
ただし、setFetchSize(Integer.MIN_VALUE) とした場合、net_write_timeout値が 600 (10分) に暗黙的に変更されるようなので、通常は問題ないと思います。
テストとして１万行読み込んだところで601秒間スリープしてみると例外が発生しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
@Autowired private JdbcTemplate jdbcTemplate; public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(Integer.MIN_VALUE); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { if (numRow.get() == 10000) { try { Thread.sleep(601000); } catch (InterruptedException e) { e.printStackTrace(); } } System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
|
1 2 |
java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:189) |
MariaDB Connector/J の場合
（以下は mariadb-java-clientバージョン 2.4.0 で動作確認しています）
MySQLのドライバと異なり、useCursorFetchパラメータは存在せず、ストリーミングモードのみ利用可能です。
ストリーミングモードを利用するには、setFetchSize()に任意の値を指定するだけで構いません。
（setFetchSize(Integer.MIN_VALUE)と指定する必要はありません）
コード例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@Autowired private JdbcTemplate jdbcTemplate; @Transactional public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(1000); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
注意点として、MySQLのドライバとストリーミングモードの挙動が異なります。
１．並列でクエリを実行できる。。。がしかし！
並列でクエリを発行した場合、クエリ発行前に先行のクエリを完了させる（即座に全件ロードし終える）ため、並列でクエリを実行可能です。
が、setFetchSize()の意味がないですね。
下記の例では "SELECT 1" を発行時に "SELECT * FROM foobar" の結果が全件ロードされ、OutOfMemoryErrorとなりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
@Autowired private JdbcTemplate jdbcTemplate; @Transactional public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(1000); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { jdbcTemplate.execute("SELECT 1"); System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
２．net_write_timeout設定値は変更されない
MySQLのドライバではクエリ実行前に 600 に変更されましたが、MariaDBのドライバでは変更されず、
サーバの設定に従います。サーバの設定に注意が必要です。
例えばサーバのグローバル設定が10秒の場合、10秒で接続を切られます。
テストとして１万行読み込んだところで11秒間スリープしてみると例外が発生しました。
|
1 2 3 4 5 6 7 |
MariaDB [test]> show global variables like 'net_write_timeout'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | net_write_timeout | 10 | +-------------------+-------+ 1 row in set (0.001 sec) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
@Autowired private JdbcTemplate jdbcTemplate; public void execute() throws Exception { AtomicInteger numRow = new AtomicInteger(1); jdbcTemplate.setFetchSize(1000); jdbcTemplate.query("SELECT * FROM foobar", (resultSet) -> { if(numRow.get() == 10000) { try { Thread.sleep(11000); } catch (InterruptedException e) { e.printStackTrace(); } } System.out.println(numRow.getAndIncrement() + " 行目 ID: " + resultSet.getInt(1)); }); } |
|
1 |
java.sql.SQLNonTransientConnectionException: (conn=104) Server has closed the connection. If result set contain huge amount of data, Server expects client to read off the result set relatively fast. In this case, please consider increasing net_wait_timeout session variable / processing your result set faster (check Streaming result sets documentation for more information) |
実装の差がはげしい！
いかがだったでしょうか？実際に各ドライバの挙動をみた結果、ここまで差があるとは調査前には思ってもいませんでした。
知らずに使っているとこわいですね。。
