秒間100万クエリを受け付ける大規模ソーシャルゲームのバックエンドDBシステムの設計・運用ノウハウ

510ec964f5d26c2724c883fd7b671e3d?s=47 Cygames
October 04, 2016

 秒間100万クエリを受け付ける大規模ソーシャルゲームのバックエンドDBシステムの設計・運用ノウハウ

2016/08/26 CEDEC 2016

510ec964f5d26c2724c883fd7b671e3d?s=128

Cygames

October 04, 2016
Tweet

Transcript

  1. None
  2. ©  2016,  Cygames,  Inc. 1 /  58 はじめに 秒間100万クエリ・・・ ⾮非常にインパクトのある数字かと思います。

    弊社が運⽤用しているアプリケーションには 「グランブルーファンタジー」 というタイトルがあり、 秒間100万クエリに貢献している アプリケーションの1つです。
  3. ©  2016,  Cygames,  Inc. 2 /  58 はじめに 特に、 グランブルーファンタジーのマルチバトルは、

    ⾮非常に沢⼭山の⽅方が遊ばれているコンテンツで、 30分間で遊ばれる数は「約23万回」、 1分間で「約7,700回」、 1秒間で「約128回」 になります。 1回のバトルに「30⼈人」まで参加出来ます。 (参加⼈人数の上限はマルチバトルの種類によって異異なります)
  4. ©  2016,  Cygames,  Inc. 3 /  58 はじめに 但し、現在遊んでくれているユーザーや これから増えるユーザーに

    快適に遊んで貰う事が⼤大切切になりますので、 「如何にしてクエリを減らすか?」 も重要になります。 これからお話する内容は、 「如何にしてクエリを減らすか?」 等の施策も含まれますので、 楽しみに聞いてください!!
  5. 4

  6. ©  2016,  Cygames,  Inc. 5 /  58 アジェンダ • 新規リリース編

    ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化+⾒見見える化によるボトルネックの キャッチアップ⽅方法
  7. ©  2016,  Cygames,  Inc. 6 /  58 アジェンダ • 新規リリース編

    ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化+⾒見見える化によるボトルネックの キャッチアップ⽅方法
  8. ©  2016,  Cygames,  Inc. 7 /  58 サマリ(新規リリース編) Cygamesでは、新規アプリや新規イベントの リリース前に必ず⾏行行っている事があります。

    1つは 「ドメインを意識識したデータモデルの改善」で、 もう1つは 「generalログを使⽤用したボトルネックの改善」 になります。 他にも様々な改善⽅方法があるのですが、 今回は上記の改善⽅方法についてお話しします。
  9. ©  2016,  Cygames,  Inc. 8 /  58 「ドメイン」について 「属性が取り得る値の範囲や制約」の事で、 簡単に⾔言ってしまうと「データ型」の事です。

  10. ©  2016,  Cygames,  Inc. 9 /  58 なぜドメインを意識識したデータモデルの改善が必要なのか? ・データの不不整合を防ぐため ・ストレージのサイズに影響を与えない

    ようにするため
  11. ©  2016,  Cygames,  Inc. 10 /  58 データの不不整合とは? Aテーブル user_̲id

    INT  UNSIGNED 値の範囲は 0 〜~ 4,294,967,295 ドメイン(INT) 値=  16,777,216 warning 16,777,216 Bテーブル user_̲id MEDIUMINT  UNSIGNED 16,777,215 値の範囲は 0 〜~ 16,777,215 Bテーブルのuser_̲idは値が⽋欠損するため、 データ不不整合が発⽣生する
  12. ©  2016,  Cygames,  Inc. 11 /  58 ストレージのサイズに影響を与えるとは? Aテーブル user_̲id

    INT  UNSIGNED 値の範囲は 0 〜~ 4,294,967,295 ドメイン(MEDIUMINT) 値=  16,777,215 16,777,215 Bテーブル user_̲id MEDIUMINT  UNSIGNED 16,777,215 値の範囲は 0 〜~ 16,777,215 Aテーブルのuser_̲idはデータの不不整合は発⽣生しないが、 不不必要なデータサイズを確保する事になる。
  13. ©  2016,  Cygames,  Inc. 12 /  58 ストレージのサイズに影響を与えるとは? Aテーブル user_̲id

    INT UNSIGNED Bテーブル user_̲id MEDIUMINT UNSIGNED ・・・4Byte ・・・3Byte 1Byteの差でも5,000万件のテーブルの場合、 5,000万件×3Byte=約150MB 5,000万件×4Byte=約200MB ∴1つのカラムで約50MBも差が出てしまう 1Byteの差
  14. ©  2016,  Cygames,  Inc. 13 /  58 ドメインを意識識したデータモデルの改善⽅方法 実際に良良く⾏行行うドメインのチェックは 「同⼀一カラム名で型が違うカラム」の

    チェックです。 具体的にはinformation_̲schemaの 「COLUMNSテーブル」を検索索して チェックします。 このチェックを⾏行行う事で、データの不不整合を 防ぐ事ができ、且つストレージのサイズに影響を 与えないドメインを設計する事ができます。
  15. ©  2016,  Cygames,  Inc. 14 /  58 ドメインを意識識したデータモデルの改善⽅方法 下記のデータモデルをサンプルとして説明します 例例)user_̲idのドメインがINTの場合

    MariaDB [test]> DESC user_info; +-----------+------------------+ | Field | Type | +-----------+------------------+ | user_id | int(10) unsigned | | user_name | varchar(60) | MariaDB [test]> DESC user_battle; +-------------+------------------+ | Field | Type | +-------------+------------------+ | user_id | int(10) unsigned | | battle_id | int(10) unsigned | MariaDB [test]> DESC user_card; +-----------+-----------------------+ | Field | Type | +-----------+-----------------------+ | user_id | mediumint(8) unsigned | | card_id | int(10) unsigned | MEDIUMINTなので データ不不整合となるカラム INTなので正しいカラム
  16. ©  2016,  Cygames,  Inc. 15 /  58 ドメインを意識識したデータモデルの改善⽅方法 SELECT  COLUMN_NAME

      FROM   (SELECT  COLUMN_NAME   FROM  information_schema.COLUMNS WHERE  TABLE_SCHEMA  =  'test'   GROUP  BY  COLUMN_NAME,  COLUMN_TYPE)  AS  colmuns GROUP  BY  COLUMN_NAME   HAVING  COUNT(*)  >=2; 不不整合となるカラムを検出するために 下記のクエリを実⾏行行すると・・・
  17. ©  2016,  Cygames,  Inc. 16 /  58 ドメインを意識識したデータモデルの改善⽅方法 +-------------+ |

    COLUMN_NAME | +-------------+ | user_id | +-------------+ 1 row in set (0.00 sec) 下記結果の通り不不整合なカラムである 「user_̲id」のみ検出する事ができます
  18. ©  2016,  Cygames,  Inc. 17 /  58 ドメインを意識識したデータモデルの改善⽅方法 SELECT  TABLE_SCHEMA,

     TABLE_NAME,  COLUMN_NAME,  COLUMN_TYPE,  COLUMN_COMMENT   FROM  information_schema.COLUMNS AS  colmuns WHERE  EXISTS(SELECT  COLUMN_NAME  FROM  (SELECT  COLUMN_NAME  FROM   (SELECT  COLUMN_NAME  FROM  information_schema.COLUMNS WHERE  TABLE_SCHEMA  =  'test'   GROUP  BY  COLUMN_NAME,  COLUMN_TYPE)  AS  colmuns GROUP  BY  COLUMN_NAME   HAVING  COUNT(*)  >=2)  AS  dup_colmuns WHERE  colmuns.COLUMN_NAME =  dup_colmuns.COLUMN_NAME)   AND  TABLE_SCHEMA  =  'test'   ORDER  BY  COLUMN_NAME,  COLUMN_TYPE; 詳細なリストを取得する場合は 下記のクエリを実⾏行行します
  19. ©  2016,  Cygames,  Inc. 18 /  58 ドメインを意識識したデータモデルの改善⽅方法 +--------------+-------------+-------------+-----------------------+----------------+ |

    TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_TYPE | COLUMN_COMMENT | +--------------+-------------+-------------+-----------------------+----------------+ | test | user_battle | user_id | int(10) unsigned | ユーザーID | | test | user_info | user_id | int(10) unsigned | ユーザーID | | test | user_card | user_id | mediumint(8) unsigned | ユーザーID | +--------------+-------------+-------------+-----------------------+----------------+ 3 rows in set (0.03 sec) 下記結果の通り不不整合なカラムである、 「user_̲id」のリストを検出する事ができます。 このカラムはINT型に修正する
  20. ©  2016,  Cygames,  Inc. 19 /  58 アジェンダ • 新規リリース編

    ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化+⾒見見える化によるボトルネックの キャッチアップ⽅方法
  21. ©  2016,  Cygames,  Inc. 20 /  58 なぜgeneralログを使⽤用して改善する必要があるのか? 「generalログ」は実⾏行行した全てのクエリを 出⼒力力する事が出来るため、

    ・キャッシュ化すべきクエリ ・纏めることが可能なクエリ ・発⾏行行する必要のないクエリ を確認する事ができます。
  22. ©  2016,  Cygames,  Inc. 21 /  58 キャッシュ化すべきクエリとは? 更更新頻度度の低いクエリはキャッシュ化する 例例えば、

    ・マスタデータ ・⼀一部ユーザーの情報 結果として、 ・DBへのアクセスとクエリの発⾏行行数を 減らすことができます
  23. ©  2016,  Cygames,  Inc. 22 /  58 纏めることが可能なクエリとは? ロジックでループしてクエリを発⾏行行している 箇所があればクエリを纏めるように修正する

    例例えば、 ・SELECTに関してはIN句句を使⽤用する ・INSERTに関してはバルクインサートを 使⽤用する 結果として、 ・コネクション数とクエリの発⾏行行数を 減らすことができます
  24. ©  2016,  Cygames,  Inc. 23 /  58 発⾏行行する必要のないクエリとは? 仕様変更更を⾏行行う際にロジックが複雑化して、 開発者の気付かない所で不不要なクエリが実⾏行行

    されてしまう事もあるので修正する。 例例えば、 ・バリデートチェックや存在チェック等で 同じクエリが発⾏行行された場合 →1回のクエリで解決するように改善する 結果として、 ・クエリの発⾏行行数を減らすことができます
  25. ©  2016,  Cygames,  Inc. 24 /  58 generalログを使⽤用したボトルネックの改善⽅方法 generalログを使⽤用したボトルネックの改善⽅方法 として2つの⽅方法があります。

    「各APIを1回実⾏行行」する⽅方法と、 「実際に端末でゲームをプレイ」する⽅方法です。 これらの⽅方法で出⼒力力されたgeneralログを解析して、 ボトルネックを改善します。 またこれらの解析は開発のフェーズによって 使い分けています。
  26. ©  2016,  Cygames,  Inc. 25 /  58 generalログを使⽤用したボトルネックの改善⽅方法 「各APIを1回実⾏行行して解析する⽅方法」は、 APIの仕様書を元に実⾏行行する⽅方法です。

    API ユーザー情報取得 クエスト⼀一覧 実⾏行行 gen-‐‑‒ log 解析 結 果 順番に 実⾏行行する APIがある程度度完成した段階で実施するので、 フェーズとしては「開発の中盤」になります。 プレゼント受け取り ・ ・ ・ デバッグ・管理理画⾯面等
  27. ©  2016,  Cygames,  Inc. 26 /  58 generalログを使⽤用したボトルネックの改善⽅方法 端末でプレイして解析する⽅方法は、 デバッガーさん等をアサインして、

    「網羅羅的にプレイ」する担当者と、 「局所的にプレイ」する担当者に別れて プレイして貰う⽅方法です。 網羅羅的にプレイ 局所的にプレイ 最終チェックとして実施するため、 フェーズとしては「リリース直前」になります。 gen-‐‑‒ log 解析 結 果
  28. ©  2016,  Cygames,  Inc. 27 /  58 generalログを使⽤用したボトルネックの改善⽅方法 SELECTの解析結果は 「EXPLAIN

     PARTITIONS」された結果と件数が保存される ************** 1. row ************** query: SELECT col_name FROM tbl_test WHERE test_id = N unique_query_count: 777 e_table: tbl_test e_partitions: pxxx e_type: ref e_possible_keys: PRIMARY e_key: PRIMARY e_extra: Using where 発⾏行行されたクエリ クエリの件数 EXPLAINの結果
  29. ©  2016,  Cygames,  Inc. 28 /  58 generalログを使⽤用したボトルネックの改善⽅方法 UPDATEの解析結果も 「EXPLAIN

     PARTITIONS」された結果と件数が保存される ************** 2. row ************** query: UPDATE tbl_test SET col_name = 'S' WHERE test_id = N unique_query_count: 1 e_table: tbl_test e_partitions: pxxx e_type: range e_possible_keys: PRIMARY e_key: PRIMARY e_extra: Using where 発⾏行行されたクエリ クエリの件数 EXPLAINの結果
  30. ©  2016,  Cygames,  Inc. 29 /  58 generalログを使⽤用したボトルネックの改善⽅方法 ********************** 3.

    row ********************** query: INSERT INTO tbl_test (test_id, col_name) VALUES (N, 'S') unique_query_count: 1 INSERTは「EXPLAIN  PARTITIONS」をする 必要がないのでクエリと件数のみ保存される 発⾏行行されたクエリ クエリの件数
  31. ©  2016,  Cygames,  Inc. 30 /  58 generalログを使⽤用したボトルネックの改善⽅方法 「クエリ取得→クエリ解析→ ボトルネック発⾒見見→クエリ改善・・・」

    を改善されるまで繰り返し実施します。 また、繰り返し実施する作業なので、 解析は⾃自動化しています。 ボトルネックが発⽣生する可能性が最も⾼高いAPIから 優先的に改善を⾏行行っていきます。
  32. ©  2016,  Cygames,  Inc. 31 /  58 アジェンダ • 新規リリース編

    ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化+⾒見見える化によるボトルネックの キャッチアップ⽅方法
  33. ©  2016,  Cygames,  Inc. 32 /  58 サマリ(運⽤用編) Cygamesでは、 常にユーザーが快適に遊べる環境を提供するために、

    ⾏行行っている事があります。 それは、「slowクエリログ」や「generalログ」 を⾃自動で「取得・解析」して結果を画⾯面に「表⽰示」 させる事でボトルネッククエリを即座にキャッチ アップして改善させる事です。 今回は、ログの取得・解析をどの様に 「⾃自動化」して「⾒見見える化」しているのか? についてお話しします。
  34. ©  2016,  Cygames,  Inc. 33 /  58 なぜ「⾃自動化」や「⾒見見える化」をする必要があるのか? ・「⾃自動化」する事によって 他の作業時間が作れる

    →特に⽇日時の作業は⾃自動化するべき ・「⾒見見える化」する事によって即時ボトルネック をキャッチアップできる →前回値との⽐比較ができる →グラフ化する事で気付き易易くなる →ボトルネックの予想がし易易くなる
  35. ©  2016,  Cygames,  Inc. 34 /  58 ボトルネックをキャッチアップするためのツールについて Cygamesでは、 DBのボトルネックをキャッチアップするツール

    を独⾃自で開発しています。 1.slowクエリログの解析結果を⾒見見える化するツール 「Slow_̲Query_̲Analyzer」 2.generalログの解析結果を⾒見見える化するツール 「General_̲Log_̲Analyzer」 3.データモデルを⾒見見える化するツール 「Schema_̲Manager」 今回は「Slow_̲Query_̲Analyzer」についてお話しします
  36. ©  2016,  Cygames,  Inc. 35 /  58 slowクエリの解析結果を⾒見見える化するツール 「Slow_̲Query_̲Analyzer」 Slow_̲Query_̲Analyzer(以下SQA)は、

    ⽇日々出⼒力力されるslowクエリログを プロジェクト毎に集計して、 解析した結果を ⾒見見える化するツールです。
  37. ©  2016,  Cygames,  Inc. 36 /  58 SQAの特徴① TOP画⾯面ではその⽇日のslowクエリログの数を ⼀一⽬目で確認する事ができる

    画像をクリックすると メイン画⾯面へ
  38. ©  2016,  Cygames,  Inc. 37 /  58 SQAの特徴② メイン画⾯面ではプロジェクト毎に改善するべき クエリを確認する事ができる

    「テーブル」リンクを クリックすると・・・
  39. ©  2016,  Cygames,  Inc. 38 /  58 SQAの特徴②-‐‑‒1 テーブル毎のユニーククエリのリストを 確認する事ができる

    「詳細」リンクを クリックすると・・・
  40. ©  2016,  Cygames,  Inc. 39 /  58 SQAの特徴②-‐‑‒2 ボトルネックとなっているクエリの詳細を 確認する事ができる

    「EXP」リンクを クリックすると・・・
  41. ©  2016,  Cygames,  Inc. 40 /  58 SQAの特徴②-‐‑‒3 EXPLAIN PARTITIONSの情報を

    確認する事ができる
  42. ©  2016,  Cygames,  Inc. 41 /  58 SQAの特徴③ 「Using  filesort」や「パーティショニング跨ぎ」

    等のボトルネックを確認する事ができる 「ボトルネック内容」リンクを クリックすると・・・
  43. ©  2016,  Cygames,  Inc. 42 /  58 SQAの特徴③-‐‑‒1 Using  Filesortのサマリー結果を

    確認する事ができる 「WHERE句句」リンクをクリックすると ボトルネックとなっているクエリの 詳細を確認する事ができる
  44. ©  2016,  Cygames,  Inc. 43 /  58 SQAの特徴④ 「クエリタイプ別」や「時間別」で集計された 内容を確認する事ができる

    「slowログ件数集計」リンクを クリックすると・・・
  45. ©  2016,  Cygames,  Inc. 44 /  58 SQAの特徴④-‐‑‒1 クエリタイプ毎の情報を 確認する事ができる

    「クエリタイプ」リンクをクリックすると クエリの詳細を確認する事ができる
  46. ©  2016,  Cygames,  Inc. 45 /  58 SQAの特徴④-‐‑‒2 レコードタイム毎の情報を 確認する事ができる

    「レコードタイム」リンクをクリックすると クエリの詳細を時系列列で確認する事ができる
  47. ©  2016,  Cygames,  Inc. 46 /  58 SQAの特徴⑤ slowクエリを時系列列で 確認する事ができる

    「タイムライン」リンクを クリックすると・・・
  48. ©  2016,  Cygames,  Inc. 47 /  58 SQAの特徴⑤-‐‑‒1 時系列列でクエリの詳細を 確認する事ができる

    slowクエリの情報
  49. ©  2016,  Cygames,  Inc. 48 /  58 SQAの特徴⑥ 「⽇日次・週次・⽉月次」のslowクエリログの数が グラフ化されているため、クエリ数の推移を

    確認する事ができる グラフが上昇しているので、 早期に改善する必要がある事がわかる グラフは週次の例例 slowクエリログ数 ログ出⼒力力⽇日
  50. ©  2016,  Cygames,  Inc. 49 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA DB DB deamon
  51. ©  2016,  Cygames,  Inc. 50 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA slowクエリログを常駐して監視する (物理理=1分間に1回、AWS=1時間に1回) deamon DB DB
  52. ©  2016,  Cygames,  Inc. 51 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA SQSを常駐して監視する (常時) deamon DB DB
  53. ©  2016,  Cygames,  Inc. 52 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA ユーザーアクセス時にslowクエリログが出⼒力力される deamon DB DB
  54. ©  2016,  Cygames,  Inc. 53 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA pos slowクエリログの出⼒力力をdeamonがキャッチして、 slowクエリログと取得時の情報をアップする slow -‐‑‒log KEY deamon slowクエリログ取得時 のポジション情報を登録 S3に転送したslowクエリログ のフルパスを登録 DB DB ポジションファイル からの差分を転送
  55. ©  2016,  Cygames,  Inc. 54 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA pos SQAに常駐されているdeamonが常にSQSを監視して、 KEYが登録されたと同時にSQAへslowクエリログを転送 KEY slow -‐‑‒log deamon slow -‐‑‒log DB DB
  56. ©  2016,  Cygames,  Inc. 55 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA pos slowクエリログ転送後にクエリの解析処理理を実⾏行行する KEY slow -‐‑‒log deamon slow -‐‑‒log DB DB クエリ解析 クエリParse EXPLAIN 集計処理理
  57. ©  2016,  Cygames,  Inc. 56 /  58 SQAのアーキテクチャ slow -‐‑‒log

    PRD(本番環境) AWS S3 SQS SQA pos SQAの画⾯面からslowクエリの情報を確認 slow -‐‑‒log slow -‐‑‒log KEY deamon DB DB
  58. ©  2016,  Cygames,  Inc. 57 /  58 SQA環境 • bash、parl、ストアドプロシージャ

    • MariaDB 10.0.14 • ruby 2.0.0p247 • Rails 4.0.0(Bootstrap) • Apache 2.2.15 • passenger 4.0.19 クエリをParseする処理理は 「サーバーレスアーキテクチャ」に変更更予定!!
  59. ©  2016,  Cygames,  Inc. 58 /  58 まとめ ・データの不不整合を防ぐ或いは、ストレージの サイズに影響を与えないようにするためには

    「ドメインを意識識したデータモデルを設計」 を意識識すると良良い。 ・「キャッシュ化すべきクエリ等」を確認する ためにgeneralログを使⽤用して改善すると良良い。 ・ボトルネッククエリを即座にキャッチアップ して改善させるために「⾃自動化+⾒見見える化」 すると良良い。