EmbulkでPostgreSQLをMySQLに移行した話

こんにちは。マッハバイトを運営するアルバイト事業部エンジニアの mnmandahalf です。

先日、マッハバイトの販売管理システムで使っているデータベースをオンプレPostgreSQLからAmazon Aurora MySQLに移行しました。本記事では移行に至った背景、吸収する必要があった差分や苦労した点についてお話しします。

環境

移行前のバージョン: PostgreSQL 9.4 ※ドキュメントはバージョン14のものを添付しています
移行後のバージョン: Aurora MySQL 3.02.0 (compatible with MySQL 8.0.23)

環境
MySQL移行の背景
データ移行方法の検討
Embulkの実行で考慮したポイント
PostgreSQLとMySQLの差分
大変だったこと
- PostgreSQLの性能に助けられていたクエリの修正
移行作業の思い出
最後に

MySQL移行の背景

ひとことで言うと「特異な運用になっていた」かつ「クラウド化でタイミングがよかったから」です。

マッハバイト、ひいてはリブセンスのほとんどのWebサービスがMySQLを利用しているのに対し、販売管理DBのみPostgreSQLになっているため独自の運用をしたりキャッチアップをするコストがかかっていました。

特に監査ログ周りの設定が複雑になっており、クエリのログをFluentdでパースして保存するといった運用を行なっていました。

またローカルでMySQLを立てて販売管理APIを動かした結果、PostgreSQL特有の機能はほとんど利用していなかったことがわかりました。そこで、できる限りキャッチアップコストや運用の負荷を下げ、データ設計やアプリケーションの開発に注力できるように、今回はMySQLに寄せるという選択を取ることにしました。

そして販売管理APIのオンプレからECSへの移行の一環でこちらのDB移行は行われました。

データ移行方法の検討

pg_dumpによるダンプファイルのリストア、pg2mysql 、Embulk、AWS DMS 等を検討した結果、Embulkを採用しました。

ダンプファイルやpg2mysqlを用いたリストアは1行につき1 INSERTが発生するため移行に膨大な時間がかかってしまいます。検証でt系のRDSインスタンスを使っていたため正確ではないですが、実行してから3時間ほど経過した時点で半分もデータが移行できていなかったため採用を見送りました。

販売管理DBはデータが644MB、インデックスが436MBなのでさほどデータ量が多いわけではないのですが、一部のAPIが顧客向けに社外からも参照されているサービスではあるのでサービス停止時間が長くなってしまうことは回避したかったです。

Embulkは並列でデータを移行してくれることで20分程度で作業が完了したため、こちらを利用することにしました。

DMSについては、今回はCDCレプリケーション（継続的に変更をキャプチャしてレプリケーションする機能）が不要だったことからEmbulkで事足りると考え見送りました。

Embulkの実行で考慮したポイント

Embulkの設定

各種ソフトウェアの利用バージョンは以下の通りです。

Ubuntu 20.4
OpenJDK 8
Embulk 0.9.23
embulk-input-postgresql 0.13.0
embulk-output-mysql 0.10.2
Go 1.17

実行環境はUbuntuのDocker imageを利用してコンテナを立て（詳しくは踏み台の管理コストを削減！ECS ExecとTerraformでつくる本番オペレーション環境 - LIVESENSE ENGINEER BLOG にてご紹介しています）、GoでEmbulkを外部コマンドとして実行する簡単なスクリプトを書き、複数のテーブルに流す操作を行いました。

必要なライブラリはDockerfileにてインストールしています。（以下は抜粋）

RUN set -ex \
    && apt-get -y -qq update \
    && apt-get -y -qq install curl openjdk-8-jdk

RUN set -ex \
    && curl --create-dirs -o /root/.embulk/bin/embulk -L "https://dl.embulk.org/embulk-latest.jar" \
    && chmod +x /root/.embulk/bin/embulk

RUN set -ex \
    && /root/.embulk/bin/embulk gem install embulk-input-postgresql \
    && /root/.embulk/bin/embulk gem install embulk-output-mysql \
    && curl -o /tmp/postgresql-42.4.2.jar "https://jdbc.postgresql.org/download/postgresql-42.4.2.jar"

Embulkの設定liquidファイル

in:
  type: postgresql
  host: {{ env.SALES_PG_HOST }} 
  user: sales_service 
  password: {{ env.SALES_PG_PASSWORD }} 
  database: {{ env.SALES_PG_DATABASE }}
  table: {{ env.SALES_TABLE }} 
  driver_path: /tmp/postgresql-42.4.2.jar
