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

鈴木利房    # 所属  株式会社アイスタイル  T&C開発センター  第1開発本部クラウドソリューション部  サービスインフラグループ    #
主な役割  データベース構築・運用、クエリレビュー、テーブル設計レビューなど    自己紹介  © istyle Inc. No.1

© istyle Inc. No.2 WHO WE ARE  MARKET DESIGN COMPANY
アイスタイルグループは、   独自のデータベースをもとに、   常に生活者視点で未来を見据え、マーケットそのものを   デザインする企業  

© istyle Inc. No.3 OUR VISION 生活者中心の市場創造ネットとリアルを融合したビューティに関わるヒト・コト・モノのデーターベースプラットフォームビジネスを展開
DATABASE 行動データクチコミデータアクションデータ商品データブランドデータマーケティングデータ生活者企業伝えたい知りたい売りたい知りたい伝えたい買いたい

© istyle Inc. No.4 • 国内No.1美容総合メディア • 20～30代女性の過半数が毎月利用  ⽉間ユニークユーザー 1,430
万人  登録会員数  680万人  登録ブランド数  40,000ブランド  クチコミ数  1,690万件  登録商品数  36万点  アイスタイル調べ　2021年6月現在  

© istyle Inc. No.5 取り扱いアイテム数約 46,000 アイテム  取り扱いブランド数約
2,400 ブランド  （2021年1月時点）   国内No.1の化粧品取扱数を誇る化粧品ECサイト

© istyle Inc. No.6 国内No.1の化粧品専門店日本の化粧品専門店で売上規模No.1店舗を要する月間来店客数   112 万人 
全国  22 店舗  (2022年現在)  (2019年10月時点)

© istyle Inc. No.7 • 国内No.1の正規取扱ブランド数 • 国内No.1の敷地面積を誇るフラッグシップ店舗 • DXチャレンジング店舗
日本最大級ラグジュアリーからプチプラまで 600 ブランド以上  10~30代が60％を占める F1/F2層リーチ（エリア特性から得られるリーチ）

•SQL Serverの便利機能で生まれる技術課題  •SQL Serverの移行先にTiDBを選んだ理由  •TiDB移行PoCの結果  本日お伝えしたいこと  © istyle Inc. No.8

•Microsoft SQL Server  •オンプレで運用中  •1999年12月のサイト開設時に採用  •現在まで22年7ヶ月運用している  •当時のMySQLはver3、PostgreSQLはver6.5の時代  •当時の主流はOracleとSQL Serverだった  @cosmeのデータベース 
© istyle Inc. No.9

昨年からアイスタイルはRebornプロジェクトに取り組んでいる    Rebornプロジェクトの目的  •今のシステム（開発基盤）をリボーン（再生）する  •新規改修や新規サービスの導入をスピーディーに行えるようにする  •運用や保守の負荷を下げる  Rebornプロジェクト  © istyle Inc.
No.10

•AWSへのオールクラウド化  •新アーキテクチャの採用  •適切な技術選定    RDBMSのクラウド化と適切な技術選定に取り組むことになった  Reborn 3つのアプローチ  © istyle Inc.
No.11

SQL Serverの便利機能で生まれる技術課題  © istyle Inc. No.12

•アプリケーションがお互いのデータベースを共有し、強い依存関係をもっている  •SQL Serverの便利機能がデータベース共有を助長している  •このため、SQL Server同等の便利機能を持たないRDBMSへの移行が難しくなってしまっている  @cosmeのデータベースが抱える課題  © istyle
Inc. No.13

1. レプリケーション  2. 異なるDB間のテーブルJOIN  3. 異なるサーバー間のテーブルJOIN  データベース共有を助長するSQL Serverの便利機能  © istyle
Inc. No.14

•SQL ServerのレプリケーションはPub/Subモデル  •テーブルを複数のDBに配布できる    この機能を利用して実現していること  1. DB間でテーブルを同期し、アプリケーションは接続先DBを切り替えることなく、複数DBのテーブルを利用する  2. 複数のDBのテーブルを一箇所に集めた参照専用DBを作成し、アプリ
ケーションは接続先DBを切り替えることなく、複数DBのテーブルを利用する  レプリケーション  © istyle Inc. No.15

