12年目を迎えた『ガールフレンド(仮)』におけるデータベースの負債解消への道のり【CAGC2024】

Slide 1

Slide 1 text

12年目を迎えた『ガールフレンド(仮)』におけるデータベースの負債解消への道のり 1 SRE / ガールフレンド(仮) 鬼海雄太　Kikai Yuta

Slide 2

Slide 2 text

鬼海　雄太 @fat47 2 2012年　 - Ameba事業本部のインフラエンジニア - ソーシャルゲームやコミュニティ系Webサービスを担当 2016年 - 株式会社QualiArtsが子会社として設立される - 引き続き所属チームがインフラ関連を担当 2024年現在 - メディア事業の横断SREとしてAmebaブログ関連サービスなど兼務株式会社サイバーエージェント　メディア統括本部サービスリライアビリティグループ

Slide 3

Slide 3 text

『ガールフレンド(仮)』とは 3

Slide 4

Slide 4 text

ガールフレンド(仮) 4 ● 2012年10月リリース　PC/スマートフォンブラウザで遊べるソーシャルゲーム ● サービス開始から12年目に突入

Slide 5

Slide 5 text

現在の『ガールフレンド(仮)』の構成図簡略版 5 APP サーバメインDB Source [MySQL] メインDB Replica メインDB Replica メインDB Replica [MySQL] APP サーバ APP Cache サーバ Cache サーバ Cache Load Balancer イベントDB Source [MySQL] メインDB Replica メインDB Replica イベントDB Replica [MySQL] ヒストリDB Source [MySQL] メインDB Replica メインDB Replica ヒストリDB Replica [MySQL] ユーザー (スマホ / PC ) レプリケーションプライベートクラウド

Slide 6

Slide 6 text

現在の『ガールフレンド(仮)』の構成図簡略版 6 APP サーバメインDB Source [MySQL] メインDB Replica メインDB Replica メインDB Replica [MySQL] APP サーバ APP Cache サーバ Cache サーバ Cache Load Balancer イベントDB Source メインDB Replica メインDB Replica イベントDB Replica ヒストリDB Source メインDB Replica メインDB Replica ヒストリDB Replica ユーザー (スマホ / PC ) レプリケーション本日お話するのはこのDBについて↑

Slide 7

Slide 7 text

『ガールフレンド(仮)』におけるデータベースの負債 7

Slide 8

Slide 8 text

『ガールフレンド(仮)』におけるデータベースの負債 8 ● 変則レプリケーション ● DBサイズの肥大化

Slide 9

Slide 9 text

変則レプリケーション 9

Slide 10

Slide 10 text

変則レプリケーションとは 10 MySQLのソースとレプリカでレプリケーションを構築しているが、 Replicaによってレプリケーションされているテーブルが異なる状態 ※変則レプリケーションは正式名称ではなく私が勝手にそう呼称しています Source Replica A Replica B Replica C Xテーブルのみレプリケーション Yテーブルのみレプリケーション全テーブルレプリケーション

Slide 11

Slide 11 text

なぜ変則レプリケーションが導入されたか 11 2013年末から2014年の年始にかけて放映されたCMがネットで話題沸騰ファミ通App: 年末年始のお茶の間を賑わせた『ガールフレンド（仮）』CMのクロエ・ルメールってどんな子？ ● ユーザー数が短期間で増加 ● ゲーム内イベントでの想定以上のアクセス増加 ● その結果、MySQLのレプリケーションの遅延が深刻化

Slide 12

Slide 12 text

MySQLのレプリケーションによってレプリカにデータが反映される流れ 12 SQL実行プロセスデータバイナリログ BinlogDump スレッドソースレプリカ I/O スレッドリレーログ SQL スレッドデータバイナリログ

Slide 13

Slide 13 text

MySQLのレプリケーションによってレプリカにデータが反映される流れ 13 ソースで更新クエリ実行〜データ・バイナリログへの書き込み SQL実行プロセスデータバイナリログ BinlogDump スレッドソースレプリカ I/O スレッドリレーログ SQL スレッドデータバイナリログ

Slide 14

Slide 14 text

14 レプリカにバイナリログイベント送信〜レプリカのリレーログ記録 SQL実行プロセスデータバイナリログ BinlogDump スレッドソースレプリカ I/O スレッドリレーログ SQL スレッドデータバイナリログ MySQLのレプリケーションによってレプリカにデータが反映される流れ

Slide 15

Slide 15 text

15 レプリカのSQLスレッドの処理が追いつかず遅延 SQL実行プロセスデータバイナリログ BinlogDump スレッドソースレプリカ I/O スレッドリレーログ SQL スレッドデータバイナリログ MySQLのレプリケーションによってレプリカにデータが反映される流れ