out:
  type: mysql
  host: {{ env.SALES_MYSQL_HOST }}
  user: sales_service
  password: {{ env.SALES_MYSQL_PASSWORD }} 
  database: {{ env.SALES_MYSQL_DATABASE }} 
  options: {useLegacyDatetimeCode: false, serverTimezone: Asia/Tokyo, tinyInt1isBit: false}
  table: {{ env.SALES_TABLE }}  
  mode: merge_direct

デフォルトでは新しいテーブルを作ってswapする方式になっていますが、merge_direct（既存のテーブルに対して実行、キーが重複の場合はUPDATE）モードを利用することで途中で何らかの理由で処理がエラーになっても上書きによるやり直しができます。

実行するGoのメソッド

func run() {
    for _, table := range tables {
        if os.Getenv("ENV") == "staging" && table == "exclude_table_name" {
            // マスク対象テーブルは中身が0件でembulkが例外を吐くためデータ移行の対象に含めない
            continue
        }

        os.Setenv("SALES_TABLE", table)
        cmd := exec.Command("java", "-jar", "/root/.embulk/bin/embulk", "run", "/app/src/embulk/sales.yml.liquid")
        fmt.Println(cmd, table)
        out, err := cmd.CombinedOutput()
        fmt.Println(string(out))
        if err != nil {
            // エラーがあれば異常終了させる
            log.Fatal(err)
        }
    }
}

GoのコードはCIでバイナリを作ってimageに渡しています。

先にテーブル定義のマイグレーションを済ませた上で上記のコードを実行することで移行を実現しました。

scram-sha-256認証への対応

諸事情によりステージングのPosgreSQLのバージョンのみ14.1になっており、scram-sha-256認証が導入されていたため対応する必要がありました。

embulk-input-postgresqlプラグインに同包されているPostgeSQL jdbcドライバのバージョン（9.4-1205-jdbc41）がscram-sha-256認証に未対応のものだっため、新しいバージョンをダウンロードしてそちらをdriver_pathオプションに指定して利用するようにしました。

scram-sha-256認証はバージョン42.2.0からサポートしているようです。

PostgreSQL: JDBC 42.2.0 Released

driver_pathオプションについてご教示くださった@dmikurubeさん、ありがとうございました！

タイムスタンプが9時間巻き戻る

DB側のTZがUTCになっているかつ、Embulkの実行環境のTZがAsia/Tokyoになっている、という特殊なケースで発生するようでした。

こちらの件は詳細な検証内容を個人ブログに記載しています。

mnmandahalf.hatenablog.com

結果的に実行時にTZ=UTCを付与することで解決しました。

$ TZ=UTC embulk preview sales.yml

# あるいは
$ TZ=UTC ./Goの実行ファイル

FK制約を無効化できない

移行対象のテーブル群はSHOW TABLES;で動的に取得することも考えましたが、結果的に事前に一覧をピックアップしてファイルに記載することにしました。

データ移行の際にFK制約でエラーになることをテーブルの移行順を決めることで回避したかったからです。

MySQLの foreign_key_checks システム変数を変更すれば一時的にFK制約のチェックを無効化できますが、RDSはグローバル変数の変更ができません。セッション変数を変更するにしてもEmbulkの実行では複数のセッションを使うため、あらかじめFKエラーにならない順番を考慮しておく必要がありました。

PostgreSQLとMySQLの差分

日付のフォーマット関数等の細かい違いもありましたが、特に今回の移行で印象に残ったものをご紹介します。

MySQLはRENAME DATABASEができない

MySQLではデータベースのRENAMEができず、一つ一つのテーブルについてRENAME TABLE old_database.table_name TO new_database.table_name のようにRENAMEを行う必要がありました。

本番環境では sales ステージング環境では sales_staging というデータベース名を用いているのですが、本番→ステージングへの日次のデータ同期の際にデータベース名を変える必要があったため、ステージングの方のデータベース名を本番環境と合わせることにしました。

TRUNCATEの違い

PostgreSQLでは以下のようなTRUNCATE文を発行していました。

TRUNCATE TABLE parent, child1, child2 RESTART IDENTITY;

PostgreSQLでは一度のTRUNCATEステートメントで複数テーブルを指定できますがMySQLではできません。

PostgreSQL: Documentation: 15: TRUNCATE

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.37 TRUNCATE TABLE ステートメント

またInnoDBをストレージエンジンに使用していたため、外部キー参照されているテーブルのTRUNCATEはたとえ子テーブルが空であってもできないため、一度セッション変数で制約チェックをオフにしてから一件ずつ行うよう処理を変更しました。

InnoDB テーブルまたは NDB テーブルを参照する他のテーブルからの FOREIGN KEY 制約がある場合、そのテーブルに対する TRUNCATE TABLE は失敗します。同じテーブルのカラム間の外部キー制約が許可されます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.37 TRUNCATE TABLE ステートメント

