Upgrade to Pro — share decks privately, control downloads, hide ads and more …

@cosmeがレガシーシステムをクラウドネイティブDBに載せ替える理由 - db tech showcase 2022 Tokyo

PingCAP-Japan
November 22, 2022

@cosmeがレガシーシステムをクラウドネイティブDBに載せ替える理由 - db tech showcase 2022 Tokyo

20年続くサービス"@COSME"のシステム刷新に伴い、データベースの課題も解決するべくTiDBを選択しました。
このスライドでは、性能検証や移行検証で出た課題や選定理由、スケールアウト以外でTiDBが向いているシステムなどについて紹介いたします。
出典:db tech showcase 2022

トピック:
・SQL Serverの便利機能で生まれる技術課題
・SQL Serverの移行先にTiDBを選んだ理由
・TiDB移行PoCの結果

アーカイブ動画:
https://youtu.be/bkfgiZ0e7m4

PingCAP-Japan

November 22, 2022
Tweet

More Decks by PingCAP-Japan

Other Decks in Technology

Transcript

  1. 鈴木利房
 
 # 所属
 株式会社アイスタイル
 T&C開発センター
 第1開発本部クラウドソリューション部
 サービスインフラグループ
 
 #

    主な役割
 データベース構築・運用、クエリレビュー、テーブル設 計レビューなど
 
 自己紹介
 © istyle Inc. No.1
  2. © istyle Inc. No.2 WHO WE ARE
 MARKET DESIGN COMPANY

    アイスタイルグループは、 
 独自のデータベースをもとに、 
 常に生活者視点で未来を見据え、 マーケットそのものを 
 デザインする企業 

  3. © istyle Inc. No.4 OUR VISION 生活者中心の市場創造 ネットとリアルを融合した ビューティに関わるヒト・コト・モノの データーベースプラットフォームビジネスを展開

    DATABASE 行動データ クチコミデータ アクションデータ 商品データ ブランドデータ マーケティングデータ 生活者 企業 伝えたい 知りたい 売りたい 知りたい 伝えたい 買いたい
  4. © istyle Inc. No.5 • 国内No.1美容総合メディア • 20~30代女性の過半数が毎月利用
 ⽉間ユニークユーザー 1,430

    万人
 登録会員数
 680万人
 登録ブランド数
 40,000ブランド
 クチコミ数
 1,690万件
 登録商品数
 36万点
 アイスタイル調べ 2021年6月現在 

  5. © istyle Inc. No.6 取り扱いアイテム数 約 46,000 アイテム
 取り扱いブランド数 約

    2,400 ブランド
 (2021年1月時点) 
 国内No.1の化粧品取扱数を誇る化粧品ECサイト
  6. © istyle Inc. No.8 • 国内No.1の正規取扱ブランド数 • 国内No.1の敷地面積を誇るフラッグシップ店舗 • DXチャレンジング店舗

    日本最大級 ラグジュアリーからプチプラまで 600 ブランド以上
 10~30代が60%を占める F1/F2層リーチ (エリア特性から得られるリーチ)
  7. •メリット
 • 開発工数の削減
 
 •デメリット
 • テーブル定義変更の影響範囲が広い
 • レプリケーション障害の影響範囲が広い
 •

    そのテーブルのデータの発生元が分かりにくい
 この機能を使うメリットとデメリット
 © istyle Inc. No.22
  8. SERVER2 SERVER1 DB3 こうなった経緯
 © istyle Inc. No.32 DB1 DB2

    Application 3 Application 2 APIコール API向け改修 API新規作成
  9. SERVER2 SERVER1 DB3 こうなった経緯
 © istyle Inc. No.33 DB1 DB2

    Application 3 Application 2 APIコール API向け改修 API新規作成 この改修工数をなんと かできないか
  10. SERVER2 SERVER1 DB3 こうなった経緯
 © istyle Inc. No.34 DB1 DB2

    Application 3 Application 2 簡単にできる、このやり 方を選んでしまった
  11. •次の3つを移行先候補に挙げた
 移行先候補
 © istyle Inc. No.38 RDBMS レプリケーション 異なるDB間の テーブルJOIN

    異なるサーバー間のテー ブルJOIN SQL Server(EC2) RDS for SQL Server RDS Aurora MySQL
  12. •必要な機能で比較すると、SQL ServerをEC2で運用するという嬉しくない結 果になってしまった
 •残念なことにRDS for SQL Serverにはレプリケーション機能が未実装だっ た
 
 移行先候補の機能比較


    © istyle Inc. No.39 RDBMS レプリケーション 異なるDB間の テーブルJOIN 異なるサーバー間のテー ブルJOIN SQL Server(EC2) ✅ ✅ ✅ RDS for SQL Server ❌ ✅ ✅ RDS Aurora MySQL ❌ ✅ ❌
  13. 移行先候補の機能比較3
 © istyle Inc. No.48 RDBMS レプリケーション 異なるDB間の テーブルJOIN 異なるサーバー間のテー

    ブルJOIN SQL Server(EC2) ✅ ✅ ✅ RDS for SQL Server ❌ ✅ ✅ RDS Aurora MySQL ❌ ✅ ❌ TiDB ✅ ※全DBが同一クラスタ内 のためレプリケーション不 要 ✅ ✅ ※全DBが同一クラスタ内 のためJOIN可能 •移行先候補に分散RDBMSのTiDBを追加した
 •SQL Server以外の選択肢が出てきた
 追加
  14. •次の条件で検証対象を選択した
 •クチコミ関連のシステムが対象に選ばれた
 TiDB移行PoCの内容・対象
 © istyle Inc. No.51 No 選定条件 1

    2つ以上のDBを使用し、DB間でJOINしたクエリを実行している 2 ストアドプロシージャを使用している(カーソル使用と未使用) 3 トリガーを使っている 4 自動採番の主キーをソート順に使用している 5 SQLが直書きされている 6 CTEが使用されている 7 Window関数が使われている 8 文字列を読み書きしている 9 古い実行環境を使用している
  15. スキーマ変換ツールの評価結果
 © istyle Inc. No.53 項目 MNMTK SQLines テーブル SQL

    Server固有のオプションがそのま ま残った SQL Server固有のオプションがそのま ま残った ビュー、トリガー、ストアドプロシージャ 関数の引数変換と スキーマ名の除去 はできなかった 関数の引数変換と、スキーマ名の除 去はできなかった 変換方法 DBへ接続して変換 ファイルを変換 •どちらも完璧な変換はできなかった
 •SQLinesの方が、ツールがシンプルで使い勝手が良かった

  16. •移行時に対応が必要な非互換性があった
 •実際に移行を実施する時は、アプリケーション仕様変更が必要になる
 スキーマ変換では課題が残った
 © istyle Inc. No.54 課題 対応案 非決定的な関数を使った計算列を作れない

    現在日時を使った計算列が作れないため、誕 生日などが計算列にできない。 次の案から選択してもらう 1. 参照処理を修正し、クエリ内に実装 2. ビューを作成する フィルター選択されたインデックスがない この機能と論理削除を併用して未削除行のみ ユニーク制約を設定しているが、これが実現で きない。 生成されたカラムを作成し、削除フラグが0の時はユニーク制約をかけ たい値を入れ、1の場合はNULLを入れる。 TiDB(MySQL)ではNULLは重複と見なされないため、間接的に部分イ ンデックス相当の動作を実現できます。
  17. •AWS Data Migration Serviceでデータ移行を行った
 •フルロード時にSQL Serverの再初期化のようなスキーマロックが発生せ ず、移行元の負荷が低く、本番稼働中に安心して実行できた
 データ移行ツールの評価
 © istyle

    Inc. No.55 検証項目 結果 データ移行速度 実測値平均 60万行/分 データ移行元サーバーのCPU負荷 10%未満の増加が見られたが問題なし データ移行結果 DMSの検証機能で移行エラーなし 日本語の文字化け SJISからUTF-8への変換だったが、文字化けは発生しなかった
  18. •TiDBはMySQL5.7プロトコル互換
 •JavaとPHPを検証したが、PHPはバージョンアップが必要だった
 DBアクセスライブラリーの変更
 © istyle Inc. No.56 言語 接続可能バージョン URL

    Go 1.1.3 以降 https://github.com/go-sql-driver/mysql Java Java 1.8.0/ JDK5.0 以降 https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-versions.html Node.js 明記無し • https://www.npmjs.com/package/mysql2 • https://www.npmjs.com/package/mysql • https://www.mysql.com/jp/products/connector/ PHP 5.6 以降 https://www.php.net/manual/ja/mysql.php Python 2.6 以降 https://dev.mysql.com/doc/connector-python/en/connector-python-versions. html Ruby 2.0.0 以降 https://github.com/brianmario/mysql2
  19. •アプリケーション内のクエリ修正を検証した
 •Window関数もCTEも使えるため、クエリの構造自体を大きく変更する必要 は無かった
 •関数の変換や、スキーマ名(dbo)の除去など軽微な修正で済んだ
 
 SQLの修正
 © istyle Inc. No.57

    オブジェクト階層 SQL Server TiDB(MySQL) データベース ✅ ✅ スキーマ ✅ ❌ テーブル ✅ ✅ 例 db1.dbo.table1 db1.table1 SQL ServerとTiDB(MySQL)のオブジェクトの階層の違い
  20. • スキーマ名「dbo」の除去 • TOPをLIMITに変更 • UPDATEのFROMを書き換え
 • WITH(NOLOCK)を除去
 • IDENTITY_INSERTの除去


    • DATEADDをTIMESTAMPADDに変更
 SQLの修正工数
 © istyle Inc. No.58 処理概要 修正工数 複数DBのJOIN、CTE、Window関数あり 0.5人日/クエリー 単一DBのSELECT、 INSERT、UPDATE 0.1人日/クエリー • 予約語と重複した名称を変更 • MERGEを複数クエリに分割 • CTEを使ってINSERTする場合の記述順序を変 更 • DB名の追記 • 文字列結合の+をCONCAT関数に変更 具体的な修正内容
  21. •DBMS単体の速度検証をするため、SQLでの負荷テストを実施した
 パフォーマンス検証
 © istyle Inc. No.64 No テストケース 処理概要 1

    ランダムIDのSELECT 複数DBのJOIN、CTE、Window関数あり 2 固定IDのSELECT 3 INSERT 4 ランダムIDでのSELECT、INSERT、UPDATE 単一DBのSELECT、INSERT、UPDATE 5 ランダムIDでのSELECT、INSERT、UPDATE (クラスタスケールアップして実施) 6 月別クチコミ件数の取得 分析系クエリ 7 ブランド毎のクチコミ件数
  22. •テスト環境
 パフォーマンス検証
 © istyle Inc. No.65 No テストケース クラスター構成 1

    ランダムIDのSELECT TiDB(8CPU, 16GB RAM) x 5 TiKV(8CPU, 64GB RAM) x 3 2 固定IDのSELECT 3 INSERT 4 ランダムIDでのSELECT、INSERT、UPDATE TiDB(8CPU, 16GB RAM) x 5 TiKV(8CPU, 64GB RAM) x 3 5 ランダムIDでのSELECT、INSERT、UPDATE (クラスタスケールアップして実施) TiDB(16CPU, 128GB RAM) x 5 TiKV(8CPU, 64GB RAM) x 3 6 月別クチコミ件数の取得 TiDB(16CPU, 128GB RAM) x 5 TiKV(8CPU, 64GB RAM) x 3 7 ブランド毎のクチコミ件数
  23. シナリオ tx 合計 tps min (ms) 50% tile 90% tile

    95% tile 99% tile max (ms) 1クライアント 60秒測定 2,318 38.6 19 20 38 39 42 62 50クライアント 60秒測定 102,281 1,704.7 17 23 41 45 60 1,518 100クライアント 60秒測定 142,284 2,371.4 15 32 59 66 80 16,136 1クライアント 120秒測定 4,684 39.0 18 20 38 39 42 76 50クライアント 120秒測定 203,628 1,696.9 16 24 42 46 60 1,060 100クライアント 120秒測定 337,113 2,809.3 16 31 56 64 83 344 テストケース1
 © istyle Inc. No.66 •ランダムIDのSELECT •99パーセンタイルで100ms以下でレスポンスを返せた
 •16秒越えの外れ値があるが、サーバースペックの問題であった

  24. シナリオ tx 合計 tps min (ms) 50% tile 90% tile

    95% tile 99% tile max (ms) 1クライアント 60秒測定 1,497 25.0 37 39 41 41 44 62 100クライアント 60秒測定 97,457 1,624.3 35 58 76 84 135 317 500クライアント 60秒測定 116,664 1,944.4 74 252 353 383 442 731 1クライアント 120秒測定 2,908 24.2 39 41 42 42 48 60 100クライアント 120秒測定 195,626 1,630.2 33 59 78 84 99 318 500クライアント 120秒測定 234,385 1,953.2 60 251 347 378 432 637 テストケース2
 © istyle Inc. No.67 •対象ID固定でSELECT •全般的にランダムIDでのSELECTより遅くなっている
 •処理が分散されないためと考えられる

  25. シナリオ tx 合計 tps min (ms) 50% tile 90% tile

    95% tile 99% tile max (ms) 1クライアント 60秒測定 2,825 47.1 19 20 21 23 28 61 100クライアント 60秒測定 243,756 4,062.6 18 24 27 28 33 279 500クライアント 60秒測定 1,092,436 18,207.3 18 26 31 33 43 266 1クライアント 120秒測定 5,524 46.0 20 21 22 22 26 242 100クライアント 120秒測定 488,361 4,069.7 18 24 27 28 35 251 500クライアント 120秒測定 2,244,617 18,705.1 18 26 29 31 44 313 テストケース3
 © istyle Inc. No.68 •INSERT •接続数の増加によるレスポンス低下は見られない
 •主キーで分散を行うため

  26. シナリオ tx 合計 tps min (ms) 50% tile 90% tile

    95% tile 99% tile max (ms) 20クライアント 60秒測定 2,825 47.1 19 20 21 23 28 61 60クライアント 60秒測定 109,676 1,827.9 24 32 35 37 43 100 100クライアント 60秒測定 168,393 2,806.6 23 33 37 39 59 15,173 20クライアント 120秒測定 76,525 637.7 23 30 34 35 43 117 60クライアント 120秒測定 214,444 1,787.0 24 32 36 38 53 274 100クライアント 120秒測定 339,252 2,827.1 23 34 38 40 56 13,933 テストケース4
 © istyle Inc. No.69 •ランダムなIDでSELECT、INSERT、UPDATEを繰り返すシナリオ •レスポンスは100ms以内
 •極端にレスポンスが遅い外れ値が録された
 •TiDBノードのマシンスペックが低すぎた

  27. シナリオ tx 合計 tps min (ms) 50% tile 90% tile

    95% tile 99% tile max (ms) 500クライアント 60秒測定 614,940 10,249.0 28 46 55 60 95 339 500クライアント 120秒測定 1,240,484 10,337.4 25 46 54 59 87 696 500クライアント 180秒測定 1,808,709 10,048.4 27 47 57 63 105 694 テストケース5
 © istyle Inc. No.70 •ランダムなIDでSELECT、INSERT、UPDATEを繰り返すシナリオ
 •TiDBノードのスペックを変更して再テスト実施
 •クライアント数を5倍にしても、最大値が秒以下になった

  28. •運用面の機能確認を実施
 • バックアップ、リストア
 • スケールアウト、スケールイン
 • スロークエリ
 • 監査ログ
 •

    PiTR
 • オブジェクトセキュリティ
 •一部未実装のものがあったが、実装予定があることを確認した
 
 PoCで他にやった事
 © istyle Inc. No.72