2009-11-30

MySQLパーティショニングについて（その2：性能検証編）

hamada
はてなブックマーク   livedoor clip

こんにちは、濱田です。

前回から時間が経ってしまいましたが、今回は「性能検証編」ということで、パーティションドテーブルに対して実際にデータを挿入・参照することでパーティショニングの性能面を検証してみようと思います。

性能検証環境

使用したマシンのスペックは以下の通りです。

OS
CentOS 5.3 32bit (on Windows XP Pro SP3 32bit via VMware Server 2.0.0)
CPU
Core2 Duo E8300 2.83GHz (VMには1CPUを割り当て)
Memory
3.25GB (VMには512MBを割り当て)

MySQL のバージョンおよび設定は以下の通りです。なお、MySQL サーバおよびクライアントは同一マシン上で動作させました。

MySQL
5.1.35-community (設定は my-medium.cnf をそのまま使用)

また、パーティションドテーブルのスキーマは以下の通りです。

CREATE TABLE logs (
    id INT NOT NULL AUTO_INCREMENT,
    client_name VARCHAR(255) NOT NULL,
    log_data VARCHAR(1024) NOT NULL,
    logged_at DATETIME NOT NULL,
    PRIMARY KEY(id, logged_at),
    INDEX(client_name)
) ENGINE=MyISAM
PARTITION BY RANGE( TO_DAYS(logged_at) ) (
    PARTITION p200901 VALUES LESS THAN ( TO_DAYS('2009-02-01') ),
    PARTITION p200902 VALUES LESS THAN ( TO_DAYS('2009-03-01') ),
    PARTITION p200903 VALUES LESS THAN ( TO_DAYS('2009-04-01') ),
    PARTITION p200904 VALUES LESS THAN ( TO_DAYS('2009-05-01') ),
    PARTITION p200905 VALUES LESS THAN ( TO_DAYS('2009-06-01') ),
    PARTITION p200906 VALUES LESS THAN ( TO_DAYS('2009-07-01') ),
    PARTITION p200907 VALUES LESS THAN ( TO_DAYS('2009-08-01') ),
    PARTITION p200908 VALUES LESS THAN ( TO_DAYS('2009-09-01') ),
    PARTITION p200909 VALUES LESS THAN ( TO_DAYS('2009-10-01') ),
    PARTITION p200910 VALUES LESS THAN ( TO_DAYS('2009-11-01') ),
    PARTITION p200911 VALUES LESS THAN ( TO_DAYS('2009-12-01') ),
    PARTITION p200912 VALUES LESS THAN ( TO_DAYS('2010-01-01') ),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

データ挿入の性能検証

検証内容

データ挿入の性能検証には下記 INSERT 文を1万回連続で発行し、それに要した時間を測定しました。INSERT 文の「?」の部分には ‘client_1′ ～ ‘client_1000′ がそれぞれ10回ずつ入ります。（実際には上記の処理を行なう Ruby スクリプトを作成し、その実行時間を time コマンドで測定しました）

INSERT INTO logs (client_name, log_data, logged_at) \
VALUES (?, 'test log data', '2009-11-01');

logs テーブルの事前条件として、以下の2パターンで測定しました。

条件1
1件もレコードが格納されていない状態
条件2
1～10月の各月に1000クライアント×1000件分のログデータ（計1000万件）が格納されている状態

  • logged_at : 10通り (’2009-01-01′ ～ ‘2009-10-01′)
  • client_name : 1000通り (’client_1′ ～ ‘client_1000′)
  • 上記組み合わせをそれぞれ1000件ずつ

検証結果

結果は図1のようになりました。

result_insert_myisam.png
図1 : データ挿入の性能検証結果 (MyISAM)

条件1を見ると、パーティションあり／なしで処理時間が殆ど変わっていません。これはデータ挿入時にパーティション振り分け処理のオーバーヘッドが殆どないことを示しています。今回振り分け処理に用いたパーティショニング表現は TO_DAYS(logged_at) であり、この程度の処理ではパーティション振り分けのオーバーヘッドを全く気にしなくてよいことが分かります。

逆に言えば、パーティショニング表現に時間のかかる処理を用いてしまうとそこがボトルネックになってしまいます。それが気になる場合は、以下のように BENCHMARK() 関数を用いて事前にパーティショニング表現の処理時間を確認しておくとよいと思います。

mysql> SELECT BENCHMARK(10000, TO_DAYS('20091101') );
+---------------------------------------+
| benchmark(10000, TO_DAYS('20091101')) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

また条件1、2での処理時間を比較すると、パーティションなしの場合は処理時間が増加しているのに対し、パーティションありの場合は処理時間が殆ど変わっていないことが分かります。これはインデックスデータがパーティション毎に独立して作成されることを示しています。今回のスキーマで作成されるインデックスは (id, logged_at) の複合インデックスと client_name ですが、このうち client_name はユニークなカラムではありません。そのため、事前に格納されているレコード数が多いほどインデックスデータ（BTREE 構造）の更新処理に時間がかかり、故にデータ挿入処理も遅くなります。

パーティションなしの場合に事前に0件と1000万件格納されている場合とで挿入時間に差が出ているのはそのためです。しかし、パーティションありの場合、条件2において p200911 （以降）には1件もレコードが格納されていない状態であるため、logged_at が ‘2009-11-01′ （以降）のデータを挿入する場合はテーブルが空の場合と同じ処理時間になるのです。つまり、ログ情報のような日毎に蓄積されるデータを格納する場合、月別にパーティションを設定しておくことで、レコード数増加に伴い累積的に遅くなるデータ挿入処理の影響を一ヶ月分に抑えられることが分かります。

なお、ストレージエンジンを InnoDB にして同じ検証を行なってみましたが、MyISAM の場合と同様の結果になりました。（図2）

result_insert_innodb.png
図2 : データ挿入の性能検証結果 (InnoDB)

データ参照の性能検証

検証内容

データ参照の性能検証には、MySQL クライアントから下記2パターンの SELECT 文を1回発行し、それに要した時間を測定しました。

条件1
client_name 指定なしで11月分のログデータ参照する

SELECT * FROM logs WHERE logged_at >= '2009-11-01' \
AND logged_at < '2009-12-01';
条件2
client_name 指定ありで11月分のログデータ参照する

SELECT * FROM logs WHERE logged_at >= '2009-11-01' \
AND logged_at < '2009-12-01' AND client_name = 'client_1';

logs テーブルの事前条件は、データ挿入の性能検証（条件2）を行なったときの状態としました。つまり、1～10月の各月に1000クライアント×1000件分のログデータが、11月に1000クライアント×10件分のログデータが格納されている状態（計1001万件）です。

検証結果

結果は図3のようになりました。

result_select_myisam.png
図3 : データ参照の性能検証結果 (MyISAM)

条件1を見ると、パーティションありの方が処理時間が劇的に速いことが分かります。これはパーティションなしでは全レコード（1001万件）を参照しているのに対し、パーティションありでは p200911 のレコード（1万件）のみを参照しているためです。これはオプティマイザが SELECT 文中のクエリ logged_at >= ‘2009-11-01′ AND logged_at < ‘2009-12-01′ から判断した結果です。理論的には検索件数が1001万から1万に減るので処理時間も1/1000になって欲しいところですが、そうなっていないのはオプティマイザの判断処理等によるオーバーヘッドの影響かもしれません。

なお、SELECT 文の前に EXPLAIN PARTITIONS を付けることで、参照時にアクセスするパーティションを調べることができます。（これを見るとなぜか p200912 にもアクセスしていることが分かります。なぜそうなるのかは分かりませんが…）

mysql> EXPLAIN PARTITIONS SELECT * FROM logs
    -> WHERE logged_at >= '2009-11-01'
    -> AND logged_at < '2009-12-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: logs
   partitions: p200911,p200912
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10010000
        Extra: Using where

また、条件2を見ると、インデックス併用時にもパーティションありの方が処理時間が劇的に速いことが分かります。この場合、まずオプティマイザが参照に必要なパーティションを判断し、そこからさらに該当するパーティションのインデックス情報を使って参照するデータ数を絞り込んでいるようです。ここまでいくと、データ参照のコストがほぼゼロになりました。

なお、ストレージエンジンを InnoDB にして同じ検証を行なってみましたが、こちらも MyISAM の場合と同様の結果になりました。（図4）

result_select_innodb.png
図4 : データ参照の性能検証結果 (InnoDB)

まとめ

本エントリでは、パーティションドテーブルに対して実際にデータを挿入・参照することでパーティショニングの性能面を検証しました。その結果、今回検証した条件ではデータ挿入・参照のいずれにおいてもパーティショニングしない場合に比べて性能が向上することが分かりました。

このように、パーティショニングは上手に利用すればいいことずくめとなる機能です。本エントリがパーティショニング活用に役立てれば幸いです。

Tags: , ,

TrackBack URI

コメント