一方で RESTART IDENTITY によるシーケンス（AUTO INCREMENT な id カラムで使用）のリセットは指定しなくても行なってくれました。

SET foreign_key_checks = 0;
-- foreign_key_checks = 0にしているのでparentから始めても削除はされる
TRUNCATE TABLE child1;
TRUNCATE TABLE child2;
TRUNCATE TABLE parent;
SET foreign_key_checks = 1;

PK, FK周りの制約の違い

PostgreSQLではINTのカラムに対してBINGINTでFKを作成することができますが、MySQLでは作成できません。

今回移行したDBのマイグレーションはActiveRecordで管理されていました。

ActiveRecord 5.1以前に生成したPK（id）はINTになりますが、5.1からはBIGINTになるため、「5.1以前のマイグレーションで生成したテーブルを5.1以降のマイグレーションで生成したテーブルから参照」を行なっていたケースで型の不整合が発生しました。

移行のタイミングでは全てのテーブルを5.1の仕様でマイグレーションする（idをBIGINTに変更する）よう修正しました。

他にもMySQLで外部キーを利用するには下記のような制約があります。

親テーブルと子テーブルは同じストレージエンジンを使用する必要があり、一時テーブルとして定義することはできません。

外部キー制約を作成するには、親テーブルに対する REFERENCES 権限が必要です。

外部キー内の対応するカラムと、参照されるキーは同様のデータ型を持っている必要があります。「INTEGER や DECIMAL などの固定精度タイプのサイズと符号は同じである必要があります」。文字列型の長さが同じである必要はありません。バイナリ以外の (文字の) 文字列カラムの場合、文字セットと照合順序が同じである必要があります。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20.5 FOREIGN KEY の制約

Bulk Insertの違い

Bulk InsertをRailsで実行するライブラリとして、activerecord-import を使っています。元のロジックで戻り値のPK一覧.idsを利用していたのですが、MySQLのBULK INSERTは戻り値にPKが返りません。

※ https://github.com/zdennis/activerecord-import#return-info より抜粋

articles = [
  Article.new(author_id: 1, title: 'First Article', content: 'This is the first article'),
  Article.new(author_id: 2, title: 'Second Article', content: ''),
  Article.new(author_id: 3, content: '')
]

demo = Article.import(articles, returning: :title) # => #<struct ActiveRecord::Import::Result

demo.failed_instances
=> [#<Article id: 3, author_id: 3, title: nil, content: "", created_at: nil, updated_at: nil>]

demo.num_inserts
=> 1,

demo.ids
=> ["1", "2"] # for Postgres
=> [] # for other DBs

こちらは値自体に意味があったのではなく意図した件数がインポートされているかの確認に利用されていたため、.failed_instances の有無のチェックやall_or_noneオプションの利用で代替可能でした。

大変だったこと

PostgreSQLの性能に助けられていたクエリの修正

今回DBサーバは2CPU メモリ8GBの現行DBに対して2vCPU メモリ16GB（db.r6g.large）のAuroraインスタンスを用意したのですが、パフォーマンス面においてPostgreSQLに分がありました。特に致命的だったのがCOUNTクエリの遅さです。

販売管理APIは主に社内管理画面で使用されているのですが、とある画面のページングにKaminari Gemを使用しており、総件数の表示や次のページがあるか判定するために内部でCOUNT クエリを発行していました。

当該ページではリソースに対して別テーブルを結合してさまざまな条件を用いての絞り込みを行なっているため、アプリ内部のクエリでLEFT OUTER JOINを多用しており、その結果に対してCOUNTクエリを実行すると40秒近くかかるという事態が発生しました。DBMSの違いもありつつ、当該クエリはPostgreSQLでも実行に10秒近くかかっていたので、クエリ自体の問題も大きかったです。

事前の確認が甘く、上記の要因により1回移行の切り戻しを行うことになってしまいました。

対処法としては、不要なタイミングでのLEFT OUTER JOIN を減らすためにActiveRecordの.eager_loadを.includesに書き換え、絞り込みがない場合は結合が発生しないようにしたり、逆に絞り込み条件のtraitに.eager_loadを足すことで結合順を変更してクエリを変え、本番DBで流すなどのテストを行いました。

結果的に2秒程度にできたので、未だ遅いものの移行前より改善することができました。

パラメータチューニングの可能性も検討しましたが、バッファプールはデータサイズとインデックスサイズの合計の10倍以上あるため、テーブル設計やクエリの問題が大きいと感じています。

まだクエリチューニングで改善できる部分が大きそうなので、今後もMySQLのスロークエリログやperformance_schemaを活用して改善を行っていきたいです。