データベースに存在する ２0 万件のデータ（350MB 弱）を取得して、 CSV ファイルでダウンロードする方法の技術検証。
取得したデータを List とかに一旦全て格納してたら、ヒープメモリのサイズによっては簡単に OutOfMemory が発生する。
HttpServletResponse#getWriter() で取得した Writer にどんどん出力すれば OutOfMemory の発生は防げる気がするけど、本当にうまくいくのか検証した。
環境
OS
Windows7 64bit
サーバー
Apache Tomcat 7.0.32
ヒープ・サイズ
512MB
Java
1.7.0_25
データベース
- MySQL 5.5.28
- Oracle 11.2.0.1.0
テーブル
MySQL
TEST_TABLE
CREATE TABLE `TEST_TABLE` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`VALUE_1` varchar(256) DEFAULT NULL,
`VALUE_2` varchar(256) DEFAULT NULL,
`VALUE_3` varchar(256) DEFAULT NULL,
`VALUE_4` varchar(256) DEFAULT NULL,
`VALUE_5` varchar(256) DEFAULT NULL,
`VALUE_6` varchar(256) DEFAULT NULL,
`VALUE_7` varchar(256) DEFAULT NULL,
`VALUE_8` varchar(256) DEFAULT NULL,
`VALUE_9` varchar(256) DEFAULT NULL,
`VALUE_10` varchar(256) DEFAULT NULL,
`VALUE_11` varchar(256) DEFAULT NULL,
`VALUE_12` varchar(256) DEFAULT NULL,
`VALUE_13` varchar(256) DEFAULT NULL,
`VALUE_14` varchar(256) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Oracle
TEST_TABLE
CREATE TABLE "TEST_TABLE" (
"ID" NUMBER(20,0),
"VALUE_1" VARCHAR2(256 BYTE),
"VALUE_2" VARCHAR2(256 BYTE),
"VALUE_3" VARCHAR2(256 BYTE),
"VALUE_4" VARCHAR2(256 BYTE),
"VALUE_5" VARCHAR2(256 BYTE),
"VALUE_6" VARCHAR2(256 BYTE),
"VALUE_7" VARCHAR2(256 BYTE),
"VALUE_8" VARCHAR2(256 BYTE),
"VALUE_9" VARCHAR2(256 BYTE),
"VALUE_10" VARCHAR2(256 BYTE),
"VALUE_11" VARCHAR2(256 BYTE),
"VALUE_12" VARCHAR2(256 BYTE),
"VALUE_13" VARCHAR2(256 BYTE),
"VALUE_14" VARCHAR2(256 BYTE)
);
ALTER TABLE "TEST_TABLE" ADD PRIMARY KEY ("ID");
※各カラムにランダムな文字列を設定したレコードを 20 万件生成して登録。
結論
確かに、 HttpServletResponse#getWriter() で取得した PrintWriter に順次出力していけば、 メモリの消費は抑えられる 。
しかし、使ってる DBMS （というか JDBC ドライバの実装）によっては、 それだけでは OutOfMemory を回避できない可能性がある 。
今回試した DBMS の場合、MySQL だとアウトだが、 Oracle ならセーフとなる。
なぜ MySQL はアウト？
MySQL の JDBC ドライバは、デフォルトだと 全ての検索結果をメモリに格納する らしく、 PrintWriter に順次書き込みをしてもメモリ消費は抑えられない。
MySQL を使う場合は、以下の方法でフェッチサイズを変える必要がある。
MySQLでフェッチサイズを「検索結果全部」じゃなくす方法
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
公式リファレンスに書いてあった。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.2 JDBC API 実装についての注記
デフォルトにより、ResultSets は 完全に摘出され、メモリに保存されます 。
（中略）
要求されるメモリのために JVM に十分なスペースを割り振れない場合は、ドライバに結果を 一行ごとにストリームし戻す よう指示することができます。
Oracle は？
Oracle の JDBC ドライバは、デフォルトのフェッチサイズが 10 になっているらしく、大量データを検索してもメモリ上は 10 件ずつしか読み込まれない。
当然、検索結果の件数が多くなれば、その分 DB サーバとのアクセス回数も多くなる。
DB とのやり取りを減らしたい場合は、 Statement#setFetchSize(int) メソッドでフェッチサイズを調整すればいい。
つまり
大量データを DB から検索してきてダウンロードさせる場合は、
- 検索結果は
HttpServletResponseから取得したPrintWriterに順次出力する - 使用する DBMS が提供してる JDBC ドライバの実装のフェッチサイズを確認し、場合によってはフェッチサイズを調整する
必要がある。
動作検証時のメモリ使用量
以下２つのパターンで、処理中のメモリ使用量を確認した。
※具体的な実装は後述。
bulk
ResultSet から一旦検索結果を全て取得し List に詰め、その後で PrintWriter に書き出す。
sequential
ResultSet から１行読むごとに PrintWriter に書き出す。
MySQL の場合
bulk
MySQL-bulk
bulk start
フェッチサイズ：0
[bulk クエリ実行前] 合計：491.50MB, 使用：119.23MB, 空き：372.27MB
[bulk クエリ実行後] 合計：491.50MB, 使用：421.16MB, 空き：70.34MB
[bulk 10000] 合計：491.50MB, 使用：457.30MB, 空き：34.20MB
Exception in thread "http-bio-8080-exec-3" java.lang.OutOfMemoryError: GC overhead limit exceeded
（後略）
クエリ実行前後でメモリが一気に消費されているのがわかる。
その後、更に検索結果を List に詰めようとしてソッコーで OutOfMemory が発生した。
※ [bulk 10000] の 10000 は ResultSet からレコードの情報を取得して処理した件数。
sequential
MySQL-sequential
sequential start
フェッチサイズ：0
[sequential クエリ実行前] 合計：491.50MB, 使用：119.33MB, 空き：372.17MB
[sequential クエリ実行後] 合計：491.50MB, 使用：421.06MB, 空き：70.44MB
[sequential 10000] 合計：491.50MB, 使用：470.36MB, 空き：21.14MB
[sequential 20000] 合計：491.50MB, 使用：467.61MB, 空き：23.89MB
[sequential 30000] 合計：491.50MB, 使用：464.27MB, 空き：27.23MB
[sequential 40000] 合計：491.50MB, 使用：462.40MB, 空き：29.10MB
[sequential 50000] 合計：491.50MB, 使用：459.33MB, 空き：32.17MB
[sequential 60000] 合計：491.50MB, 使用：456.25MB, 空き：35.25MB
[sequential 70000] 合計：491.50MB, 使用：453.88MB, 空き：37.62MB
[sequential 80000] 合計：491.50MB, 使用：452.12MB, 空き：39.38MB
[sequential 90000] 合計：491.50MB, 使用：449.05MB, 空き：42.45MB
[sequential 100000] 合計：491.50MB, 使用：446.00MB, 空き：45.50MB
[sequential 110000] 合計：491.50MB, 使用：442.92MB, 空き：48.58MB
[sequential 120000] 合計：491.50MB, 使用：439.84MB, 空き：51.66MB
[sequential 130000] 合計：491.50MB, 使用：437.81MB, 空き：53.69MB
[sequential 140000] 合計：491.50MB, 使用：434.74MB, 空き：56.76MB
[sequential 150000] 合計：491.50MB, 使用：431.67MB, 空き：59.83MB
[sequential 160000] 合計：491.50MB, 使用：428.60MB, 空き：62.90MB
[sequential 170000] 合計：491.50MB, 使用：425.53MB, 空き：65.97MB
[sequential 180000] 合計：491.50MB, 使用：422.46MB, 空き：69.04MB
[sequential 190000] 合計：491.50MB, 使用：470.34MB, 空き：21.16MB
[sequential 200000] 合計：491.50MB, 使用：467.49MB, 空き：24.01MB
sequential end
一応、１リクエストならダウンロードが完了した。
しかし、 ResultSet を操作している間は検索結果がメモリ上に確保されているため、メモリの空きが少ない状態が続いている。
この状態で別のリクエストが届くと、簡単に OutOfMemory が発生してしまう。
Oracle の場合
bulk
Oracle-bulk
bulk start
フェッチサイズ：10
[bulk クエリ実行前] 合計：491.50MB, 使用：126.92MB, 空き：364.58MB
[bulk クエリ実行後] 合計：491.50MB, 使用：129.00MB, 空き：362.50MB
[bulk 10000] 合計：491.50MB, 使用：69.77MB, 空き：421.73MB
[bulk 20000] 合計：491.50MB, 使用：125.68MB, 空き：365.82MB
[bulk 30000] 合計：491.50MB, 使用：183.33MB, 空き：308.17MB
[bulk 40000] 合計：491.50MB, 使用：239.25MB, 空き：252.25MB
[bulk 50000] 合計：455.50MB, 使用：209.22MB, 空き：246.28MB
[bulk 60000] 合計：455.50MB, 使用：276.34MB, 空き：179.16MB
[bulk 70000] 合計：455.50MB, 使用：301.11MB, 空き：154.39MB
[bulk 80000] 合計：455.50MB, 使用：290.85MB, 空き：164.65MB
[bulk 90000] 合計：455.50MB, 使用：354.81MB, 空き：100.69MB
[bulk 100000] 合計：455.50MB, 使用：375.39MB, 空き：80.11MB
[bulk 110000] 合計：455.50MB, 使用：396.53MB, 空き：58.97MB
Exception in thread "http-bio-8080-exec-3" java.lang.OutOfMemoryError: GC overhead limit exceeded
（後略）
クエリ実行前後では、デフォルトの MySQL ほどは大きくメモリを消費していない。
しかし、その後の List に詰める処理でどんどんがメモリ消費されていき、最後にはやはり OutOfMemory が発生した。
sequential
Oracle-sequential
sequential start
フェッチサイズ：10
[sequential クエリ実行前] 合計：491.50MB, 使用：126.93MB, 空き：364.57MB
[sequential クエリ実行後] 合計：491.50MB, 使用：129.00MB, 空き：362.50MB
[sequential 10000] 合計：491.50MB, 使用：37.15MB, 空き：454.35MB
[sequential 20000] 合計：491.50MB, 使用：59.67MB, 空き：431.83MB
[sequential 30000] 合計：491.50MB, 使用：81.81MB, 空き：409.69MB
[sequential 40000] 合計：481.00MB, 使用：106.66MB, 空き：374.34MB
[sequential 50000] 合計：491.00MB, 使用：130.32MB, 空き：360.68MB
[sequential 60000] 合計：488.50MB, 使用：28.64MB, 空き：459.86MB
[sequential 70000] 合計：489.00MB, 使用：55.97MB, 空き：433.03MB
[sequential 80000] 合計：489.00MB, 使用：83.55MB, 空き：405.45MB
[sequential 90000] 合計：489.00MB, 使用：110.83MB, 空き：378.17MB
[sequential 100000] 合計：490.00MB, 使用：137.09MB, 空き：352.91MB
[sequential 110000] 合計：491.00MB, 使用：39.65MB, 空き：451.35MB
[sequential 120000] 合計：490.50MB, 使用：66.05MB, 空き：424.45MB
[sequential 130000] 合計：493.00MB, 使用：90.34MB, 空き：402.66MB
[sequential 140000] 合計：492.00MB, 使用：111.29MB, 空き：380.71MB
[sequential 150000] 合計：494.50MB, 使用：134.99MB, 空き：359.51MB
[sequential 160000] 合計：496.50MB, 使用：17.24MB, 空き：479.26MB
[sequential 170000] 合計：495.50MB, 使用：33.73MB, 空き：461.77MB
[sequential 180000] 合計：498.00MB, 使用：47.32MB, 空き：450.68MB
[sequential 190000] 合計：497.50MB, 使用：57.29MB, 空き：440.21MB
[sequential 200000] 合計：500.00MB, 使用：67.19MB, 空き：432.81MB
sequential end
メモリの空きは常に余裕がある。見てて安心できる。
２，３個のリクエストを同時に送ってみたが、OutOfMemory は発生しなかった。
MySQL（フェッチサイズ修正後）
MySQL で、前述したフェッチサイズの修正を施すと次のようになる。
MySQL-sequential（改）
sequential start
フェッチサイズ：-2147483648
[sequential クエリ実行前] 合計：491.50MB, 使用：124.35MB, 空き：367.15MB
[sequential クエリ実行後] 合計：491.50MB, 使用：124.35MB, 空き：367.15MB
[sequential 10000] 合計：491.50MB, 使用：80.44MB, 空き：411.06MB
[sequential 20000] 合計：491.50MB, 使用：25.61MB, 空き：465.89MB
[sequential 30000] 合計：491.50MB, 使用：95.72MB, 空き：395.78MB
[sequential 40000] 合計：472.50MB, 使用：37.76MB, 空き：434.74MB
[sequential 50000] 合計：490.00MB, 使用：109.89MB, 空き：380.11MB
[sequential 60000] 合計：490.50MB, 使用：55.47MB, 空き：435.03MB
[sequential 70000] 合計：490.50MB, 使用：132.41MB, 空き：358.09MB
[sequential 80000] 合計：491.00MB, 使用：77.06MB, 空き：413.94MB
[sequential 90000] 合計：492.00MB, 使用：20.33MB, 空き：471.67MB
[sequential 100000] 合計：494.00MB, 使用：90.06MB, 空き：403.94MB
[sequential 110000] 合計：495.50MB, 使用：26.32MB, 空き：469.18MB
[sequential 120000] 合計：495.00MB, 使用：89.50MB, 空き：405.50MB
[sequential 130000] 合計：496.50MB, 使用：15.47MB, 空き：481.03MB
[sequential 140000] 合計：499.00MB, 使用：76.00MB, 空き：423.00MB
[sequential 150000] 合計：498.00MB, 使用：133.54MB, 空き：364.46MB
[sequential 160000] 合計：500.00MB, 使用：45.21MB, 空き：454.79MB
[sequential 170000] 合計：502.00MB, 使用：101.61MB, 空き：400.39MB
[sequential 180000] 合計：501.50MB, 使用：157.79MB, 空き：343.71MB
[sequential 190000] 合計：502.50MB, 使用：55.73MB, 空き：446.77MB
[sequential 200000] 合計：504.50MB, 使用：106.23MB, 空き：398.27MB
sequential end
Oracle-sequential と同じように、メモリの空き容量に大きな余裕が生まれた。
実装
使用したサードパーティのライブラリ
- Apache Commons Lang 3
- Apache Commons DBCP 1.4
サーバ起動時にデータソースを作成するリスナー
ApplicationInitializer.java
package test.download.listener;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import test.download.util.MemoryUtil;
@WebListener
public class ApplicationInitializer implements ServletContextListener {
@Override
public void contextInitialized(ServletContextEvent event) {
MemoryUtil.printMemoryInfo("サーバ起動");
// DataSource ds = getOracleDatasource();
DataSource ds = getMySQLDatasource();
event.getServletContext().setAttribute("datasource", ds);
}
private DataSource getOracleDatasource() {
System.out.println("init datasource : oracle");
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@192.168.61.10:1521:test");
ds.setUsername("test");
ds.setPassword("test");
return ds;
}
private DataSource getMySQLDatasource() {
System.out.println("init datasource : mysql");
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost/test");
ds.setUsername("test");
ds.setPassword("test");
return ds;
}
@Override
public void contextDestroyed(ServletContextEvent arg0) {}
}
サーブレット
基底サーブレット
AbstractCSVDownloadServlet.java
package test.download.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import test.download.service.DatabaseAccess;
public abstract class AbstractCSVDownloadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
public void doGet(HttpServletRequest req, HttpServletResponse res) throws IOException {
res.setContentType("application/octet-stream");
res.setHeader("Content-Disposition", "filename=" + createFileName());
DataSource ds = (DataSource)req.getServletContext().getAttribute("datasource");
DatabaseAccess databaseAccess = new DatabaseAccess(getName(), ds);
try (PrintWriter writer = res.getWriter()) {
download(databaseAccess, writer);
}
}
private String createFileName() {
String date = DateFormatUtils.format(new Date(), "yyyyMMdd_HHmmss");
return getName() + "_" + date + ".csv";
}
protected String toStringLine(ResultSet rs) throws SQLException {
List<String> list = new ArrayList<>();
list.add(String.valueOf(rs.getLong("ID")));
list.add(rs.getString("VALUE_1"));
list.add(rs.getString("VALUE_2"));
list.add(rs.getString("VALUE_3"));
list.add(rs.getString("VALUE_4"));
list.add(rs.getString("VALUE_5"));
list.add(rs.getString("VALUE_6"));
list.add(rs.getString("VALUE_7"));
list.add(rs.getString("VALUE_8"));
list.add(rs.getString("VALUE_9"));
list.add(rs.getString("VALUE_10"));
list.add(rs.getString("VALUE_11"));
list.add(rs.getString("VALUE_12"));
list.add(rs.getString("VALUE_13"));
list.add(rs.getString("VALUE_14"));
return StringUtils.join(list, ",");
}
abstract protected void download(DatabaseAccess databaseAccess, final PrintWriter writer);
abstract protected String getName();
}
一旦 List にデータを突っ込むロジックのサーブレット
BulkCSVDownloadServlet.java
package test.download.servlet;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.annotation.WebServlet;
import test.download.service.DatabaseAccess;
import test.download.service.DatabaseAccess.Processor;
import test.download.util.MemoryWatcher;
@WebServlet("/bulk")
public class BulkCSVDownloadServlet extends AbstractCSVDownloadServlet {
private static final long serialVersionUID = 1L;
@Override
protected String getName() {
return "bulk";
}
@Override
protected void download(DatabaseAccess databaseAccess, final PrintWriter writer) {
databaseAccess.execute(new Processor() {
@Override
public Statement getStatement(Connection con) throws SQLException {
return con.createStatement();
}
@Override
public void process(ResultSet rs) throws SQLException {
MemoryWatcher watcher = new MemoryWatcher(getName());
List<String> lineList = new ArrayList<>();
while (rs.next()) {
String line = toStringLine(rs);
lineList.add(line);
watcher.step();
}
for (String line : lineList) {
writer.println(line);
}
}
});
}
}
順次 PrintWriter に書き出すロジックのサーブレット
SequentialCSVDownloadServlet.java
package test.download.servlet;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.annotation.WebServlet;
import test.download.service.DatabaseAccess;
import test.download.service.DatabaseAccess.Processor;
import test.download.util.MemoryWatcher;
@WebServlet("/sequential")
public class SequentialCSVDownloadServlet extends AbstractCSVDownloadServlet {
private static final long serialVersionUID = 1L;
@Override
protected String getName() {
return "sequential";
}
@Override
protected void download(DatabaseAccess databaseAccess, final PrintWriter writer) {
databaseAccess.execute(new Processor() {
@Override
public Statement getStatement(Connection con) throws SQLException {
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
return stmt;
}
@Override
public void process(ResultSet rs) throws SQLException {
MemoryWatcher watcher = new MemoryWatcher(getName());
while (rs.next()) {
String line = toStringLine(rs);
writer.println(line);
watcher.step();
}
}
});
}
}
データベースアクセス用クラス
DatabaseAccess.java
package test.download.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import test.download.util.MemoryUtil;
public class DatabaseAccess {
private String name;
private DataSource ds;
public DatabaseAccess(String name, DataSource ds) {
this.name = name;
this.ds = ds;
}
public void execute(Processor processor) {
System.out.println(name + " start");
try (Connection con = ds.getConnection();
Statement stmt = processor.getStatement(con)) {
System.out.println("フェッチサイズ：" + stmt.getFetchSize());
MemoryUtil.printMemoryInfo(name + " クエリ実行前");
stmt.execute("SELECT * FROM TEST_TABLE");
MemoryUtil.printMemoryInfo(name + " クエリ実行後");
ResultSet rs = stmt.getResultSet();
processor.process(rs);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(name + " end");
}
public static interface Processor {
Statement getStatement(Connection con) throws SQLException;
void process(ResultSet rs) throws SQLException;
}
}
メモリ使用量計測用のユーティリティ
メモリ情報出力クラス
MemoryUtil.java
package test.download.util;
public class MemoryUtil {
public static void printMemoryInfo(Object tag) {
Runtime runtime = Runtime.getRuntime();
double total = runtime.totalMemory() / 1024.0 / 1024.0;
double free = runtime.freeMemory() / 1024.0 / 1024.0;
System.out.println(String.format("[%s] 合計：%.2fMB, 使用：%.2fMB, 空き：%.2fMB", tag, total, total-free, free));
}
}
カウンターを内蔵したメモリ使用量計測用クラス
MemoryWatcher.java
package test.download.util;
public class MemoryWatcher {
private String tag;
private int counter = 0;
private int interval = 10000;
public MemoryWatcher(String tag) {
this.tag = tag;
}
public MemoryWatcher(String tag, int interval) {
this(tag);
this.interval = interval;
}
public void step() {
counter++;
if (counter % interval == 0) {
MemoryUtil.printMemoryInfo(tag + " " + counter);
}
}
}