【2025年度新卒技術研修】100分で学ぶ サイバーエージェントのデータベース 活用事例とMySQLパフォーマンス調査

Transcript

1. 2025年度新卒技術研修 100分で学ぶ サイバーエージェントのデータベース 活用事例とMySQLパフォーマンス調査 （外部公開版)
2. はじめに 1. データベース管理システムの種類 2. 社内でよく採用されているデータベースの事例 3. MySQLのインシデントやパフォーマンス改善事例 4. MySQLのパフォーマンス悪化時の初動調査手法
3. はじめに
4. 鬼海 雄太 Yuta Kikai •メディア統括本部サービスリライアビリティグループ (メディア事業 横断SRE組織) 2012年　サイバーエージェント中途入社 　コミュニティサービスやソーシャルゲームの 　インフラやデータベースを担当 　現在は横断SRE組織である現チームで 　AmebaのDBREとして従事 　チームブログであるSRGポータルに毎月記事執筆中 　https://ca-srg.dev 2025年~　AWS Community Builders (Category: Data)
5. 本日の研修のゴール •データベース管理システム(DBMS)には多くの種類があることを知る •サイバーエージェント内でよく採用されているデータベースを知り、 なぜそれが採用されているかなんとなく理解できている •MySQLのパフォーマンス悪化事例とその調査手法を知る
6. 本日の研修で扱わない部分 •SQLの書き方 •テーブル設計 •OLAP（オンライン分析処理）のデータベース
7. 現時点で完全理解できなくてOK チームに配属後、今日の内容を思い出して資料を活用してください
8. データベース管理システム(DBMS)の種類
9. そもそも、なぜデータベースを学ぶのか • データベースはアプリケーションと比較するとライフサイクルが長い あとから変更しにくい。社内でも20年超えのサービスが出てきている 要件に合った適切なデータベースの選定が重要 • データベースはサービスの根幹である データベースが止まると事業が止まる • 重要であるが、普段は意識されづらい 今日からちょっと意識してみましょう
10. データベース管理システム(DBMS) •階層型、ネットワーク型、リレーショナル型などが存在 •本日はこの図のリレーショナルDB(RDB)以降の話をします https://developers.cyberagent.co.jp/blog/archives/48070/　2024年度サイバーエージェント新卒研修「データベースの歴史」より引用
11. リレーショナルデータベース •表（テーブル）を利用してデータを格納 テーブルは行( row )と列( column )で構成 • テーブル同士が 関係・関連( リレーション )をもつ • SQL( Structured Query Language )というデータ操作言語でデータを管理 • データの一貫性、整合性を担保するために厳格な制約 • 代表的なリレーショナルデータベース管理システム（ RDBMS ) Oracle Database, MySQL , SQL Server , PostgreSQL
12. RDBMSの一般的なテーブルイメージ 行→ 列 ↓ 　　↑ レコード
13. RDBMSの強み •汎用性が高くさまざまな要件に対応できる ユーザーが使いやすいインターフェースとデータ構造 トランザクションによるデータ信頼性の高さ •レプリカによるデータ参照性能のスケールアウトのしやすさ 例）MySQLのリードレプリカによる参照分散
14. RDBMSの弱み •データの書き込み性能の拡張が難しい 書き込み性能の拡張には水平分割や垂直分割などをアプリケーション側で実装する必要がある ユーザー数やデータ量でシステム規模を伸縮させづらい •要件の変化で求められる性能が変わってきた 単純なデータへの大量アクセス、非構造データ、グラフの取り扱いなど ※近年のRDBMSの進化でこのあたりの強化はされてきている場合もあり
15. データの書き込み性能の拡張方法 •テーブルの水平分割 •テーブルの垂直分割 id ユーザー名 職業 レベル 1 鈴木 A子 戦士 10 2 佐藤 B太 僧侶 3 3 山田 C助 勇者 7 4 高橋 D次 戦士 22 id ユーザー名 職業 レベル 1 鈴木 A子 戦士 10 3 山田 C助 勇者 7 id ユーザー名 職業 レベル 2 佐藤 B太 僧侶 3 4 高橋 D次 戦士 22 id ユーザー名 職業 レベル 1 鈴木 A子 戦士 10 2 佐藤 B太 僧侶 3 3 山田 C助 勇者 7 4 高橋 D次 戦士 22 id ユーザー名 職業 1 鈴木 A子 戦士 2 佐藤 B太 僧侶 3 山田 C助 勇者 4 高橋 D次 戦士 lv_id uid レベル 1 1 10 2 2 3 3 3 7 4 4 22 同じテーブルをid単位で分割 テーブル単位で分割 頻繁に書き換わりそうなデータを分離
16. NoSQL •Not Only SQLの略と言われている SQLに囚われない 定義は曖昧だが非リレーショナル・分散型・水平拡張可能などの特徴を持つ製品が多い • NoSQLには様々な種類がある RDBMSで課題になっていた点を独自のアプローチで解決を目指した
17. NoSQLのデータモデルによる分類の一例 分類 特徴と強み 弱み 製品の一例 キーバリュー型 - key:value単位でデータを格納 - シンプルで高速 - 同時アクセス性能が高い - データの関係性の表現が難しい - トランザクションがないのでデータの信頼 性が低い - Redis ( Valkey含む ) - Memcached - Amazon DynamoDB(※) 列指向型 - 高い書き込み性能 - 大量データを効率的に扱える - ビッグデータの分析 - 複雑なクエリが困難 - ランダムアクセスには向いていない - トランザクションサポートが限定的 - Apache Cassandra - HBase - Google Cloud Bigtable グラフ型 - 関係性の検索（ユーザー同士 の繋がりや地図の経路情報な ど）に強い - 他と比べるとデータ更新が高コスト - Neo4j - Amazon Neptune ドキュメント型 - データ型や定義を決めずに JSONなどの構造をもったドキュ メントデータを格納 - データの関係性の表現が難しい - トランザクションサポートが限定的 - MongoDB - Google Cloud Firestore - ElasticSearch ( OpenSearch含む ) - Amazon DynamoDB(※)
18. データの書き込み性能拡張問題の解決の例 (MongoDBの場合) •自動シャーディングによる水平スケーリング range 0 - 3 range 4 - 7 range 8 -
19. データの書き込み性能拡張問題の解決の例 (MongoDBの場合) •自動シャーディングによる水平スケーリング range 0 - 3 range 4 - 7 range 8 - アプリケーションは書き込み先を意識せず、 自動でルールに則って分散される
20. 要件に最適なデータベースが選択できるように •RDBMSとNoSQLの強み弱みを理解して、要件に最適なデータベースを選択 •NoSQLを利用していくうちに次の要望も出てきた
21. NoSQLの次に求めたもの •やっぱりSQLで柔軟にデータを取り出せるのは便利だった •トランザクション機能は欲しい
22. NewSQLの登場 •RDBMSとNoSQLの良いところ取りをした設計 RDBMSのデータの整合性（ トランザクション機能 ） NoSQLのスケーラビリティ( 分散アーキテクチャ ) • 代表的な製品 TiDB , CockroachDB , YugabyteDB , Cloud Spanner , Aurora DSQL • NewSQLの弱み 小規模なデータセットや単純なクエリではRDBMSやNoSQLにパフォーマンスで劣る可能性 複雑なアーキテクチャ
23. 1章のまとめ • RDBMSが広く普及 RDBMSは汎用性が高いが苦手な要件もある • 要件をクリアするためのNoSQLが登場 NoSQLには個性的な特性をもった種類がある • NoSQLとRDBMSの特性を併せ持つNewSQLが登場 NewSQLが適さない要件ももちろんある。今後も適材適所は変わらない
24. 社内でよく採用されているデータベースの事例
25. 社内でよく採用されているデータベース 外部に公開されている一部情報 • SRE Technology Map • AI / Data Technology Map
26. 社内でよく採用されているデータベース 社内限定公開の資料 • Technology Map 2023年版
27. 社内でよく採用されているデータベース
28. 社内でのデータベース採用割合 TOP10 1. MySQL 33.3% 2. Memcached,Redis 33.3 % 3. ElasticSearch 9.1% 4. Amazon DynamoDB 7.8% 5. Mongo DB 4.2% 6. Google Cloud Spanner 3.2% 7. Google Cloud Firestore 2.9% 8. PostgreSQL 2.3% 9. Google Cloud Bigtable 1.9% 10. TiDB 1%
29. 社内でのデータベース採用割合 TOP10 1. MySQL 33.3% 2. Memcached,Redis 33.3 % 3. ElasticSearch 9.1% 4. Amazon DynamoDB 7.8% 5. Mongo DB 4.2% 6. Google Cloud Spanner 3.2% 7. Google Cloud Firestore 2.9% 8. PostgreSQL 2.3% 9. Google Cloud Bigtable 1.9% 10. TiDB 1%
30. MySQLについて •MySQLはオープンソースのRDBMS •今年30周年を迎えた歴史ある製品 •ライセンスには無料のCommunity Editionと商用ライセンスがある 主に運用や監視に役立つ機能の有無やサポートの違い
31. MySQLのレプリケーション機能 •MySQLのデータ（ソース）の複製を、別のMySQLサーバー（レプリカ）に転送 •ソース側で更新されたデータを継続的にレプリカに伝送できる •多段レプリケーションや、1:N 、N:1のレプリケーション構成も可能 多段 1:N N:1
32. レプリケーションでよく利用されるパターン MySQLバージョンアップ 参照性能スケールアウト 調査クエリ用MySQL MySQL 5.7 MySQL 8.0 MySQL 5.7 リーダーエンドポイント アプリケーション 参照クエリ アプリケーション 参照クエリ ※サポート対象外
33. NoSQL分類表から社内で採用されているもの再確認 分類 特徴と強み 弱み 製品の一例 キーバリュー型 - key:value単位でデータを格納 - シンプルで高速 - 同時アクセス性能が高い - データの関係性の表現が難しい - トランザクションがないのでデータの信頼 性が低い - Redis ( Valkey含む ) - Memcached - Amazon DynamoDB(※) 列指向型 - 高い書き込み性能 - 大量データを効率的に扱える - ビッグデータの分析 - 複雑なクエリが困難 - ランダムアクセスには向いていない - トランザクションサポートが限定的 - Apache Cassandra - Hbase - Google Cloud Bigtable グラフ型 - 関係性の検索（ユーザー同士 の繋がりや地図の経路情報な ど）に強い - 他と比べるとデータ更新が高コスト - Neo4j - Amazon Neptune ドキュメント型 - データ型や定義を決めずに JSONなどの構造をもったドキュ メントデータを格納 - データの関係性の表現が難しい - トランザクションサポートが限定的 - MongoDB - Google Cloud Firestore - ElasticSearch ( OpenSearch含む ) - Amazon DynamoDB(※)
34. Memcached 特徴 • 高パフォーマンスなインメモリKVS( Key-Value-Store ) • ディスクアクセスが無いので高速 • 容量が溢れると最終アクセス日時が古い順にデータを削除 • データの永続性はない 主なユースケース • キャッシュ、セッションデータの保存
35. Redis 特徴 • 高パフォーマンスなインメモリKVS( Key-Value-Store ) • 多様なデータ型が存在 String , List , Set , Hash , Sorted Set • データの永続化も可能 • レプリケーションやRedis Clusterを用いたスケーラビリティと高可用性 主なユースケース • キャッシュ、セッションデータ、キュー、メッセージング、リアルタイムランキング
36. 特徴 • フルマネージドでサーバレスなNoSQLデータベース • 応答1桁ミリ秒でのパフォーマンスとスケーラビリティが高い • Key-Value-Storeとドキュメントデータの両方に対応 • データの永続性あり 主なユースケース • キャッシュ、低遅延が必要な処理、ユーザー行動履歴、IoTデータなどの大量データ Amazon DynamoDB
37. 特徴 • ドキュメント指向データベース • JSON形式のデータを格納できる • 事前にフィールド名やデータ型を定義しなくてもよい • シャーディングによる水平分割 • レプリカセットによる高可用性 主なユースケース • 大量データの書き込み、スキーマが頻繁に変更されるアプリケーション MongoDB
38. 特徴 • 分散型の検索エンジン • 複数のノードで構成される分散型アーキテクチャ • 転置インデックスを用いたリアルタイム検索 • レプリカによる高可用性 主なユースケース • 大量のログデータの分析、全文検索 Elasticsearch
39. 特徴 • MySQL互換の分散型SQLデータベース • トランザクション機能あり • 分散アーキテクチャによる高いスケーラビリティと可用性 • OLAP（オンライン分析処理）にも対応(HTAP) • セルフホスティングも可能 主なユースケース • 分析処理併用、大規模MySQLの置換 TiDB TiDB Docs | https://docs.pingcap.com/ja/tidb/stable/tidb-architecture/ より引用
40. 特徴 • Googleが提供する高性能なデータベース • SQL対応 ◦ GoogleSQL ◦ PostgreSQL互換 • 高いスケーラビリティと高可用性 • トランザクション機能あり • OLAP対応（ Spanner Data Boost ) 主なユースケース • データ量の多いシステム、分析処理併用 Google Cloud Spanner Google Cloud Docs | https://cloud.google.com/spanner/docs/databoost/databoost-overview より引用
41. 社内で開発しているサービスのデータベース活用事例 • サービスごとのデータベースの使い分けを紹介 • どのデータベースに、どんなデータを格納しているかの参考に 各サービスの開発チームにヒアリングを実施 開発チームからOK出たものは外部公開用資料にも掲載します
42. データベース活用事例の紹介1 サービス名: Amebaブログ データベース 用途・入れているデータの一例 Amazon Aurora MySQL - ユーザー情報 - ブログ記事データ - 下記以外のデータすべて Amazon ElasticCache ( Redis ) - ブログ記事情報のキャッシュ - いいね機能のキャッシュ Amazon ElastiCache ( Memcache ) - ユーザープロフィールのキャッシュ - ブログ設定情報のキャッシュ Amazon OpenSearch Service ( 旧名 Elasticsearch Service ) - 広告商品検索用データ Amazon DynamoDB - アクセス解析機能のデータ
43. データベース活用事例の紹介2 サービス名: ピグパーティ データベース 用途・入れているデータの一例 MongoDB - マスターデータ - ユーザーデータ - 下記以外のデータすべて Redis - ランキングデータ - 一時的な購入情報、エリア情報など永続化不要なデー タ
44. データベース活用事例の紹介3 サービス名: IDOLY PRIDE データベース 用途・入れているデータの一例 Google Cloud Spanner - ユーザーデータ - ユニオン(ギルド)データ - 下記以外のデータすべて Google CloudSQL ( MySQL ) - マスターデータ - 運営用の管理データ Google Cloud Memorystore - ランキングデータ - その他一時キャッシュ
45. データベース活用事例の紹介4 (非公開)
46. データベース活用事例の紹介5 サービス名: tapple CyberAgent Developers Blog | 75億ドキュメント以上のデータを保持するMongoDBを、Amazon EC2からMongoDB Atlasへ約3ヶ月で移設した方法 CyberAgent Developers Blog | タップルにおける約4年越しのキャッシュストア大幅アップデートの軌跡 データベース 用途・入れているデータの一例 MongoDB - ユーザーデータ - マスターデータ - 下記以外のデータすべて Amazon ElasticCache ( Redis ) - MongoDBの前段キャッシュ - 排他制御ロックデータ Amazon OpenSearch Service ( 旧名 Elasticsearch Service ) - 恋愛対象の候補検索用データ Amazon DynamoDB - メイン機能ではないシステムデータ等
47. データベース活用事例の紹介6 サービス名: Dynalyst データベース 用途・入れているデータの一例 Aurora MySQL + MySQL併用 - 広告主情報 - 予算情報 - ホワイトリスト情報 - その他マスターデータ Amazon ElasticCache ( Redis ) - フリークエンシーキャップ (短期間で同一ユーザーに複数回広告を当てるのを防ぐ機構) - その他キャッシュ Amazon ElastiCache ( Memcache ) - MySQLに載せているデータのキャッシュ Amazon DynamoDB - リターゲティング対象ユーザのデータ - 広告動画視聴履歴の一時保存 - クリック履歴の一時保存 SpeakerDeck | 月間数千億リクエストをさばく技術 CyberAgent Way | ハイブリッドクラウドを駆使したコスト最適化：SREと連携したDynalystの移設 より引⽤（右図）
48. データベース選定の理由　MySQL MySQLを利用しているサービス: Amebaブログ AmebaはAmebaブログを中心としたサービス 2004年からスタートし、今年21周年を迎える歴史あるサービス MySQL選定理由 ➢ 運営初期の頃、2006年頃にMySQL4系の時代から利用。( 当時はOracle RACと併用 ) ➢ Amebaブログは参照ヘビーなサービスで、当時利用できるOSSのデータベースでは参照性能の スケールアウトが比較的容易だったMySQLが選ばれた。 ➢ 何度かシステム刷新を経て、現在はMySQL 8.0（Aurora MySQL)を使用 CyberAgent Developers Blog | アメブロ2016：インフラ編　〜大規模リニューアルの裏側〜 CyberAgent Developer Conference 2022 | 事業と歩むAmebaシステム刷新の道 日経クロステック|100億PVにも耐えられる、アメブロがOracle RACで性能向上
49. データベース選定の理由　MongoDB MongoDBを利用しているサービス:ピグパーティ 仮想空間内でアバター（ ピグ ）を使って着せ替えや部屋の模様替えを楽しむアバターSNS MongoDB 選定理由 ➢ サーバサイドにNode.jsを採用していて、JSONをそのまま格納できるMongoDBと相性が良い ➢ 柔軟なスキーマ設計により仕様変更が多いゲーム開発にマッチ ➢ 自動シャーディングによる負荷分散とレプリケーションによる高可用性 ➢ ピグパーティより前に運営開始したピグライフでもMongoDBを採用しており知見があった Slideshare | MongoDB + node.js で作るソーシャルゲーム Speakerdeck | ピグパーティにおけるMongoDB CommunityバージョンからAtlasへの移行事例
50. データベース選定の理由　Elasticsearch Elasticsearchを利用しているサービス:tapple tappleは2014年にサービス開始したマッチングサービス 共通の趣味を通して異性との交流や出会いのきっかけを提供 Elasticsearch選定理由 ➢ リリース当初はユーザー検索機能もメインDBであるMongoDBを利用してきた ➢ MongoDBは検索に強いわけではないので、ユーザー数の増加により性能が悪化してきた ➢ ユーザー検索機能を大規模データの検索に強みのあるElasticsearchに切り替えた Speakerdeck | タップル誕生における、事業成長に合わせた継続的なシステム改善について ※サービス名称が変更されたAmazon OpenSearch Service 利用中。名称変更については下記記事を参照 Publickey | 「Amazon Elasticsearch Service」の名称が「Amazon OpenSearch Service」に変更。ElasticsearchからフォークしたOpenSearchも採 用
51. データベース選定の理由　Cloud Spanner Cloud Spannerを利用しているサービス:IDOLY PRIDE IDOLY PRIDEはアイドルマネジメントRPGのゲーム 2021年リリース　ゲーム開発と運営はSGEのQualiArtsが担当 Cloud Spanner選定理由 ➢ 過去の別ゲームではMySQLやMongoDBを採用してきた ➢ MySQLでは高負荷なゲームの書き込みの分散で苦労していた ➢ MongoDBは当時トランザクションがなかったり、MongoDBクラスタの運用に苦労していた ➢ Cloud Spannerはフルマネージドで、自動水平分割機能やトランザクション機能をもっていた speakerdeck | ゲーム「IDOLY PRIDE」を構成するGCPアーキテクチャの全貌 「IDOLY PRIDE」におけるGoogle Cloud Spannerの活用
52. データベース選定の理由　TiDB TiDBを利用しているサービス:大規模データ処理基盤 サイバーエージェント内で横断的に利用される大規模データ処理基盤 以前は基盤全体がHadoopエコシステムに依存していて、データストアにHBaseを利用していた TiDB選定理由 ➢ HBaseの利用と運用の面から複数の課題があった ▪ 簡単な集計、検索にもJavaでコードを書く必要がある ▪ HBaseはプライベートクラウドのVM上で運用していて手間が多かった ➢ TiDBはMySQL互換であるため利用者が使いやすく、複雑なクエリの実行も可能 ➢ TiDB OperatorによってKubernetes上でTiDBを運用可能 speakerdeck |TiUG #1 HBaseからTiDBへの移行を選んだ理由
53. 2章のまとめ • 社内でのデータベース採用率のトップはMySQL RedisやElasticsearch,MongoDBなどのNoSQLに加え、SpannerなどのNewSQLも使われている • サービスはそれぞれ要件に適したデータベースを選択している サービスが成長すると求める要件が変わってくることもある
54. MySQLのインシデントや パフォーマンス改善事例
55. 社内でのデータベース採用割合 TOP10 1. MySQL 33.3% 2. Memcached,Redis 33.3 % 3. ElasticSearch 9.1% 4. Amazon DynamoDB 7.8% 5. Mongo DB 4.2% 6. Google Cloud Spanner 3.2% 7. Google Cloud Firestore 2.9% 8. PostgreSQL 2.3% 9. Google Cloud Bigtable 1.9% 10. TiDB 1%
56. 社内で採用率の高いMySQLでの事例 •配属後のチームがMySQLを利用している可能性が高い •MySQLを利用しているサービスでは過去にどういったトラブルに遭遇したか •そのトラブルをどう解決したかのケーススタディ
57. 用語 インデックス •テーブル内の大量のデータから必要なデータを素早く見つけ出す索引 •特定の列に対して作成し、その列に格納されたデータの検索を高速化 WHERE id = 13 WHERE id = 13 INDEXがあると4回の探索で到達 上から順に探索し13回目で到達
58. 用語 テーブル結合（JOIN) •SQLのJOINは複数のテーブルから関連するデータを組み合わせて1つのテーブルとして扱える •内部結合（INNER JOIN）, 外部結合（OUTER JOIN）, クロス結合(CROSS JOIN)など 社員テーブル 部署テーブル 内部結合 （INNER JOINの例) 外部結合 （LEFT OUTER JOINの例) クロス結合 （CROSS JOIN)
59. 用語 VIEW ( 仮想テーブル ) •MySQLのVIEWはあたかもテーブルが存在しているかのように仮想のテーブルを定義 •データは保持しておらず、実体のテーブルからデータを取得
60. 用語 サブクエリ •クエリの中で使用される別のクエリのこと •複雑なデータ操作を単一のクエリで実行できる
61. ガールフレンド(仮)での事例
62. ガールフレンド(仮)とは PC/スマートフォン　ブラウザで遊べるソーシャルゲーム 2012年リリースでサービス開始から13年目 アーキテクチャなど詳細は下記の発表資料を参照 12年目を迎えた『ガールフレンド(仮)』におけるデータベースの負債解消への道のり
63. ガールフレンド(仮): ギフトボックスの仕様による性能悪化と データサイズの肥大化 概要 1. サービスリリース当初から「ギフトボックス」という機能があった • ログインボーナスやイベント報酬などのアイテムがここに送られる • ユーザーは任意のタイミングで自身のボックスに受取り可能 2. ギフトの受取日時の期限の設定がなかった • ユーザー側のボックスがいっぱいでギフトボックスに溜め込むユーザーが多発 3. 溜め込んだユーザーがギフトボックスを開くだけでMySQLの負荷増大 • 1ユーザーで100万件以上溜め込むユーザーもいた 4. 一時対応後、テーブルのデータサイズの肥大化が問題に
64. 原因の特定方法 ▪ MySQLのスロークエリログの調査 ▪ 大量にギフトボックス関連のクエリが記録されていた ▪ 発生したユーザーIDに偏りがあることが判明 ▪ ユーザーIDに紐づくギフトボックスのレコードを調査すると1ユーザーで100万件を超えていた ガールフレンド(仮): ギフトボックスの仕様による性能悪化と データサイズの肥大化
65. 一次対応 • ギフトボックスを表示するクエリに最適化した複合インデックス作成で窮地を脱した パフォーマンスは向上したが、複合インデックスによってデータサイズが倍近くまで膨張 ガールフレンド(仮): ギフトボックスの仕様による性能悪化と データサイズの肥大化
66. 根本対応 1. ギフトボックスの仕様変更によるレコード件数の大幅削減 ギフトボックスの一部アイテムをポイント(数値)に変換する仕様を追加 1アイテム = 1レコードだったので集約できる ユーザーの不利にならないような一定ルールで一括変換 2. 複合インデックスの削除 レコード件数が減ったことで複合インデックスを削除しても影響が出なくなった レコード件数削減と複合インデックス削除によりテーブルのデータサイズは半減した ガールフレンド(仮): ギフトボックスの仕様による性能悪化と データサイズの肥大化
67. 参考：ゲームの仕様を変えるような対応をおこなうための社内フローの一例 【議題】 ギフトボックスのデータサイズ肥大化問題の対応をどうするか 【実現方法のパターン】 ・全アイテムに受取期限を設定し、古いアイテムは一括削除 ・一定ルールのアイテムをすべて数値に変換し、変換した レコードを削除 ・サーバースペック増強で対応 1. 開発チームのエンジニアや開発リーダーと実現方法の協議 開発チーム
68. 【要件】 ギフトボックスのデータサイズ肥大化問題の対応をどうするか 【実現方法のパターン】 ・全アイテムに受取期限を設定し、古いアイテムは一括削除 ・一定ルールのアイテムをすべて数値に変換し、変換した レコードを削除 ・サーバースペック増強で対応 1. 開発チームのエンジニアや開発リーダーと実現方法の協議 「一番効果的に件数を 減らせるのはこの方法ですね。」 「デメリットはユーザーの 不利益になってしまう 可能性が高いことですね。」 開発チーム 参考：ゲームの仕様を変えるような対応をおこなうための社内フローの一例
69. 【要件】 ギフトボックスのデータサイズ肥大化問題の対応をどうするか 【実現方法のパターン】 ・全アイテムに受取期限を設定し、古いアイテムは一括削除 ・一定ルールのアイテムをすべて数値に変換し、変換した レコードを削除 ・サーバースペック増強で対応 1. 開発チームのエンジニアや開発リーダーと実現方法の協議 開発チーム 参考：ゲームの仕様を変えるような対応をおこなうための社内フローの一例 「次に件数を 減らせるのはこの方法ですね。」 「どういうルールでアイテムを 数値化するかが重要そう。」
70. 【要件】 ギフトボックスのデータサイズ肥大化問題の対応をどうするか 【実現方法のパターン】 ・全アイテムに受取期限を設定し、古いアイテムは一括削除 ・一定ルールのアイテムをすべて数値に変換し、変換した レコードを削除 ・サーバースペック増強で対応 1. 開発チームのエンジニアや開発リーダーと実現方法の協議 開発チーム 参考：ゲームの仕様を変えるような対応をおこなうための社内フローの一例 「開発の工数はほぼかけずに 対応できますね。」 「その代わり毎月のサーバー 費用がかなり高額になります。」 「今後さらにデータが増えたら 対応できなくなりそう。 この方法は今回無しで」 ✗
71. 【要件】 ギフトボックスのデータサイズ肥大化問題に対応させる 【実現方法のパターン】 ・全アイテムに受取期限を設定し、古いアイテムは一括削除 ・一定ルールのアイテムをすべて数値に変換し、変換した レコードを削除 ・サーバースペック増強で対応 2. サービスの責任者（プロダクトマネージャー等)も交えて、対応の結論を出す サービス責任者 開発チーム 「全アイテムに受取期限設定 はユーザーへの不利益が大きい のでやめましょう」 「一定のレアリティ以下の アイテムなら、数値変換しても 不利益にならない設計にできそうです。」 参考：ゲームの仕様を変えるような対応をおこなうための社内フローの一例 ✗ ✗
72. 改善結果 • データサイズの肥大化によって顕在化していた負債を取り除けた この課題の解決によってハードウェアの制約で実現できなかったデータセンター移設が 行えるようになり、毎月のサーバーコストが削減された 反省 • データ数の増大を見越した仕様とテーブル設計が望ましかった あとから仕様を変更するのは調整を含めて大変 ガールフレンド(仮): ギフトボックスの仕様による性能悪化と データサイズの肥大化
73. Amebaブログでの事例
74. Amebaブログ: コメント返信通知機能追加による性能悪化 概要 1. Amebaブログのコメント機能は昔からある機能だが、後から返信機能が実装された • 返信機能では元のコメント用テーブルではなく、他の複数テーブルと情報を組み合わせて 作成した仮想テーブルを参照するように改修されていた。 2. 数年後、コメント返信時に通知する機能を追加実装 • 先ほどの仮想テーブルを参照するようにして機能を追加実装した 3. 機能リリース後、APIサーバーの応答速度が悪化 • 一部ユーザーへの応答速度が極端に悪化していることが判明
75. Amebaブログ: コメント返信通知機能追加による性能悪化 原因の特定方法 • AWSのパフォーマンスインサイト機能による調査 特定のDBインスタンスに負荷が偏っていることが判明した • MySQLのスロークエリログの調査 スロークエリログの出現量が平常時より数十倍になっていた
76. Amebaブログ: コメント返信通知機能追加による性能悪化 根本対応 • 問題となっていたクエリを特定し効率の良い書き方に修正
77. 問題となっていたクエリ (外部公開用に抽象化しています ) SELECT コメントID, ブログID, エントリID,〜中略〜, IFNULL(リプライカウント, 0) as REPLY_COUNT FROM コメント仮想テーブル LEFT OUTER JOIN ( SELECT 親コメントID as コメントID, COUNT(コメントID) as REPLY_COUNT FROM コメント仮想テーブル WHERE ブログID = 【集計するブログID】 AND 親コメントID is not null GROUP BY 親コメントID ) as T_REPLY_COUNT USING (コメントID) WHERE (コメントID = 【集計するコメントID】); このクエリの応答に数十秒かかる状態になっていた
78. コメント用テーブルの構造 (外部公開用に抽象化しています ) CREATE TABLE `コメントテーブル` ( `コメントID` bigint NOT NULL DEFAULT '0’, `ブログID` bigint NOT NULL DEFAULT '0’, `エントリーID` bigint NOT NULL DEFAULT '0’, 〜中略〜 `コメント本文` text COLLATE utf8mb4_general_ci, 〜中略〜 PRIMARY KEY (`コメントID`), KEY `コメントテーブルインデックス_2` (〜中略〜), KEY `コメントテーブルインデックス_1` (`エントリーID`,`追加日時`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; テーブルの主な用途：コメント本文の情報
79. 複数テーブルから構成された仮想テーブル (外部公開用に抽象化しています ) CREATE VIEW `コメント仮想テーブル` AS SELECT `コメントテーブル`.`コメントID` AS `コメントID`, `コメントテーブル`.`ブログID` AS `ブログID`, `コメントテーブル`.`エントリーID` AS `エントリーID`, 〜中略〜 `コメントテーブル`.`コメント本文` AS `コメント本文`, 〜中略〜 `コメント投稿者のブログIDテーブル`.`コメント投稿者ブログID` AS `コメント投稿者ブログID`, `返信関連テーブル`.`返信先コメントID` AS `返信先コメントID`, `返信関連テーブル`.`返信スレッドの起点の親コメントID` AS `親コメントID` FROM `コメントテーブル` LEFT JOIN `コメント投稿者のブログIDテーブル` ON `コメントテーブル`.`コメントID` = `コメント投稿者のブログIDテーブル`.`コメントID` LEFT JOIN `返信関連テーブル` ON `コメントテーブル`.`コメントID` = `返信関連テーブル`.`コメントID`; テーブルの主な用途：コメント本文のテーブルとその他テーブルを組み合わせて完全なコメント情報を持っている
80. SELECT コメントID, ブログID, エントリID,〜中略〜, IFNULL(リプライカウント, 0) as REPLY_COUNT FROM コメント仮想テーブル LEFT OUTER JOIN ( SELECT 親コメントID as コメントID, COUNT(コメントID) as REPLY_COUNT FROM コメント仮想テーブル WHERE ブログID = 【集計するブログID】 AND 親コメントID is not null GROUP BY 親コメントID ) as T_REPLY_COUNT USING (コメントID) WHERE (コメントID = 【集計するコメントID】); 問題となっていたクエリのサブクエリ部分 (外部公開用に抽象化しています ) 赤枠内のサブクエリの実行が遅い
81. サブクエリで仮想テーブル内のブログIDに一致する返信コメントを 全て集計していた 応答結果
82. サブクエリで全件集計した結果をT_REPLY_COUNTとして、 仮想テーブルとJOINし、そこから該当のコメントIDだけ 検索していた SELECT コメントID, ブログID, エントリID,〜中略〜, IFNULL(リプライカウント, 0) as REPLY_COUNT FROM コメント仮想テーブル LEFT OUTER JOIN ( SELECT 親コメントID as コメントID, COUNT(コメントID) as REPLY_COUNT FROM コメント仮想テーブル WHERE ブログID = 【集計するブログID】 AND 親コメントID is not null GROUP BY 親コメントID ) as T_REPLY_COUNT USING (コメントID) WHERE (コメントID = 【集計するコメントID】);
83. Amebaブログ: コメント返信通知機能追加による性能悪化 根本原因 • 集計が必要なのは該当のコメントIDの1件だけなのに、該当のブログIDに一致するコメントが 全件集計される非効率なクエリが実行されていた
84. 改善後のクエリ(外部公開用に抽象化しています ) SELECT コメントID, ブログID, エントリーID,〜中略〜, コメント本文, ( SELECT COUNT(*) FROM コメント仮想テーブル AS IVC WHERE IVC.返信スレッドの起点のコメントID = OVC.コメントID ) AS REPLY_COUNT FROM コメント仮想テーブル AS OVC WHERE (コメントID = 【集計するコメントID】); 仮想テーブルから一意のコメントIDだけ検索
85. 改善後のクエリ(外部公開用に抽象化しています ) 仮想テーブルから一意のコメントIDに紐づく返信だけ集計 SELECT コメントID, ブログID, エントリーID,〜中略〜, コメント本文, ( SELECT COUNT(*) FROM コメント仮想テーブル AS IVC WHERE IVC.返信スレッドの起点のコメントID = OVC.コメントID ) AS REPLY_COUNT FROM コメント仮想テーブル AS OVC WHERE (コメントID = 【集計するコメントID】);
86. Amebaブログ: コメント返信通知機能追加による性能悪化 改善結果 1. 数十秒かかっていたクエリが即返ってくるようになった 2. RDSのCPU負荷が激減したためインスタンス台数の縮小による大幅なコスト削減 反省点 1. 過去の経緯から複雑なテーブル構成のため仕様把握している人が少なかった 作成されていた仮想テーブルでは想定されていない使い方をしてしまった 2. レコード件数が少ない場合は問題が起きず、検証環境でも発見できなかった 本番環境でも再現したのはコメントが大量についたブログ記事だけだった
87. 3章のまとめ • ガールフレンド(仮)ではゲーム仕様とテーブル設計で苦労した 複合インデックスで窮地を脱したが、次はデータ肥大化問題に苦労した データの増大を見越した仕様決めやテーブル設計が重要 • Amebaブログでは複雑な仕様への機能追加で苦労した 非効率なクエリでも開発環境や特定条件では問題ないことも多い 効率的なクエリに改善することで劇的に応答速度を早くできた クエリ改善によってサーバー費用を大幅に削減できる可能性がある
88. MySQLのパフォーマンス悪化時の 初動調査手法
89. Amebaブログ: コメント返信通知機能追加による性能悪化 原因の特定方法 • AWSのパフォーマンスインサイト機能による調査 特定のDBインスタンスに負荷が偏っていることが判明した • MySQLのスロークエリログの調査 スロークエリログの出現量が平常時より数十倍になっていた 「実際なにをしたんだ？」
90. AWSのパフォーマンスインサイト機能による調査
91. AWS パフォーマンスインサイトとは ▪ 2018年にリリースされたRDSのパフォーマンスをモニタリングできる機能 ▪ 2019年にAurora MySQLに対応 ▪ 無料で直近7日間分のモニタリング履歴を閲覧可能
92. AWS パフォーマンスインサイトとは ▪ 2020年にSQLレベルのメトリクスをサポート
93. AWS パフォーマンスインサイトの見方 データベース負荷 • 平均アクティブセッション (Average Active Sessions ) • データベースのパフォーマンスを評価するための指標 • 1秒ごとにアクティブなセッション情報をサンプリング • アクティブなセッションとは”CPU使用中“ , ”他の処理を待機中”の2つがある
94. 平均アクティブセッション（AAS）が高い場合 AAS >= RDSインスタンスの最大vCPU数の場合 • vCPUと同じぐらいかやや超えていた場合 → パフォーマンス影響の可能性あり • vCPU数を大きく超えていた場合 → ほぼ確実にパフォーマンスに問題が起きていた 下記の状況の場合 • 断続的にvCPUのラインを超えてAAS が4になっているのでパフォーマンス影響の可能性あり
95. 平均アクティブセッション（AAS)が異常に高い例 AAS 20 ~ 30ぐらい >> vCPU 4 (r6g.large) スペックアップで対応するならvCPUが32(r6g.8xlarge)ぐらいは必要な見積もり
96. ディメンション　トップ待機 データベースが何の処理にリソースをつかっているか待機イベントを表示
97. 待機イベントの一例 CPU • CPUがアクティブかCPU割当を待っている状態 io/table/sql/handler • I/O待機などでテーブルへのアクセスを待っている状態　 wait/io/socket/sql/client_connetion • クライアント接続処理のスレッド割当を待っている状態 AWS公式ドキュメント : Aurora MySQLの待機イベント
98. ディメンション　トップSQL データベース負荷に影響度の高いSQLを表示 • テーブルごとに集計 • WHERE句などの条件は「?」に置き換えられて一行にまとめられる • 左側にある「＋」を押すと実際のSQLを確認可能
99. パフォーマンスインサイトをつかった調査の流れ 1. 問題があった時間帯の「平均アクティブセッション(AAS)」がいくつだったか確認 2. 「トップSQL」で影響度の高かったクエリを確認 3. そのクエリの待機イベントを見て、何で時間かかっているかを確認 4. クエリの改善やインスタンスの増強などの解決策を検討する
100. MySQLスロークエリログの調査
101. MySQLのスロークエリログとは MySQLの機能で設定した閾値（long_query_time)を超えたクエリをログとして記録 ▪ Aurora MySQLでもスロークエリログをCloudWatch Logsやテーブルに出力できる機能がある ▪ 閾値を超えたクエリが1件ずつ全て記録されるので、状況によっては内容が膨大になることも
102. pt-query-digestをつかったログの解析
103. pt-query-digestとは Percona社が提供しているMySQLユーティリティキットのpercona-toolkitに含まれる機能 • pt-query-digestはスロークエリログを集計し、人が見やすい形式で出力してくれる • スロークエリログ以外にもジェネラルログやバイナリログ、パケットキャプチャに対応 pt-query-digest /path/to/slow.log > query_digest.txt
104. pt-query-digestの集計結果は3つのセクションで表示 スロークエリログ全体の集計結果 遅いクエリのランキング クエリ個別の集計結果
105. セクション1 : スロークエリログ全体の集計結果 Exec time 　実行時間 Lock time 　ロックした時間 Rows sent 返した行数 Rows examine レスポンス返すまでに走査した行数 Query size　 クエリ文字列のバイトサイズ
106. セクション2 : 遅いクエリのランキング Rank Response time順のランキング Query ID　 クエリパターンごとに一意な識別子 Response time クエリの合計実行時間とその割合 Calls クエリの実行された回数 R/Call 実行ごとの平均応答時間 V/M　 クエリの実行時間のばらつき
107. セクション3 : クエリ個別の集計結果 Count クエリを実行した回数 Exec time 実行時間 Lock time ロックした時間 Rows sent 返した行数 Rows examine レスポンス返すまでに走査した行数 Query size　 クエリ文字列のバイトサイズ Query time distribution クエリ実行時間の平均 Tables 関係しているテーブル情報 EXPLAIN 　解析用クエリサンプル
108. クエリ個別の集計結果を読んだ例 Count このクエリの実行回数は24回(ログ全体の54%) Rows Sent このクエリは1行しか結果を返していない Rows examine このクエリは平均54万行走査している Query_time distribution クエリ実行時間分布は100ms ~ 1s台で 一部が1s以上かかっている
109. pt-query-digestをつかったスロークエリログ解析の流れ 1. 問題があった時間帯のスロークエリログを取得し、pt-query-digest実行 2. スロークエリログの集計結果から遅いクエリを特定する 3. 遅いクエリにたいしてEXPLAINを実行し、クエリ実行計画を確認する
110. pt-query-digestをつかったスロークエリログ解析の流れ 1. 問題があった時間帯のスロークエリログを取得し、pt-query-digest実行 2. スロークエリログの集計結果から遅いクエリを特定する 3. 遅いクエリにたいしてEXPLAINを実行し、クエリ実行計画を確認する
111. パフォーマンスインサイトをつかった調査の流れ 1. 問題があった時間帯の「平均アクティブセッション(AAS)」がいくつだったか確認 2. 「トップSQL」で影響度の高かったクエリを確認 3. そのクエリの待機イベントを見て、何で時間かかっているかを確認 4. クエリの改善やシステムの増強などの解決策を検討する 「クエリの改善ってどうするんだ？」
112. EXPLAINによるクエリの実行計画調査
113. EXPLAINとは MySQLがクエリをどのように実行するのかを可視化する機能 • どのテーブルから読み取るか • どのような順序でテーブル結合するか • どのインデックスを使用するか • 一時テーブルやソートが必要か というようなクエリの実行計画を確認できる
114. EXPLAINの実行方法 解析したいクエリの前にEXPLAINを付加するだけ mysql> EXPLAIN SELECT COUNT(*) FROM test_table;
115. EXPLAIN結果の各項目の見方 id 　　　 クエリを識別しているid番号 select type　 SELECTの種類 SIMPLE , PRIMARY , SUBQUERY , DERIVED, DEPENDENT SUBQUERYなど table 使用されるテーブル名 partitions 対象のパーティション (パーティションテーブルじゃない場合はNULL)
116. EXPLAIN結果の各項目の見方 type 　　　 テーブルへのアクセス方法(access_type) 　　　　　　 system 1行しかないsystemテーブル ( constの特殊例 ) const PRIMARYまたはUNIQUEインデックスを使い一致するレコード数が最大1行の場合(等価検索) eq_ref PRIMARYまたはUNIQUEインデックスを使いJOINしている場合 ref PRIMARYまたはUNIQUEインデックス ではない 等価検索やJOIN fulltext 全文検索インデックスを使っている場合 ref_or_null PRIMARYまたはUNIQUEインデックス ではない 等価検索にOR条件でNULL値の検索した場合 index_merge 複数のインデックスをマージしている場合 unique_subquery サブクエリ内でPRIMARYまたはUNIQUEインデックスを使い等価検索をしている場合 index_subquery サブクエリ内でPRIMARYまたはUNIQUEインデックス ではない 等価検索をしている場合 range インデックスを使用した範囲検索（ WHERE <>, >, >=, <, <=や BETWEENやIN )の場合 index インデックスフルスキャン もしくはカバリングインデックスの場合 ALL インデックス不使用のフルテーブルスキャン https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html#explain-join-types 遅い可能性
117. EXPLAIN結果の各項目の見方 possible_keys 利用可能なインデックス key　 選択されたインデックス
118. EXPLAIN結果の各項目の見方 key_len 使用することが決定したキーの長さ ref keyと比較されているカラムまたは定数。JOINの場合は結合する相手側テーブルの検索条件 として利用されているカラム rows 目的の行を検索するために読み取る必要があると推定した行数 filtered フィルタ処理される行数の割合 Extra 実行計画の追加情報 Using index , Using Where , Using Temporary , Using filesort
119. EXPLAINをつかったクエリ解析の簡単な例
120. EXPLAINをつかったクエリ解析の簡単な例 サンプルテーブル名: i_user_test レコード件数: 100万件
121. インデックスが全く設定されていない場合 type ALL rows 90万件 Extra Using Where WHERE句はつかっているが、全件フルスキャンとなっている EXPLAIN結果
122. インデックスを一つ作成してからEXPLAIN type ref key idx1 rows 40386件 Extra Using Where EXPLAIN結果
123. 複合インデックスを作成してからEXPLAIN type ref key idx2 rows 227件 Extra Using Where EXPLAIN結果
124. クエリ条件を変更しソートとLIMIT句を追加した場合 type ref key idx1 rows 40486件 Extra Using filesort EXPLAIN結果
125. ソート対象のカラムを含めた複合インデックス作成 type ref key idx3 rows 40486件 Extra NULL 　← Using Filesortが消えた。インデックスからLIMITの10件分取り出して終了するので高速 EXPLAIN結果
126. このようにEXPLAINをつかってクエリを調査していく
127. Amebaブログの事例でのEXPLAINの活用
128. 先程のAmebaブログの事例で問題となっていたクエリ (外部公開用に抽象化しています ) SELECT コメントID, ブログID, エントリID,〜中略〜, IFNULL(リプライカウント, 0) as REPLY_COUNT FROM コメント仮想テーブル LEFT OUTER JOIN ( SELECT 親コメントID as コメントID, COUNT(コメントID) as REPLY_COUNT FROM コメント仮想テーブル WHERE ブログID = 【集計するブログID】 AND 親コメントID is not null GROUP BY 親コメントID ) as T_REPLY_COUNT USING (コメントID) WHERE (コメントID = 【集計するコメントID】);
129. Amebaブログの事例で問題となっていたクエリのEXPLAIN 拡大 拡大 rowsが19544件で多そうだな🤔 select typeがDERIVEDだからサブクエリで派生テーブルも生成している EXPLAIN結果
130. MySQL8.0.18から使えるEXPLAIN ANALYZE actual time 数値A..数値Bという表記がされる(単位ミリ秒) 　　　左側の数値Aが最初の行を読み取るまでの時間 　　　右側の数値Bがすべての行を読み取るまでの時間 rows 　実際に処理された行数 loops 　　イテレータ呼び出し回数 EXPLAIN ANALYZE結果 mysql> EXPLAIN ANALYZE 【解析したいクエリ】; EXPLAIN ANALYZEは実際にクエリを実行して各ステップにかかった時間や処理された行数を確認できる
131. EXPLAIN ANALYZEだと問題の箇所がわかりやすい rowsが42911件返ってきたあとに、次の処理で42911回loopsが記録されている loopsが42911回の処理のactual timeは0.417ミリ秒 0.417ミリ秒 × 42911回 = 17,893ミリ秒 クエリ全体の時間が18,763ミリ秒なので、ほぼここの処理で時間がかかっていたことが特定 EXPLAIN ANALYZE結果
132. EXPLAINやEXPLAIN ANALYZEを活用して、 クエリの何が問題なのかを特定していくことができます
133. MySQL調査はまだまだ深い... 時間の都合上紹介しきれないので、興味ある人は以下のキーワードで調べてみてください • SHOW ENGINE INNODB STATUS • performance_schema • オプティマイザトレース
134. 4章のまとめ • AWS環境ならパフォーマンスインサイトという機能で便利に調査が可能 ※Google CloudにもQuery Insightsという似た機能あり • スロークエリログの解析はpt-query-digestを使うと便利 • 遅いクエリが特定できたらEXPLAIN / EXPLAIN ANALYZE でクエリの実行計画を調査
135. 本日の研修のまとめ
136. 研修のまとめ • サイバーエージェントではRDBMS,NoSQL含め複数のデータベースが活用されている サービスごとの要件にあったデータベースを採用 特に社内で採用率が高いのがRDBMSのMySQL • MySQLをつかっているサービスでの具体的な改善事例 設計の変更やクエリの改善によって、パフォーマンス改善やサーバコスト削減につながる • MySQLのパフォーマンス調査に便利な手法がある AWSパフォーマンスインサイト、MySQLスロークエリログ、EXPLAIN / EXPLAIN ANALYZE
137. 現時点で完全理解できなくてOK チームに配属後、今日の内容を思い出して資料を活用してください もう一度...
138. 本日の内容をちゃんと理解したい人におすすめの書籍
139. ありがとうございました