InnoDBのすゝめ（仮）

InnoDBの特性など考慮しつつ、ゲーム向けのDBやSQLを設計する上でのヒントや、注意するポイントなどです。

Transcript

1. Copyright © GREE, Inc. All Rights Reserved. InnoDBのすゝめ（仮） Takanori Sejima
2. Copyright © GREE, Inc. All Rights Reserved. 自己紹介 • わりと MySQL のひと • 3.23.58 から使ってる • 前職の頃、10年以上前は、 MMORPG の DB の設計などもやってました • むかしは Resource Monitoring も力入れてやってた • ganglia & rrdcached の（たぶん）ヘビーユーザ • というわけで、自分は Monitoring を大事にする • 一時期は Flare という OSS の bugfix などもやってた • さいきんは、ハードウェアの評価をしている時間が長かった • たまに Linux の TCP プロトコルスタックについて調べたりもする 2
3. Copyright © GREE, Inc. All Rights Reserved. • （個人的な見解ですが） MySQL が広く普及した理由の一つに、 InnoDB とレプリケーションが、かなりむかしから良くできていた、というのが挙げら れると思います。 • InnoDB についての知識が深まると、他のデータストアを理解していく上 でも、プラスになるかなと思いましたので • 本日は InnoDB などに関する話をしたいと思います。 本日のお話 3
4. Copyright © GREE, Inc. All Rights Reserved. • まだ読まれたことのない方は • 次の資料もあわせて読んでいただけると、よりわかりやすいかと思います • さいきんの InnoDB Adaptive Flushing （仮） • できればこちらひととおり読んでいただけると、より理解が深まるかと思います • https://www.slideshare.net/takanorisejima/ • MySQL について、会社の blog にもときどき書いています • https://labs.gree.jp/blog/author/sejima/ 本日のお話の補足資料 4
5. Copyright © GREE, Inc. All Rights Reserved. • はじめに • ゲームの DB とゲーム以外の DB は、考え方を変えて良い • sharding のメリット • MySQL の機能を活かして、 Transaction はほどほどに考える • MySQL 以外のデータストアやキャッシュと、うまく組み合わせる • どのようにしてテーブルをコンパクトに保つか • システム全体の負荷試験の前に、まずは単体で性能評価。 • monitoring は重要。最適化はその後。 • I/O を最適化する • mutex の競合を避ける Agenda 5
6. Copyright © GREE, Inc. All Rights Reserved. はじめに 6
7. Copyright © GREE, Inc. All Rights Reserved. • これからお話する内容は、ゲーム向けの DB を設計する上でヒントになり そうなことを、個人的な見解の上に述べているのであって • 基幹系システムや金融系のシステムなどを前提にした内容では有りませ ん。 • 弊社の DB の設計が、すべてこれに基づいているわけでもありません。 • また、わたしはゲーム開発者ではなく、インフラエンジニアですので、 MySQL の観点から見て、このように設計したほうが望ましいのでは、と いったお話をさせていただきます。 • スケーラビリティ優先の設計、かつ、LAMP 構成が前提となっております。 注意事項 7
8. Copyright © GREE, Inc. All Rights Reserved. ゲームの DB とゲーム以外の DB は、 考え方を変えて良い 8
9. Copyright © GREE, Inc. All Rights Reserved. • 私は、次のようなことを、最低限クリアすべきラインではないかと考えてい ます。 • まずはログインできること • ユーザの不利益にならないこと • 正常に遊んでいたにも関わらず、データが欠損するという事態は避けること。非常時に は、補填可能な仕組みがあること • 正常に決済処理できること • サービス開始時やイベント開始時、あるいはプロモーション期間中など、高 負荷状態になり、ログインさえできなくなるようなことが、ゲームには起こり えます。これは、他の業種だと、そこそこ珍しい事態ではないでしょうか。 原則 9
10. Copyright © GREE, Inc. All Rights Reserved. • 例えば、 MMORPG の RvR ( Realm vs Realm ) で、範囲攻撃魔法を 使って、大量のプレイヤーキャラにダメージを与えた場合、それらのダメー ジは、すべてトランザクション管理されている必要があるでしょうか？ • 大規模戦闘をやってる最中に、データストアのサーバで予期せぬ故障が 発生した場合、クラッシュリカバリできる方が良いでしょうか？それとも、大 事を取って正常な状態にまで安全に復旧し、そのバトルは仕切り直しにし た方が良いでしょうか？どちらが、ユーザにとって公平性が高いでしょう か？ • 仕切り直しにすることが仕様として許容されるなら、その方がシステムのラ ンニングコスト下げられるでしょうし、システムの設計もシンプルになるので はないでしょうか。 性能要件や、整合性の要件が異なる 10
11. Copyright © GREE, Inc. All Rights Reserved. • 一方、決済処理に関わる部分は、データの整合性を求められます。 • 決済処理のように、かっちりトランザクション管理したい部分と、仕切り直し が許されるゲームの一機能は、少なくとも、別々のデータベースとして切り 離せるようになっていたほうが良いでしょう。 • 最終的に、守るべきはユーザのデータや体験なので • 仕様も踏まえて守るのか、しっかりコストをかけて堅牢強固な構成にして守 るのか、機能ごとにバランス良く検討すれば良いのではないでしょうか。 機能ごとに、整合性の要件を考える 11
12. Copyright © GREE, Inc. All Rights Reserved. sharding のメリット 12
13. Copyright © GREE, Inc. All Rights Reserved. • 私の経験を過去10年ほど振り返ってみると • ゲームがある程度の規模になると、 master の DB のデータをかき集め ると、 TiB クラスに達します。 • さいきんは NVMe の容量も増えてきましたし、パブリッククラウドを活用す ると、一本の block device に収まるかもしれませんが。 • ゲームの用途ですと、 TiB クラスのデータベースに対して、ものすごい勢 いで SQL が飛んできます。 • 大きくなるのがわかっているのであれば、はじめから sharding しておい た方が無難、というのがあります。 ある程度の規模になると、分割せざるを得ない 13
14. Copyright © GREE, Inc. All Rights Reserved. • 垂直分割する際は、局所参照性と整合性を意識した方が良いです • アクセス頻度が高いデータと、そうでないデータを切り分ける • 局所参照性を考慮して、アクセス頻度が高いデータをなるべく小さくし、 InnoDB の buffer pool のヒット率を上げるようにする • かっちりトランザクション管理したい部分を切り出す • 整合性を保ちたい決済処理などは、整合性を保ちやすい範囲で別のデータベースに切り出 す • だがしかし、そうであっても、データベースを垂直分割しすぎない • 一人のユーザのデータが多数のデータベースに分割されればされるほど、整合性を保つの が難しくなっていく • そこで、ユーザ ID による水平分割が有効な手段となっていきます。 • ユーザごとにアクセスするデータベースが異なっていても、一人のユーザ のデータを保存しているデータベースの数が少なければ、データベース間 の整合性をとりやすくなります。 • ただし、ユーザ間で直接やりとりできるデータがあると、難しくなります。 垂直分割と水平分割 14
15. Copyright © GREE, Inc. All Rights Reserved. • MySQL における database は、その database 内の table に対応す るファイル（*.ibd）を含む、ディレクトリとして扱われます。 • 一つの Transaction は一つの Connection に紐付いており、一つの Connection は一つの database に紐付けると、管理しやすいです。 • Transaction:Connection:database = 1:1:1 ですね。 • よって、一人のユーザの Transaction は、一つの database 、一台の master に集約できているのが望ましいです。 • ゲーム内の有償ポイントを扱うような table 群は、ユーザ ID 単位で水平 分割しつつ、一つの database の中に集約できていると、sharding とト ランザクション管理を両立しやすい気がします。 補足・ 決済処理は database を分割しすぎない 15
16. Copyright © GREE, Inc. All Rights Reserved. • ざっくりいうと、 InnoDB の表領域（ *.ibd ）の内部は、 INDEX によって 管理されています。primary key をキーにした clustered index か、 primary key を値として持つ secondary index か、になります。 • （厳密に言うと、Spatial index などもあるのでしょうが） InnoDB の index はだいたい B+Tree という構造をとっていて、ゲーム用途で InnoDB を扱う場合、おそらく B+Tree 以外は意識することはほぼない でしょう。 • Tree というからには、更新していると leaf の split や merge が発生し ます。場合によっては、より広範囲に Tree の更新が発生することも有り えます。 InnoDBから見たときの分割のメリット・その１ 16
17. Copyright © GREE, Inc. All Rights Reserved. • InnoDB のソースコード中で index->lock と呼ばれるロックがありま す。ツリー構造を更新する際などに、 index->lock は取得されます。 • MySQL5.7 以降、ツリー構造更新時のロックの競合は改善した のですが • そもそも、ユーザ ID をベースに table が予め水平分割されていれば、 index->lock の競合が発生しにくくなると期待できます。 • また、 secondary index 更新時の負荷も、 table のサイズに比例して 低くなると期待できます。 • MySQL の master の数をあまり分割していなくても、table を分割して おくことは、InnoDB 的にメリットが発生しうるのです。 index->lock 17
18. Copyright © GREE, Inc. All Rights Reserved. • table が分割されていると、 ALTER TABLE や TRUNCATE TABLE の 実行時間が、サイズに比例して短くなります。 • ALTER 対象の table が小さいと、 ALTER のために必要なディスクの空 き容量も少なくなりますし、 innodb_online_alter_log_max_size といった上限 にも引っかかりにくくなります。 • それらの table が複数の master に分散配置されているのであれば、同時に ALTER して作業時間を短縮することも容易になります。 • 現状、一つの ALTER TABLE は、マルチスレッドで実行して実行時間を短縮できるわけではあり ません。シングルスレッドで実行されるので、実行時間は ALTER 対象の table のサイズによると ころが大きいです。 • 大規模なゲームでデータベースが数 TiB 単位に達するとわかっているならば、 個々の table は大きくなりすぎないよう、適切に分割しておくことが長期運用して いく上で望ましいでしょう。 InnoDBから見たときの分割のメリット・その２ 18
19. Copyright © GREE, Inc. All Rights Reserved. • かつて MySQL は JOIN も subquery もあまり最適化されていなかった のですが、近頃はかなり改善されました（と聞きます）。 • JOIN 自体は RDBMS の重要な機能ですし、分析用途ではとても便利な 機能だと思います。 • しかし、ゲームのワークロードは分析とは異なるので、「分析するときは JOIN もするけど、ゲームの中では極力使わない」と割り切って良いので はないでしょうか。 JOIN を諦めることで、 sharding の自由度が上がり ます。 • MySQL 5.7 以降、 Multi Source Replication という、複数の master とレプリケーションするための機能が追加されました。その機能で 分析用 slave を作って、そこで JOIN するのもアリだと思います。 sharding のために、 JOIN を諦める 19
20. Copyright © GREE, Inc. All Rights Reserved. • Tencent Games さんから patch が寄贈され、 MySQL 8.0.12 で INSTANT ADD COLUMN という機能が追加されました。 • Oracle Open World 2017 のセッションで、 Tencent Games の方がこの patch などについての説明され、その後 MySQL 8.0 に取り込まれました。 Oracle Open World 2018 では、Booking.com など様々な企業の DBA に、 INSTANT ADD COLUMN は絶賛されていました。 • Tencent Games さんがこの patch を書いた理由はいくつかあるそうで すが、「カラム追加のためにメンテナンス入れるにしても、メンテ時間を短く したい」「カラム追加のために大量のディスクスペースが必要になるのが厳 しい」といったことを仰られてました。 • やはり、ゲーム業界では、長期運用していると、 ALTER TABLE が一つ の課題となりうるのです。 補足・ INSTANT ADD COLUMN について 20
21. Copyright © GREE, Inc. All Rights Reserved. MySQL の機能を活かして、 Transaction はほどほどに考える 21
22. Copyright © GREE, Inc. All Rights Reserved. • mysql_affected_rows() という API を活用すると、いろいろメリットが あります。 PHP などでも使えるので、ぜひ活用してください。 • 例えば、 SQL だと次のような書き方ができます。 • UPDATE user_data SET gold=gold-100 WHERE user_id=? AND gold>=100; • （デフォルトの挙動だと） mysql_affected_rows() が１の場合、実際に 更新された行が１行ということなので、 -100 されたことが保証されます。 • 一方、mysql_affected_rows() が 0 の場合、更新できなかったという ことで、そのユーザの gold が足りなかった、と判断できます。 mysql_affected_rows() をうまく使う 22
23. Copyright © GREE, Inc. All Rights Reserved. • duplicate-key error がログに落ちたら alert を上げたいところですが、 「 duplicate していたら update する」といった設計がしたいこともありま す。 • そういった場合、 INSERT ... ON DUPLICATE KEY UPDATE を使い たいと相談されるんですが、私は INSERT IGNORE … と mysql_affected_rows() を組み合わせて使うことを推奨しています。 • 「初回だけ INSERT して、後はひたすら UPDATE しまくる」という設計な らば、 UPDATE してから mysql_affected_rows() を実行し、更新が 空振ったかを確認するのでも良いでしょう。 • 何度も UPDATE を実行するのであれば、 INSERT … ON DUPLICATE KEY UPDATE より、単なる UPDATE と mysql_affected_rows() の組み合わせにでき ないか、検討しても良いでしょう。 INSERT IGNORE と組み合わせて使う 23
24. Copyright © GREE, Inc. All Rights Reserved. • ここで InnoDB の INSERT ... ON DUPLICATE KEY UPDATE に関 するソースコードを読んでみましょう • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/handler/ha_innodb.cc#L7543 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/handler/ha_innodb.cc#L7629 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0mysql.cc#L1678 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0mysql.cc#L1685 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0mysql.cc#L1474 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0mysql.cc#L1556 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3597 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3683 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3484 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3517 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3378 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3395 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3378 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3263 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3168 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L3217 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L2771 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L2912 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L1852 • https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/row/row0ins.cc#L1946 補足・ロックの粒度や範囲を意識する 24
25. Copyright © GREE, Inc. All Rights Reserved. • と、いうように • InnoDB のレイヤーで INSERT を みていくと、 secondary index を INSERT する際、 INSERT ... ON DUPLICATE KEY UPDATE だと exclusive lock に、 そうでない場合は shared lock になる場合がある ので、 INSERT ... ON DUPLICATE KEY UPDATE しない方が、 lock のコストが軽い場合があると考えられます。 補足・ロックの粒度や範囲を意識する 25
26. Copyright © GREE, Inc. All Rights Reserved. • 一人のユーザのデータを複数のテーブルに分散配置し、一回の request ごとにそれぞれにデータを書き出す際、デッドロックを回避しつつ整合性を 保つのは、なかなか大変です。 • 対策として、複数のテーブルを更新する前に、重要な更新は、ログテーブ ルに更新内容を書き出すという手法が考えられます。 • 例えば、イベント期間限定でドロップしたレアアイテムは、サーバが hostdown したとし ても、ユーザとしては巻き戻ってほしくないものです。そういった重要な記録を保存するた めに、事前にログ情報を書き出すテーブルを定義しておいて、更新処理の最初にログを INSERT する手法が考えられます。更新処理の途中でサーバがクラッシュしてしまった 場合、ログと突合して補填するといった対応も実施できるでしょう。 • MySQL5.7 以降、 JSON 型のカラムがサポートされています。ログを書 き出すのに、 JSON 型は相性が良いかもしれません。 垂直分割したときは、ログテーブルを検討する 26
27. Copyright © GREE, Inc. All Rights Reserved. • 例えば、ゲーム内の無償ポイントを使って交換できるアイテムがあったとし ます。 • 無償ポイントとユーザのアイテムが別々の DB 、別々のサーバ上で管理 されていた場合、更新処理の途中で DB が hostdown した場合、整合 性を保つのが難しくなります。 • がんばってトランザクション管理することもできますが、次のようなフローに すると、後から補填しやすくなります。 • 1. 一連の更新処理についてログテーブルに INSERT する • 2. アイテムを交換する • 3. 無償ポイントを消費する ログテーブルの使いどころ 27
28. Copyright © GREE, Inc. All Rights Reserved. • InnoDB のようにトランザクションセーフな RDBMS にはよくある実装な のですが、 InnoDB はトランザクションの内容をまずは Redo ログ (ib_logfile*) に書き出して、 その後でゆっくり表領域（ *.ibd ）に反映さ せています。 • ものすごくざっくり言いますと、一連のトランザクションをログに書き出すと いうのを、アプリケーション側で実装しているのが、先程の例になるわけで す。 補足・InnoDB も内部的にログを持っている 28
29. Copyright © GREE, Inc. All Rights Reserved. MySQL 以外の データストアやキャッシュと、 うまく組み合わせる 29
30. Copyright © GREE, Inc. All Rights Reserved. • memcached など揮発性のキャッシュにデータを保存する場合、 再構築 可能なデータのみをキャッシュするべきです。 • DB を垂直分割していた場合、ユーザ１人のデータを複数のテーブルに分 割して保存していた場合、何度も DB を参照する必要が出てくるので、そ ういったデータを合成して、 memcached などのキャッシュに積んでおく と良いでしょう。 DB を複数回参照するのが、 memcached への get 一回で済ませられるようになります。 • ユーザのデータは、ものによって更新頻度がまるで違います。可能であれ ば、更新頻度を考慮して、キャッシュを分割しておくと効果的かもしれませ ん。ただ、ユーザのアイテムデータなどは整合性を保ちたいので、不整合 を避けたいデータの集合は、キャッシュを分割しないほうが無難でしょう。 垂直分割されたデータをまとめ、キャッシュする 30
31. Copyright © GREE, Inc. All Rights Reserved. • すべてのユーザから参照されるキャッシュデータを、単一の key で memcached にキャッシュすると、特定の node に参照が偏ってしまうの で、スケールしません。 • そのキャッシュが read only で複製可能なものであれば、次のように key を設計すると良いでしょう • cache_key_${N} • N は 0 ～ 9 などとし、ユーザ ID の剰余などで分散させる。 • 複数の memcached に重複してキャッシュを持たせることで、複数の node に参照を分散させることが可能になります。 補足・ hash などで分散しているキーの扱い 31
32. Copyright © GREE, Inc. All Rights Reserved. • ゲームに限らず多くのサービスでは、ピークタイムとそうでない時間帯で、 アクセスしているユーザの数が異なります。 • また、ユーザによってアクセスしている時間帯は異なるので、すべての ユーザのデータが同時にキャッシュに載っている必要は、必ずしもないで しょう。ログイン中のユーザのデータが、キャッシュから参照できれば。 • 例えば、ログインする際はディスクからデータを読んでやや重かったとしても、ゲームを中 断するまでの間、キャッシュから溢れなければ、快適に遊んでもらえるでしょう。 • 最も多くのユーザがアクセスしているピークタイムに、キャッシュのヒット率 が高いのであれば、すべてがメモリ上に展開されていなくても、快適に遊 べるのではないかと思います。 補足・ すべて buffer pool に載せなくてもいい 32
33. Copyright © GREE, Inc. All Rights Reserved. • 弊社で昔からあるゲームでは、 PvE でレイドボスの HP を KVS に保存 し、 HP が 0 になったとき、その結果を MySQL に保存するということをし ていました。 • 更新性能が高くスケーラブルなデータストアがあるならば、そこにテンポラ リデータを保存するのも一つの選択肢かもしれません。雑に考えると、 key がハッシュで分散できるものでしょうか。 • ただ、そのテンポラリデータに書き込んだ内容が fix した時点で MySQL に保存しておけば、後日、調査などがしやすくなって利便性が高まるかと 思います。 • ただ、さいきんの InnoDB もハードウェアも進化が目覚ましいので、ほと んどのケースでは、もはや InnoDB で解決するのでは？という気もしま す。 テンポラリデータを、別のデータストアに書く 33
34. Copyright © GREE, Inc. All Rights Reserved. • 近年、パブリッククラウド事業者は、RDBMS 以外のスケーラブルなデータ ストアを、マネージドサービスで提供していたりします。 • そういったものを採用したゲームの事例はいくつもあって、弊社にも、そう いった事例はあります。 • ただやはり、ワークロードの相性というものはあるようです。 • たいへんざっくりいうと、ソロプレイのゲームであれば、そういったデータス トアとの相性が良好なように思われます。しかし、 PvP や PvE 、 GvG な どのような、複数のユーザ間でデータのやりとりをし合うような設計のゲー ムであれば、 RDBMS が使えると、何かと作りやすい印象があります。あ くまで個人的な見解ですが。 • 何事も、そういった使い分けは重要でしょう。 補足・ InnoDB に向いているもの 34
35. Copyright © GREE, Inc. All Rights Reserved. どのようにして テーブルをコンパクトに保つか 35
36. Copyright © GREE, Inc. All Rights Reserved. • 巨大なテーブルを扱うより小さいテーブルを扱うほうが、何かと SQL は軽 くなります。 index を外したときの性能的なペナルティも小さくなります。 • ただ、あまりにも大量の *.ibd を扱うのは、ファイルシステム的にも重いで す。 • 程々に分割しつつ、テーブルを小さく維持できるように、削除できるデータ は定期的に消していった方が良い、ということになります。 • イベントのタイミングや月次で CREATE TABLE し、メンテナンスなどのタ イミングで DROP TABLE などするのは、良い考えだと思います。 テーブルは程よく小さい方が良い 36
37. Copyright © GREE, Inc. All Rights Reserved. • そこそこのスペックで良いので、バッチサーバや調査用に、専用の slave を一台用意しておくことをオススメします。 • 例えば、一年間の受取期限のあるプレゼントボックスであれば、定期的 に、一年以上前のデータを削除したいでしょう。 • そういったときは、バッチサーバ専用の slave で次のような SQL を投げ て該当する id を収集し • SELECT id FROM table_name WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR); • master には、 created_at ではなく、 id 指定で少しずつ DELETE を 発行すると、比較的 DELETE のコストが軽くなります。 • 深夜など、ピークタイムを外して、このようなバッチを実行するとよいでしょ う。 サービスから参照されない slave を用意する 37
38. Copyright © GREE, Inc. All Rights Reserved. • primary key を指定して更新するのと secondary index を指定して更 新するのでは、 primary key 指定の方が、ロックの競合が発生しにくく なります。 • 極力、 secondary index は slave へ SELECT を投げるときに使うよう にして、 master に対しては、 primary key 指定で更新を発行するよう にするとよいでしょう。 • InnoDB は primary key 指定で SELECT し、 primary key 指定で 更新するのが、処理としては最も軽くなります。 補足・secondary index での更新を避ける 38
39. Copyright © GREE, Inc. All Rights Reserved. • ゲームのマッチングやセッション情報など、分単位で期限切れとなるけれ ど、柔軟に WHERE 句で条件を指定して参照したくなるデータがあったり します。 • そういった場合、例えば、 session_00, session_01, session_02 と いうように、同じテーブル定義で３つ以上のテーブルを作成し、時間ごとに ローテーションして使うと良いでしょう。 複数のテーブルをローテーションして使う 39
40. Copyright © GREE, Inc. All Rights Reserved. 1. 毎時00分～19分の間、 INSERT は session_00 に対して行う。参照は session_0{2,0} 双方に対して行う a. この間、 session_01 にアクセスされないので、cron などで TRUNCATE session_01; を実行し、 session_01 を初期化する 2. 毎時20分～39分の間、INSERT は session_01 に対して行う。参照は session_0{0,1} 双方に対して行う a. この間、 session_02 にアクセスされないので、cron などで TRUNCATE session_02; を実行し、 session_02 を初期化する 3. 毎時40分～59分の間、INSERT は session_02 に対して行う。参照は session_0{1,2} 双方に対して行う a. この間、 session_00 にアクセスされないので、cron などで TRUNCATE session_00; を実行し、 session_00 を初期化する ローテーションして使う例 40
41. Copyright © GREE, Inc. All Rights Reserved. • 協力プレイなどのマッチングは、複雑な条件を指定したくなるものです。そ うなると、有効な INDEX を張るのが難しくなります。 • INDEX を効かせるのが難しくなると、 Rows_examined が大きくなりが ちですが、テーブルをコンパクトに保てるなら、それでもなんとか動いたりし ます。 • テーブルが小さいと TRUNCATE のコストも軽くなります。 TRUNCATE するのが難しいほどテーブルが大きいなら、先程の例にあるように、 slave で primary key を取得して、少しずつ master に DELETE を 発行しても良いでしょう。 • また、クエストの難易度など、テーブル分割に使えそうな要素があれば、そ れに基づいてテーブルをさらに分割しても良いでしょう。 ローテーションすれば TRUNCATE しやすい 41
42. Copyright © GREE, Inc. All Rights Reserved. • テーブルをローテーションする代わりに、 DROP PARTITION が使える のでは？という考え方もあるでしょう。 • 私は、分析用途以外では、partitioning の使用は推奨していません。特 に、更新頻度が高いテーブルでの使用は推奨していません。 • 具体的には、 partition をまたいで AUTO_INCREMENT のカラムが定 義されていると、複数の partition に INSERT するとき、ロックの競合が 発生しやすくなったりします。 • MySQL のパラメータを変更して軽減できなくもないのですが、アプリケーション側でテー ブルをローテーションして使う方が、ロックの競合は減るでしょう。 • partition を使う場合は primary key の設計にも制限があるので、 MySQL の OLTP 用途では、あまり使わなくても良いのではないでしょう か。 補足・ partitioning は制限がある 42
43. Copyright © GREE, Inc. All Rights Reserved. システム全体の負荷試験の前に、 まずは単体で性能評価。 43
44. Copyright © GREE, Inc. All Rights Reserved. • リリース前に負荷試験を実施することはよくあるかと思いますが、 DB の 性能試験は、早い段階で単体で実施しておいたほうが良いでしょう。アプリ ケーションサーバと結合して負荷試験を実施したときに問題が出ると、修 正コストが高いからです。 • 想定しているユーザ数、一つのクエストをクリアするのに要する時間、一人 のユーザが持てるアイテムの数、フレンドの数、そういったものを踏まえた 上で、テストデータを生成すると良いでしょう。 • DB 単体の試験であれば、 SQL の観点で意味のあるデータであれば良くて、データは ダミーで構いません。 • 例えば、フレンドの数がボトルネックになりそうであれば、ユーザのレベル に応じてフレンド数の上限を増やすよう仕様を変更することで、リリース直 後の負荷を抑えるなどできると思います。 DB の負荷試験は、 事前に単体でやっておく 44
45. Copyright © GREE, Inc. All Rights Reserved. • アプリケーションサーバと結合して負荷試験を実施したとき、 TCP のレイ ヤーで問題が発生するケースなども考えられます。また、 MySQL 以外の コンポーネントがボトルネックになるケースも考えられます。 • 結合した状態での負荷試験では、どのコンポーネントで問題が発生してい るか、切り分けることから始めることもありえます。事前に MySQL 単体で 性能を担保しておけると、結合してからの負荷試験がスムーズに進むで しょう。 補足・結合試験の前に、ある程度性能を担保する 45
46. Copyright © GREE, Inc. All Rights Reserved. • カラムが多すぎると、性能に影響を及ぼすこともあります。かといって、 ゲームデザインによっては、大量のパラメータを持たせたいこともあるで しょう。 • 仮に、ゲーム内で 512 種類のクエストがあって、どのクエストをクリアした かフラグで管理したいだけならば、 512 個のカラムを定義するのではな く、 512bit のバイナリデータを、バイナリのカラムに保存するのも有効な 方法です。 • このような細かい調整は、 DB 単体の性能試験でも気づき得るところで す。 カラムが多すぎるなら、 BLOB も検討する 46
47. Copyright © GREE, Inc. All Rights Reserved. • 一つのテーブルに大量の secondary index が定義されていた場合、そ のテーブルはアクセスが集中する重要なテーブルかもしれません。また、 secondary index が多いということは、それだけ更新処理が重くなった り、ロックの競合が発生しやすいことになります。 • MySQL は Multi Column Index を定義できて、それをうまく使うと、本 当に必要な index の数は、かなり減らせると考えられます。 • とてもざっくり考えると、ゲーム用途なら多くの場合、 secondary index の数は 2-3 程度でもかなり有効であり、片手で数えられないほど index が定義されていたならば、何か設計を見直したほうが良い気がします。 • 前述したように、ローテーションすることでテーブルを小さく保てるなら、 index を追加す るより、テーブルを小さくすることを、検討しても良いでしょう。 index が多すぎるなら、設計を見直す 47
48. Copyright © GREE, Inc. All Rights Reserved. • primary key の長さが長いと、 secondary index が肥大化しがちなの で、 primary key は適切な大きさのカラムを使うのが望ましい、文字列 など使わないほうが望ましいのですが • アイテムデータなど、一人のユーザが N件のデータを保持しているとわ かっていて、ユーザが自分のデータをまとめて取得するテーブルであれば • PRIMARY KEY (user_id, item_id); • のような primary key を定義して • SELECT * FROM user_items WHERE user_id=?; • というような SELECT を実行すると、 InnoDB の場合、アクセス効率が 良くなります。 補足・注意深く primary key を設計する 48
49. Copyright © GREE, Inc. All Rights Reserved. • 例えば、次のような SELECT があったとします。 • SELECT * FROM table1 FORCE INDEX (col1_index) WHERE col1=1 ORDER BY id; • 私の場合、次のように書けないか検討します。 • SELECT * FROM table1 IGNORE INDEX (PRIMARY) WHERE col1=1 ORDER BY id; • MySQL のインデックスヒントには、次のような特性があります。 • https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html • index_name 値は、完全なインデックス名である必要はありません。インデックス名のあい まいでないプリフィクスにすることができます。プリフィクスがあいまいな場合は、エラーが発 生します。 • PRIMARY は不変かつ完全なインデックス名なので、このあたりの振る舞 いに悩まされず、使うことが可能です。 補足・IGNORE INDEX PRIMARY 49
50. Copyright © GREE, Inc. All Rights Reserved. monitoring は重要。 最適化はその後。 50
51. Copyright © GREE, Inc. All Rights Reserved. • アプリケーションサーバと結合して負荷試験を開始する前に、できれば monitoring の環境を整えておくと良いでしょう。 • 具体的には以下の blog にまとめてありますので、参照してください。 • MySQLのmetricに関する話 本格的に負荷試験する前に、 monitoring 51
52. Copyright © GREE, Inc. All Rights Reserved. I/O を最適化する 52
53. Copyright © GREE, Inc. All Rights Reserved. • 何はともあれ、InnoDB Adaptive Flushing の仕組みを理解しておいて 損はないかと思います。 • 詳しくは以下の資料を参照してください。 • さいきんの InnoDB Adaptive Flushing （仮） InnoDB Adaptive Flushing 53
54. Copyright © GREE, Inc. All Rights Reserved. • InnoDB Adaptive Flushing を踏まえた上で、ゲーム向けに効きそうな パラメータを一つ補足させていただきます。 • 以前、次の blog で言及させていただいたのですが • 忙しい人のための MySQL 5.7.6 DMR における InnoDB Flushing の変更点につ いて • 数分間だけ更新処理が集中するような場合（例：ゲームのイベント開始 or 終了時、メンテナンス明けなどでアクセスが集中する場合）があると思いま すが • そういったとき、 innodb_flushing_avg_loops を調整することで、 I/O のバーストを抑えられる可能性があります。 InnoDB Adaptive Flushing を踏まえた上で 54
55. Copyright © GREE, Inc. All Rights Reserved. mutex の競合を避ける 55
56. Copyright © GREE, Inc. All Rights Reserved. • MySQL 5.6 で、 Read-Only Transaction が最適化されました。 • https://dev.mysql.com/doc/refman/5.6/ja/innodb-performance-ro-txn.html • MySQL 5.7 で、さらに次の最適化が追加されました。 • https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-ro-txn.html • The transaction is started without the READ ONLY option, but no updates or statements that explicitly lock rows have been executed yet. Until updates or explicit locks are required, a transaction stays in read-only mode. • autocommit が default の ON のままで non-locking な SELECT を実行すると、 InnoDB 的に軽くなります。 READ ONLY TRANSACTION 56
57. Copyright © GREE, Inc. All Rights Reserved. • 簡単な SELECT を slave にガンガン投げても、 MySQL 5.7 以降、そ の SELECT は Read-Only Transaction として扱われ、最適化される ようになりました。 • slave の SQL_Thread から見ても、有益な最適化が入ったと言えるの ではないでしょうか。かつては SELECT でも内部的に transaction ID が必要だったわけですが、MySQL 5.7 以降、 slave では SQL_Thread 以外、内部的に transaction ID 使わなくていいでしょう から。 • slave から更新対象のレコードを抽出し、 primary key 指定で master を更新するのは、この観点からも有効と言えます。 できれば SELECT は slave に、シンプルに 57
58. Copyright © GREE, Inc. All Rights Reserved. • InnoDBでは、内部の mutex や rw_lock のロック待ちでは、spin lock と条件変数が用いられています。 • そのあたりの仕組みがわかると、 SHOW ENGINE INNODB STATUS などから読み取れる情報が増えます。 • わりと込み入った話になるので、詳しくは以下の blog を参照してください • InnoDB の mutex の話（入門編） InnoDB の sync array について理解する 58
59. Copyright © GREE, Inc. All Rights Reserved. • InnoDB の安定性を向上させる上で、 innodb_thread_concurrency というパラメータを設定することが有効なケースがあり、弊社ではよく設定 しています。 • これも込み入った話になるので、詳しくは以下の blog を参照してください • innodb_thread_concurrencyに関する話 innodb_thread_concurrency を検討する 59
60. Copyright © GREE, Inc. All Rights Reserved. • ゲームの DB 設計は、他の分野の DB 設計と考え方が異なる部分もあり ます。ゲームの仕様と併せて考えて、ユーザのデータを守るために設計す るのがよいでしょう。 • 複数の DB サーバを横断してトランザクション管理するのは、かなり難し い問題です。MySQL には mysql_affected_rows() など有効な機能 があるので、それらをうまく活用することで、よりシンプルな設計をすること もできます。 • DB はコンパクトに保てるよう、工夫すると良いでしょう。 • InnoDB はソースコードが公開されており、実装を確認することができま す。いざというとき、調査可能で融通が利きやすいと言えるのではないで しょうか。 まとめ 60
61. Copyright © GREE, Inc. All Rights Reserved. おわり