SERVER2 SERVER1 DB3 DB間テーブル単位レプリケーション  © istyle Inc. No.16 DB1 DB2
DB3の Application DB1の Application DB2の Application

SERVER2 SERVER1 DB3 DB間テーブル単位レプリケーション  © istyle Inc. No.17 DB1 DB2
DB3の Application DB1の Application DB2の Application

SERVER2 SERVER1 DB3 参照専用DBの作成  © istyle Inc. No.18 DB1 DB2
DB3の Application

SERVER2 SERVER1 DB3 参照専用DBの作成  © istyle Inc. No.19 DB1 DB2
DB3の Application

おそらく、アプリケーションの改修工数を減らしたかったのでは？    •最初はひとつのDBから始まった  •アプリケーションが増える時にDBの分割を行った  •どちらのアプリケーションでも使用したいテーブルはどうしようか？  •API経由にするとかなり修正が必要だ  •一時的にRDBMSの便利機能でカバーしよう...  こうなった経緯  © istyle
Inc. No.20

SERVER2 SERVER1 DB3 こうなった経緯  © istyle Inc. No.21 DB1 DB2
DB3の Application DB2の Application

DB3の Application DB2の Application APIコール

DB3の Application DB2の Application APIコール API向け改修 API新規作成

DB3の Application DB2の Application APIコール API向け改修 API新規作成この改修工数をなんとかできないか

DB3の Application DB2の Application 簡単にできる、このやり方を選んでしまった

•SQL Serverでは異なるDB間のテーブルをJOINする事ができる    この機能を利用して実現していること  1. 同一サーバーで異なるアプリケーションが管理しているテーブルをJOIN する  異なるDB間のテーブルJOIN  © istyle
Inc. No.26

SERVER1 異なるDB間のテーブルをJOIN  © istyle Inc. No.27 SELECT * FROM DB1.t1
INNER JOIN DB2.t2 DB1 DB2

•SQL Serverでは異なるサーバー間のテーブルをJOINできる  •ODBCドライバーがあれば、どんなDBMSでも接続できる    この機能を利用して、実現していること  1. 異なるサーバーで、異なるアプリケーションが管理しているテーブルを JOINする  2. SQL
Serverのテーブルと、MySQL、PostgreSQL、BigQueryのテーブルを JOINする  異なるサーバー間のテーブルJOIN  © istyle Inc. No.28

SERVER2 SERVER1 異なるサーバー間のテーブルをJOIN  © istyle Inc. No.29 SELECT * FROM
SERVER1.DB1.t1 INNER JOIN SERVER2.DB2.t2 DB1 DB2

•そんな便利機能を駆使して、アプリケーションがお互いのデータベースを共有し、強い依存関係をもった状態ができあがってしまった  •DBは分かれてはいるが、複雑に依存し合って、数十台のDBサーバー群は全体で一つの巨大なDBになってしまっている  •このため、SQL Server同等の便利機能を持たないRDBMSへの移行が難しくなってしまっている  •そんなSQL Serverをクラウド移行しなくてはならない   
@cosmeのデータベースが抱える課題（ふたたび）  © istyle Inc. No.30

SQL Serverの移行先にTiDBを選んだ理由  © istyle Inc. No.31

•いずれはDB間の依存関係は分離したい  •しかしクラウド移行と同時に実施するのは難しい  •今移行するには次の機能が備わっているRDBMSが必要と考えられた    1. SQL Server同等のレプリケーション  2. 異なるDB間のテーブルJOIN  3.
異なるサーバー間のテーブルJOIN  SQL Serverのクラウド移行先候補  © istyle Inc. No.32

•次の3つを移行先候補に挙げた  移行先候補  © istyle Inc. No.33 RDBMS レプリケーション異なるDB間のテーブルJOIN
異なるサーバー間のテーブルJOIN SQL Server（EC2） RDS for SQL Server RDS Aurora MySQL

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

