InnoDB vs MyISAM パフォーマンス測定環境）（ブログ風パターン）

今度はブログ系のテーブル構成になっています。

サーバ環境

HP DL350
Linux version 2.6.9-5.ELsmp (bhcompile@decompose.build.redhat.com) (gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)) #1 SMP Wed Jan 5 19:30:39 EST 2005

Intel(R) Pentium(R) III CPU family×2
cpu MHz : 1396.686
cache size : 512 KB
3GB of RAM

OSもMySQLも32bit。
MySQLは5.0.37。

1.テーブルの構造

1.1 InnoDBのテーブル

PLAIN TEXT

SQL:

CREATE TABLE IF NOT EXISTS entry (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`status` NUMERIC(5) NOT NULL,
`category_id` NUMERIC(10) NOT NULL,
`user_type` NUMERIC(10) NOT NULL,
`name` VARCHAR(64) NOT NULL,
`favorites` NUMERIC(10) NOT NULL,
`comments` NUMERIC(10) NOT NULL,
`trackbacks` NUMERIC(10) NOT NULL,
`comment_permission` NUMERIC(10) NOT NULL,
`trackback_permission` NUMERIC(10) NOT NULL,
`edited` DATETIME NOT NULL,
`image` NUMERIC(10) NULL,
`caption` VARCHAR(128) NULL,
`user_name` VARCHAR(64) NOT NULL,
`created` DATETIME NOT NULL,
`modified` DATETIME NOT NULL,
`removed` DATETIME NULL,
`title` VARCHAR(1000) NOT NULL,
`body` TEXT NULL,
`comment_captcha_permission` NUMERIC(10) NOT NULL DEFAULT 1,
`favorite_permission` NUMERIC(10) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE INDEX `entry_unique` (`name`, `user_name`),
INDEX `entry_idx1` (`user_name`,`status`,`edited`),
INDEX `entry_idx2` (`name`,`user_name`,`removed`),
INDEX `entry_idx3` (`edited`),
INDEX `entry_idx4` (`trackbacks`,`edited`),
INDEX `entry_idx5` (`comments`,`edited`),
INDEX `entry_idx6` (`favorites`,`edited`),
INDEX `entry_idx7` (`category_id`,`edited`),
INDEX `entry_idx8` (`created`)
)" ENGINE=innoDB;
CREATE TABLE IF NOT EXISTS entry_comment (
`id` int(10) unsigned NOT NULL auto_increment,
`guest_name` VARCHAR(300) null,
`entry_id` NUMERIC(10) not null,
`user_name` VARCHAR(64) null,
`created` DATETIME not null,
`removed` DATETIME null,
`body` TEXT null,
`guest_url` VARCHAR(1000) null,
PRIMARY KEY (`id`),
INDEX `entry_comment_idx1` (`entry_id`,`created`,`removed`),
INDEX `entry_comment_idx2` (`user_name`,`removed`),
INDEX `entry_comment_idx3` (`removed`,`created`),
INDEX `entry_comment_idx4` (`user_name`)
)" ENGINE=innoDB;;

1.2 MyISAMのテーブル。

PLAIN TEXT

SQL:

CREATE TABLE IF NOT EXISTS entry2 (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`status` NUMERIC(5) NOT NULL,
`category_id` NUMERIC(10) NOT NULL,
`user_type` NUMERIC(10) NOT NULL,
`name` VARCHAR(64) NOT NULL,
`favorites` NUMERIC(10) NOT NULL,
`comments` NUMERIC(10) NOT NULL,
`trackbacks` NUMERIC(10) NOT NULL,
`comment_permission` NUMERIC(10) NOT NULL,
`trackback_permission` NUMERIC(10) NOT NULL,
`edited` DATETIME NOT NULL,
`image` NUMERIC(10) NULL,
`caption` VARCHAR(128) NULL,
`user_name` VARCHAR(64) NOT NULL,
`created` DATETIME NOT NULL,
`modified` DATETIME NOT NULL,
`removed` DATETIME NULL,
`title` VARCHAR(1000) NOT NULL,
`body` TEXT NULL,
`comment_captcha_permission` NUMERIC(10) NOT NULL DEFAULT 1,
`favorite_permission` NUMERIC(10) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE INDEX `entry_unique` (`name`, `user_name`),
INDEX `entry_idx1` (`user_name`,`status`,`edited`),
INDEX `entry_idx2` (`name`,`user_name`,`removed`),
INDEX `entry_idx3` (`edited`),
INDEX `entry_idx4` (`trackbacks`,`edited`),
INDEX `entry_idx5` (`comments`,`edited`),
INDEX `entry_idx6` (`favorites`,`edited`),
INDEX `entry_idx7` (`category_id`,`edited`),
INDEX `entry_idx8` (`created`)
)";
CREATE TABLE IF NOT EXISTS entry_comment2 (
`id` int(10) unsigned NOT NULL auto_increment,
`guest_name` VARCHAR(300) null,
`entry_id` NUMERIC(10) not null,
`user_name` VARCHAR(64) null,
`created` DATETIME not null,
`removed` DATETIME null,
`body` TEXT null,
`guest_url` VARCHAR(1000) null,
PRIMARY KEY (`id`),
INDEX `entry_comment_idx1` (`entry_id`,`created`,`removed`),
INDEX `entry_comment_idx2` (`user_name`,`removed`),
INDEX `entry_comment_idx3` (`removed`,`created`),
INDEX `entry_comment_idx4` (`user_name`)
)";