Slide 16

Slide 16 text

16 ※現在のMySQLではSQLスレッドの並列化オプションがあるが当時は存在せず SQL実行プロセスデータバイナリログ BinlogDump スレッド Source Replica I/O スレッドリレーログ SQL スレッドデータバイナリログ SQL スレッド SQL スレッド MySQLのレプリケーションによってレプリカにデータが反映される流れ

Slide 17

Slide 17 text

レプリケーションの遅延を解消するには？ 17 ● 根本解決にはDBの分割で書き込みを分散させる必要があった ○ イベント開催中ですでに障害が発生している状況 ○ アプリケーションの大幅な設計変更が必要で対応が困難 ● レプリカ側の更新負荷を分散させる方法はないか検討 ○ 数日以内に解決できる必要がある

Slide 18

Slide 18 text

レプリケーションフィルター 18 ● MySQLのレプリケーションフィルタを使用 ● レプリケーションするテーブルを制御し、Replica側の更新量を絞る replicate-do-table レプリケーションするテーブルを指定 replicate-ignore-table レプリケーションを除外するテーブルを指定

Slide 19

Slide 19 text

19 レプリカを4つのグループに分けてフィルタリング設定 Source Replica Card 変則レプリケーション：レプリケーションフィルター Replica Gift Replica Other Replica All カード関連テーブルのみギフトボックス関連テーブルのみカードとギフトボックス以外のテーブルのみすべてのテーブル

Slide 20

Slide 20 text

変則レプリケーション導入後のメインDB構成 20 メインDB Source メインDB Replica メインDB Replica メインDB Replica 「CARD」 APP サーバ APP サーバ APP Load Balancer ユーザー (スマホ / PC ) レプリケーションプライベートクラウドメインDB Replica メインDB Replica メインDB Replica 「GIFT」メインDB Replica メインDB Replica メインDB Replica 「OTHER」メインDB Replica 「ALL」書き込みクエリー読み取りクエリー

Slide 21

Slide 21 text

21 この対応でレプリケーション遅延は解消してイベントを乗り切った ● 副次的な効果でInnoDB Buﬀer Poolがより効率的に利用レプリケーションフィルター導入後しかし、フィルター導入後は以下の課題がでてきた ● pt-online-schema-changeが利用できない ● MySQL MHAを利用した冗長化ができない ● サーバコストが余計にかかる

Slide 22

Slide 22 text

「pt-online-schema-changeが利用できない」 22

Slide 23

Slide 23 text

23 pt-online-schema-change(pt-osc)とは ● Percona社が公開しているPercona Toolkitに含まれるツールの一つ ● オンラインでMySQLのスキーマ変更が可能になる ● 変則レプリケーション構成の前から使用していた replicate-ignore-tableを指定したレプリカがいる環境で障害発生 ● pt-osc実行後、レプリカ側でreplicate-ignore-tableで指定しているはずのテーブルで全件データコピーが始まり、レプリケーションが遅延し続けた ● replicate-do-table指定のレプリカでは発生しない pt-online-schema-changeが利用できない

Slide 24

Slide 24 text

「MySQL MHAを利用した冗長化ができない」 24

Slide 25

Slide 25 text

25 ● 自動フェイルオーバーを実現可能なオープンソースソフトウェア ● データ不整合を防ぎつつフェイルオーバーが可能な仕組み MySQL MHAとは Source Replica 1 Replica 2 MHA Manager MHA Node MHA Node MHA Node レプリケーション監視・昇格オペレーション

Slide 26

Slide 26 text

26 1. ソースで障害がおきてMHA Managerがダウンを検知 MySQL MHAでのフェイルオーバーの流れ Source Replica 1 Replica 2 MHA Manager MHA Node MHA Node MHA Node レプリケーション監視・昇格オペレーション

Slide 27

Slide 27 text

27 2. MHA Managerがレプリカ構成の再確認と新ソースの決定 Source Replica 1 (新Source) Replica 2 MHA Manager MHA Node MHA Node MHA Node 監視・昇格オペレーション MySQL MHAでのフェイルオーバーの流れ

Slide 28

Slide 28 text

28 3. 各Nodeが全サーバからデータを収集して差分の修正適用 Source Replica 1 (新Source) Replica 2 MHA Manager MHA Node MHA Node MHA Node ・リレーログ解析・バイナリログ生成、適用 MySQL MHAでのフェイルオーバーの流れ

Slide 29

Slide 29 text

