Planet MySQL :: Planet MySQL - Archives - MySQL のトリガーの実用性を確認する�...

　最近 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

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

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

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

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

MySQL Links