2016/08/26 CEDEC 2016
View Slide
© 2016, Cygames, Inc.1 / 58はじめに秒間100万クエリ・・・⾮非常にインパクトのある数字かと思います。弊社が運⽤用しているアプリケーションには「グランブルーファンタジー」というタイトルがあり、秒間100万クエリに貢献しているアプリケーションの１つです。
© 2016, Cygames, Inc.2 / 58はじめに特に、グランブルーファンタジーのマルチバトルは、⾮非常に沢⼭山の⽅方が遊ばれているコンテンツで、30分間で遊ばれる数は「約23万回」、1分間で「約7,700回」、1秒間で「約128回」になります。1回のバトルに「30⼈人」まで参加出来ます。（参加⼈人数の上限はマルチバトルの種類によって異異なります）
© 2016, Cygames, Inc.3 / 58はじめに但し、現在遊んでくれているユーザーやこれから増えるユーザーに快適に遊んで貰う事が⼤大切切になりますので、「如何にしてクエリを減らすか？」も重要になります。これからお話する内容は、「如何にしてクエリを減らすか？」等の施策も含まれますので、楽しみに聞いてください！！
4
© 2016, Cygames, Inc.5 / 58アジェンダ• 新規リリース編・ドメインを意識識したデータモデルの改善・generalログを使⽤用したボトルネックの改善• 運⽤用編・⾃自動化＋⾒見見える化によるボトルネックのキャッチアップ⽅方法
© 2016, Cygames, Inc.6 / 58アジェンダ• 新規リリース編・ドメインを意識識したデータモデルの改善・generalログを使⽤用したボトルネックの改善• 運⽤用編・⾃自動化＋⾒見見える化によるボトルネックのキャッチアップ⽅方法
© 2016, Cygames, Inc.7 / 58サマリ（新規リリース編）Cygamesでは、新規アプリや新規イベントのリリース前に必ず⾏行行っている事があります。１つは「ドメインを意識識したデータモデルの改善」で、もう１つは「generalログを使⽤用したボトルネックの改善」になります。他にも様々な改善⽅方法があるのですが、今回は上記の改善⽅方法についてお話しします。
© 2016, Cygames, Inc.8 / 58「ドメイン」について「属性が取り得る値の範囲や制約」の事で、簡単に⾔言ってしまうと「データ型」の事です。
© 2016, Cygames, Inc.9 / 58なぜドメインを意識識したデータモデルの改善が必要なのか？・データの不不整合を防ぐため・ストレージのサイズに影響を与えないようにするため
© 2016, Cygames, Inc.10 / 58データの不不整合とは？Aテーブルuser_̲id INT UNSIGNED値の範囲は0 〜～ 4,294,967,295ドメイン（INT） 値= 16,777,216warning16,777,216Bテーブルuser_̲id MEDIUMINT UNSIGNED 16,777,215値の範囲は0 〜～ 16,777,215Bテーブルのuser_̲idは値が⽋欠損するため、データ不不整合が発⽣生する
© 2016, Cygames, Inc.11 / 58ストレージのサイズに影響を与えるとは？Aテーブルuser_̲id INT UNSIGNED値の範囲は0 〜～ 4,294,967,295ドメイン（MEDIUMINT） 値= 16,777,21516,777,215Bテーブルuser_̲id MEDIUMINT UNSIGNED 16,777,215値の範囲は0 〜～ 16,777,215Aテーブルのuser_̲idはデータの不不整合は発⽣生しないが、不不必要なデータサイズを確保する事になる。
© 2016, Cygames, Inc.12 / 58ストレージのサイズに影響を与えるとは？Aテーブルuser_̲id INT UNSIGNEDBテーブルuser_̲id MEDIUMINT UNSIGNED・・・4Byte・・・3Byte1Byteの差でも5,000万件のテーブルの場合、5,000万件×3Byte＝約150MB5,000万件×4Byte＝約200MB∴1つのカラムで約50MBも差が出てしまう1Byteの差
© 2016, Cygames, Inc.13 / 58ドメインを意識識したデータモデルの改善⽅方法実際に良良く⾏行行うドメインのチェックは「同⼀一カラム名で型が違うカラム」のチェックです。具体的にはinformation_̲schemaの「COLUMNSテーブル」を検索索してチェックします。このチェックを⾏行行う事で、データの不不整合を防ぐ事ができ、且つストレージのサイズに影響を与えないドメインを設計する事ができます。
© 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なので正しいカラム
© 2016, Cygames, Inc.15 / 58ドメインを意識識したデータモデルの改善⽅方法SELECT COLUMN_NAME FROM (SELECT COLUMN_NAME FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = 'test' GROUP BY COLUMN_NAME, COLUMN_TYPE) AS colmunsGROUP BY COLUMN_NAME HAVING COUNT(*) >=2;不不整合となるカラムを検出するために下記のクエリを実⾏行行すると・・・
© 2016, Cygames, Inc.16 / 58ドメインを意識識したデータモデルの改善⽅方法+-------------+| COLUMN_NAME |+-------------+| user_id |+-------------+1 row in set (0.00 sec)下記結果の通り不不整合なカラムである「user_̲id」のみ検出する事ができます
© 2016, Cygames, Inc.17 / 58ドメインを意識識したデータモデルの改善⽅方法SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS AS colmunsWHERE EXISTS(SELECT COLUMN_NAME FROM (SELECT COLUMN_NAME FROM (SELECT COLUMN_NAME FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = 'test' GROUP BY COLUMN_NAME, COLUMN_TYPE) AS colmunsGROUP BY COLUMN_NAME HAVING COUNT(*) >=2) AS dup_colmunsWHERE colmuns.COLUMN_NAME = dup_colmuns.COLUMN_NAME) AND TABLE_SCHEMA = 'test' ORDER BY COLUMN_NAME, COLUMN_TYPE;詳細なリストを取得する場合は下記のクエリを実⾏行行します
© 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型に修正する
© 2016, Cygames, Inc.19 / 58アジェンダ• 新規リリース編・ドメインを意識識したデータモデルの改善・generalログを使⽤用したボトルネックの改善• 運⽤用編・⾃自動化＋⾒見見える化によるボトルネックのキャッチアップ⽅方法
© 2016, Cygames, Inc.20 / 58なぜgeneralログを使⽤用して改善する必要があるのか？「generalログ」は実⾏行行した全てのクエリを出⼒力力する事が出来るため、・キャッシュ化すべきクエリ・纏めることが可能なクエリ・発⾏行行する必要のないクエリを確認する事ができます。
© 2016, Cygames, Inc.21 / 58キャッシュ化すべきクエリとは？更更新頻度度の低いクエリはキャッシュ化する例例えば、・マスタデータ・⼀一部ユーザーの情報結果として、・DBへのアクセスとクエリの発⾏行行数を減らすことができます
© 2016, Cygames, Inc.22 / 58纏めることが可能なクエリとは？ロジックでループしてクエリを発⾏行行している箇所があればクエリを纏めるように修正する例例えば、・SELECTに関してはIN句句を使⽤用する・INSERTに関してはバルクインサートを使⽤用する結果として、・コネクション数とクエリの発⾏行行数を減らすことができます
© 2016, Cygames, Inc.23 / 58発⾏行行する必要のないクエリとは？仕様変更更を⾏行行う際にロジックが複雑化して、開発者の気付かない所で不不要なクエリが実⾏行行されてしまう事もあるので修正する。例例えば、・バリデートチェックや存在チェック等で同じクエリが発⾏行行された場合→１回のクエリで解決するように改善する結果として、・クエリの発⾏行行数を減らすことができます
© 2016, Cygames, Inc.24 / 58generalログを使⽤用したボトルネックの改善⽅方法generalログを使⽤用したボトルネックの改善⽅方法として２つの⽅方法があります。「各APIを１回実⾏行行」する⽅方法と、「実際に端末でゲームをプレイ」する⽅方法です。これらの⽅方法で出⼒力力されたgeneralログを解析して、ボトルネックを改善します。またこれらの解析は開発のフェーズによって使い分けています。
© 2016, Cygames, Inc.25 / 58generalログを使⽤用したボトルネックの改善⽅方法「各APIを１回実⾏行行して解析する⽅方法」は、APIの仕様書を元に実⾏行行する⽅方法です。APIユーザー情報取得クエスト⼀一覧実⾏行行 gen-‐‑‒log解析 結果順番に実⾏行行するAPIがある程度度完成した段階で実施するので、フェーズとしては「開発の中盤」になります。プレゼント受け取り・・・デバッグ・管理理画⾯面等
© 2016, Cygames, Inc.26 / 58generalログを使⽤用したボトルネックの改善⽅方法端末でプレイして解析する⽅方法は、デバッガーさん等をアサインして、「網羅羅的にプレイ」する担当者と、「局所的にプレイ」する担当者に別れてプレイして貰う⽅方法です。網羅羅的にプレイ局所的にプレイ最終チェックとして実施するため、フェーズとしては「リリース直前」になります。gen-‐‑‒log解析 結果
© 2016, Cygames, Inc.27 / 58generalログを使⽤用したボトルネックの改善⽅方法SELECTの解析結果は「EXPLAIN PARTITIONS」された結果と件数が保存される************** 1. row **************query: SELECT col_nameFROM tbl_testWHERE test_id = Nunique_query_count: 777e_table: tbl_teste_partitions: pxxxe_type: refe_possible_keys: PRIMARYe_key: PRIMARYe_extra: Using where発⾏行行されたクエリクエリの件数EXPLAINの結果
© 2016, Cygames, Inc.28 / 58generalログを使⽤用したボトルネックの改善⽅方法UPDATEの解析結果も「EXPLAIN PARTITIONS」された結果と件数が保存される************** 2. row **************query: UPDATE tbl_testSET col_name = 'S'WHERE test_id = Nunique_query_count: 1e_table: tbl_teste_partitions: pxxxe_type: rangee_possible_keys: PRIMARYe_key: PRIMARYe_extra: Using where発⾏行行されたクエリクエリの件数EXPLAINの結果
© 2016, Cygames, Inc.29 / 58generalログを使⽤用したボトルネックの改善⽅方法********************** 3. row **********************query: INSERT INTO tbl_test (test_id, col_name)VALUES (N, 'S')unique_query_count: 1INSERTは「EXPLAIN PARTITIONS」をする必要がないのでクエリと件数のみ保存される発⾏行行されたクエリクエリの件数
© 2016, Cygames, Inc.30 / 58generalログを使⽤用したボトルネックの改善⽅方法「クエリ取得→クエリ解析→ボトルネック発⾒見見→クエリ改善・・・」を改善されるまで繰り返し実施します。また、繰り返し実施する作業なので、解析は⾃自動化しています。ボトルネックが発⽣生する可能性が最も⾼高いAPIから優先的に改善を⾏行行っていきます。
© 2016, Cygames, Inc.31 / 58アジェンダ• 新規リリース編・ドメインを意識識したデータモデルの改善・generalログを使⽤用したボトルネックの改善• 運⽤用編・⾃自動化＋⾒見見える化によるボトルネックのキャッチアップ⽅方法
© 2016, Cygames, Inc.32 / 58サマリ（運⽤用編）Cygamesでは、常にユーザーが快適に遊べる環境を提供するために、⾏行行っている事があります。それは、「slowクエリログ」や「generalログ」を⾃自動で「取得・解析」して結果を画⾯面に「表⽰示」させる事でボトルネッククエリを即座にキャッチアップして改善させる事です。今回は、ログの取得・解析をどの様に「⾃自動化」して「⾒見見える化」しているのか？についてお話しします。
© 2016, Cygames, Inc.33 / 58なぜ「⾃自動化」や「⾒見見える化」をする必要があるのか？・「⾃自動化」する事によって他の作業時間が作れる→特に⽇日時の作業は⾃自動化するべき・「⾒見見える化」する事によって即時ボトルネックをキャッチアップできる→前回値との⽐比較ができる→グラフ化する事で気付き易易くなる→ボトルネックの予想がし易易くなる
© 2016, Cygames, Inc.34 / 58ボトルネックをキャッチアップするためのツールについてCygamesでは、DBのボトルネックをキャッチアップするツールを独⾃自で開発しています。１．slowクエリログの解析結果を⾒見見える化するツール「Slow_̲Query_̲Analyzer」２．generalログの解析結果を⾒見見える化するツール「General_̲Log_̲Analyzer」３．データモデルを⾒見見える化するツール「Schema_̲Manager」今回は「Slow_̲Query_̲Analyzer」についてお話しします
© 2016, Cygames, Inc.35 / 58slowクエリの解析結果を⾒見見える化するツール「Slow_̲Query_̲Analyzer」Slow_̲Query_̲Analyzer（以下SQA）は、⽇日々出⼒力力されるslowクエリログをプロジェクト毎に集計して、解析した結果を⾒見見える化するツールです。
© 2016, Cygames, Inc.36 / 58SQAの特徴①TOP画⾯面ではその⽇日のslowクエリログの数を⼀一⽬目で確認する事ができる画像をクリックするとメイン画⾯面へ
© 2016, Cygames, Inc.37 / 58SQAの特徴②メイン画⾯面ではプロジェクト毎に改善するべきクエリを確認する事ができる「テーブル」リンクをクリックすると・・・
© 2016, Cygames, Inc.38 / 58SQAの特徴②-‐‑‒1テーブル毎のユニーククエリのリストを確認する事ができる「詳細」リンクをクリックすると・・・
© 2016, Cygames, Inc.39 / 58SQAの特徴②-‐‑‒2ボトルネックとなっているクエリの詳細を確認する事ができる「EXP」リンクをクリックすると・・・
© 2016, Cygames, Inc.40 / 58SQAの特徴②-‐‑‒3EXPLAIN PARTITIONSの情報を確認する事ができる
© 2016, Cygames, Inc.41 / 58SQAの特徴③「Using filesort」や「パーティショニング跨ぎ」等のボトルネックを確認する事ができる「ボトルネック内容」リンクをクリックすると・・・
© 2016, Cygames, Inc.42 / 58SQAの特徴③-‐‑‒1Using Filesortのサマリー結果を確認する事ができる「WHERE句句」リンクをクリックするとボトルネックとなっているクエリの詳細を確認する事ができる
© 2016, Cygames, Inc.43 / 58SQAの特徴④「クエリタイプ別」や「時間別」で集計された内容を確認する事ができる「slowログ件数集計」リンクをクリックすると・・・
© 2016, Cygames, Inc.44 / 58SQAの特徴④-‐‑‒1クエリタイプ毎の情報を確認する事ができる「クエリタイプ」リンクをクリックするとクエリの詳細を確認する事ができる
© 2016, Cygames, Inc.45 / 58SQAの特徴④-‐‑‒2レコードタイム毎の情報を確認する事ができる「レコードタイム」リンクをクリックするとクエリの詳細を時系列列で確認する事ができる
© 2016, Cygames, Inc.46 / 58SQAの特徴⑤slowクエリを時系列列で確認する事ができる「タイムライン」リンクをクリックすると・・・
© 2016, Cygames, Inc.47 / 58SQAの特徴⑤-‐‑‒1時系列列でクエリの詳細を確認する事ができるslowクエリの情報
© 2016, Cygames, Inc.48 / 58SQAの特徴⑥「⽇日次・週次・⽉月次」のslowクエリログの数がグラフ化されているため、クエリ数の推移を確認する事ができるグラフが上昇しているので、早期に改善する必要がある事がわかるグラフは週次の例例slowクエリログ数ログ出⼒力力⽇日
© 2016, Cygames, Inc.49 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQADB DBdeamon
© 2016, Cygames, Inc.50 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAslowクエリログを常駐して監視する（物理理＝1分間に1回、AWS＝1時間に1回）deamonDB DB
© 2016, Cygames, Inc.51 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQASQSを常駐して監視する（常時）deamonDB DB
© 2016, Cygames, Inc.52 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAユーザーアクセス時にslowクエリログが出⼒力力されるdeamonDB DB
© 2016, Cygames, Inc.53 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAposslowクエリログの出⼒力力をdeamonがキャッチして、slowクエリログと取得時の情報をアップするslow-‐‑‒logKEY deamonslowクエリログ取得時のポジション情報を登録S3に転送したslowクエリログのフルパスを登録DB DBポジションファイルからの差分を転送
© 2016, Cygames, Inc.54 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAposSQAに常駐されているdeamonが常にSQSを監視して、KEYが登録されたと同時にSQAへslowクエリログを転送KEYslow-‐‑‒logdeamonslow-‐‑‒logDB DB
© 2016, Cygames, Inc.55 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAposslowクエリログ転送後にクエリの解析処理理を実⾏行行するKEYslow-‐‑‒logdeamonslow-‐‑‒logDB DBクエリ解析クエリParseEXPLAIN集計処理理
© 2016, Cygames, Inc.56 / 58SQAのアーキテクチャslow-‐‑‒logPRD（本番環境） AWSS3SQSSQAposSQAの画⾯面からslowクエリの情報を確認slow-‐‑‒logslow-‐‑‒logKEY deamonDB DB
© 2016, Cygames, Inc.57 / 58SQA環境• bash、parl、ストアドプロシージャ• MariaDB 10.0.14• ruby 2.0.0p247• Rails 4.0.0（Bootstrap）• Apache 2.2.15• passenger 4.0.19クエリをParseする処理理は「サーバーレスアーキテクチャ」に変更更予定！！
© 2016, Cygames, Inc.58 / 58まとめ・データの不不整合を防ぐ或いは、ストレージのサイズに影響を与えないようにするためには「ドメインを意識識したデータモデルを設計」を意識識すると良良い。・「キャッシュ化すべきクエリ等」を確認するためにgeneralログを使⽤用して改善すると良良い。・ボトルネッククエリを即座にキャッチアップして改善させるために「⾃自動化＋⾒見見える化」すると良良い。
cygames
hayatokudou
ad5jp
regonn
line_developers_tw
doradora09
takashiinui
nikkei_engineer_recruiting
iseki
mizunohiroaki
hanhan1978
shimashima35
yutaro527
jnunemaker
carmenintech
edds
hatefulcrawdad
shlominoach
dotmariusz
sachag
quramy
pedronauck
jensimmons
jeffersonlam
notwaldorf