•DBサーバーを物理的に分離して負荷分散すると、SQL Server的なレプリケーションが必要になってしまう  •全DBを一つのサーバーに集約すればレプリケーションは不要では無いか  少し発想を転換してみた  © istyle Inc. No.35

SERVER2 SERVER1 DB3 物理的に分割されているからレプリケーションが必要  © istyle Inc. No.36 DB1 DB2

全部入りSERVER DB3 こうすればレプリケーションは不要  © istyle Inc. No.37 DB1 DB2

•全DBを1サーバーに集約すると、全ての候補が選択肢になった  •インスタンスクラスを試算すると12xlargeで収まりそうだった  移行先候補の機能比較2  © istyle Inc. No.38 RDBMS レプリケーション異なるDB間の
テーブルJOIN 異なるサーバー間のテーブルJOIN SQL Server（EC2） ✅ ✅ ✅ RDS for SQL Server ✅ ✅ ✅ RDS Aurora MySQL ✅ ✅ ✅

全DB1サーバー集約案の問題点  © istyle Inc. No.39 •12xlargeではスケールアップの上限が見えてきている  •DBは30以上あるのでI/Oの競合が不安  上限イマココ

•全DBを一つのサーバーに集約しても、内部がシャーディングされていれば負荷が分散されるのではないか  •シャーディングを自前で管理するのではなく、その機能が備わったRDBMS を採用してはどうか  •分散RDBMSを検討してみることにした    もう少し発想を転換してみた  © istyle Inc.
No.40

Server2 Server1 従来のRDBMSではライターノードのスケールアウトは難しい  © istyle Inc. No.41 DB1 Server DB2
DB3 Server

分散RDBMS～物理構成分散RDBMS～論理構成分散RDBMSであれば内部的にスケールアウトができる  © istyle Inc. No.42 DB1 Server Server
Server Server Server Server Server Server DB2 DB3

移行先候補の機能比較3  © istyle Inc. No.43 RDBMS レプリケーション異なるDB間のテーブルJOIN 異なるサーバー間のテー
ブルJOIN SQL Server（EC2） ✅ ✅ ✅ RDS for SQL Server ❌ ✅ ✅ RDS Aurora MySQL ❌ ✅ ❌ TiDB ✅ ※全DBが同一クラスタ内のためレプリケーション不要 ✅ ✅ ※全DBが同一クラスタ内のためJOIN可能 •移行先候補に分散RDBMSのTiDBを追加した  •SQL Server以外の選択肢が出てきた  追加

•SQL Server方言のクエリの移行工数は？  •ストアドプロシージャ、トリガーの移行工数は？  •移行できないものはあるのか？  •SQL Serverと同等なクエリ速度がでるのか？  •データ移行はどうやるのか？  •問題が出たときのサポート体制はどうなっているのか？    PoCを実施して確認することにした 
TiDB移行での疑問点  © istyle Inc. No.44

TiDB移行PoCの結果  © istyle Inc. No.45

•次の条件で検証対象を選択した  •クチコミ関連のシステムが対象に選ばれた  TiDB移行PoCの内容・対象  © istyle Inc. No.46 No 選定条件 1
2つ以上のDBを使用し、DB間でJOINしたクエリを実行している 2 ストアドプロシージャを使用している（カーソル使用と未使用） 3 トリガーを使っている 4 自動採番の主キーをソート順に使用している 5 SQLが直書きされている 6 CTEが使用されている 7 Window関数が使われている 8 文字列を読み書きしている 9 古い実行環境を使用している

次のツールを使ってスキーマ移行を行い、使い勝手を検証した  •Ispirer MnMTK  •SQLines SQL Converter  スキーマ変換ツールの評価  © istyle Inc.
No.47

