« Q4M の mutex まわりを整理 | Main | 今更 C++ で JSON パーサ「picojson」を書いたわけ »

June 29, 2009

MySQL のトリガーの実用性を確認するために InnoDB の SELECT COUNT(*) を高速化してみる

 最近 RDBMS のトリガーを色々書いているのですが、知らない人にトリガーが何かいちいち説明するのに簡単な例はないかな、というのと、MySQL の処理速度はトリガーによってどの程度変化するか、ということを確認するために、以下のような実験を行ってみました。

 InnoDB はしばしば、「SELECT COUNT(*) が遅い!」と批判されます。では、トリガーを使って行数を別のテーブルにキャッシュすればいいのではないでしょうか? 以下のように、極めて小さなテーブル t1 を作り、その行数を t1_cnt にキャッシュしてみることにします。

mysql> create table t1 (
    ->   id int unsigned not null primary key auto_increment,
    ->   v int unsigned not null
    -> ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1_cnt (
    ->   cnt int unsigned not null
    -> ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1_cnt values (0);
Query OK, 1 row affected (0.00 sec)

 テーブルを定義し、t1_cnt に行数の初期値 (0) をセットしたら、次にトリガーを登録します。

mysql> delimiter |
mysql> create trigger t1_insert after insert on t1 for each row begin       
    ->   update t1_cnt set cnt=cnt+1;
    -> end|
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t1_delete after delete on t1 for each row begin
    ->   update t1_cnt set cnt=cnt-1;
    -> end|
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

 試しに t1 テーブルに値を出し入れして、t1_cnt の行数が変化することを確認します。

mysql> insert into t1 (v) values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1_cnt;
+-----+
| cnt |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

mysql> delete from t1;   
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t1_cnt;
+-----+
| cnt |
+-----+
|   0 |
+-----+
1 row in set (0.01 sec)

 では、トリガーをセットしたことで、パフォーマンスはどれほど劣化するのでしょう。mysqlslap を使って測定してみます。

# トリガーが有効な場合
$ time /usr/local/mysql51/bin/mysqlslap -u root -S tmp/mysql.sock -c 20 -i 1000 -q 'insert into test.t1 (v) values (1)'
Benchmark
Average number of seconds to run all queries: 0.008 seconds
Minimum number of seconds to run all queries: 0.003 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 20
Average number of queries per client: 1


real 0m9.893s
user 0m0.590s
sys 0m1.975s

# トリガーが無効な場合
$ time /usr/local/mysql51/bin/mysqlslap -u root -S tmp/mysql.sock -c 20 -i 1000 -q 'insert into test.t1 (v) values (1)'
Benchmark
Average number of seconds to run all queries: 0.003 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.014 seconds
Number of clients running queries: 20
Average number of queries per client: 1


real 0m4.851s
user 0m0.612s
sys 0m1.911s

 トリガーを使うことで、書き込みパフォーマンスが約半分になっていることが確認できます。トリガーを使うことで、1回の INSERT で更新する行数は2倍になっているのですから、この結果は妥当だと考えられます。逆に言うと、トリガーという機能特有のオーバーヘッドは、少なくともこの場合はない (クライアントサイドでがんばってチューニングしなくても、トリガーに頼ってよい) ということになります。

 実際の運用では、テーブルの1行のサイズは、このテストで使った t1 テーブルよりもずっと大きいでしょうし、また、INSERT や DELETE 以外の SELECT や UPDATE 実行時には、今回セットしたトリガーはそもそも実行されないので、行数をカウントすることによるオーバーヘッドは、もっと小さいでしょう。一方で、MySQL のトリガーは行ベース (1行毎に処理を行うタイプ) なので、多数の行を一度に更新するようなクエリを実行する場合は、注意が必要になってきます。

 というわけで、以上、トリガーの紹介と簡単なベンチマークでした。MySQL とトリガーについて詳しく知りたい方は、MySQL :: MySQL 5.1 リファレンスマニュアル :: 18 トリガ等をご参照ください。

 なお、上記ベンチマークは、MySQL 5.1.35 で innodb_flush_log_at_trx_commit=0 に設定して実行しました。

TrackBack

TrackBack URL for this entry:
http://bb.lekumo.jp/t/trackback/404050/20399813

Listed below are links to weblogs that reference MySQL のトリガーの実用性を確認するために InnoDB の SELECT COUNT(*) を高速化してみる:

Comments

行数カウントがどれくらい速くなるのか書かないと片手落ちなんじゃないでしょうか。

napy さん、InnoDB の SELECT COUNT(*) の速度はテーブルの大きさに比例しますが、キャッシュしてしまえばテーブルの大きさに関係なく一瞬になります。

Post a comment