SNSやソーシャルゲーム、アドネットワークなどのシステムではいろいろなログ情報をDBに保存することもあると思います。
そのさい、日々増えつづけるデータやパフォーマンスをどの様にさばいていくかが重要になってきます。
今回はログ系のデータをMysqlでどのように運用していくか、をテーマにいくつかのノウハウをまとめました。
ログ系テーブルの特徴
ログ系のデータとは、つまり何かのアクションの履歴データのことです。
一般的にはこのような形になるかと思います。
CREATE TABLE `t_logs` ( `id` bigint(20) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL DEFAULT '0', `event_id` int(10) unsigned NOT NULL DEFAULT '0', `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
基本的にはどんどんInsertが発生していきます。そして要求に応じてそのデータを参照します。
時系列でデータは増えていきますから、運用が長くなればそれに比例してログの量も増えていきます。
1レコード単位で参照が必要なのであれば過去の参照に期限を設けるとか、
集約したデータが参照したいのであれば集計結果を別テーブルに保存しておくなど
どこかでデータを切り捨てる事を考えておく必要があります。
データサイズに注意する
上記のようにどこかでデータ捨てるということを決めたとしましょう。
しかしInsertされたログデータはどこかで削除しないとずっとテーブルに残り続けます。
アクセスログ系のデータの場合人気のソーシャルゲームやアドネットワークだと1日に数千万〜のPVが発生するので
あっという間に億単位のデータサイズになってしまいます。こうなると物理的な容量が問題になってきます。
こういった場合よく用いる方法が、深夜のバッチプログラムなどでDeleteする、ということなのですがこの規模になってくると、
Deleteは非常に重くなります。また、その間ログテーブルもロックされてしまいますのでこちらも注意が必要です。
Limitを使い、100件、1000件ずつ削除する方法もありますが、いずれInsertの方がスピードがうわまってきます。
innodb_file_per_tableでファイル分割
InnoDBでテーブルを運用する場合、そのデータはibdata1というバイナリファイルに蓄積されていき、
Mysqlで使われるすべてのInnoDBで共有されています。
このibdata1ファイルはデータが蓄積されるごとに自動拡張を続け、ひとたび成長してしまったデータファイルを小さくする方法はありません。
そのため深夜バッチなどでDeleteしてもディスクサイズはへりません。
innodb_file_per_tableオプションを設定すると、テーブルごとにデータファイルが作成されるようになります。
my.cnfファイルに設定します。
[mysqld] innodb_data_file_path=ibdata1:1G innodb_file_per_table
作成されるデータファイルは各スキーマのフォルダに格納され .ibd という拡張子になります。
こちらのファイルも要求に応じて自動拡張されますが、テーブルを削除するとファイルも削除されます。
そのためテーブルが大きくなってからでもディスクの空き容量を増やすことが可能になります。
パーティションを使う
Mysql5.1以降で実装された機能で、1つのテーブルを「ある規則」に基づいて分割し別テーブルのようにデータを格納させる仕組みです。
「ある規則」は4つに分類されますが、ログのデータ削除という意味でよく用いられるのが「RANGEパーティション」です。
前述のinnodb_file_per_tableオプションのもとで行うと、パーティション毎にもデータファイルが分割されます。
テーブルは以下のようになります。
CREATE TABLE `t_logs` ( `id` bigint(20) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL DEFAULT '0', `event_id` int(10) unsigned NOT NULL DEFAULT '0', `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`,`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (to_days(created)) (PARTITION p20120708 VALUES LESS THAN (to_days('2012-07-09')), PARTITION p20120709 VALUES LESS THAN (to_days('2012-07-10')), PARTITION p20120710 VALUES LESS THAN (to_days('2012-07-11')), PARTITION p20120711 VALUES LESS THAN (to_days('2012-07-12')), PARTITION p20120712 VALUES LESS THAN (to_days('2012-07-13')), PARTITION p20120713 VALUES LESS THAN (to_days('2012-07-14')), PARTITION pmax VALUES LESS THAN MAXVALUE)
実際のファイルはこのようになります。
-rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120708.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120709.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120710.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120711.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120712.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#p20120713.ibd -rw-rw---- 1 mysql mysql 98304 7月 10 11:36 2012 t_logs#P#pmax.ibd -rw-rw---- 1 mysql mysql 8666 7月 10 11:35 2012 t_logs.frm -rw-rw---- 1 mysql mysql 92 7月 10 11:35 2012 t_logs.par
この場合、pYYYYMMDD という名前のパーティションに、YYYY-MM-DDまでに作られたログデータが格納されます。
ここで一点注意が必要ですが、PARTITION節でしようされるカラムはPRIMARY KEYかそれに含まれている必要があります。
そのため、この例ではPARTITION節で使う ceratede カラムをPRIMARY KEYに含めています。
その他パーティションに関する詳しい内容はこちらを御覧ください。
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-management.html
このようにパーティションを利用していると、例えば2012-07-08のデータはもう要らないから削除しよう、というときに
mysql> DELETE FROM t_logs WHERE created <= '2012-07-08';
としていたものが
mysql> ALTER TABLE t_logs DROP PARTITION p20120708;
このように書けます。前者の場合は巨大なInnoDBであるほど処理に時間がかかり非現実的な方法でしたが、
パーティションを使うことで後者のようにまるでテーブルをDropするかのようにデータを削除できます。
また、パーティションには「刈りこみ」という機能がMysql5.1.6から実装されています。
パーティション刈りこみのコンセプトは「合致する値が存在し得ないパーティションはスキャンしない」といものです。
詳しくはこちらを御覧ください。
http://dev.mysql.com/doc/refman/5.1/ja/partitioning-pruning.html
MEMORYテーブルでINSERTスピードアップ
やはりINSERTの実行速度はSELECTに比べると圧倒的に遅いのですが、
使用するテーブルのストレージエンジンによってもINSERTの実行速度は変化します。
以下の2つのテーブルで簡単な実験をしました。違いはInnoDBかMEMORYか、です。
CREATE TABLE `t_data_inno` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `value` int(11) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `t_data_mem` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `value` int(11) DEFAULT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8
これらにデータをINSERTするためのPHPは以下のとおりです。
それぞれのテーブルに10000件のINSERTを実行します。
# insert_inno.php <?php $link = mysql_connect('127.0.0.1:3306', 'root', ''); mysql_select_db('test', $link); for ($i = 0; $i < 10000; $i++) { mysql_query("INSERT INTO t_data_inno (value) VALUES ($i)", $link); } mysql_close($link); ?> # cat insert_mem.php <?php $link = mysql_connect('127.0.0.1:3306', 'root', ''); mysql_select_db('test', $link); for ($i = 0; $i < 10000; $i++) { mysql_query("INSERT INTO t_data_mem (value) VALUES ($i)", $link); } mysql_close($link); ?>
これを実行すると以下のようになりました。
$ time php insert_inno.php real 0m3.983s user 0m0.138s sys 0m0.337s $ time php insert_mem.php real 0m1.392s user 0m0.130s sys 0m0.321s
サーバ環境やMysqlのパラメータによって変化するでしょうが、この環境では3倍速いことがわかります。
このMemoryテーブルをうまく使えばINSERTにより処理時間を短縮することができます。
では、どの様に使用するのがいいのか？一つの使用方法を紹介します。
最終的にログを保存するテーブルはInnoDBで作成し、ログを直接書きこむテーブルをMemoryで作成します。
図のようにMemoryテーブルを2つ用意し、プログラムはログをMemoryテーブルの一方に書き込みます。
Switcherにより書きこむMemoryテーブルは切り替えられ、書き込みが起こっていない方のテーブルから
InnoDBにデータをコピーし、次の切り替えに備えます。
もう少し詳しく見ていきましょう。
まずSwitcherですがDBに設定用のテーブルを用意し、その値を参照し書きこむMemoryテーブルを切り替える、というふうにします。
以下のようにテーブルを作成しデータを設定します。
CREATE TABLE `settings` ( `key` varchar(255) NOT NULL, `int_value` int NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB mysql> select * From settings; +----------+-----------+ | key | int_value | +----------+-----------+ | switcher | 1 | +----------+-----------+
ログを書き込むプログラムでは、この値を参照してINSERTするテーブルを切り替えるようにします。
PHPだとこういう感じでしょうか。
<?php /* Switcher の値を取得 */ $sql = "SELECT int_value FROM settings WHERE key = 'switcher'"; $result = $db->query($sql); $switcher = $result['int_value']; /* Switcher によってINSERT先を変更 */ if ($switcher == 1) { $sql = 'INSERT INTO t_logs_m1 VALUES(xxx, xxx, xxx)'; } else if ($switcher == 2) { $sql = 'INSERT INTO t_logs_m2 VALUES(xxx, xxx, xxx)'; } $db->query($sql); ?>
こうしておけばsettingsテーブルの値を変更するだけで、ログの書込み先を制御できます。
次は切り替えとログのコピーを自動化しましょう。
切り替えは settings テーブルのUPDATEで、コピーは t_logs へのINSERTで行います。
これらを一連に処理するためのストアドプロシージャを作成しましょう。
BEGIN
/** switcher を参照して分岐 **/
IF (SELECT int_value FROM settings WHERE key = 'switcher') = 1 THEN
/* 出力先のテーブルを切り替え */
UPDATE settings SET int_value = 2 WHERE key = 'switcher';
/* データをコピーしてテーブルを空にする */
INSERT INTO t_logs (SELECT * FROM t_logs_m1);
TRUNCATE TABLE t_logs_m1;
ELSE
/* 出力先のテーブルを切り替え */
UPDATE settings SET int_value = 2 WHERE key = 'switcher';
/* データをコピーしてテーブルを空にする */
INSERT INTO t_logs (SELECT * FROM t_logs_m2);
TRUNCATE TABLE t_logs_m2;
END IF;
END
このプロシージャを5分や10分毎にEventにて設定すれば自動でログの切り替えを行ってくれます。
ログ系テーブルのまとめ
最後に
いろいろとテクニックを紹介しましたが、どれもよく知られた方法だと思います。
またシステムの要件や環境によっていろいろ違ってくるのでだれにでも使えるわけではないですが、
だれかの助けになれば幸いですね。