スキーマ変換ツールの評価結果  © istyle Inc. No.48 項目 MNMTK SQLines テーブル SQL
Server固有のオプションがそのまま残った SQL Server固有のオプションがそのまま残ったビュー、トリガー、ストアドプロシージャ関数の引数変換とスキーマ名の除去はできなかった関数の引数変換と、スキーマ名の除去はできなかった変換方法 DBへ接続して変換ファイルをコマンドラインでバッチ変換 •どちらも完璧な変換はできなかった  •SQLinesの方が、コマンドラインが使えて使い勝手が良かった 

•移行時に対応が必要な非互換性があった  •実際に移行を実施する時は、アプリケーション仕様変更が必要になる  スキーマ変換では課題が残った  © istyle Inc. No.49 課題対応案計算列がない
1. 更新処理を修正し、計算結果を格納 2. 参照処理を修正し、クエリ内に実装 3. ビューを作成するフィルター選択されたインデックスがないこの機能と論理削除を併用して未削除行のみユニーク制約を設定しているが、これが実現できない。 1. ユニーク制約を廃止する 2. 削除フラグを0（未削除）、NULL（削除）に変更する 3. 論理削除を物理削除に変更し、削除された行を別のテーブルに移動させる

•AWS Data Migration Serviceでデータ移行を行った  •フルロード時にSQL Serverの再初期化のようなスキーマロックが発生せず、移行元の負荷が低く、本番稼働中に安心して実行できた  データ移行ツールの評価  © istyle
Inc. No.50 検証項目結果データ移行速度実測値平均 60万行/分データ移行元サーバーのCPU負荷 10％未満の増加が見られたが問題なしデータ移行結果 DMSの検証機能で移行エラーなし日本語の文字化け SJISからUTF-8への変換だったが、文字化けは発生しなかった

•TiDBはMySQL5.7プロトコル互換  •JavaとPHPを検証したが、PHPはバージョンアップが必要だった  DBアクセスライブラリーの変更  © istyle Inc. No.51 言語接続可能バージョン 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

•アプリケーション内のクエリ修正を検証した  •Window関数もCTEも使えるため、クエリの構造自体を大きく変更する必要は無かった  •関数の変換や、スキーマ名（dbo）の除去など軽微な修正で済んだ    SQLの修正  © istyle Inc. No.52
オブジェクト階層 SQL Server TiDB（MySQL）データベース ✅ ✅ スキーマ ✅ ❌ テーブル ✅ ✅ 例 db1.dbo.table1 db1.table1 SQL ServerとTiDB（MySQL）のオブジェクトの階層の違い

• スキーマ名「dbo」の除去 • TOPをLIMITに変更 • UPDATEのFROMを書き換え  • WITH(NOLOCK)を除去  • IDENTITY_INSERTの除去 
• DATEADDをTIMESTAMPADDに変更  SQLの修正工数  © istyle Inc. No.53 処理概要修正工数複数DBのJOIN、CTE、Window関数あり 0.5人日/クエリー単一DBのSELECT、 INSERT、UPDATE 0.1人日/クエリー • 予約語と重複した名称を変更 • MERGEを複数クエリに分割 • CTEを使ってINSERTする場合の記述順序を変更 • DB名の追記 • 文字列結合の+をCONCAT関数に変更具体的な修正内容

•TiDBはストアドプロシージャ未実装  •アプリケーション側で処理を実装した  ストアドプロシージャの修正  © istyle Inc. No.54 処理概要修正工数カーソル操作なし（内部に1クエリー）
0.5人日カーソル操作あり（内部に6クエリー） 1.8人日

•自動採番列（identity列）をソート順に利用している所があった  •TiDBでは各ノードが採番するため連番にならずソートには使えない  •対応案  • アプリケーション側で採番を実装する  • シーケンスを利用する  •検証では定義するだけで連番を採番できるシーケンスを採用した    自動採番列のソート 
© istyle Inc. No.56 処理概要修正工数テーブル定義にシーケンスを追加し、ソート順を変更 0.25人日/クエリー

