ソーシャルゲームのためのMySQL入門

by iwanaga | November 15, 2010
| #mysql

こんにちはこんにちは。最近お腹痛いばっかり言ってることで有名なiwanagaです。

DeNAは外部的にはプラットフォーム的な部分の方がフィーチャーされることが多いですが、実はソーシャルゲームの提供も行っています。怪盗ロワイヤルとか、どこかで聞いたことがあるのではないでしょうか。

僕はDeNAでソーシャルゲームが誕生した辺りからずっとサーバサイドを見てきましたが、そんな運用の中で自分が貯めてきた知見とかTIPSをご紹介したいと思います。 かれこれ10タイトル近くはレビューしたり運用したりしてるため結構言いたいことはいっぱいあるので、小出しにしつつ評判よければ次も書きます。

ソーシャルゲームのためのMySQL入門一覧

「MySQLの話か、なんだアプリ開発の自分には関係ないな」と思ったそこのあなた!今回僕がこういう記事を書いている趣旨は、むしろ開発側の人たちにこそもっといいMySQLの使い方を知ってもらいたいと思ったからです。データストア層を使いこなせてこそ真のアプリ開発者ですよね!

その前に、なぜMySQL?

世の中流行りは「NoSQL」とか言われてるみたいですが、DeNAでは携帯ソーシャルゲームという高トラフィックなシステムでもデータストア層にはMySQLをヘビーに使っています。なぜでしょうか。

それは僕がMySQLが好きだからです

嘘です。すいませんすいません。真面目に書きます。MySQLにはなんといっても「輝かしい実績」があります。10年を数えるレプリケーションシステム、高速・安全なトランザクショナルストレージエンジンInnoDB、そしてFacebookやGoogle、弊社のモバゲータウンをはじめとした大規模サイトでの実績。

今では枯れた技術感のあるmemcachedですら、先般mixiさんが遭遇したようなクリティカルなバグがあったりしました。その意味では大規模サイトでの実績充分、バクデータベースも豊富なMySQLは最も安定したデータストアであると言えます。NoSQLとかで話題になっているミドルウェアは確かに新しく、適切な使い方をすれば非常に有効だと思います。しかし、安定感や使い方という意味でMySQL程に枯れたものは存在しません。さらに弊社松信の言葉にもある様に「これから」も安心できるミドルウェアでもあります。

例えばその昔は「InnoDBは遅い」と言われていた時代もありましたが、今では十分に速く、弊社樋口が開発したHandlerSocket pluginの能力を見てもらえれば分かるとおり、うまく使えばInnoDBは十分に高速です。うまく使えば。。。

そう、この「うまく」の部分がなかなか難しいのです。特にソーシャルゲームは普通のSNSと比べて高トランザクションになりがちで、それでいてテーブルのサイズは猛烈な勢いで肥大化します。InnoDBを効率良く使いこなすためには、必然的にそのデータ構造を理解した上でスキーマを考えクエリを考えていく必要があります。

というわけで前置きが長くなりましたが、今回はInnoDBをソーシャルゲームで使う時にどういう風にテーブルを作ったらいいのか、ということを少しだけお話したいと思います。(MySQLの設定とかの話はまた今度でー)

ソーシャルゲームのテーブルの特徴

MySQLの設定パラメータを変更するだけで高速にできることは限られています。何よりもスキーマとクエリを効率的にすることが重要です。ソーシャルゲームでよく用いられるテーブルにはどういうタイプのモノがあるかを考えてみましょう。全てではないですが、よくあるタイプはこんな感じです。(一度でもRDBを使ってアプリ作ったことがない人には少々わかりにくいかも知れません><)

ユーザID単位で1つだけもつデータ

これは分かりやすいですね。例えばuser_idをプライマリーキーにして、ニックネームや経験値などのデータを保存する類です。

