2009-05-28

ピンポイントチューニング講座です。まずは結果から。

このグラフは、以下のテーブルに50,000レコードINSERTしたときの処理時間を示したものです。性能に70倍以上もの差が出ているのはなぜか、見ていきたいと思います。

CREATE TABLE `loadtest` (
  `id` int(11) NOT NULL,
  `data` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

方法1 ベースライン

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)");
for (int i = 1; i <= COUNT; i++) {
	pstmt.clearParameters();
	pstmt.setInt(1, i);
	pstmt.setString(2, DATA);
	pstmt.execute();
}

単純にINSERTを繰り返し実行する方法です。MyISAMで246.6秒、InnoDBで360.2秒かかっています。

方法2 1,000レコードごとにCOMMIT

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)");
for (int i = 1; i <= COUNT; i++) {
	pstmt.clearParameters();
	pstmt.setInt(1, i);
	pstmt.setString(2, DATA);
	pstmt.execute();
	if (i % COMMIT_SIZE == 0) {
		conn.commit();
	}
}
conn.commit();

InnoDBでトランザクションを使い、1,000レコードINSERTするごとにCOMMITする方法です。31.5秒と方法1に比べて10倍以上速くなり、この時点でMyISAMの性能を超えます。
JDBCはデフォルトでオートコミットなので、方法1では1レコードINSERTするごとにディスクの書き込み完了を待つことになります。これでは性能がでないのは当たり前です。方法2がもっとも一般的な実装だと思います。

方法3 100レコードずつINSERT、1,000レコードごとにCOMMIT

conn = DriverManager.getConnection(JDBC_URL + "?rewriteBatchedStatements=true",
		JDBC_USER, JDBC_PASS);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into loadtest (id, data) values (?, ?)");
for (int i = 1; i <= COUNT; i += BATCH_SIZE) {
	pstmt.clearBatch();
	for (int j = 0; j < BATCH_SIZE; j++) {
		pstmt.setInt(1, i + j);
		pstmt.setString(2, DATA);
		pstmt.addBatch();
	}
	pstmt.executeBatch();
	if ((i + BATCH_SIZE - 1) % COMMIT_SIZE == 0) {
		conn.commit();
	}
}
conn.commit();

JDBCのバッチ更新を利用して一度に複数レコードを処理する方法です。addBatch()でクライアント内にデータを溜め込み、executeBatch()でサーバにまとめてデータを送ります。処理時間は5.2秒となり、方法2と比べてさらに6倍速くなります。
ただしMySQLのJDBCバッチ更新を高速化するには、以下の二つの条件を満たす必要があります。

接続プロパティにrewriteBatchedStatements=trueを指定すること
サーバサイドPreparedStatementを使わないこと(useServerPrepStmts=trueにしないこと)

rewriteBatchedStatements=trueとすると、Connector/JがINSERT文を次のように書き換えます。

insert into loadtest (id, data) values
(5101, '12345678901234567890123456789012345678901234567890123456789012345678901234567890'),
(5102, '12345678901234567890123456789012345678901234567890123456789012345678901234567890'),
(5103, '12345678901234567890123456789012345678901234567890123456789012345678901234567890'),
(5104, '12345678901234567890123456789012345678901234567890123456789012345678901234567890'),
(5105, '12345678901234567890123456789012345678901234567890123456789012345678901234567890'),
〜

MySQLの通信プロトコルは非常に単純に作られているため、JDBCバッチ更新を直接サポートするための仕組みはありません。Connector/JはJDBCバッチ更新のリクエストをなかば無理やり複数行INSERT構文に書き換えることで、高速化を実現しているのです。
このとき、サーバサイドPreparedStatementを使ってしまうとConnector/JがSQLを書き換えるチャンスがなくなってしまいます。そのためJDBCバッチ更新による高速化を狙う際は、サーバサイドPreparedStatementを使ってはいけません。

方法4 1,000レコードずつLOADしてCOMMIT

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("load data local infile '' "
		+ "into table loadtest fields terminated by ','");
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= COUNT; i++) {
	sb.append(i + "," + DATA + "\n");
	if (i % COMMIT_SIZE == 0) {
		InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
		((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
		pstmt.execute();
		conn.commit();
		sb.setLength(0);
	}
}
InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
pstmt.execute();
conn.commit();

LOAD DATA LOCAL INFILE文を利用してデータロードを行う方法です。処理時間は4.6秒となっており、これが今回最も速い方法でした。
Connector/JでもLOAD DATA LOCAL INFILE文はサポートされています。ただしわざわざファイルを作ってそれを読み込ませるのは手間ですので、Connector/J 5.1.3から追加されたsetLocalInfileInputStream()メソッドを用いてメモリ上のバイトストリームを直接サーバに流し込むように工夫しています。setLocalInfileInputStream()は世の中に一つもサンプルがなかったのでちょっと苦労しました(^^;
この方法はJDBC標準から外れているという点に注意してください。方法1〜3はJDBC URLだけ書き換えれば他のRDBMSでも動かせますが、方法4はMySQL専用となります。
また今回の測定では1,000レコードに決め打ちしていますが、この方法4についてはもっと大きい単位で処理した方が性能が伸びるようです。一方で方法3はSQLの書き換え負荷がどんどん重くなるため、これ以上処理単位を大きくしてもあまり性能が伸びません。

まとめ

方法3、4を知っているのといないのとではアプリケーションの性能がかなり違ってくるでしょう。是非、

rewriteBatchedStatementsを設定してJDBCバッチ更新を使う
setLocalInfileInputStream()と組み合わせてLOAD DATA LOCAL INFILE文を使う

この二つを覚えていただいて、機会があったら使ってみてください。

SH2の日記

MySQL Connector/Jにおける大量INSERTのチューニング

方法1 ベースライン

方法2 1,000レコードごとにCOMMIT

方法3 100レコードずつINSERT、1,000レコードごとにCOMMIT

方法4 1,000レコードずつLOADしてCOMMIT

まとめ