はじめに こんにちは、タイミーでエンジニアをしている徳富( @yannKazu1 )です。 タイミーではメインサービスのバックエンドを Rails で開発しています（Go を採用しているプロダクトもありますが、本記事では Rails を前提とします）。 突然ですが、皆さんのチームでは CI の待ち時間、気になっていませんか？ 「Push した、コーヒー淹れた、戻ってきた、まだ回ってる……」みたいな経験は、開発者なら一度はあるのではないでしょうか。 本記事では、そんな状況を改善するために GitHub Actions 上のテスト実行パイプラインで取り組んだ 3 つの高速化テク を紹介します。どれも「知っていれば明日から試せる」くらいの温度感なので、気軽に読んでいただければと思います。 1. キャッシュの保存先を GitHub Cache から S3 に移行 課題: actions/cache が安定して速くない 最初にぶつかった壁が actions/cache の速度でした。 vendor/bundle （数百 MB〜1 GB 超）の save/restore でやたら時間がかかることがあり、リストアだけで数分待たされる場面がちょくちょくありました。これはセルフホストランナーに限った話ではなく、GitHub ホステッドランナーでも起きます。 実際、公式リポジトリにも Extremely slow cache on self-hosted from time to time という Issue が立っていて、セルフホスト・GitHub ホステッド問わず同様の報告が寄せられています。 さらに私たちの場合、 AWS 上のセルフホストランナー を使っているのでなおさらです。 actions/cache のバックエンドは Azure Blob Storage のため、セルフホストランナーからだとインターネット経由のアクセスになり、スループットが 約 20 MB/s まで落ちる ケースも報告されています（ Actuated Blog ）。突発的に遅いうえに経路も遠い——これでは安定した速度は望めません。 容量面でも、リポジトリあたり 10GB の制限があります。また、7 日間アクセスのないキャッシュは自動削除されます。その結果、ブランチが増えるとすぐに上限に達し、必要なキャッシュが消えてしまうのも地味にストレスでした。 解決策: runs-on/cache で S3 をバックエンドに そこで [runs-on/cache](https://github.com/runs-on/cache) を導入し、キャッシュの保存先を 同一リージョン（東京）の S3 バケット に切り替えました。 前述のとおり、セルフホストランナーで  actions/cache  を使うとスループットが ~20 MB/s まで落ちるケースがあります。一方  runs-on/cache  は同一リージョンの S3 を使えるため、200 MiB/s 以上 のスループットが出ます（ 公式ドキュメント ）。単純計算で 10 倍近い改善 です。 actions/cache とインターフェースがそのまま同じなので、 uses: を差し替えて環境変数を 1 つ足すだけで移行できました。 # .github/actions/setup-ruby-with-s3-cache/action.yml - name : Restore cache uses : runs-on/cache@v4.2.3-r2 env : RUNS_ON_S3_BUCKET_CACHE : your-gha-cache-bucket with : path : "**/vendor/bundle" key : bundle-v1-${{ runner.os }}-${{ inputs.ruby_version }}-${{ hashFiles('Gemfile.lock') }} restore-keys : | bundle-v1-${{ runner.os }}-${{ inputs.ruby_version }}- bundle-v1-${{ runner.os }}- なぜ runs-on/cache を選んだか S3 をキャッシュバックエンドにする方法は他にもあります（ tespkg/actions-cache 、 whywaita/actions-cache-s3 、自前の aws s3 cp スクリプトなど）。その中で runs-on/cache にした決め手はこのあたりです。 環境変数 1 つで切り替え : RUNS_ON_S3_BUCKET_CACHE を設定するだけで S3 バックエンドに切り替わる 自前実装が不要 : 圧縮・展開・キャッシュキーのマッチング・フォールバックなど、地味にめんどくさい部分を全部やってくれる 容量無制限 : S3 なので 10GB の制限もキャッシュの自動削除もなし キャッシュキーの設計 キャッシュキーは 3 段階のフォールバック構造にしています。 bundle-v1-Linux-3.3.6-<Gemfile.lock のハッシュ> ← 完全一致（最速） bundle-v1-Linux-3.3.6- ← Ruby バージョン一致 bundle-v1-Linux- ← OS のみ一致 完全一致しなくても、部分一致したキャッシュをリストアして bundle install すれば差分の gem だけで済みます。ゼロからインストールするより圧倒的に速いので、新しいブランチでもほぼキャッシュが効く状態を維持できます。 OIDC 認証で安全に S3 にアクセス AWS へのアクセスには OIDC 認証 を使っています。長期的なアクセスキーをシークレットに保存しなくて済むので、セキュリティ面でも安心です。 - name : Configure AWS credentials uses : aws-actions/configure-aws-credentials@v6 with : role-to-assume : arn:aws:iam::123456789012:role/your-gha-role aws-region : ap-northeast-1 2. マイグレーション結果をまるごとキャッシュ 課題: 毎回のマイグレーションが地味に重い テストジョブは毎回データベースをセットアップします。ここで問題になったのが、マイグレーション数が数百を超えてくると rails db:create db:schema:load だけで 数分かかる ということ。 「schema:load だからすぐ終わるでしょ？」と思いきや、テーブル数が多いとそうでもないんですよね。 解決策: MySQL のデータディレクトリごと S3 にキャッシュ 発想を変えて、 マイグレーション済みの MySQL データディレクトリ ( /var/lib/mysql ) をまるごと S3 にキャッシュ することにしました。要は「マイグレーション済みの DB をそのまま持ってくれば、マイグレーション自体を省略できるよね」という作戦です。 仕組みの全体像 【キャッシュの生成】 【キャッシュの利用】 master ブランチ feature ブランチ db/migrate/** 変更 テストジョブ起動 or 毎日定時 │ │ ▼ ▼ S3 からキャッシュをリストア MySQL 起動 → ./tmp/mysql_data に展開 │ │ ▼ ▼ rails db:create db:migrate MySQL 起動（データマウント済み） │ │ ▼ ▼ ./tmp/mysql_data を S3 に保存 rails db:migrate（差分のみ） │ ▼ テスト実行 キャッシュの生成: master で定期的に焼き直す master ブランチでマイグレーションファイルが変更されたとき、または毎日定時に、専用のワークフローがキャッシュを更新します。 # .github/workflows/update-migration-cache.yml on : push : branches : [ master ] paths : - 'db/migrate/**' - 'db/schema.rb' - '.github/workflows/update-migration-cache.yml' - '.github/actions/migration-hash/**' schedule : - cron : '0 2 * * *' # 毎日 UTC 2:00（JST 11:00）に実行 workflow_dispatch : # 手動実行も可能 やっていることはシンプルです。 MySQL コンテナを起動（データディレクトリを ./tmp/mysql_data にマウント） rails db:create db:migrate でフルマイグレーション実行 ./tmp/mysql_data をまるごと S3 にアップロード - name : Run database migration run : bundle exec rails db:create db:migrate - name : Save migration cache uses : runs-on/cache/save@v4.2.3-r2 env : RUNS_ON_S3_BUCKET_CACHE : your-gha-cache-bucket with : path : ./tmp/mysql_data key : test-${{ runner.os }}-${{ runner.arch }}-mysql${{ steps.migration-hash.outputs.mysql_version }}-${{ runner.environment }}-db-migration-${{ steps.migration-hash.outputs.hash }} キャッシュキーの設計: 何をキーに含めるかが大事 キャッシュキーには地味に気を使っています。 test-Linux-X64-mysql8.0.28-self-hosted-db-migration-<db/schema.rb のハッシュ> │ │ │ │ │ OS ARCH MySQL Ver ランナー環境 スキーマハッシュ ポイントは db/schema.rb のハッシュを含めていること。 マイグレーションの内容が変われば schema.rb も変わる ので、自動的に新しいキャッシュが生成されます。MySQL バージョンやアーキテクチャもキーに入れているのは、バイナリ非互換でハマらないための保険です（一度やらかしました……）。 キャッシュの利用: Composite Action で再利用しやすく キャッシュの利用ロジックは Composite Action に切り出して、RSpec だけでなく Steep（型チェック）など他のワークフローからも使い回しています。 # .github/actions/setup-mysql/action.yml - name : Create MySQL data directory run : mkdir -p ./tmp/mysql_data - name : Restore migration cache id : cache-hit-check uses : runs-on/cache/restore@v4.2.3-r2 env : RUNS_ON_S3_BUCKET_CACHE : your-gha-cache-bucket with : path : ./tmp/mysql_data key : test-${{ runner.os }}-${{ runner.arch }}-mysql${{ mysql_version }}-${{ runner.environment }}-db-migration-${{ hash }} - name : Start MySQL service with docker compose run : docker compose -f compose.ci.yml up -d mysql8 Docker Compose では、リストアしたデータディレクトリをそのままボリュームマウントします。 # compose.ci.yml services : mysql8 : volumes : - ./tmp/mysql_data:/var/lib/mysql MySQL が起動すると、キャッシュ内のデータファイルがそのまま認識されるので、 マイグレーション済みのデータベースが即座に使える 状態になります。 テストジョブでの分岐: キャッシュがあれば差分だけ 各テストジョブでは、キャッシュがヒットしたかどうかで処理を分岐しています。 - name : RSpec run : | if [ "${{ steps.setup-mysql.outputs.cache_hit }}" == "true" ] ; then echo "Using cached migration data, running incremental migration" bundle exec rails db:migrate # ← ブランチ固有の差分だけ else echo "No cache found, running schema load" bundle exec rails db:create db:schema:load fi キャッシュヒット時 : master のマイグレーション済みデータが復元されているので、 db:migrate で差分だけ適用。たいていは数秒で終わります キャッシュミス時 : MySQL バージョンアップ直後などキャッシュがない場合は db:schema:load にフォールバック この仕組みのおかげで、並列のテストジョブそれぞれで数分かかっていた DB セットアップが数秒になりました。体感で一番効果が大きかった施策かもしれません。 3. CI 用 MySQL のパフォーマンスチューニング 課題: デフォルト設定の MySQL が意外とボトルネック テスト環境の MySQL をデフォルト設定のまま使っていたのですが、ある日ふと気づきました。テストでは各テストケースごとに BEGIN / ROLLBACK やテーブルのクリーンアップが走るので、 書き込みが尋常じゃない量になっている んですよね。 デフォルト設定だと、コミットのたびにディスクへの fsync が走ります。本番では安全のために必要ですが、テスト環境では……正直、オーバースペックです。 解決策: テスト環境に限定して、耐久性よりパフォーマンスを優先する CI 専用の compose.ci.yml で、 データ耐久性を思い切って犠牲にして、書き込みパフォーマンスを最大化 しました。 # compose.ci.yml services : mysql8 : image : ${MYSQL_IMAGE} command : > mysqld --innodb-flush-log-at-trx-commit=0 --sync-binlog=0 --skip-innodb-doublewrite environment : MYSQL_ALLOW_EMPTY_PASSWORD : "yes" ports : - "3306:3306" volumes : - ./tmp/mysql_data:/var/lib/mysql 各パラメータの解説 innodb-flush-log-at-trx-commit=0 InnoDB のログ書き込み動作を制御するパラメータです。 値 動作 用途 1（デフォルト） コミットのたびにログをディスクに fsync 本番環境（ACID 完全準拠） 2 コミットのたびに OS バッファに書き込み、 fsync は毎秒 レプリカなど 0 ログの書き込みも fsync も毎秒のバッチ処理 テスト環境 テストで 1 秒以内にクラッシュリカバリが必要な場面はないので、 0 にして コミットごとの fsync オーバーヘッドを完全に排除 しています。 sync-binlog=0 バイナリログ（レプリケーション用）の同期タイミングです。 値 動作 1（デフォルト） コミットごとにバイナリログを fsync 0 OS のファイルシステムキャッシュに任せる テスト環境ではレプリケーションを使わないので、バイナリログを sync_binlog=0 にし、同期を OS のキャッシュに任せることでコミットごとの fsync を省いています。 skip-innodb-doublewrite InnoDB の doublewrite バッファを無効化します。これは書き込み途中のクラッシュに備えて全ページを 2 回書く安全機構なのですが、テスト環境では不要です。無効化すれば 書き込み I/O が大幅に減ります 。 注意: 本番では絶対にやらないでください 念のため書いておきますが、上記の設定は データの耐久性・整合性を犠牲にしています 。 innodb-flush-log-at-trx-commit=0 : クラッシュで最大 1 秒分のトランザクションが消える sync-binlog=0 : クラッシュでバイナリログが不完全になる可能性 skip-innodb-doublewrite : 部分書き込みでデータ破損のリスク テスト環境は「テストが通ればデータは捨てる」使い捨ての世界なので、これらのリスクは許容しています。くれぐれも本番には適用しないように！ まとめ 施策 何をキャッシュ/最適化しているか 効果 S3 キャッシュ vendor/bundle （Gem パッケージ） ダウンロード高速化・容量制限の解消 マイグレーションキャッシュ マイグレーション済み MySQL データ DB セットアップ時間を数分→数秒に MySQL チューニング fsync・doublewrite の無効化 テスト中の書き込み I/O を削減 CI の高速化に近道はなくて、結局はボトルネックを一つずつ潰していくしかありません。 DB のデータ耐久性は不要なので無効化する。マイグレーションは毎回ゼロからやる必要がないのでキャッシュする。キャッシュの保存先は、ネットワーク的に近い場所に置く。こうした「当たり前だけど意外とやっていない」割り切りが、大きな高速化につながりました。 同じような課題を抱えるチームの参考になれば嬉しいです。

はじめに こんにちは、MA部SREブロックの片桐です。MA部ではメルマガやLINE、アプリプッシュ通知を配信するためのマーケティングオートメーションシステムを開発・運用しています。 MA部ではDBとして主にCloud SQL for MySQLを利用しており、調査や不具合対応のために開発メンバーがDBにログインして各種SQLを実行する場面があります。 このとき、共用の特権DBユーザーとパスワード認証を利用していました。しかし、この方式ではパスワード管理が必要になるほか、DB上のログイン主体も個人に紐づけにくい状態でした。 これらの課題を解決するために、人間によるDBへのログイン方式を、共用の特権DBユーザーとパスワード認証から個人のGoogle Cloudアカウントを使ったIAM認証へ移行しました。 あわせて、IAM認証でログインする各ユーザーには通常時は参照権限のみを付与し、書込系権限が必要な場合だけGitHub Actionsの承認付きワークフローから一時付与する運用にしました。 本記事では、共用DBユーザーによる運用から個人のIAM認証を使った運用へ移行した背景と、MySQLロールの一時付与を実現するための構成例を紹介します。 目次 はじめに 目次 従来の運用の課題 強い権限が常時使える 個人単位で追跡できない 目指した状態 全体構成 IAM認証で個人ログインにする IAM認証の有効化 IAMデータベースユーザーの作成 Cloud SQLへのログイン権限の付与 MySQLロールでDB内権限を分ける 通常時と一時付与用のロール ロールの作成 参照用ロールの付与 GitHub Actionsを承認ゲートにして書込系ロールを一時付与する GitHub Environmentで申請者以外の承認を必須にする 権限操作用サービスアカウントの作成 ワークフロー設定例 一時付与した書込系ロールを剥奪する 手動でロールを剥奪する 定期実行でロールを剥奪する 運用上の注意点と今後の改善 一時付与したロールを使うときの注意 ワークフロー入力値の検証 SQL本文のレビューと監査 一時付与した権限の失効タイミングの厳密化 MySQLロールの粒度 おわりに 従来の運用の課題 従来の構成ではデータベース操作用の共用特権DBユーザーを作成し、パスワード認証でCloud SQL for MySQLへログインしていました。 構成としては次のとおりです。 ここで利用している Cloud SQL Studio は、Google Cloudコンソール上からCloud SQLへ接続してSQLを実行できるWebベースの画面です。 この運用では、複数人が同じDBユーザーを使ってログインします。そのため、主に次のような課題がありました。 強い権限が常時使える 日常運用におけるデータ調査であれば、多くの場合は SELECT を実行できれば十分です。 しかし、共用の特権DBユーザーを使うと、参照だけで済む作業時でも特権によりデータ変更やDDL操作まで実行できてしまいます。 強い権限を持った状態でSQLを実行すると、誤操作時に本来不要だったデータ更新やスキーマ変更まで起きてしまう可能性があります。そのため、通常時は参照のみを許可し、必要なときだけ書込系権限を一時的に付与する運用にしたいと考えました。 個人単位で追跡できない 共用ユーザーでログインするため、データベースから見ると誰が操作しても同じユーザーに見えます。 そのため、DB上のログイン主体を開発メンバー個人のGoogle Cloudアカウントと結びつけにくい状態でした。 人間のDBログインを個人のIAM認証に寄せることで、少なくともDBへのログイン主体は個人単位で扱えるようになります。 目指した状態 共用特権DBユーザーの課題を踏まえ、今回の移行では次の状態を目指しました。 人間によるDBへのログインを、共用ユーザーではなく個人のGoogle Cloudアカウントに紐づける 通常時は参照権限のみを付与する 書込系権限は常時付与せず、必要なときだけ一時的に付与する 書込系権限の付与申請を簡単に行えるようにする 書込系権限の付与には、申請者以外の承認を必須にする 付与した書込系権限は、作業後または定期実行で剥奪する 今回の構成では、Cloud SQLへのログインにはIAM認証を利用します。一方で、ログイン後にどのSQLを実行できるかはMySQL側の権限で制御します。 さらに、書込系権限は常時付与せず、必要なときだけ承認付きで一時付与して、作業後または定期実行で権限を剥奪します。 そのため、今回の構成ではログイン可否、DB内権限、権限の一時付与、付与後の剥奪を次のように分けて考えました。 項目 役割 利用する仕組み 認証 誰がCloud SQLへログインできるかを制御する Cloud SQL IAM認証 認可 ログイン後に何を実行できるかを制御する MySQLロール 一時付与 必要時だけ書込系権限を付与する GitHub Actionsの承認付きワークフロー 剥奪 一時付与した権限を戻す 手動または定期実行のREVOKEワークフロー この構成により、通常時は参照権限のみを使い、書込系権限が必要な場合だけ承認付きで一時的に付与する運用にしました。 全体構成 今回構築した仕組みは、通常時のログイン経路、必要時における書込系権限の一時付与フロー、一時付与した権限の剥奪フローに分かれます。 通常時は、開発メンバーがCloud SQL Studioから自分のGoogle CloudアカウントでCloud SQL for MySQLへログインします。 本記事ではCloud SQL Studioから接続する例で説明しますが、接続元はこれに限りません。IAM認証に対応した接続方式であれば、同じ考え方を適用できます。 Cloud SQLへのログイン可否はIAMで制御し、ログイン後に実行できるSQLはMySQLロールで制御します。通常時は、開発メンバーに参照用のMySQLロールのみを付与します。 IAM認証へ移行した後の通常時の構成は次のとおりです。 この状態では、開発メンバーはCloud SQL Studioから SELECT を実行できます。一方で、書込系権限は通常時には付与しません。 データ修正などで書込系権限が必要な場合は、GitHub Actionsの手動ワークフローを実行します。ワークフローはGitHub Environmentの承認待ちになり、申請者以外のメンバーが承認すると、対象ユーザーに書込系のMySQLロールを一時的に付与します。 書込系権限を一時付与する流れは次のとおりです。 一時付与した書込系ロールは、作業後の手動実行または定期実行で剥奪します。剥奪の流れは次のとおりです。 剥奪は権限を戻す操作であるため、今回の例では付与時のような承認ゲートは設けていません。手動実行または定期実行でGitHub ActionsからSQL実行基盤を起動し、MySQLロールの REVOKE を実行します。 以降のコード例では、次のプレースホルダーを使います。 プレースホルダー 意味 YOUR_PROJECT_ID Google CloudプロジェクトID YOUR_PROJECT_NUMBER Google Cloudプロジェクト番号 YOUR_MEMBER_NAME 開発メンバーのメールアドレスの @ より前の部分 YOUR_MEMBER_DOMAIN 開発メンバーのメールアドレスのドメイン YOUR_SA_NAME 権限操作用サービスアカウント名 YOUR_DB_NAME 対象のデータベース名 YOUR_TABLE_NAME 対象のテーブル名 YOUR_COLUMN_NAME 対象のカラム名 YOUR_WIF_POOL Workload Identity Pool名 YOUR_WIF_PROVIDER Workload Identity Provider名 YOUR_GITHUB_ENVIRONMENT_NAME 承認ゲートとして利用するGitHubのEnvironment名 IAM認証で個人ログインにする まず、個人のGoogle CloudアカウントでCloud SQL for MySQLへログインできる状態を作ります。 Cloud SQL for MySQLでIAM認証を利用するため、主に次の項目を設定しました。 Cloud SQLインスタンスでIAM認証を有効化する 開発メンバーごとのIAMデータベースユーザーを作成する Cloud SQLへログインするためのIAMロールを付与する Cloud SQL Studioを利用するためのIAMロールを付与する これらの設定は、Google Cloudコンソール、gcloud CLI、Terraformなどで行えます。MA部ではインフラ設定をTerraformで管理しているため、以降ではTerraformでの設定例を示します。 IAM認証の有効化 Cloud SQL for MySQLで IAM認証 を有効化するには、インスタンスのデータベースフラグ cloudsql_iam_authentication を有効にします。 resource "google_sql_database_instance" "main" { # name, database_version, region などは省略しています settings { database_flags { name = "cloudsql_iam_authentication" value = "on" } } } IAMデータベースユーザーの作成 次に、開発メンバーをIAMデータベースユーザーとして作成します。 人間のGoogle CloudアカウントをIAMデータベースユーザーとして作成する場合は、 type に CLOUD_IAM_USER を指定します。 resource "google_sql_user" "member" { project = "YOUR_PROJECT_ID" name = "YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" instance = google_sql_database_instance.main.name type = "CLOUD_IAM_USER" } この例では、IAMデータベースユーザーを YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN として作成しています。 Cloud SQL for MySQLでは、IAMデータベースユーザーのメールアドレスの @ より前の部分をMySQL上のユーザー名として扱います。そのため、後続の GRANT では YOUR_MEMBER_NAME を指定します。 Cloud SQLへのログイン権限の付与 IAM認証でCloud SQLへログインするには、 cloudsql.instances.login 権限が必要です。この権限は、事前定義ロールの roles/cloudsql.instanceUser に含まれています。 resource "google_project_iam_member" "cloudsql_login" { project = "YOUR_PROJECT_ID" role = "roles/cloudsql.instanceUser" member = "user:YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" } また、本記事では開発メンバーがCloud SQL Studioから接続する前提のため、Cloud SQL Studioを利用するためのIAMロールも付与します。 resource "google_project_iam_member" "cloudsql_studio" { project = "YOUR_PROJECT_ID" role = "roles/cloudsql.studioUser" member = "user:YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" } ここまでで、開発メンバーが自分のGoogle Cloudアカウントを使ってCloud SQLへログインするための準備が整います。 ただし、IAM認証はCloud SQLへログインする主体を制御する仕組みです。ログイン後にどのSQLを実行できるかはMySQL側の権限で制御します。 MySQLロールでDB内権限を分ける Cloud SQLへのログイン可否はIAMで制御しますが、ログイン後にどのデータベースやテーブルに対して、どのSQLを実行できるかはMySQL側の権限で制御します。 MySQLにおけるロールは、複数の権限をまとめて管理してユーザーへ付与するための仕組みです。本記事では、Cloud SQL for MySQLのMySQL 8.0系でロールを利用する前提で説明します。 通常時と一時付与用のロール 今回は例として、次の2種類のMySQLロールを用意します。 ロール 用途 権限 viewer 通常時の参照用ロール SELECT editor 承認後に一時付与する書込系ロール SELECT , INSERT , UPDATE , DELETE 通常時は、開発メンバーに viewer のみを付与します。これにより、Cloud SQL Studioへログインした直後は参照のみ実行できる状態です。 一方、 editor は通常時には付与しません。データ修正などで書込系権限が必要になった場合だけ、後述するGitHub Actionsの承認付きワークフローから一時的に付与します。 ロールの分け方、付与する権限、対象範囲は、実際の運用や対象データによって調整が必要です。本記事では通常時の参照権限と、承認後に一時付与する書込系権限とで2つに分ける例として説明します。 ロールの作成 MySQL上にロールを作成して、参照や更新に必要な権限を付与します。 この例では、 YOUR_DB_NAME.* に対して権限を付与しています。実際の運用では必要以上に広い範囲へ権限を付与しないよう、対象データや作業内容に応じて、データベース単位、テーブル単位、権限種別を調整してください。 CREATE ROLE ' viewer ' , ' editor ' ; GRANT SELECT ON YOUR_DB_NAME.* TO ' viewer ' ; GRANT SELECT, INSERT, UPDATE, DELETE ON YOUR_DB_NAME.* TO ' editor ' ; 参照用ロールの付与 開発メンバーには、通常時の権限として viewer ロールを付与します。 また、データベースへのIAMログイン直後から標準で有効になるように、 viewer をデフォルトロールとして設定します。 MySQLユーザーは 'user'@'host' の形式で扱われます。本記事のサンプルでは 'YOUR_MEMBER_NAME'@'%' としており、 % は任意の接続元を表すhost部です。 実際の運用では、Cloud SQLへの到達経路やネットワーク制御に応じてhost部を調整してください。 GRANT ' viewer ' TO ' YOUR_MEMBER_NAME ' @ ' % ' ; SET DEFAULT ROLE ' viewer ' TO ' YOUR_MEMBER_NAME ' @ ' % ' ; GitHub Actionsを承認ゲートにして書込系ロールを一時付与する editor ロールが必要な場合は、GitHub Actionsの手動ワークフローから申請するようにしました。 この仕組みにおけるGitHub Actionsの役割は、Cloud SQLへの接続経路そのものではなく、書込系ロールを一時付与するための承認ゲートです。実際に GRANT を実行する処理はCloud SQLへ接続できる実行環境で行います。本記事ではCloud Buildを利用した例として説明します。 GitHub Environmentで申請者以外の承認を必須にする 今回利用するGitHub Actionsのワークフローの中では、 GitHub Environment を承認ゲートとして利用します。 Environmentには Required reviewers を設定し、 Prevent self-review を有効にします。ワークフローのjobで対象Environmentを指定すると、そのEnvironment上での実行前に承認を要求できます。 これにより、申請者以外のメンバーによる承認を挟んでから後続の処理を実行できるようになります。今回の例では、この承認ゲートを使って承認後に editor ロールを一時付与するようにしました。 権限操作用サービスアカウントの作成 承認後に GRANT を実行するため、権限操作用サービスアカウントを用意します。 権限操作用サービスアカウントは、GitHub ActionsからWorkload Identity Federation経由で利用します。承認後は、SQL実行基盤がこのサービスアカウントでMySQLへ接続し、対象ユーザーへ editor ロールを付与します。 このサービスアカウントもCloud SQLへIAM認証でログインできるようにするため、IAMデータベースユーザーとして作成しておきます。 resource "google_sql_user" "grant_sa" { project = "YOUR_PROJECT_ID" name = "YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com" instance = google_sql_database_instance.main.name type = "CLOUD_IAM_SERVICE_ACCOUNT" } この例では、権限操作用サービスアカウントを YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com として作成しています。 サービスアカウントの場合も、MySQL上では @YOUR_PROJECT_ID.iam.gserviceaccount.com を除いた部分をユーザー名として扱います。そのため、後続の GRANT では YOUR_SA_NAME を指定します。 権限操作用サービスアカウントがMySQL上で editor ロールを他ユーザーへ付与できるように、MySQL側では WITH ADMIN OPTION 付きで editor ロールを付与しておきます。 GRANT ' editor ' TO ' YOUR_SA_NAME ' @ ' % ' WITH ADMIN OPTION ; WITH ADMIN OPTION を付与されたユーザーは、そのロールを他のユーザーへ付与できます。つまり、この権限操作用サービスアカウントは書込系ロールの付与経路です。 そのため、Workload Identity Federationの条件やサービスアカウントの利用権限を絞る必要があります。想定したGitHubリポジトリ、ブランチ、Environment以外から利用されないようにしておきます。 承認後に実行するSQLは、最終的には次のような形です。 GRANT ' editor ' TO ' YOUR_MEMBER_NAME ' @ ' % ' ; ワークフロー設定例 GitHub ActionsからGoogle Cloudへの認証には、 Workload Identity Federation を利用します。 サービスアカウントキーをGitHub Secretsに保存せず、GitHub ActionsのOIDCトークンを使ってGoogle Cloudのサービスアカウントを利用できます。 GitHub Actionsの ワークフロー構文 を使った設定ファイルの例は次のとおりです。 name : grant-cloudsql-db-role on : workflow_dispatch : inputs : target_user : description : 対象ユーザーのIAMアカウントメールアドレス type : string required : true role : description : 付与するMySQLロール type : choice options : - editor required : true jobs : grant : runs-on : ubuntu-latest # このEnvironmentにRequired reviewersとPrevent self-reviewを設定する environment : YOUR_GITHUB_ENVIRONMENT_NAME permissions : contents : read # OIDCトークンを発行するために必要 id-token : write steps : - uses : actions/checkout@v4 # Workload Identity FederationでGoogle Cloudへ認証する - uses : google-github-actions/auth@v2 with : workload_identity_provider : projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account : YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name : Grant DB role run : gcloud builds submit --config=grant.yaml --substitutions="_TARGET_USER=${{ inputs.target_user }},_ROLE=${{ inputs.role }} " ここでは、Cloud SQLがPublic IPを持たず、プライベート経路でのみ到達できる構成として、Cloud Build上で権限操作SQLを実行します。 今回の構成では、Cloud SQLへ到達できるVPC内に中継用Compute Engineインスタンスを配置しました。Cloud Buildからは、そのインスタンス上の Cloud SQL Auth Proxy を経由してCloud SQLへ接続します。 なお、SQLの実行経路は環境に依存します。Cloud SQLへの到達方式や既存の実行基盤によっては、GitHub Actionsのself-hosted runnerやCloud Run jobsなども選択肢になります。 一時付与した書込系ロールを剥奪する editor ロールは一時的な付与を前提としているため、作業後には剥奪できるようにします。 editor ロールの付与は権限を強める操作のため、GitHub Environmentによる承認を必須にしています。一方、 editor ロールの剥奪は一時付与した権限を戻す操作のため、今回の例では承認ゲートを設けていません。 ロール剥奪の方法には、手動実行と定期実行の2つを用意しました。 作業後に任意のタイミングで剥奪するための手動ワークフロー 戻し忘れを抑止するための定期実行ワークフロー いずれの場合も、GitHub ActionsからCloud Buildへ処理を渡し、権限操作用サービスアカウントでMySQLへ接続して REVOKE を実行します。 手動でロールを剥奪する 手動剥奪では、対象ユーザーを入力として受け取り、次のようなSQLを実行します。 REVOKE ' editor ' FROM ' YOUR_MEMBER_NAME ' @ ' % ' ; GitHub Actionsのワークフロー設定ファイルの例は次のとおりです。 name : revoke-cloudsql-db-role on : workflow_dispatch : inputs : target_user : description : 対象ユーザーのIAMアカウントメールアドレス type : string required : true jobs : revoke : runs-on : ubuntu-latest permissions : contents : read id-token : write steps : - uses : actions/checkout@v4 - uses : google-github-actions/auth@v2 with : workload_identity_provider : projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account : YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name : Revoke DB role run : gcloud builds submit --config=revoke.yaml --substitutions="_TARGET_USER=${{ inputs.target_user }} " 定期実行でロールを剥奪する 手動での剥奪漏れを防ぐため、 editor ロールを定期的にも剥奪するように設定しておきます。 定期剥奪では、MySQL上の現在のロール付与状態を確認し、 editor ロールを保持しているユーザーを対象に REVOKE を実行します。 GitHub Actionsのワークフロー設定ファイルの例は次のとおりです。 name : revoke-cloudsql-db-role-scheduled on : workflow_dispatch : schedule : # 毎日 00:00 JST に実行する # GitHub Actions の cron は UTC 基準のため、15:00 UTC を指定する - cron : "0 15 * * *" jobs : revoke : runs-on : ubuntu-latest permissions : contents : read id-token : write steps : - uses : actions/checkout@v4 - uses : google-github-actions/auth@v2 with : workload_identity_provider : projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account : YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name : Revoke all temporary DB roles run : gcloud builds submit --config=revoke-all.yaml この例でCloud Buildに渡している revoke-all.yaml では、MySQL上で editor ロールを保持しているユーザーを取得します。そのうえで、権限操作用サービスアカウントのDBユーザーを剥奪対象から除外し、残ったユーザーに対して順に REVOKE を実行します。 権限操作用サービスアカウントから editor ロールを剥奪すると、以降の GRANT や REVOKE を実行できなくなるためです。 運用上の注意点と今後の改善 今回の構成により、人間によるCloud SQL for MySQLへのログインを個人のIAM認証に寄せ、通常時に書込系権限を持たない運用にできました。 一方で、この仕組みをより安全に、かつ便利に運用するには、権限付与後のロールの使い方、ワークフロー入力値の扱い、ロール粒度などを継続的に見直す必要があります。 ここでは、今回の構成における運用上の注意点と、今後の改善余地を整理します。 一時付与したロールを使うときの注意 今回の例では、 editor ロールはMySQLユーザーに対するデフォルトロールとして設定していません。そのため、Cloud SQL Studioなどから一時付与された権限を使う場合は、実行するSQLと同じセッション内で SET ROLE を実行します。 特に、実行単位によってセッションが変わりうる接続方式では、 SET ROLE と対象SQLを同じ実行単位にまとめます。 SET ROLE ' editor ' ; UPDATE YOUR_TABLE_NAME SET YOUR_COLUMN_NAME = ' XXXXX ' WHERE id = XXX ; なお、Cloud SQL for MySQLでは activate_all_roles_on_login フラグを有効にすると、ログイン時に付与済みのロールを自動的に有効化できます。ただし、その場合は一時付与した書込系ロールもログイン時に自動で有効化されるため、通常時に有効化したいロールと一時付与するロールの扱いを踏まえて設計する必要があります。 ワークフロー入力値の検証 本記事では、GitHub ActionsからCloud Buildへ target_user や role を渡し、SQL実行基盤側で GRANT や REVOKE を実行する構成を例にしています。 ワークフロー入力値をもとにSQLを組み立てる場合、想定外のユーザー名やロール名がSQLに含まれる可能性があります。 そのため、GitHub Actions側で入力形式を絞るだけでなく、SQL実行基盤側でも許可したユーザー名やロール名だけを扱うように制御する必要があります。 SQL本文のレビューと監査 今回の構成では、GitHub Actionsから対象ユーザーへの editor ロールの一時付与を申請し、承認を挟むようにしています。 ただし、承認対象は editor ロールの一時付与です。承認後に実行されるSQL本文そのものは、今回の仕組みではレビュー対象にしていません。 SQL本文まで事前に確認する場合は、申請時に実行予定のSQLや作業内容を添付し、承認者による確認を挟む運用も考えられます。 実行後の追跡性を高めるには監査ログやDB監査機能を組み合わせ、誰がいつ、どの操作をしたかを確認できる状態にしておくことも重要です。 一時付与した権限の失効タイミングの厳密化 今回の例では、手動剥奪と毎日0時の定期剥奪で editor ロールを戻す構成にしています。 より厳密に制御したい場合は、付与時刻や申請IDを記録してユーザー単位で失効時刻を管理する設計が必要になります。 MySQLロールの粒度 本記事でのMySQLロールの例としては、書込系権限を editor ロールにまとめました。 ただし、 INSERT 、 UPDATE 、 DELETE 、各種DDLでは影響範囲が異なります。特に DELETE 、 DROP 、 ALTER のような操作は対象データや運用ルールによっては別ロールに分けるほうが安全です。 例えば次のように、MySQLのロールを細分化する余地があります。 ロール 権限 用途 viewer SELECT 通常調査 data_writer INSERT , UPDATE など 手動データ補正 data_deleter DELETE 削除が必要な例外対応 schema_editor CREATE , ALTER など スキーマ変更 schema_dropper DROP 破壊的DDL ただし、ロールを細かく分けるほど、申請フローや承認基準も複雑になります。そのため、対象データ、作業頻度、レビュー体制に応じてロール粒度を調整していく必要があります。 おわりに 本記事では、人間によるCloud SQL for MySQLへのアクセスを、共用の特権DBユーザーとパスワード認証から、IAM認証とMySQLロールを使った運用へ移行した例を紹介しました。 今回の構成では、Cloud SQLへのログインは個人のGoogle Cloudアカウントに寄せ、DB内の権限はMySQLロールで制御しています。通常時は参照用の viewer ロールのみを利用し、書込系権限が必要な場合だけGitHub Actionsの承認付きワークフローから editor ロールを一時付与する形にしました。 これにより、共用特権DBユーザーに依存した人間によるアクセスをやめ、強い権限が常時使える状態を避けられるようになりました。 SQL本文のレビューや監査、ロール粒度の細分化、失効タイミングの厳密化などは、引き続き改善の余地があります。 今回の対応を足がかりとして、運用負荷と安全性のバランスを見ながら改善に取り組んでいきます。 ZOZOでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください。 corp.zozo.com

G-gen の今村です。当記事では、Cloud SQL で提供されているパフォーマンス最適化機能の1つである インデックスアドバイザー について解説します。 概要 インデックスアドバイザーとは 仕組み 料金と要件 料金 対象エディションと要件 有効化の手順 推奨事項の確認 推薦インデックスの確認方法 画面に表示される評価データの内容 Gemini による支援 推奨事項の適用 概要 インデックスアドバイザーとは Cloud SQL の インデックスアドバイザー 機能は、Cloud SQL で実行されたクエリを自動的に分析し、パフォーマンス向上につながる最適なインデックスを提案する機能です。当機能は Cloud SQL の Enterprise Plus エディションでのみ使用できます。 2026年6月現在、Cloud SQL の主要な3つのデータベースエンジン（MySQL、PostgreSQL、SQL Server）のすべてでサポートされています。各データベースエンジンにおける基本的な機能の目的や仕組みに大きな違いはありません。 リレーショナルデータベースの運用において、クエリの実行速度低下を解決するためのインデックス追加は非常に有効な手段です。しかし、どのカラムにどのようなインデックスを定義すべきかの判断には、高度な知識と経験が必要です。インデックスアドバイザーを使用することで、システムが実際のクエリ負荷に基づいて具体的な推奨インデックスを提示するため、データベース管理者の運用負荷を大幅に削減できます。 参考 : インデックス アドバイザーを使用する | Cloud SQL for PostgreSQL Cloud SQL の機能の詳細は、以下の記事を参照してください。 blog.g-gen.co.jp 仕組み インデックスアドバイザーがクエリを分析する際は、具体的な数値や文字列などのリテラルを除外した「正規化クエリ」と呼ばれる共通のパターンで追跡されます。例えば、検索する数値が異なっていても、同じ型のクエリとしてまとめて統計が取られる仕組みです。 このデータを基に、パフォーマンス向上につながる新しいインデックスが自動で識別され、具体的な CREATE INDEX 文として推奨事項に保存されます。生成された推奨事項は、Google Cloud コンソールの Query Insights ダッシュボードや推奨事項画面からいつでも確認できます。 なお当機能が提案するのは、データの検索速度を高めるためのインデックスであり、主キーや外部キーなどのテーブル構成の推奨事項などは生成されません。またリレーショナルデータベースの一般的な仕様として、テーブルにインデックスを追加すると、データ更新時にインデックス更新も同時に行われることで書き込み処理の負荷が増加する点にも留意が必要です。 よって、インデックスアドバイザーによる推奨事項を無条件に採用するのではなく、パフォーマンス影響等を考慮して、適用の可否を判断してください。 料金と要件 料金 インデックスアドバイザー機能自体の使用に追加料金はかかりません。Cloud SQL の Enterprise Plus エディションを利用していれば、無料で使用できます。 対象エディションと要件 インデックスアドバイザーを使用するには、Cloud SQL の Enterprise Plus エディション を使用している必要があります。また、インスタンスで Query Insights 機能が有効化されていることが要件となります。 Query Insights が収集する指標データは、原則として Cloud SQL インスタンスのストレージ領域を占有しません。指標は Cloud Monitoring に保存されるため、インスタンス自体のディスク容量を圧迫しない設計になっています。ただし、収集された指標の転送や API リクエストに伴い、Cloud Monitoring 側の料金が適用される場合がある点に留意してください。 参考 : Cloud SQL のエディションの選択 | Cloud SQL for PostgreSQL Cloud Monitoring の機能の詳細は以下の記事を参照してください。 blog.g-gen.co.jp 有効化の手順 インデックスアドバイザーを有効化する手順は以下の通りです。 Cloud SQL の「インスタンス」ページに移動し、対象のインスタンス名をクリック 概要ページから「編集」をクリック 「構成の編集」をクリック 「インスタンスのカスタマイズ」セクションで「Query Insights」を展開 「Query Insights を有効にする」チェックボックスをオンにする 「インデックスアドバイザーを有効にする」チェックボックスをオンにする 「保存」をクリックして設定を反映 インデックスアドバイザーを有効にするには、インスタンスの再起動が必要です。本番環境で実施する際は、必ずメンテナンス可能な時間帯を計画してください。 参考 : Query Insights を使用してクエリのパフォーマンスを向上させる | Cloud SQL for SQL Server 推奨事項の確認 推薦インデックスの確認方法 推奨インデックスを確認する手順は以下の通りです。 Cloud SQL の「インスタンス」ページに移動 対象のインスタンス名をクリックし、「Query Insights」をクリック 「上位のクエリとタグ」セクションで目的のクエリをクリック 画面に表示される評価データの内容 クエリの詳細画面では、推奨されたインデックスを適用すべきかを総合的に判断するための情報がまとまっています。 まず確認したいのがパフォーマンスへの影響です。推定クエリ速度が「高」「中」「低」の3段階で示されるため、導入によってどれほどの速度向上が見込めるかが一目でわかります。次に、その効果に対してどれほどのリソースが必要になるかを、影響を受けるテーブルと必要な追加ストレージサイズの推定値から見積もります。さらに、そのインデックスがシステム全体のどれほど多くの処理に貢献するかを、影響を受けるクエリの数で評価します。 このように、見込める効果とリソースへの影響を天秤にかけて適用の可否を判断した上で、画面上の「インデックスの作成」をクリックすると、実行すべき具体的な DDL が表示されます。 推奨事項は、自動的にデータベースへ適用されるわけではありません。そのため、ユーザー自身がコンソール画面から CREATE INDEX 文をコピーし、手動で適用作業を行う必要があります。 Gemini による支援 2026年6月現在、Cloud SQL ではインデックスアドバイザーによる自動分析だけでなく、生成 AI モデル Gemini を使用したクエリ作成のアシスタンス機能も提供されています。 Google Cloud コンソールの Cloud SQL Studio などの画面において、自然言語のプロンプトから SQL の生成や、クエリ構造の説明を取得できます。これにより、インデックスアドバイザーが提案した CREATE INDEX などの DDL の意図を深く理解したり、最適化されたクエリの記述を効率化できます。機能の具体的な使用方法や最新の要件については、公式ドキュメントを参照してください。 参考 : Gemini を使用して SQL クエリを作成する | Cloud SQL for PostgreSQL 推奨事項の適用 推奨事項を適用するには、コピーした CREATE INDEX 文を、Cloud SQL インスタンス内のデータベースに対して手動で実行します。実行にあたり、普段運用で使用しているデータベース管理ツール経由で DDL を実行することも、Google Cloud コンソール上で直接 SQL を実行できる Cloud SQL Studio を使用することもできます。 一般的に、インデックスの作成処理は、既存のテーブル内にあるレコードを読み込んだうえで新しくインデックスを構築してディスクに書き込むため、データベースの CPU やディスク I/O などのリソースを大きく消費します。対象テーブルのデータ量によっては処理に長い時間がかかり、本番環境のパフォーマンスに影響を与える可能性がある点に留意してください。 そのため、本番環境へ適用する際は、事前に検証環境で処理時間や影響度を確認し、システムへの負荷を最小限に抑えられるよう、利用者の少ない時間帯に実行を計画してください。 今村 壱生 (記事一覧) クラウドソリューション部 ソリューションアーキテクト課 2026年3月にG-genへ入社。約7年間 Web 広告運用やウェブ解析に携わり、その後は社内 SE として開発業務に従事。広告運用の現場感と技術的な視点、その双方を併せ持つ経験をベースに、現在は Google Cloud のスキルアップに注力。データ活用とクラウド技術を融合させ、お客様のビジネス成長を支えるエンジニアを目指している。 Follow