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

2016/08/26 CEDEC 2016

2. ©  2016,  Cygames,  Inc. 1 /  58 はじめに 秒間100万クエリ・・・ ⾮非常にインパクトのある数字かと思います。 弊社が運⽤用しているアプリケーションには 「グランブルーファンタジー」 というタイトルがあり、 秒間100万クエリに貢献している アプリケーションの１つです。
3. ©  2016,  Cygames,  Inc. 2 /  58 はじめに 特に、 グランブルーファンタジーのマルチバトルは、 ⾮非常に沢⼭山の⽅方が遊ばれているコンテンツで、 30分間で遊ばれる数は「約23万回」、 1分間で「約7,700回」、 1秒間で「約128回」 になります。 1回のバトルに「30⼈人」まで参加出来ます。 （参加⼈人数の上限はマルチバトルの種類によって異異なります）
4. ©  2016,  Cygames,  Inc. 3 /  58 はじめに 但し、現在遊んでくれているユーザーや これから増えるユーザーに 快適に遊んで貰う事が⼤大切切になりますので、 「如何にしてクエリを減らすか？」 も重要になります。 これからお話する内容は、 「如何にしてクエリを減らすか？」 等の施策も含まれますので、 楽しみに聞いてください！！
6. ©  2016,  Cygames,  Inc. 5 /  58 アジェンダ • 新規リリース編 ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化＋⾒見見える化によるボトルネックの キャッチアップ⽅方法
7. ©  2016,  Cygames,  Inc. 6 /  58 アジェンダ • 新規リリース編 ・ドメインを意識識したデータモデルの改善 ・generalログを使⽤用したボトルネックの改善 • 運⽤用編 ・⾃自動化＋⾒見見える化によるボトルネックの キャッチアップ⽅方法
8. ©  2016,  Cygames,  Inc. 7 /  58 サマリ（新規リリース編） Cygamesでは、新規アプリや新規イベントの リリース前に必ず⾏行行っている事があります。 １つは 「ドメインを意識識したデータモデルの改善」で、 もう１つは 「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 発⾏行行する必要のないクエリとは？ 仕様変更更を⾏行行う際にロジックが複雑化して、 開発者の気付かない所で不不要なクエリが実⾏行行 されてしまう事もあるので修正する。 例例えば、 ・バリデートチェックや存在チェック等で 同じクエリが発⾏行行された場合 →１回のクエリで解決するように改善する 結果として、 ・クエリの発⾏行行数を減らすことができます
25. ©  2016,  Cygames,  Inc. 24 /  58 generalログを使⽤用したボトルネックの改善⽅方法 generalログを使⽤用したボトルネックの改善⽅方法 として２つの⽅方法があります。 「各APIを１回実⾏行行」する⽅方法と、 「実際に端末でゲームをプレイ」する⽅方法です。 これらの⽅方法で出⼒力力されたgeneralログを解析して、 ボトルネックを改善します。 またこれらの解析は開発のフェーズによって 使い分けています。
26. ©  2016,  Cygames,  Inc. 25 /  58 generalログを使⽤用したボトルネックの改善⽅方法 「各APIを１回実⾏行行して解析する⽅方法」は、 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のボトルネックをキャッチアップするツール を独⾃自で開発しています。 １．slowクエリログの解析結果を⾒見見える化するツール 「Slow_̲Query_̲Analyzer」 ２．generalログの解析結果を⾒見見える化するツール 「General_̲Log_̲Analyzer」 ３．データモデルを⾒見見える化するツール 「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ログを使⽤用して改善すると良良い。 ・ボトルネッククエリを即座にキャッチアップ して改善させるために「⾃自動化＋⾒見見える化」 すると良良い。