参照クエリがエラーで終了する  •TiDBにはクエリーのメモリーサイズ制限がある  •閾値を超えたクエリーはエラー終了する  •PoCではクエリーを修正せず、エラーを抑止して対応した  •この制限自体は良いことだと考える  • 取得件数を気にしないクエリが原因でレスポンスが悪化することが多い  • この制限があればアプリケーション実装時にエンジニアに気をつけてもらえる   
    PoC開始直後に発生して一瞬不安になったエラー  © istyle Inc. No.57

更新クエリがエラーで終了する  •TiDBにはトランザクションサイズ制限がある  •閾値を超えたクエリーはエラー終了する  •PoCではクエリーを修正せず、エラーを抑止して対応した  •この制限自体は良いことだと考える  • 一括更新、一括削除が原因でサーバーの負荷が上がってトラブルが起きることがある  • この制限があればアプリケーション実装時にエンジニアに気をつけてもらえる 
    PoC開始直後に発生して一瞬不安になったエラー  © istyle Inc. No.58

•DBMS単体の速度検証をするため、SQLでの負荷テストを実施した  パフォーマンス検証  © istyle Inc. No.59 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 ブランド毎のクチコミ件数

•テスト環境  パフォーマンス検証  © istyle Inc. No.60 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 ブランド毎のクチコミ件数

シナリオ 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.61 •ランダムIDのSELECT •99パーセンタイルで100ms以下でレスポンスを返せた  •16秒越えの外れ値があるが、サーバースペックの問題であった 

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.62 •対象ID固定でSELECT •全般的にランダムIDでのSELECTより遅くなっている  •処理が分散されないためと考えられる 

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.63 •INSERT •接続数の増加によるレスポンス低下は見られない  •主キーで分散を行うため 

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.64 •ランダムなIDでSELECT、INSERT、UPDATEを繰り返すシナリオ •レスポンスは100ms以内  •極端にレスポンスが遅い外れ値が録された  •TiDBノードのサーバースペックが低すぎた 

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.65 •ランダムなIDでSELECT、INSERT、UPDATEを繰り返すシナリオ  •TiDBノードのスペックを変更して再テスト実施  •クライアント数を5倍にしても、最大値が秒以下になった 

•集計クエリの実行はTiDBの方が速かった  • TiDBはカラムナストレージのTiFlashを利用  • SQL Serverは通常のインデックスで列ストアインデックスではない  • フェアな比較ではないので参考程度である  テストケース6，7  ©
istyle Inc. No.66 シナリオ SQL Server TiDB 月別クチコミ件数 12.959s 7.83s ブランド毎のクチコミ件数 31.149s 0.908s

•運用面の機能確認を実施  • バックアップ、リストア  • スケールアウト、スケールイン  • スロークエリ  • 監査ログ  •
PiTR  • オブジェクトセキュリティ  •一部未実装のものがあったが、実装予定があることを確認した    PoCで他にやった事  © istyle Inc. No.67

非常に手厚いサポートをしてもらえた    •やり取りはチャット（Slack）なので質問しやすかった  •ちょっとした質問には直ぐに回答が来た  •不具合が出たときは再現テストを行ってくれた  •TiDBの修正が必要な場合は、数日内に修正パッチを適用してくれた  PingCAP社のサポート  © istyle Inc.
No.68

•SQL Serverからの移行は可能だと確信が持てた  •TiDBであれば、密結合なシステムを密結合なまま移行できそう  •クエリをSQL ServerからMySQL互換に変更する事でDBMSの選択肢が広がる  PoCを終えて  © istyle Inc.
No.70

•負荷予想が難しいシステム  • 小規模から超大規模まで、ほぼ無限にスケールアウトができる  •大規模システム  •リアルタイム集計が必要なシステム  •DBが密結合すぎて分離が難しくなっているシステム  TiDBが向いているシステム  © istyle Inc.
No.71

@cosmeがレガシーシステムをクラウドネイティブDBに載せ替える理由 - TiDB User...

@cosmeがレガシーシステムをクラウドネイティブDBに載せ替える理由 - TiDB User Day 2022 /istyle session

More Decks by PingCAP-Japan

Other Decks in Technology

Featured

Transcript