要するに同じ構造でInnoDB（テーブル名：entry,entry_comment）、MyISAM（テーブル名：entry2,entry_comment2）用のテーブルを作成しました。

entryとentry2は記事を格納するテーブルで100万件登録されています。
entry_commentとentry_comment2はコメントを格納するテーブルで、こちらも100万件登録されています。

PRIMARY KEYであるidには1～1000000の値がセットされています。
1記事に対して、1コメントが存在しています。

2.show indexの結果

PLAIN TEXT

SQL:

mysql> SHOW INDEX FROM entry;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| entry | 0 | PRIMARY | 1 | id | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 0 | entry_unique | 1 | name | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 0 | entry_unique | 2 | user_name | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx1 | 1 | user_name | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx1 | 2 | STATUS | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx1 | 3 | edited | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx2 | 1 | name | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx2 | 2 | user_name | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx2 | 3 | removed | A | 1090408 | NULL | NULL | YES | BTREE | |
| entry | 1 | entry_idx3 | 1 | edited | A | 4161 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx4 | 1 | trackbacks | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx4 | 2 | edited | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx5 | 1 | comments | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx5 | 2 | edited | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx6 | 1 | favorites | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx6 | 2 | edited | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx7 | 1 | category_id | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx7 | 2 | edited | A | 1090408 | NULL | NULL | | BTREE | |
| entry | 1 | entry_idx8 | 1 | created | A | 3894 | NULL | NULL | | BTREE | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
19 rows IN SET (0.66 sec)
mysql> SHOW INDEX FROM entry_comment;
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| entry_comment | 0 | PRIMARY | 1 | id | A | 1018735 | NULL | NULL | | BTREE | |
| entry_comment | 1 | entry_comment_idx1 | 1 | entry_id | A | 1018735 | NULL | NULL | | BTREE | |
| entry_comment | 1 | entry_comment_idx1 | 2 | created | A | 1018735 | NULL | NULL | | BTREE | |
| entry_comment | 1 | entry_comment_idx1 | 3 | removed | A | 1018735 | NULL | NULL | YES | BTREE | |
| entry_comment | 1 | entry_comment_idx2 | 1 | user_name | A | 1018735 | NULL | NULL | YES | BTREE | |
| entry_comment | 1 | entry_comment_idx2 | 2 | removed | A | 1018735 | NULL | NULL | YES | BTREE | |
| entry_comment | 1 | entry_comment_idx3 | 1 | removed | A | 18 | NULL | NULL | YES | BTREE | |
| entry_comment | 1 | entry_comment_idx3 | 2 | created | A | 3801 | NULL | NULL | | BTREE | |
| entry_comment | 1 | entry_comment_idx4 | 1 | user_name | A | 1018735 | NULL | NULL | YES | BTREE | |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows IN SET (0.31 sec)

3.同時スレッドを2,4,8,16,32,64と変えながら1秒間のクエリー処理数を計測します。

4.60秒間事前に実行（アイドリング）してから、60秒3回計測してその平均を算出します。

5.MySQLの初期化パラメータは以下の通りです。

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking

sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 1024
max_allowed_packet = 16M
key_buffer_size=1250M
innodb_buffer_pool_size=1250M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=1500
table_cache=1024
net_read_timeout=30
net_write_timeout=30
back_log=128