移転しました。

参照と更新が頻繁に発生するテーブルで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();
                }
            }
        }
    }

}