29 4. レプリケーション再設定実行 Replica 1 (新Source) Replica 2 MHA Manager MHA Node MHA Node 監視・昇格オペレーションレプリケーション MySQL MHAでのフェイルオーバーの流れ

Slide 30

Slide 30 text

「MySQL MHAを利用した冗長化ができない」 30

Slide 31

Slide 31 text

31 ● MHAはすべてのレプリカが同じフィルタリングルールである必要がある ○ 変則レプリケーションでは利用不能 ○ MHAを利用した即時フェイルオーバーができなくなった MySQL MHAを利用した冗長化ができない [error] Replication filtering check failed All slaves must have same replication filtering rules. Check SHOW SLAVE STATUS output and set my.cnf correctly. [warning] Bad Binlog/Replication filtering rules: 変則レプリケーション利用時のMHA起動のエラー文

Slide 32

Slide 32 text

32 全テーブルデータを持っている「Replica All」DBを手動で昇格させても、非同期レプリケーションの為、他のReplicaとデータが揃っている保証がない Replica Card Replica Gift Replica Other 新Source (旧Replica All) MySQL MHAがない状態でソースDBで障害が起きると旧Sourceのポジション: 101　まで実行済み旧Sourceのポジション: 100　まで実行済み旧Sourceのポジション: 102 まで実行済み旧Sourceのポジション: 101　まで実行済みレプリケーション

Slide 33

Slide 33 text

33 データを揃える為に新Sourceを元にデータをコピーしなおす必要があるデータサイズが巨大なため、全Replicaへのコピーだけで半日はかかる試算データコピーが不要な方法を考える必要があったソースレプリカ間のデータ不整合を防ぐために Replica Card Replica Gift Replica Other 新Source (旧Replica All) 旧Sourceのポジション: 101　まで実行済み新Sourceのデータを各Replicaにコピーしなおすデータコピー

Slide 34

Slide 34 text

34 そこで採用したのが、待機系レプリカを用いた多段レプリケーションの構成多段レプリケーション構成 34 Source Card Gift Other Replica All 待機系 Card 待機系 Gift 待機系 Other 待機系 All レプリケーションレプリケーション

Slide 35

Slide 35 text

35 現ソースがダウンしても、次期ソースとその配下のレプリカ群は同期がとれている 35 Source Card Gift Other Replica All 旧Sourceのポジション: 101　まで実行済み旧Sourceのポジション: 102　まで実行済み待機系 Card 待機系 Gift 待機系 Other 待機系 All 旧Source ポジション: 101　相当まで実行済みレプリケーション多段レプリケーション構成によるデータコピーの回避

Slide 36

Slide 36 text

「サーバコストが余計にかかる」 36

Slide 37

Slide 37 text

37 レプリケーションフィルタリング＋多段レプリケーションの構成で約1年間運用 ● 幸運なことにソースDBがダウンする障害発生なし ● 負荷増加で待機系DBにもアプリ参照を向けた時もあった... 状況が少し落ち着いたタイミングで ● DBのサーバー費用が倍額かかるのはもったいない ● 待機系DBをなくすことはできないか検討サーバコストが余計にかかる

Slide 38

Slide 38 text

Blackhole Storage Engine 38

Slide 39

Slide 39 text

39 MySQLのストレージエンジンの一種このストレージエンジンが設定されているテーブルは、更新クエリを受け付けるがデータは破棄して更新しないこれを各Replicaのグループごとに設定することでフィルタリングと同様の状態にした Blackhole Storage Engineとは Source Card カード関連テーブルのみInnoDB、それ以外Blackhole Gift Other All ギフトボックス関連テーブルのみInnoDB、それ以外Blackhole カードとギフトボックス関連テーブルのみInnoDB、それ以外Blackhole すべてのテーブルがInnoDB レプリケーション

Slide 40

Slide 40 text

MySQLの設定からフィルタリングを消せるのでMHAが利用できる ● 待機系DBを撤去可能になるのでコスト削減既存テーブルをBlackhole化するにあたっては注意が必要 ● 外部キー制約などがあるテーブルをBlackhole化しようとするとエラーとなる場合がある ● 一度DROP TABLEして定義を変更して作成しなおすなどの対応が必要になる Blackhole Storage Engineを導入をする 40

Slide 41

Slide 41 text

Blackholeを導入して運用した結果、以下のような課題がでてきた ● 新規テーブル追加時はレプリカのグループごとに毎回Blackhole化が必要 ○ どのレプリカグループでBlackholeにするか認識があっていないと障害につながる ● ソースでテーブル定義変更のクエリを実行した際、エラーでレプリケーションが停止 ○ 制約でレプリカのテーブル定義を変更している為、ソースとテーブル定義が異なる ● データベースの復旧手順が複雑で属人化 ○ バックアップからデータを戻したあと、レプリカグループごとにルールにそって Blackhole化する必要がある ○ ドキュメント化したがルールと手順を完全に把握していた私一人 Blackhole Storage Engine導入後の課題 41

