参照と更新が頻繁に発生するテーブルでMyISAMとInnoDBを比較

[追記 2012/09/29]
最近でもこの記事を参照してくださる方がいるので追記します。下記エントリを書いた時点では非常に局所的なケースで重い現象に悩まされていたことを前提に調査しており、その延長線上で「一律InnoDBというのは言い過ぎな印象を受けるパフォーマンス差に感じる」ということを書いてしまっていますが、その後色々と勉強した結果、特定箇所のニッチなベンチマークではなく一般的な運用上の負荷を焦点にした場合はInnoDBは適切に設定しておれば十分にパフォーマンスがある（もしくはInnoDBの方が有利）というのが現在の意見です。

「MyISAM InnoDB」で検索するとあちらこちらであるように、今時は理由がなければInnoDB、ということでMyISAMのテーブルをいくつかInnoDBに変更したところ、かなりパフォーマンスが落ちるケースがあった。
InnoDBにしたら軒並み遅くなったということではなくて、遅くなったのは参照と更新が同程度頻繁に発生するテーブルだった。InnoDBはトランザクションのオーバーヘッドがあるので、ある程度遅くなることは仕方がないかもしれないけれども、かなり速度にダメージがあったので調べることにした。

細かいケースを比較したベンチが取りたいというよりは、実際に近い環境でどういう症状がでるのかを確認したかったので、Javaでスレッドを複数立ち上げてSELECTとUPDATEが入り乱れるようにして測定してみた。検証に使ったコードはだらだらと書いたひどいコードだけど最後に貼っておきます。

動作環境はMacのParallels Desktopに2GBのメモリを割り当てたCentOS 5.3。インストールしたMySQLはMySQL-server-community-5.1.34-0.rhel5.x86_64.rpmでmy.cnfはmy-huge.cnf.shをそのまま利用。作成したテーブルは次の2つ。

CREATE TABLE t_myisam (
id INT UNSIGNED NOT NULL,
value VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL,
updated_at TIMESTAMP,
PRIMARY KEY(id)
) ENGINE=MyISAM;
CREATE TABLE t_innodb (
id INT UNSIGNED NOT NULL,
value VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL,
updated_at TIMESTAMP,
PRIMARY KEY(id)
) ENGINE=InnoDB;

それぞれ100万件のデータを入れて測定。Javaの処理は、SELECTとUPDATEをx回繰り返すスレッドをy個作成するようになっている。idとvalueは常にランダムな値が使われる。数値は3回実行して一番速いものを採用。

スレッド数（1スレッド辺り200クエリ）	MyISAM	InnoDB
1	393ms	694ms
5	1331ms	2238ms
20	4924ms	7278ms
50	9873ms	22606ms

それなりにInnoDBが遅い。UPDATEを外してSELECTのみにしてみた。

スレッド数（1スレッド辺り200クエリ）	MyISAM	InnoDB
1	324ms	198ms
5	986ms	995ms
20	3567ms	3261ms
50	8949ms	9859ms

InnoDBのプライマリキーはクラスターインデックスなので速いと思ったけれども、MyISAMと同程度。逆にUPDATEのみにしてみた。

スレッド数（1スレッド辺り200クエリ）	MyISAM	InnoDB
1	217ms	459ms
5	979ms	1608ms
20	2037ms	5362ms
50	6075ms	14252ms

InnoDBはSELECTとUPDATEをそれぞれ足した時間がかかっているけれども、MyISAMはうまく並列処理をしている様子。あと、MyISAMはSELECTよりもUPDATEの方が速い。

次にプライマリキー以外にインデックスがある場合を測定してみる。

