【試行錯誤編①】億り人をおくりびとした話

Java

Spring-boot

Spring Batch

MyBatis

まずは分割移行をしようじゃないか

数億件規模のレコードを全件SELECTするのはあまりに現実的ではないので、まずは移行対象をある程度の範囲で分割して移行する方針にしました。

移行元のテーブルたちには日付系のカラムがあり、かつインデックスもはられていたりしたので、日付のfrom / toをバッチパラメータとして外から指定するようにすることで、移行バッチのプログラム本体は変えずに分割移行するようにしました。

1日単位で見れば多いものでも200万〜300万件になることが分かったので、これなら勝ち目があるなと。

移行元の現行システムはSQLServerであるため、データの一括エクスポートやインポートとしてbcpユーティリティというものがあるのは知っていました。それにローカル環境で動作確認をするためにSQLServer側のデータが欲しいなーなんて時に手元でbcpユーティリティを使ってデータをエクスポートし、ローカル環境のSQLServerのDockerコンテナにインポートするみたいなのは割と日常的にやっていたので、これならどうかと。

とりあえずサクッとbashスクリプトを書いて、

bcp-SourceTable.sh

#!/bin/bash

set -eo pipefail

OUTPUT_FILE=$1
TARGET_DATE=$2

QUERY="
SELECT *
FROM SourceDB.dbo.SourceTable
WHERE TargetDate = '${TARGET_DATE}'
"

bcp "${QUERY}" queryout ${OUTPUT_FILE} -S "${DB_HOST},${DB_PORT};Encrypt=No" -U ${DB_USER} -P ${DB_PASSWORD} -c -t ','

このスクリプトをJavaのプロセスから実行するようにして、

ExportSourceTable.java

@Component
@RequiredArgsConstructor
public class ExportSourceTable {

  @Value("classpath:/bcp-SourceTable.sh")
  private Resource script;
  
  public Path export(LocalDate targetDate) throws Exception {
    // tmpディレクトリに適当に出力ファイルを作成
    var outputFile = Paths.get(System.getProperty("java.io.tmpdir"))
        .resolve("%s.csv".formatted(UUID.randomUUID().toString()));
    Files.createFile(outputFile);

    // bashスクリプトの実行
    var process = new ProcessBuilder()
        .command(
      script.getFile().getAbsolutePath(),
      outputFile.toString(),
      targetDate.format(DateTimeFormatter.ofPattern("uuuu-MM-dd"))
    )
    .inheritIO()
    .redirectErrorStream(true)
    .start();
    process.waitFor();

    if (process.exitValue() != 0) {
      throw new RuntimeException("export failed. SouceTable.TargetDate=%s".formatted(targetDate));
    }
    
    return outputFile;
  }
}

実行してみるとビンゴ！結構なスピードでデータを抽出できて、MyBatisでSQLを打ってクエリタイムアウトの設定時間を気にしないといけないことからも解放されたのでこれはいけるなと。

ここでうっすらと気づくわけです。 あ、ファイルI/Oベースにした方が速いなと。

そうは問屋が卸さない

ですが喜んだのも束の間、すぐにハマるポイントがやってきます。

これECSのコンテナ上でどうやって動かしたらええねん。

というのもベースのフレームワークとしてSpring-bootを採用していて、ECSで動かすためのDockerイメージの生成をSpring-bootのGradleプラグイン任せにしていたため、Dockerfileをそもそも書いていませんでした。（Spring-bootのGradleプラグインにある bootBuildImage タスクを使っていた）

この bootBuildImage タスクというのは、内部的にはPaketo Buildpacksというのが使われていて、ソースコードからいい感じにDockerイメージを生成してくれるものです。 https://paketo.io/

確かSpring-bootのv2.6系あたりで実装されたGradleタスクなのですが、Dockerfileの実装をしなくてよくなるので以前から使っていました。 Dockerfileって地味にメンテナンスが面倒なので。

そう、勘の良い読者ならお気づきでしょうが、この Dockerfileが存在しない というのが大きなポイントになります。つまりPaketo Buildpacksによって生成されるDockerイメージはあくまでJavaのコンテナになるので、SQLServer固有のbcpユーティリティなんてものは入っていません。（さすがにbashスクリプトまで見てインストールしてくれるなんてことはしてくれません）

ローカル環境では開発のしやすさのために、シンプルにSpring-bootの bootRun タスクを実行すればいいようにしてあったので、bcpユーティリティは別途インストールさえしておけばよかったのですが、ECSの実行環境ではDockerイメージにパッケージングされたものが動くため、今のままではせっかく実装したbashスクリプトが動かせない状態でした。

