👋

MySQL8.0でのテスト実行速度を改善する

ichikawa

2024/04/08に公開

概要

MySQL5.7を使用しているプロダクトをMySQL8.0にアップグレードする計画が持ち上がり、まずはGithub Actions（以下GHA）で行っているテスト環境をアップグレードすることになりました。
MySQL8.0ではいくつかのデフォルト設定が変更されており、そのまま使用するとMySQL5.7よりも遅くなりました。そのため、設定を調整して現行と遜色ない程度に速度低下を抑えられないか調査、実践してみました。

結果

パターン	全体 /分	テスト実行 /分
5.7 変更前	33	29
5.7 調整後	37	21
8.0 調整後	43	34

結果としてはバイナリログとredoログ、truncateを多用していることが速度低下の原因でした。
バイナリログとredoログの無効化、truncate をdeleteへ置き換えることでMySQL5.7では約30%の高速化、MySQL8.0では約10%増加に抑えることができました。

検証作業

MySQL5.7, 8.0両方でテストできるようにする

GHAではマトリックスを使用することで複数パターンのジョブを簡単に実行することができます。

ワークフロー抜粋

  test:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        mysql_version: ['5.7', '8.0']
    services:
      mysql:
        image: mysql:${{ matrix.mysql_version }}

これで完了と思ったらMySQL8.0では速度が大幅に低下する結果になりました。

MySQL	ジョブ実行時間 /分	うちテスト実行 /分
5.7	33	29
8.0	52	48

類似ケースを調べたところ、MySQL8.0ではバイナリログ、redoログがデフォルトで有効のため速度低下が起きるようでした。

速度低下の原因を調べる

類似ケース同様の問題が起きているのか調査します。
redoログ無効化はMySQL起動後に ALTER INSTANCE DISABLE INNODB REDO_LOG を実行することで適用できます。
バイナリログ無効化は起動時パラメーター --disable-log-bin またはmy.cnfに disable-log-bin を記載することで適用できます。

GHAのservicesで指定できるMySQLは起動時パラメーターを渡せないため、my.cnfを変更可能な別のアクション（以下ユーザーアクション）を使用することにしました。

稼働プロダクトのテストケースは件数が多く検証に向かないため、別途ワークフローを作り類似ケースの計測同様のSQLを実行して所要時間を測定を行いました。

比較観点はMySQL5.7, 8.0での差異、現行通りのサービスコンテナとユーザーアクションでMySQLを立ち上げた場合の差異、実際に設定変更した場合の5パターンを調査しました。

ワークフロー抜粋

  run_on_mysql_service:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        mysql_version: ['5.7', '8.0']
    services:
      mysql:
        image: mysql:${{ matrix.mysql_version }}
        env:
          MYSQL_ROOT_PASSWORD: root
          MYSQL_DATABASE: test
        ports:
          - 3306:3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=10

    steps:
      - uses: actions/checkout@v4
      - run: mysql -u root -h 127.0.0.1 -proot test < create_db.sql
      - run: chmod 755 ./run.sh
      - run: time ./run.sh

  run_on_mysql_action:
    runs-on: ubuntu-latest
    timeout-minutes: 120
    strategy:
      matrix:
        mysql_version: ['5.7', '8.0']

    steps:
      - uses: actions/checkout@v4
      - uses: shogo82148/actions-setup-mysql@v1
        with:
          mysql-version: ${{ matrix.mysql_version }}
          root-password: root
      - run: mysql -u root -h 127.0.0.1 -proot < create_db.sql
      - run: chmod 755 ./run.sh
      - run: time ./run.sh

  run_on_mysql_action_custom:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: shogo82148/actions-setup-mysql@v1
        with:
          mysql-version: 8.0
          root-password: root
          my-cnf: |
            disable-log-bin
      - run: mysql -u root -h 127.0.0.1 -proot -e "ALTER INSTANCE DISABLE INNODB REDO_LOG;"
      - run: mysql -u root -h 127.0.0.1 -proot < create_db.sql
      - run: chmod 755 ./run.sh
      - run: time ./run.sh

結果

パターン	全体 /秒	MySQLセットアップ /秒	SQL実行時間 /秒	バイナリログ	redoログ
MySQL5.7 service	45	26	6.418	OFF	null
MySQL8.0 service	45	29	6.941	ON	ON
MySQL5.7 action	59	26	3.094	OFF	null
MySQL8.0 action	31	11	6.932	ON	ON
MySQL8.0 action 設定変更	31	14	4.234	OFF	OFF

サービスコンテナを使用する場合、MySQLのバージョンによる速度差はほとんどない
MySQL5.7でユーザーアクションを使用するとサービスコンテナと比べて約2倍速い
MySQL8.0でユーザーアクションを使用するとサービスコンテナと速度差はほとんどない
MySQL8.0でバイナリログ、redoログを無効にすると有効の場合に比べて約1.5倍速い

このとから設定変更が有効と言えそうです。

バイナリログ、redoログを無効にしてみる

実際に適用してみると速度改善するどころかさらに遅くなりました。
1時間を超えたので中断しています。進捗からして2時間以上かかりそうでした。

MySQLバージョン	ジョブ実行時間 /分	うちテスト実行 /分
5.7	33	29
8.0	>60	-

truncateが原因？

MySQL8ではtruncateが drop table;create table と等価に変更されたようです。

対象のプロダクトではDBデータに依存するテストが大量にあり、テストケース毎に200以上あるテーブルに対しtruncateを実行しています。

テーブル初期化メソッド

    private function truncateAllTables(): void
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        foreach ($this->getAllTables() as $table) {
            DB::statement('TRUNCATE TABLE '.$table);
        }
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }

※ Illuminate\Foundation\Testing\RefreshDatabaseトレイトを使っていないのは一部マスタテーブルを保持したいため

テストケース毎にいくつかのテーブルに作成される数レコードのデータを消すためにわざわざテーブル作り直していたらそれは重いのでは...と。

truncate vs delete

速度低下検証で使ったコードのtruncateをdeleteで置き換えたパターンも加えて再検証しました。

結果

パターン	全体 /秒	SQL実行時間 /秒
8.0 設定変更 truncate	27	3.739
8.0 設定変更 delete	24	2.257
8.0 service truncate	43	7.763
8.0 service delete	42	3.804
5.7 service truncate	41	6.047
5.7 service delete	42	5.300

全てのケースで速度向上しました。

deleteへ置き換え

実際のテストケースをdeleteに置き換えて検証します。

テーブル初期化メソッド（改）

    private function truncateAllTables(): void
    {
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');
        foreach ($this->getAllTables() as $table) {
            DB::statement('DELETE FROM '.$table);
            DB::statement('ALTER TABLE '.$table.' auto_increment = 1');
        }
        DB::statement('SET FOREIGN_KEY_CHECKS=1;');
    }

一部のテストで自動採番の値をハードコードされているためtruncate相当となるようauto_incrementをリセットしています。

結果（再掲）

パターン	全体 /分	テスト実行 /分
5.7 変更前	33	29
5.7 調整後	37	21
8.0 調整後	43	34

5.7では速度向上、8.0では5分増加にとどめることができました。
自動採番のリセットを省ければさらに速度向上する可能性もありそうです。

Micoworks株式会社Publication

LINEを活用した、コミュニケーションプラットフォーム「MicoCloud（ミコクラウド）」を開発、提供しています。

概要

結果