Slide 42

Slide 42 text

『ガールフレンド(仮)』におけるデータベースの負債 42 ● 変則レプリケーション ● DBサイズの肥大化

Slide 43

Slide 43 text

MySQLへの負荷が落ち着いてきたのでBlackhole構成をやめたいしかしこの間にDBデータサイズが肥大化 Gift レプリカグループの10テーブルだけで1.6TBを超えた機材の都合で全レプリカのテーブルをInnoDB化することができなくなっていた Blackhole Storage Engineやめたい...が Source Card NVMe SSD 1.6TB 1枚 Gift Other All NVMe SSD 1.6TB 2枚 RAID 0 NVMe SSD 1.6TB 1枚 NVMe SSD 1.6TB 2枚 RAID 0 NVMe SSD 1.6TB 2枚 RAID 0 レプリケーション 43

Slide 44

Slide 44 text

ギフトボックス 44

Slide 45

Slide 45 text

ギフトボックス ● ログインボーナスやイベントの報酬などがここに送られる ● ユーザーの任意のタイミングで自身のインベントリなどに追加ガールフレンド(仮)ではこのギフトボックスに下記の課題があった ● 受取日時の期限がないギフトが多かった ● インベントリが一杯であえてギフトボックスに貯めておくユーザーが一定数存在したギフトボックス問題 45

Slide 46

Slide 46 text

全テーブルのデータサイズ: 2.1TB Giftテーブルデータサイズ: 1.6TB Giftテーブル総レコード数:約38億レコード貯めた件数が多い1ユーザのレコード数：数十万〜百数十万レコード DBデータの状況 46

Slide 47

Slide 47 text

ギフトボックスの仕様の変更 ● ギフトボックスの一部アイテムをポイント（数値）に変換する仕様を追加 ● 1アイテム=1レコードだったので集約ができる ● ユーザーの不利にならないような一定のルールで一括変換をおこなう巨大な複合インデックスの削除 ● レコード件数が極端に多いユーザーがギフトボックスを開くだけで負荷高騰した ○ 多数のカラムが指定された巨大な複合インデックスで対応していた ● 1ユーザあたりのレコード件数を減らすことで、複合インデックスを削除できるか Giftテーブルの改善の方針 47

Slide 48

Slide 48 text

仕様変更によるギフトボックスデータの一括変換 ● 数時間のメンテナンスが3回ほど必要になったが、半年かけてデータの変換をおこなった巨大な複合インデックスの削除 ● Giftレプリカから1台ずつ複合インデックスを削除し、大きな影響がないことを確認 OPTIMIZE TABLEを実行 ● MySQLの仕様上DELETEをしてもデータ領域は開放されないので最適化実行 Giftテーブルの改善の結果改善前改善後 Giftテーブルレコード総数 38億レコード 26億レコード(31%減) Giftテーブルデータサイズ 1.6TB 0.6TB(61%減) DB全テーブルデータサイズ 2.1TB 1.0TB(52%減) 48

Slide 49

Slide 49 text

DBサイズが縮小したことで、全レプリカが全テーブルInnoDB化しても1枚のSSDに収まるように ● Blackhole特有のオペレーションが不要になった ● DB復旧手順も単純になったので属人化要素が解消 Blackholeをやめて単純なレプリケーション構成に戻せた Source Replica 全テーブルInnoDB化 DBサイズ 1.0TB Replica Replica Replica レプリケーション 49

Slide 50

Slide 50 text

今後の課題 50

Slide 51

Slide 51 text

● Giftレコードの総数26億レコードはまだ多いので引き続き削減したい ○ 1ユーザーが数十万レコード抱えている状況はまだある ● 3系統あるメインDB,イベントDB,ヒストリDBを統合できないか ○ メインDBの構成はシンプルになったがレプリカの合計は数十台規模 ○ レコード削減による負荷軽減などで台数や構成の最適化余地がある今後の課題 51

Slide 52

Slide 52 text

● 歴史の長いサービスには特有の課題もでてくる ○ 負荷対策 ○ データ肥大化 ● 課題解決の手段が後になって負債化することもある ○ レプリケーションフィルタリング ○ 多段レプリケーション ○ Blackhole Storage Engine ● 負債を放置せず改善を続けていくことが大切まとめ 52

Slide 53

Slide 53 text

ご清聴ありがとうございました 53