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

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

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

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

動作環境はMacParallels Desktopに2GBのメモリを割り当てたCentOS 5.3。インストールしたMySQLMySQL-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はかなり遅くなった。

速度だけでストレージエンジンを選択することはできないし、MyISAMInnoDBだけを比べてもかなりサポートしている機能が違うけれども、一律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();
}
}
}
}
}

3件のコメント

  1. my-huge.cnfはInnoDB関連の設定は何もされていないです。
    # Uncomment the following if you are using InnoDB tables
    とあるところをコメント解除すると傾向が変わるかもしれません。

    InnoDBのCREATE INDEXがMyISAMより遅いのは確かですが、
    innodb_buffer_pool_sizeをめいいっぱい増やせばある程度良くなると思います。

    あとUPDATEでAutoCommitしているところがInnoDBの性能ボトルネックになっていると思われますので、
    innodb_flush_log_at_trx_commitをあえて0にしてデータを取ってみると面白いと思います。

    また、my-huge.cnfはquery_cache_sizeが設定されているので、SELECTのみのテストは
    ストレージエンジンではなくクエリーキャッシュの性能を測っている可能性があります。
    テスト目的であれば外すことをおすすめします。

    実環境ではバイナリログをONにすることが多いと思います。
    log-binをONにしてsync-binlogを1に設定すると、
    MyISAMのUPDATE性能が大幅に劣化して現在のInnoDBと近い状態になると思います。

  2. 情報ありがとうございます。
    早速、時間見つけて再度試してみようと思います。

  3. MyISAM と InnoDB はまぜて使うよりもどっちかに統一して、その設定を my.cnf へしたほうがいいです。
    ベンチマークは難しい。

コメントする

メールアドレスが公開されることはありません。