Engineers in CARTA vol.2 #MySQL https://cartaholdings.connpass.com/event/231708/
記事 https://codezine.jp/article/detail/14062
株式会社 CARTA HOLDINGS 浪川 大輔 脱Oracle、MySQL一本化への道
View Slide
アジェンダ 2● 自己紹介 ● ECナビとは ● ECナビのDBの歴史 ● 脱Oracleの背景 ● 脱Oracle、MySQL一本化 （a.k.a引っ越し）の紹介
自己紹介 3株式会社CARTA HOLDINGS/ 株式会社VOYAGE MARKETING （改メ 株式会社 DIGITALIO 2022年1月より） メディア事業本部 ポイントメディア事業部 浪川 大輔 ● 入社13年、ECナビに関わって約10年 ● ここ数年はインフラを中心に改善を行うチームに所属 ● 「浪川大輔」の ”声じゃない” 方担当
ECナビとは？ 「ECナビ」は、約760万人の会員に利用されている日本最大級のポイントサイトです。ショッピングや会員登録、アンケートなど、毎日参加することのできるポイントコンテンツを多数取り揃え、会員が毎日訪れ、サイト上で積極的に行動することを支援しています。会員にとって、満足度の高いサイト作りを目指しています。 ECナビキャラクター ナビック 4毎日たまるポイントサイト https://ecnavi.jp
まずは歴史を少し振り返ります ECナビ PCバナー広告 5
2016年ごろのDB事情 6● オンプレ時代 ● Oracle ○ ODA X4-2 ってアプライアンス製品 ● MySQL ○ フロント向け、バックオフィス向けの大きく2つ ○ それぞれmaster1台, slave2台の構成 構成
2016年ごろのDB事情 7テーブル数 インスタンス データベース数 テーブル数 Oracle 1 650 MySQL (フロント向け) 5 797 MySQL (バックオフィス向け) 3 235
現在のDB事情 8● AWS移転後 ● Oracle ○ RDS Oracle Standard Edition Two(se2) ○ インスタンスタイプは r5.2xlarge ● MySQL ○ フロント向け、バックオフィス向けの大きく2つ ○ インスタンスタイプは ■ フロント向け r5.2xlarge ■ バックオフィス向け r5.large ○ それぞれmaster1台 構成
現在のDB事情 9テーブル数 インスタンス データベース数 テーブル数 Oracle 1 650 -> 180 MySQL (フロント向け) 5 -> 3 797 -> 194 MySQL (バックオフィス向け) 3 -> 2 235 -> 51 MySQLはこの後、別の統廃合プロジェクトによって、フロントに１データベース、181テーブル、バックオフィスは廃止予定
現在のDB事情 10 ここまでの奮闘の歴史は Engineers in VOYAGE ~事業をエンジニアリングする技術者たち~ 第3章 VOYAGE MARKETING 20年級大規模レガシーシステムとの戦い をお読みいただけると幸いです。 まとめ
脱Oracleの背景 ~ ECナビ Oracleやめるってよ ~ ECナビ PCバナー広告 11
なんで？ 12● 専用のミドルウェアやライブラリを別途導入する必要がある。それがyumのリポジトリにないので別途導入する仕組みを用意する必要がある ● ネット上での事例などが少なく、スキルを持ったエンジニアが少ないので障害時の対応の時間がかかる ● Oracleでないとできないような機能を利用するシーンがない 技術面
なんで？ 13● OracleとMySQLを併用しているので、それぞれ概念や使い方、クライアントツールに差異があり開発時にどちらを使ってるか意識しないといけない。 ● クライアントツールは使い勝手が悪いため開発効率が悪い。 ● ローカル開発環境構築、外部CIサービス利用のハードルになっている。 ● OracleとMySQLを併用しているが、明確な使い分けもない。 開発面
なんで？ 14● 導入当時はOSSのDBの信頼性は低く、ビジネス用途の場合はOracleが選択されることが多かった。現在、OSSもOracleと比較して遜色ない機能を提供できるようになってきている。 ● 不要機能削除や統廃合によってOracle、MySQL共に200テーブル弱になり現実的に移行可能な規模になった。 環境面
なんで？ 15Oracleシンドイっす。。。 まとめ
前置きが長くなりましたが ここからが本題 ECナビ PCバナー広告 16
脱Oracle、MySQL一本化 (a.k.a 引っ越し)の紹介 ECナビ PCバナー広告 17
引っ越し先は？ 18● 既存 or 新DB ● v5.7 or v8.0 ● MySQL or Aurora
引っ越し先は？ 19● Oracle -> MySQLへの単純移行なら思い切って最新技術にチャレンジしてた ● でも、既存との兼ね合いはある程度考慮しないといけない ● 規模や使い方的にも1データベースにまとめてしまってその後の方がチャレンジはしやすい
引っ越し先は？ 20● 既存 or 新DB ● v5.7 or v8.0 ● MySQL or Aurora
引っ越し方法は？ 21サービスメンテナンスで一括移行 or 部分メンテナンスで分割移行
引っ越し方法は？ 22● 既存の開発や別に大規模な統廃合プロジェクトが並行で動いてる ● そもそも一括でやるにはデカすぎる ● 複数DBで運用してきたためトランザクション制御などアプリの実装もそれなりに実績がある ● ユーザーには不便をかけるものの数時間のメンテによる事業インパクトは小さい
引っ越し方法は？ 23サービスメンテナンスで一括移行 or 部分メンテナンスで分割移行
まずは調査 24● Oracle独自のものがないか？ ○ トリガー ○ プロシージャ ○ シーケンス（特殊ケース） ● データの規模 ● データ型
まずは調査 25● トリガー -> なし ● プロシージャ -> なし ● シーケンス（特殊ケース）-> あり ○ シーケンス:1-テーブル:n で使ってるところが1箇所 Oracle独自のもがないか？
まずは調査 26データの規模
まずは調査 27データの規模 ● 10万レコード以下が半分くらい ○ 短時間でのテーブル移行ができそう ● 最大レコード数は2億4700万が10テーブル ○ ポイントのログを10テーブルにシャーディング
まずは調査 28● VARCHAR2, CHAR, NUMBER, DATE ○ ベーシックな型で置き換えができるものばかり ● BLOB ○ 一箇所 データ型
引っ越し 29● どこから手を付ける？ ● どう進める？
引っ越し 30● 機能毎にテーブルを仕分け ● テーブル数、ユーザーの利用頻度、メンテナンスの入れやすさを軸に難易度を「低・中・高・鬼」に設定 ● 難易度の低い方から手を付ける どこから手を付ける？
引っ越し 31● 低 ○ マスターデータ系 ○ まとメール ● 中 ○ パスワードリマインダー ○ アドレス変更 ○ CSRF対策 ○ ポイント失効 ○ ナビック新着 ○ カードプラス ○ 友達紹介 ○ 会員退会 ○ 会員停止 ○ キャンペーン 難易度 ● 高 ○ まいにち動画 ○ ガラポン ○ 誕生日 ○ 募金 ○ ハッピーアンケート ○ 珍コレ ○ 宝くじ ○ 連携用ユーザID ○ 会員登録 ○ ポイント付与API ○ ポイント付与申請 ○ タップインセ ○ キャンペーンシステム ○ BID関連 ○ MFA系 ○ PeX交換 ○ アプリ ● 鬼 ○ ポイント ○ 成果案件 + 成果通知 ○ ユーザー
引っ越し 32● 以下を1セットとして ○ アプリの調査、データ移行、メンテナンスの入れ方決定 ○ DDLの用意 ○ アプリ改修 ○ データ移行準備 ○ 動作、パフォーマンス検証 ○ メンテナンス日調整 ○ 切り替え作業 どう進める
引っ越し開始！ ECナビ PCバナー広告 33
データ型どうする？ 34● VARCHAR2, CHAR, NUMBER, DATE ○ 既存のMySQLにあるテーブルの型と乖離しないように変換ルールを策定 ● BLOB ○ 中身はJSONなのでMySQLではTEXTにした 変換ルール
データ型どうする？ 35変換ルール Oracle MySQLDATE DATETIMENUMBER(n, 0): n < 3 TINYINT(n)NUMBER(n, 0): 3 <= n < 10 INT(n)NUMBER(n, 0): 10 <= n < 19 BIGINT(n)NUMBER(n, 0): 20 <= n DECIMAL(n, 0)NUMBER(n, s) DECIMAL(n,s)VARCHAR2(n) VARCHAR(n)CHAR(n) VARCHAR(n)
DDLは？ 36● データ型は原則変換ルールに則る ● シーケンスを利用している列はauto_incrementにする ● インデックスは基本そのまま ○ explainして想定したインデックスが効いていなければ調整 方針
DDLは？ 37● AWS Schema Conversion Tool は使わなかった ○ databaseに直接繋ぐ必要がある ○ NUMBERが軒並みDECIMALになったり、細かくマッピングの設定をしないといけなかった ○ DDLの管理はflywayで管理してるのでリリースの兼ね合い 変換ツール
DDLは？ 38● どうしたか？ ○ 結局調査段階で、各テーブルのカラム、データ型、インデックスなど調査していたので、それらをインプットにDDL出力するスクリプトを書いた 変換ツール
DDLの罠 39● DECIMAL型はauto_incrementが付けれない ○ NUMBER(n, 0): 20 <= n に該当した数ケース ○ 自動変換でNUMBER -> DECIMALにしていたら軒並みハマってた ○ 実データみると、データの増加量みても20桁以上行きそうにないものばかり ○ 実データと増加量から適切な桁を調べてINT,BIGINTにして対応
DDLの罠 40 ● 予約語 ○ カラム名にOracleで使えてMySQLでは予約語で使えないものがある ○ うちでは “CONDITION” で引っかかりました
アプリ改修 41SQLの関数周り Oracle MySQLsysdate now()rownum limit, offsetto_date （date型との比較） なしto_char （日付の変換で利用） なしnextval last_insert_id()
アプリ改修 42rownum ● 管理画面のページャーで主に利用 ● rownumは、開始行数と終了行数を指定 ● limit, offsetは件数と開始位置を指定
アプリ改修 43rownum 例: 15〜20行目を取る場合（rownum版） SELECT ID, NAME, ROWNUM FROM USERS WHERE ROWNUM BETWEEN 15 AND 20
アプリ改修 44rownum 例: 15〜20行目を取る場合（limit, offset版） SELECT ID, NAME FROM USERS LIMIT 5 OFFSET 14
アプリ改修 45rownum こんな実装があると地味にダメージありますw function find($start, $end, $con) // <- 開始行、終了行が引数になってるやつ { $sql = "SELECT ID, NAME, ROWNUM FROM USERS WHERE ROWNUM BETWEEN :start AND :end"; return $con->execute($sql, ['start' => $start, 'end' => $end]); }
アプリ改修 46トランザクション ● OracleとMySQLの両テーブルを更新する場合 ● 例えば: クイズに答えてポイントを取得 ○ Oracleのポイントログに追加（ポイントを付与） ○ MySQLのクイズの回答のログに追加 ● Oracleにポイント情報、MySQLにコンテンツ系の情報を持ってるパターンが結構ある
アプリ改修 47トランザクション try { $oracleCon = Database::getConnection('ORACLE_ECNAVI); $mysqlCon = Database::getConnection('MYSQL_ECNAVI'); $pointLogDao = Dao::get('POINT_LOG'); $quizAnswerDao = Dao::get('QUIZ_ANSWER'); // トランザクション開始 $oracleCon->begin(); $mysqlCon->begin(); // それぞれのテーブルへ追加 $pointLogDao->insert($userId, $pointValue, $oracleCon); // Oracleのテーブルへの追加 $quizAnswerDao->insert($userId, $answer, $mysqlCon); // MySQLのテーブルへの追加 // コミット $oracleCon->commit(); $mysqlCon->commit(); } catch (Exception $e) { if (isset($oracleCon) && $oracleCon->isTransaction()) { $oracleCon->rollback(); } if (isset($mysqlCon) && $mysqlCon->isTransaction()) { $mysqlCon->rollback(); } }
アプリ改修 48トランザクション ● こんなコードが結構ある ● 歴史のある大規模アプリなので、コードの作りにグラデーションがある ○ こんなコードがControllerにあったり、ビジネスロジッククラスにあったりいろいろパターンがあって苦労する ● 引っ越しの過渡期にはOracleにあったコンテンツをMySQLに移すので、こんなコードを追加する必要もある
データ移行 49 ● データ移行は以下ツールを利用 ○ Embulk ■ 現在Oracle、MySQLからRedshiftへ日次データ移行に利用 ○ AWS Database Migration Service（DMS） ■ 複数テーブルの移行 ■ CDC（差分更新）
IOPS 50● データ移行に際して事前にIOPSの確認は重要 ● データ移行先（今回だとMySQL）の書き込みのIOPSがかなり上がる ● RDSのストレージタイプ（とストレージサイズ）によって上限がありそれを超えてI/Oクレジットバランス使い切ると極端に性能悪化 ● 移行先はサービスですでに利用しているためデータ移行によってI/Oクレジットバランス使い切るとサービスにも影響が出る ○ この辺は本番と同様のデータの検証環境を使って事前に確認をとりつつ進めました
IOPS 51● プロビジョンドIOPS SSDストレージを利用していてIOPSは2000で設定していた ● 5400万件のデータを移行するタイミングでIOPSが7000強まで上がり、今は1万までIOPSを上げてます MySQLのIOPS
文字化け問題 52● Oracle、MySQL共に文字コードはEUC-JPを利用してDMSを使ってデータ移行をした際に以下が文字化け ○ ローマ数字、略式文字など ○ 旧漢字（はしご高 など） ○ 全角チルダ ○ 全角バックスラッシュ
文字化け問題 53● 全角バックスラッシュはあまり聞いたことがなかったので？？となった ● それも結構な件数できて、利用用途も？？？
文字化け問題 54答え: ＼＼回答者限定！！／／ のXXXXXに関するアンケート こういうのだった。。。。
文字化け問題 55● 今回のケースではDMSのCDCを使いたかったので、DMS上で変換をかけたり、元データを代替え文字に変換して回避した ● ユーザーデータは特に旧漢字があるケースも多いと思うのでその辺は手動でEUC-JPとしてファイル出力して移す必要がありそう
いろいろありながら ECナビ PCバナー広告 56
現在の状況 57● 引っ越し過程のMySQLのメトリクス ● 億単位のテーブルも移行してたりするが比較的大きな変化もなく動いている ● コネクションが伸びてるのは別のトラブルによるもの ● ディスクは途中で増やしてるのでグラフは大きく変化しています
現在の状況 58● IOPSもじわじわ上がってきてはいるが平時は上限に迫るような大きな変化はない
現在の状況 59● 引っ越しは道半ば ● 180テーブル中 110テーブルほど引っ越しが完了 ● 難易度を鬼に設定したサービスのコアとなるユーザー系や2億4700万レコード x 10テーブルを擁するポイント系はこれから
この先順調に進むのか？ 本当に引っ越ししきれるのか？ ECナビ PCバナー広告 60
この続きは またいずれ何かの機会に ECナビ PCバナー広告 61
ご清聴ありがとうございました ECナビ PCバナー広告 62
carta_engineering
chumajin
bigwheel
bun913
onlinesalon
knsg16
you
tyrmanuz
coconala_engineer
tomoaki25
takesection
wakamatsu_takumu
peisuke
62gerente
afnizarnur
aleyda
jakevdp
chriscoyier
cromwellryan
notwaldorf
nonsquared
dougneiner
smashingmag
kneath
shpigford