MySQL の JDBC ドライバで設定しておきたい rewriteBatchedStatements プロパティ

公開日: : 最終更新日:2014/01/26 SpringFramework , ,

データベースに MySQL を使った Java アプリのバッチ更新処理について非常に勉強になることがあったのでメモしておきます。

icatch-mysql_5578829409

目次

1. 背景

次の環境で構築したシステムのバッチ更新(大量のデータを一括に登録する処理)に時間がかかりすぎるので調査しました。

  • Java: ver 1.6
  • Spring: ver 3.0.5
  • MySQL: 5.1
  • JDBC ドライバ: 5.1.6

5000 件のデータを insert するのに約5分かかっており明らかに遅いです。

最終的には約1.5秒程度で insert できるようになり事なきを得たのですが、そのときの調査内容をメモしておきます。

2. 調査メモ

システムのデータアクセス部分には SpringJDBC を採用しており、バッチ更新部分には batchUpdate メソッドで実装しています。

なので、この batchUpdate の使い方が間違っているのではないかと考え検索してみると、次のサイトを見つけました。

以下は上記サイトからの引用です。

まず,本来なら前回見ておくべきだったのが,
int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss)
です.一応update系なので.いわゆるバッチ更新をやってくれるメソッドですね.
JDBCドライバがバッチ更新をサポートしていない場合は,普通にPreparedStatement#execute()を呼び出してくれます.こういうのは気が利いていていいかも.

“JDBCドライバがバッチ更新をサポートしていない場合” という部分が気になり、MySQL のバッチ更新について検索すると次のサイトを見つけました。

以下は上記サイトからの引用です。

batchInsert() や batchUpdate() などのバッチ更新ですが、MySQLのJDBCドライバの制限で実際にはバッチ更新されず、JDBCドライバの中でループでそれぞれ一件ずつ処理されます。 よって、他のDBMSに比べてバッチ更新のメリットは極端に少ないです。

一括でガーッと insert されるのではなく、1件ずつ insert されるとは。
だからあんなに遅いのか。。。

一方で MySQL は、一つのinsert文で values 部分をカンマ区切り(複数values方式)でつなげて複数レコードを登録することができます。

なるほどー。こういう回避策が使えるのか。

また、Connectionのプロパティで、rewriteBatchedStatements を true に設定すると、MySQLがバッチ登録時に内部的に “複数values方式” に変換して登録を行ってくれます。

“rewriteBatchedStatements” というプロパティを有効にしないと “複数values方式” も有効にならないと。

この “rewriteBatchedStatements” プロパティをプロジェクトで採用している JDBC ドライバがサポートしているのかを調べてみました。

上述しましたが、プロジェクトで採用している JDBC ドライバは次の通りです。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
</dependency>

MySQL のバージョンは 5.1 なので次のページで確認しました。

“rewriteBatchedStatements” の項目を見ると次のようになっていました。

  • デフォルト値: false
  • 対応開始したバージョン: 3.1.13

どうやら対応しているようです。
なので、”複数values方式” で一括にガーッと insert できます。よかった。

3. applicationContext.xml に rewriteBatchedStatements を設定

Spring の設定ファイル applicationContext.xml にある DataSource の設定を次のように変更しました。

<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/hoge?rewriteBatchedStatements=true" />
    <property name="username" value="USERNAME" />
    <property name="password" value="PASSWORD" />
    <property name="initialSize" value="20" />
</bean>

url の value に “?rewriteBatchedStatements=true” を追加しました。
コネクションプーリングには DBCP – Overview を使っています。

4. 効果比較

効果を比較してみました。

  • rewriteBatchedStatements を付けずに 100 件のデータを insert → 約 4.0 seconds
  • rewriteBatchedStatements を付けて 100 件のデータを insert → 約 0.4 seconds
  • rewriteBatchedStatements を付けて 5000 件のデータを insert → 約 1.4 seconds

効果ありすぎです。

5. 調査まとめ

  • SpringJDBC にて大量データを insert する場合は batchUpdate メソッドを使う、で正解
  • MySQL の JDBC ドライバの制限で、一括にデータを登録する「バッチ更新」はできない
  • 「バッチ更新」ができない代わりに、rewriteBatchedStatements を使った「複数values方式」を使う

6. 大量データ対策

「ホントに大量のデータが来た場合にメモリが心配だから、データをテキトーなサイズで分割しておくこと」とアドバイスをいただいた。
なるほど。

リストを指定したサイズで分割するメソッドが Guava にあった。
Lists#partition というメソッドがそれです。
Guava、ホント気が利いている。

  • [Lists (Guava: Google Core Libraries for Java 16.0-SNAPSHOT API)](http://docs.guava-libraries.googlecode.com/git/javadoc/com/google/common/collect/Lists.html#partition(java.util.List,%20int)

Lists#partition を使って 1000 件単位に分割して insert するように変更したところ、分割前よりもちょっとだけ遅くなりました。
ですが、メモリの心配を回避できるようになったので良しとするところでしょう。

7. その他の Spring に関する記事

Spring に関する記事は次の通りです。
気になる記事があったらぜひチェックしてみてください!

Googleアドセンス用(PC)

  • このエントリーをはてなブックマークに追加
  • follow us in feedly

関連記事

icatch-cron_5578829409

Spring の Scheduled アノテーションを使った cron サンプル

Spring の @Scheduled アノテーションを使った cron サンプルプログラムを作成し

記事を読む

icatch-log_5578829409

SpringJDBC が発行する SQL をログに出力する

SpringJDBC が発行する SQL を確認する必要があったので Apache log4j 1.

記事を読む

icatch-spring

spring framework を使ったデスクトップアプリ(standalone app)で context から getBean でオブジェクトを取得する

Java では珍しく Web ではなくデスクトップアプリの開発があり、そのプロジェクトで Sprin

記事を読む

icatch-java

Spring の設定ファイル applicationContext.xml の内容をプロパティファイルに切り出す

Spring の設定ファイルである applicationContext.xml に DataSou

記事を読む

Financial injection

spring framework を使ったデスクトップアプリ(standalone app)で context から getBean でオブジェクトを取得する。@Autowired による紐付け

先日 spring framework を使ったデスクトップアプリ(standalone app)で

記事を読む

icatch-dbcp-219581864

Spring の DataSource に DBCP を使用する

Spring の DataSource に DBCP を設定してみたのでメモしておきます。 目

記事を読む

Googleアドセンス用(PC)

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


4 × = 三十 六

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Googleアドセンス用(PC)

icatch-jersey_multi_pathparams
Jerseyの@PathParamはスラッシュの間に複数指定できる

http://hoge-api/user/{id}.{format}

icatch-vagrant_box_customize
VagrantのBoxファイルをカスタマイズして独自のBoxファイルを作成する

配布されている Vagrant の Box ファイルを使って検証環境を

icatch-2015-006-1
バリデーションチェックにJava8のOptionalを使ってスマートに書く(自分比)

Web アプリのバリデーションチェックにアノテーションを使うことが増え

icatch-2015-005-1
ユニットテストの偏りを防ぐ命名規則の付け方

ユニットテスト名に以下の命名規則を付けるようにして二ヶ月ぐらい経った。

icatch-2015-004-1
Vagrantで起動したCentOS上のOctopressをホストOSから確認する設定

タイトルの通りだが、Vagrant を使って起動した CentOS に

→もっと見る

PAGE TOP ↑