Paketo BuildpacksのRunnerイメージをカスタマイズする

前項で「Dockerfileをそもそも書いていませんでした」と言ったのですが、実は半分本当で半分嘘です。

このバッチとは別にREST APIのプロセスを同じくSpring-bootで実装、GradleプラグインでDockerイメージにパッケージング、ECSで稼働させるというのを既にやっていました。 Paketo Buildpacksが生成する素のDockerイメージだとcurlコマンドがインストールされていなくて、ECSのコンテナヘルスチェックが出来ないという問題があって、この時にPaketo BuildpacksのRunnerイメージをカスタマイズすることで問題を回避していました。

Paketo BuildpacksにはBuilderイメージとRunnerイメージというものがあって（Dockerfileのマルチステージビルドみたいなもの）、最終成果物としてECSで稼働させるためのDockerイメージはRunnerイメージの方が適用されます。今回はこのRunnerイメージの方をカスタマイズ、つまりRunnerイメージにbcpユーティリティをインストールさせることでECSで稼働させた時に前項で実装したbashスクリプトが動かせる状態にします。（RunnerイメージだけでなくBuilderイメージもカスタマイズすることも出来たりしますが、ここでは必要ないので割愛します。詳しくは公式まで。）

RunnerイメージをカスタマイズするDockerfileを書いて、

run.Dockerfile

FROM paketobuildpacks/run:base-cnb

USER root

RUN apt-get -y --no-install-recommends update && \
    apt-get -y --no-install-recommends install curl locales sudo && \
    echo "ja_JP.UTF-8 UTF-8" > /etc/locale.gen && \
    locale-gen ja_JP.UTF-8

RUN curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc && \
    curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list && \
    sudo apt-get -y update && \
    sudo ACCEPT_EULA=Y apt-get -y install msodbcsql18 mssql-tools18 unixodbc-dev
ENV PATH $PATH:/opt/mssql-tools18/bin

USER cnb

Runnerイメージ自体をDockerビルドしておきます。

$ docker build -t i-zacky/cnb-runner:v1 -f run.Dockerfile .

Spring-bootのGradleプラグインの bootBuildImage タスクにビルドしたRunnerイメージを選択させます。

build.gradle

plugins {
  id('java')
  id('org.springframework.boot').version('3.1.1')
}

bootBuildImage {
  runImage = 'i-zacky/cnb-runner:v1'
  environment = [
    'BP_JVM_VERSION': '17'
  ]
  pullPolicy = 'IF_NOT_PRESENT'
}

これによって bootBuildImage タスクを実行して生成されるDockerイメージでbcpユーティリティを使うことが可能になりました。

馬鹿と鋏は使いよう

QueryTimeoutもOutOfMemoryErrorの問題もある程度解消出来た、これで移行バッチが動くようになったということで、ウキウキで他のバッチにも同様の対応を横展開させていきました。すると今度は別の問題が発生します。

パフォーマンスが出ないバッチがあると。

一難去ってまた一難とはまさにこのこと。仕方ないのでまたログを漁りながら原因を突き止めます。

結果として分かったのは 複雑なクエリの場合はスループットが極端に下がる ということでした。

この時、データ移行として単一テーブルの単純SELECTだけではいかないケースがあるのは知っていたのですが、思うようなパフォーマンスが出ていなかったのはまさにそういうケースでした。

単一テーブルの単純SELECTのようなケースだと、bcpエクスポートのスループットが秒間数千件出ていたのですが、複雑なクエリのケースだと秒間数百件程度しか出ていないことが分かったのです。（ひどいものだと秒間100件程度しかエクスポートできていなかった）

これは後々調べて分かったことなのですが、bcpユーティリティというのは単純SELECTの結果をエクスポートするのにはパフォーマンスが出るのですが、そうでない場合は出ないとのこと。 https://dba.stackexchange.com/questions/318928/why-is-bcp-out-much-faster-than-select-even-when-bcp-uses-select-internally

こうなってしまってはもうどうしようもありません。 bcpユーティリティを使う方式を捨てることにしました。

【試行錯誤編①】億り人をおくりびとした話

まずは分割移行をしようじゃないか

bcpユーティリティの威力

そうは問屋が卸さない

Paketo BuildpacksのRunnerイメージをカスタマイズする

馬鹿と鋏は使いよう