mysql> CREATE INDEX value ON t_myisam (value);
Query OK, 1000000 rows affected (9.79 sec)
Records: 1000000  Duplicates: 0  Warnings: 0
mysql> CREATE INDEX value ON t_innodb (value);
Query OK, 1000000 rows affected (2 min 13.81 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

InnoDBのインデックス作成にかなりの時間がかかった。SELECTとUPDATEの両方を行ってみる。

スレッド数（1スレッド辺り200クエリ）	MyISAM	InnoDB
1	343ms	769ms
5	1351ms	2636ms
20	4977ms	11536ms
50	11539ms	33084ms

インデックスを作成する前に比べてMyISAMはそれほど遅くならないけれど、InnoDBはかなり遅くなった。

速度だけでストレージエンジンを選択することはできないし、MyISAMとInnoDBだけを比べてもかなりサポートしている機能が違うけれども、一律InnoDBというのは言い過ぎな印象を受けるパフォーマンス差に感じる。用途によってはMyISAMは存分に活躍すると思う。

今回の例とは異なるけれども、MySQLによるデータウェアハウス構築 (Yahoo! JAPAN Tech Blog)も事例としては参考になるかな。

最後に検証に使ったJavaコード。

package net.clonedoppelganger.dbtest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DB {
static {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
e.printStackTrace();
}
}
private static final int DATA_MAX = 1000000;
private Connection conn = null;
private String table = null;
public DB(String table) {
this.table = table;
}
public void open() throws SQLException {
conn = DriverManager.getConnection(
"jdbc:mysql://xxx.xxx.xxx.xxx/xxx?user=xxx&password=xxx");
}
public void close() throws SQLException {
if (conn != null) {
conn.close();
}
}
public void importData() throws SQLException {
String truncate = "TRUNCATE TABLE %s";
String insert = "INSERT INTO %s VALUES (?, ?, NOW(), NULL)";
PreparedStatement stmt = null;
String query = String.format(truncate, table);
try {
stmt = conn.prepareStatement(query);
stmt.executeUpdate();
} finally {
if (stmt != null) {
stmt.close();
}
}
for (int i = 1; i <= DATA_MAX; i++) {
query = String.format(insert, table);
try {
stmt = conn.prepareStatement(query);
stmt.setInt(1, i);
stmt.setString(2, String.valueOf(Math.random()));
stmt.executeUpdate();
} finally {
if (stmt != null) {
stmt.close();
}
}
}
}
public void select() throws SQLException {
String select = "SELECT * FROM %s WHERE id = ?";
PreparedStatement stmt = null;
ResultSet rs = null;
String query = String.format(select, table);
int id = (int)(Math.random() * (DATA_MAX + 1));
try {
stmt = conn.prepareStatement(query);
stmt.setInt(1, id);
rs = stmt.executeQuery();
rs.next();
} finally {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
}
}
public void update() throws SQLException {
String select = "UPDATE %s SET value = ? WHERE id = ?";
PreparedStatement stmt = null;
String query = String.format(select, table);
int id = (int)(Math.random() * (DATA_MAX + 1));
try {
stmt = conn.prepareStatement(query);
stmt.setString(1, String.valueOf(Math.random()));
stmt.setInt(2, id);
stmt.executeUpdate();
} finally {
if (stmt != null) {
stmt.close();
}
}
}
public static void ready(String table) {
DB db = null;
try {
db = new DB(table);
db.open();
db.importData();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (db != null) {
try {
db.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
int tryCount = 3;
int threadCount = 50;
int queryCount = 200;
String[] tables = {"t_myisam", "t_innodb"};
for (int i = 0; i < tables.length; i++) {
String table = tables[i];
//DB.ready(table);
long[] results = new long[tryCount];
for (int j = 0; j < tryCount; j++) {
Thread[] threads = new Thread[threadCount];
for (int k = 0; k < threadCount; k++) {
threads[k] = new Tester(table, queryCount);
}
long start = System.currentTimeMillis();
for (int k = 0; k < threadCount; k++) {
threads[k].start();
}
for (int k = 0; k < threadCount; k++) {
try {
threads[k].join();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
long end = System.currentTimeMillis();
results[j] = end - start;
}
java.util.Arrays.sort(results);
for (int j = 0; j < results.length; j++) {
System.out.println(table + ": " + results[j] + "ms");
}
}
}
}
class Tester extends Thread {
private String table = null;
private int queryCount = 1;
public Tester(String table, int queryCount) {
this.table = table;
this.queryCount = queryCount;
}
public void run() {
DB db = null;
try {
db = new DB(table);
db.open();
for (int i = 0; i < queryCount; i++) {
try {
db.select();
db.update();
} catch (SQLException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (db != null) {
try {
db.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}