TiDBにおけるテーブル設計と最適化の事例

2024/03/08 TiDB GAME DAY 2024

Transcript

1. TiDB GAME DAY 2024 株式会社Cygames サーバーサイド 牧内 一真 TiDBにおけるテーブル設計と最適化の事例
2. 1/46 Cygamesについて
3. 2/46 Cygamesについて 「最高のコンテンツを作る会社」
4. 3/46 Cygamesについて スマートフォン向けゲーム コンシューマーゲーム・アニメ e-sportsなど
5. 4/46 1. TiDBの性能検証について 2. テーブル設計の改善 3. 負荷試験で発生した事例 4. まとめ アジェンダ
6. 5/46 牧内 一真 サーバーサイド /サーバータスクチーム（PJ横断部署） ソーシャルゲーム開発会社を経て、2023年に株式会社Cygamesに合流 サーバーサイドの横断プロジェクトに所属し Cygamesの新規プロジェクトの性能改善とTiDBの導入支援に従事
7. 6/46 TiDBの性能検証について
8. 7/46 検証チームによるTiDBの性能検証 基本方針 性能に課題がある場合、テーブル設計/クエリチューニングを実施 検証段階でチューニングして改善 ゲーム特有のワークロードで検証 今回パラメーターチューニングは対象外 Cygamesがこれまで運用してきたテーブルやクエリを用いることで、 検証結果に説得力を持たせることができる
9. 8/46 検証チームによるTiDBの性能検証 フレンド申請/許可 ゲーム特有のワークロードを実現するシナリオ例 ガチャ/10連ガチャ TOPページ レイドバトル プレゼント配布/参照/受取 ランキング参照/ユーザーの順位参照 など...
10. 9/46 検証チームによるTiDBの性能検証 性能評価の結果 新規タイトルにてTiDB Cloudの負荷試験が進行中！ 運用に耐えられる性能を引き出すことができた チューニングで得た知見で開発チームをサポート 設計ガイドラインを制定 テーブル設計とクエリのレビューを実施 性能検証でのチューニングの事例をご紹介します！
11. 10/46 テーブル設計の改善
12. 11/46 今回の事例は... プレゼントBOX
13. 12/46 今回のシナリオ 書き込みシナリオ プレゼント(100件)を付与する 読み込みシナリオ プレゼントの一覧を1ページ(100件)ずつ取得していく 1ユーザーあたり合計1000件のプレゼントを100万ユーザー sysbenchを使って限界性能を確認
14. 13/46 検証環境のテーブルとスペック Field Type Extra id bigint auto_increment user_id bigint ※アカウント作成時にランダムな値を発行 item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime PrimaryKey id, delete_time INDEX user_id, create_time TiDB Cloud TiDB (16 vCPU, 32 GiB) x2台, TiKV (16 vCPU, 64 GiB) x3台 Aurora db.r6g.4xlarge (16vCPU, 128GB) x1台
15. 14/46 書き込みのパフォーマンスが課題 書き込みシナリオの結果 1374.97 904.88 0 500 1000 1500 Aurora (auto_increment) TiDB (auto_increment) プレゼント書き込み (QPS)
16. 15/46 読み込みのパフォーマンスも課題 読み込みシナリオの結果 4422.81 728.27 0 2000 4000 6000 Aurora (auto_increment) TiDB (auto_increment) プレゼント参照 (QPS)
17. 16/46 ホットスポットの確認 rowの書き込みにホットスポットが発生しボトルネックに Key Visualizer rowの書き込みが 特定のリージョンに集中 Indexの書き込みは 分散している
18. 17/46 書き込みのボトルネック解消 Field Type Extra id bigint auto_random user_id bigint ※アカウント作成時にランダムな値を発行 item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime auto_increment から auto_random に変更 TiDBのベストプラクティスに変更
19. 18/46 ホットスポットが解消 rowの書き込みが分散 Indexは 変わらず Key Visualizer
20. 19/46 auto_randomの結果(書き込み) 書き込みのパフォーマンスが若干改善 1374.97 904.88 1205.89 0 500 1000 1500 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) プレゼント書き込み (QPS)
21. 20/46 auto_randomの結果(読み込み) 読み込みのパフォーマンスはまだ課題 4422.81 728.27 794.38 0 1000 2000 3000 4000 5000 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) プレゼント参照 (QPS)
22. 21/46 実行計画の確認 id actRows access object time Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500; TableRowIDScanの時間が多い
23. 22/46 SQLステートメントを実行するために必要なサブタスクのひとつ IndexRangeScanで取得したRowIDをもとに 一致するデータを１行ずつ取得する 取得したデータ行をIndexLookUpで集計 TableRowIDScan とは
24. 23/46 実行計画の確認 id actRows access object time Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present Explain Analyze の結果(一部抜粋) １ ①IndexからRowIDを取得 select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
25. 24/46 実行計画の確認 Explain Analyze の結果(一部抜粋) id actRows access object time Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present 2 ②RowIDをもとにレコードを１件ずつ取得 select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
26. 25/46 実行計画の確認 Explain Analyze の結果(一部抜粋) id actRows access object time Limit_13 100 70.7ms └─Projection_31 600 70.7ms └─IndexLookUp_30 600 70.7ms ├─Selection_28(Build) 1000 idx_user_id(user_id,create_time) 26.2ms │└─IndexRangeScan_26 1000 └─Selection_29(Probe) 1000 83.3ms └─TableRowIDScan_27 1000 table:user_present 3 ③取得したレコードを集計する select * from user_present where user_id = ? and not received and delete_time = ? order by create_time desc limit 100 offset 500;
27. 26/46 テーブル設計のポイント Indexの設計を間違えると スキャンの件数が増えてボトルネックに！ ストレージが分散KVSのTiDBでは MySQLよりもパフォーマンスへの影響が大きい TableRowIDScanの件数に注意！
28. 27/46 TableRowIDScanを避けるには IndexReader Selectクエリがカバリングインデックスになる場合に選択 IndexRangeScanなど Indexが増えるので書き込みに影響があるかも... TableReader テーブル情報をスキャン TableRangeScanなど TableFullScanにならないように要注意！
29. 28/46 TableReaderになるように変更 Field Type Extra user_id bigint ※アカウント作成時にランダムな値を発行 ulid char(26) item_id bigint item_count bigint received tinyint(1) create_time datetime delete_time datetime PrimaryKey user_id, ulid, delete_time ①user_idで絞り込めるようにPKの先頭に追加 ②ulidで時間順に保存されるようにPKに含める
30. 29/46 変更後の実行計画(ulid1) id actRows access object time Limit_12 100 13.3ms └─TableReader_25 600 13.3ms └─Limit_24 704 └─Selection_23 704 └─TableRangeScan_22 704 table:user_present 実行時間の短縮に成功！ Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and not received and delete_time = ? order by ulid desc limit 100 offset 500;
31. 30/46 変更後の実行計画(ulid2) id actRows access object time Limit_11 100 4.1ms └─TableReader_20 100 4.09ms └─Limit_19 100 └─Selection_18 100 └─TableRangeScan_17 100 table:user_present 検索行数が減りスキャンがより効率的に ※仕様によっては適用できない Explain Analyze の結果(一部抜粋) select * from user_present where user_id = ? and ulid < ? not received and delete_time = ? order by ulid desc limit 100;
32. 31/46 ulidの結果(読み込み) 読み込みのパフォーマンスが改善！ 4422.81 728.27 794.38 17516.38 24294.22 0 10000 20000 30000 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) TiDB (ulid1) TiDB (ulid2) プレゼント参照 (QPS)
33. 32/46 1374.97 904.88 1205.89 4078.99 0 2000 4000 6000 Aurora (auto_increment) TiDB (auto_increment) TiDB (auto_random) TiDB (ulid) プレゼント書き込み (QPS) ulidの結果(書き込み) 書き込みのパフォーマンスも改善！ ULIDで改善したのは、INDEXがなくなったのが理由の一つと考えられる
34. 33/46 同様にPrimary Keyを変更することによって 性能が変化する事例をご紹介します
35. 34/46 ランキングのシナリオ ランキング表示を想定したシナリオ 指定した範囲の順位のデータを100件取得する ユーザーのランク表示を想定したシナリオ user_idで1件取得する
36. 35/46 ランキングの結果 104634.95 30797.64 77305.51 41360.63 user_idで一件取得 rankで範囲取得 QPS PRIMARY KEY rank UNIQUE KEY user_id ranking rank bigint user_id bigint score bigint PKがrank の場合 TableRangeScan になるので効率が良い UniqueKey より PrimaryKey の方が効率が良い PRIMARY KEY user_id INDEX rank 重視するクエリの効率が良い設計を選択
37. 36/46 テーブル設計のポイント まとめ 読み込みはユーザー単位で集中 書き込みはシステム全体で分散 ホットスポットは書き込みのボトルネックになる Indexの設計を間違えるとスキャン件数が増えてボトルネックになる テーブル設計を工夫してRangeScanで取れるように 分散KVSであることを意識して設計しましょう！
38. 37/46 負荷試験で発生した事例
39. 38/46 UPDATE クエリ 「プレゼント200件を受け取り済みにする」クエリ COMMIT クエリ 新規タイトルの負荷試験で SlowQueryが発生！
40. 39/46 TiDB CloudコンソールからMetricsやDiagnosisを確認するも テーブルやクエリによる問題はなかった 原因がわからず... PingCAPさんに調査を依頼
41. 40/46 SlowQuery発生中のTiKVのCPU使用率 Metrics：TiKV CPU Usage (800%) CPU使用率が 70%～90% 程度 シナリオによっては70%程度を超えるとリソースが不足
42. 41/46 TiKVのポイント CPU使用率70%程度でも書き込みに影響が出る TiKVの特性に合わせたモニタリングを実施しましょう CPU使用率に注意！
43. 42/46 補足: UPDATEの実行結果 Time tab QueryTimeは1.5s TiKVの実行時間 が少ない
44. 43/46 補足: COMMITの実行結果 Time tab PrewriteTimeに1.3s TiKVのリソース不足を示唆しているかも...？
45. 44/46 まとめ
46. 45/46 MySQLの設計を流用してもうまくいくとは限らない 本番のクエリの割合や重要度に合わせて設計しよう TiDBはチューニングによる効果が大きいので ユースケースに合わせてチューニングしよう TiKVのCPUには余裕を持って！
47. 最強のバックエンドで最高のゲームを！ 私たちは「最高のコンテンツ」を支えるために、 常に技術を進化させるチャレンジをしていきます。