CREATE TABLE `user_data` (
  `user_id` int(10) unsigned NOT NULL,
  `nickname` varchar(40) NOT NULL,
...
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB

ユーザID1つで複数もつデータ

これは例えばユーザの持っているアイテムのデータなどです。user_idとitem_idの複合キーがプライマリーキーになる感じです。

CREATE TABLE `user_item` (
  `user_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`user_id`,`item_id`)
) ENGINE=InnoDB

ソーシャルグラフ的データ

これがソーシャルゲームならではのデータです。例えばあるユーザの友達のデータとか、あるユーザと別のユーザのやり取りを記録するデータとか。user_id-Aとuser_id-Bの複合キーがプライマリーキーになる様な感じです。

CREATE TABLE `user_relations` (
  `user_id` int(10) unsigned NOT NULL,
  `tgt_user_id` int(10) unsigned NOT NULL,
  `visit_num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`user_id`,`tgt_user_id`)
) ENGINE=InnoDB

ログ系のinsert中心のデータ

これは例えば何かのアクションの履歴データなどです。自分や他人からのアクションの履歴を表示したいときなどに活用すると思います。これはシーケンシャルな数字がプライマリーキーになり、それを他のテーブルに格納しておいたり、セカンダリインデックスで検索する形になると思います。

CREATE TABLE `action_log` (
  `id` bigint(20) unsigned NOT NULL,
  `date` timestamp NULL DEFAULT 0,
  `value` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

パラメータなどのマスターデータ

これは敵の出現確率とかアイテムの名前とか、主に運営側のみが更新するデータです。こういうデータは別にRDBに保持する必要はなくて、ファイルにまとめてアプリサーバにデプロイする運用もあると思いますが、バックエンドツールを使ってエンジニア以外の人でもパラメータが変更したいとかがある場合にはRDBに保持している方が楽ちんだと思います。

 CREATE TABLE `item_conf` (
  `item_id` int(10) unsigned NOT NULL,
  `item_type` tinyint(3) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
...
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB

意外と少ない!

もちろんこれら以外にいろんなデータはあると思いますが、多くのソーシャルゲームで共通しているのはここに当てはまるものが多いと思いますし、この辺のデータの扱い方が分かってくれば、これら以外のデータについても適切な扱い方のコツが分かってくると思います。

これらのテーブルについて、ソーシャルゲーム+InnoDBという組み合わせにおいてどういったアプローチを取るのがよいかという話をしたいのですが、全部やると多いので今回はまずログ系のテーブルについてフィーチャーしてみたいと思います。

ログ系テーブルの上手な使い方

いろんな欲求からログ系のテーブルを作りたくなることはしばしばあると思います。お金がらみのものが動くときの記録や、あるアクションの履歴をユーザ毎に見せたいとか。

「最近」のデータしか使わない工夫

ログ系のテーブルでは基本は新しくinsertされた行への参照がほとんどになる様に設計すべきです。何よりもこれが大事。ログ系のテーブルは放っておけばどんどんサイズが大きくなります。アクセスされるデータの量が昔のものまで満遍なく存在していると、buffer poolというメモリキャッシュに載せるべきデータが時間と共にどんどん増えていき、すぐにメモリから溢れてIOが発生します。IOは怖いです。まんじゅう怖いとは違います。マジで怖いです。

これにはサービス的な割り切りが必要になることもあります。「2週間より前のデータは見れません」という仕様にしてしまえば、サービス的に2週間より前のデータは不要になるはずですが、「サービス開始時点から全て見れます」という仕様にしてしまうと、結構大変です。

下手にこんな仕様にしてしまうと、Twitterさんがやっている(と言っている)様に、いずれ時系列でサーバ自体を分ける様な運用が必要になってしまうでしょう。

とは言え、こういう背景を知らない人が普通の感覚で言ったら「全部見れて当たり前」という風になってしまうもの。ここはエンジニアが費用対効果を良く検討して、どういう仕様が適切なのかを考えてあげる必要があると思います。

次なる敵は「データサイズ」

さて、最近のデータだけあればよい、という設計ができたとしましょう。しかし、一度insertしてしまったデータは消さない限り基本的にはずっとDBには残り続けてしまいます。極端な例で言えばアクセスログの様に全PVで1レコードをこのテーブルに入れてしまうと、ソーシャルゲームの様にPVの多いサービスではあっという間に数億件とか数十億件とか、そういうサイズになってしまいます。

buffer pool的にはアクセスのないデータはメモリから追い出されるのであまり性能的な劣化はないかも知れません。しかしこのままでは物理的な「容量」がいずれ問題になってしまいます。DISKも無限ではありません。

そこでよくある方法としては、古いデータをdeleteで消す(パージする)というやり方です。先程も書いた様にこの手のテーブルで必要なデータは「最近」のものであることが多いので、思い切って消してしまうという戦略は当然アリです。ただし注意が必要で、deleteはクソ重いです。大切なので2回言います。deleteはクッソ重いです。ログ系のテーブルの場合、わざわざbuffer poolに乗ってないデータを読みだして消す必要があるため、ゲロ遅いです。

パーティションを使おう!

仕方ないので、MySQL 5.0の頃はパージするスクリプトを作って夜中のアクセスが少ない時間に毎日回してせっせと消すといった作業が必要でした。

しかし、MySQL 5.1からは念願の「パーティション機能」が追加されていますのでこれを使わない手はないでしょう。

パーティションを知らない人のために説明しますと、簡単に言えば1つのテーブルを「ある規則」に従って別のテーブルの如く格納させる仕組み、と言えると思います。「ある規則」は色々設定できますが、データパージという意味でよく使うのは「RANGEパーティション」といって、あるカラムの値の幅で切るというやり方です。言葉の説明だけだと分かりにくいですが、こんな感じ。

CREATE TABLE `action_log` (
  `id` bigint(20) unsigned NOT NULL,
  `date` timestamp NULL DEFAULT 0,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(date))
(PARTITION p20101004 VALUES LESS THAN (UNIX_TIMESTAMP('20101004')),
 PARTITION p20101011 VALUES LESS THAN (UNIX_TIMESTAMP('20101011')),
 PARTITION p20101018 VALUES LESS THAN (UNIX_TIMESTAMP('20101018')),
 PARTITION p20101025 VALUES LESS THAN (UNIX_TIMESTAMP('20101025')),
 PARTITION p20101101 VALUES LESS THAN (UNIX_TIMESTAMP('20101101')),
 PARTITION p20101108 VALUES LESS THAN (UNIX_TIMESTAMP('20101108')),
 PARTITION p20101115 VALUES LESS THAN (UNIX_TIMESTAMP('20101115')),
 PARTITION over VALUES LESS THAN MAXVALUE)

idはログのユニークな番号でこれについてはまた後述します。pYYYYMMDDというパーティションはYYYYMMDDまでのレコードが格納されるように設定されています。なおパーティションの細かい文法とかはMySQLのドキュメントを参考にして下さい。

一つ注意する必要があるのは、現在の仕様ではパーティションに切りたいカラム(↑ならdate)は全てのunique indexについてその一部である必要があります。そのため、action_logはidカラムだけで完全にユニークにできるのですが、敢えてPKにdateが入っています。

さて、こうしておくと例えば20101004のデータはもう不要なのでパージしたいと思ったときに、5.0時代は


delete from hoge_log where date < XXXXXXXX;

とか書いてたものを(本当はこんなの実行したら死にますが)


alter table hoge_log drop partition p20101004;

とするだけで、あたかもdrop tableするかの如く、高速にパージすることができます。データが巨大なInnoDBを触ったことがないと実感がわかないかも知れませんが、これは本当にありがたい機能です。もはやちまちまdeleteするスクリプトを書く必要もなく、スピード調整に失敗してレプリ遅延に泣かされることもなく(しかも大抵夜中><!)、テーブルサイズをある程度のサイズで管理することができるようになるわけです。DBAにはヨダレものですね!

実はパーティションには他にも恩恵があります。それがプルーニング(刈り込み)と呼ばれる機能です。さっきのテーブルをselectする際に、dateの条件を与えてあげると、場合によっては見る必要のないパーティションが出てくると思いますが、それをオプティマイザが判断してくれて必要なパーティションのみ見るようになってくれます。1週間単位の集計を行いたいとか、サービスからの参照は2週間前までのみでよいとかを明示的にwhere句で与えてあげることで、より効果的にテーブルを使うことができます。

プルーニングが効いているかどうかは「explain partitions」を実行することで分かります。

mysql> explain partitions select * from hoge_log where id > XXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101004,p20101011,p20101018,p20101025,p20101101,p20101108,p20101115,over
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: *********
        Extra: Using where

mysql> explain partitions select * from hoge_log where date > XXXXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101108,p20101115,over
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: **************
        Extra: Using where

下のクエリではwhere句にdateを入れていて、刈り込みが効いていることが「partitions」のカラムを見ると分かりますね!

なんか通販みたいですが、パーティションにはさらにさらに特典があります。それは削除だけでなくINSERTについても性能が向上するということです。前述の様にログ系のテーブルはINSERT中心になるのでこれもおいしい恩恵ですね。RANGEパーティションによるINSERT性能の改善効果については、弊社松信の発表資料(英語ですが)も参考にしてください。http://www.mysqlconf.com/mysql2009/public/schedule/detail/6661(ここからたどれるPDFのp.44-46)

idはどうするか?

さて、ログ系のテーブルのもう一つのトピックとしては「id」をどうやって決めるか、というのがあります。素朴にやるなら、idカラムにauto_increment属性をつけてしまえば、MySQLが勝手にユニークなidをつけてくれるのでお手軽ですね。

ところが、規模が大きくなり秒間クエリ数が増大してくると困ったことになってきます。一つはMySQL 5.0以下のInnoDBを使っている場合には、弊社松信が書いているこちらのエントリにもあるように、並列性が低いという問題があります。こちらの解決の1つの方法はMySQL5.1以上のInnoDBを使うことで解決されます。

もう一つの大きな問題としては、このテーブルを2つ以上のデータベースに分割(Sharding)したくなった時です。この場合、複数データベースにまたがってユニークなidを確保するにはauto_incrementだけではちょっと面倒です。

そこでモバゲータウンではidだけを払い出すテーブルを1つ作って1レコードだけinsertして、updateにより採番し、実際のテーブルには採番したidを指定してinsertするという手段を用いています。

CREATE TABLE `seq_log` (
  `id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM
INSERT INTO seq_log VALUES (0);
my $sth = $dbh->prepare_cached("update seq_log set id=LAST_INSERT_ID(id+1)");
$sth->execute();
return($dbh->{'mysql_insertid'});

Perlでユニークなidを引っ張るならこんな感じ

この方式であれば、Shardingした系統がいくつになっても常にユニークなidが採番できるため、アプリの作りもデータの持ち方もシンプルにすることができます。なお、MyISAMのテーブルロックがあるため同時並列性はないですが、現状のモバゲータウンのトラフィックであってもこの方式で特に問題は発生していません。ある程度の規模が見込まれて、Shardingすることが予想されるのであれば、こういった方式がおすすめです。

ログ系テーブルのまとめ

というわけでまとめ

  • なるべく最新のデータへのアクセスだけで済む様に設計する
    • 全データを永遠にDBに残すとかの仕様にしない
  • MySQL 5.1以上オヌヌメ
  • RANGEパーティション使って、古いのを簡単に消せる様にする
    • 刈り込みも使える時は有効活用
  • idの払い出しは専用の採番テーブルを使うか、5.1以上のauto_increment
    • Shardingする場合には、採番テーブルが便利

実は、今回紹介した以外の方法でログ系のテーブルを一定程度のサイズに押さえこむテクニックもあるのですが、それはこの記事が人気が出たら書くことにしましょう^^

おわりに

今回は以上です。列挙しといて1種類しか話しなくてすみませんすみません。また、言葉ばっかりで分かり辛く、ベンチとか正確に取ってるわけじゃないのでいい加減な話ですが、「モバゲータウンではこうやってるよ」的な話として小耳に挟んで頂ければ幸いです。

buffer poolとかInnoDBのデータ構造とか、そういう話も今回は省略してしまいました。その辺の仕組みは小難しい本を読むのが一番なのですが、忙しいあなたのために3分クッキングしたいなとかも思ってたりします。

で、なんで僕がこんなことやってるかと言うと、丁度約1年前には僕はselect文すら満足に書けない人だったんですが、1年もやればこんだけ色々できるようになるんだよー、インフラって別に怖くないよー、みんなインフラも興味持とうよー、ということが伝えたいのであります。

ではでは。

God bless your MySQL!