Zenn
🍰

CloudSQLの「Query Insight」を有効にしたら謎のCOMMITが大量に記録されていた件（cakePHP 5.x）

2024/11/12に公開
SQL
Google Cloud
CakePHP
Cloud SQL
tech

こんにちは、もっちーです。

Webマーケティング会社のエンジニアとして働いています。

社内で使っているWebメディアの分析ツールで、SQLサーバーの使用率が最大で100%になっていることが判明しました😢

タイトルにも書いたようにSQLサーバーの実行ログを確認したところ

ということに気づきました。

今回の記事では、この原因と解決策をまとめていこうと思います（備忘録として）

はじめに

アプリケーション部分はCakePHP（5.0.1）、インフラにはGCPを使用しています。

詳しい構成は以下のようになっています。

  • データベース（Cloud SQL）
  • データ受け取り（App Engine）
  • 集計処理（Compute Engine）
  • 画面表示（Compute Engine）
  • オブジェクトの保存（Cloud Storage）

規模の大きいWebメディアからリクエストを受け取る仕組みになっているため、テレビ放送やSNSで話題になったときに、急激にアクセス数が増えたりSQLの処理が重くなったりします。

ここ最近でSQLサーバーのCPU使用率が90%近くまで上がることが増えてきたので、これは危ない・・と思い調査を始めることにしました。

興味本位でQueryInsightを有効化してみた

CloudSQLの画面を開いたところ、なにやら分析データが見られそうな「QueryInsight」という項目を見つけたので、さっそく有効化してみました。

データ取得のSQLではなく「COMMIT」の負荷が高い...

有効にしてみた結果はこちら。


実際のQueryInsightの画面

もっと重い処理のSQLがあるはずなのに、いちばん上に来ているはCOMMITという結果に・・

データ受け取り部分ではトランザクション処理を使っている部分はなかったので、なぜCOMMITが記録されているのだろうと不思議な気持ちでした。

とはいえCOMMITの実行を減らすことができれば、CPU使用率はかなり下がるはずなので調査を始めました。

原因・解決方法

CakePHPのレコード保存処理はデフォルトでトランザクションになっていた

CakePHPのORMに用意されている保存処理（{モデル名}->save）では、デフォルトの動作がトランザクション処理になっていることが分かりました。

$articles->save($entity);
上記は各エンティティーの保存で個別のトランザクションが走ります

cakePHP公式サイトの解説

この内容を見た感じではデフォルトではトランザクション処理になり、もしトランザクションをしたくないのであれば['atomic' => false]を指定する必要があると読み取れました。

詳しく知りたかったのでCakePHPのコードを探したところ、たしかにTable.phpでトランザクション処理をしていることが確認できました。

長いので一部省略しています

Table
    public function save(
        EntityInterface $entity,
        array $options = []
    ): EntityInterface|false {
        $options = new ArrayObject($options + [
            'atomic' => true,
        ]);

        $success = $this->_executeTransaction(
            fn () => $this->_processSave($entity, $options),
            $options['atomic']
        );

        if ($success) {
            if ($this->_transactionCommitted($options['atomic'], $options['_primary'])) {
                $this->dispatchEvent('Model.afterSaveCommit', compact('entity', 'options'));
            }
            if ($options['atomic'] || $options['_primary']) {
                if ($options['_cleanOnSuccess']) {
                    $entity->clean();
                    $entity->setNew(false);
                }
                $entity->setSource($this->getRegistryAlias());
            }
        }

        return $success;
    }

    protected function _executeTransaction(callable $worker, bool $atomic = true): mixed
    {
        if ($atomic) {
            return $this->getConnection()->transactional(fn () => $worker());
        }

        return $worker();
    }

まずが_executeTransactionを実行して、これが成功したら_transactionCommittedでコミットするという流れになっています。

そのためsave時に何もパラメータを設定しなければ、トランザクション処理を使ってレコードの保存がおこなわれることが分かりました。

保存処理のときにオプションで['atomic' => false]を指定することで解決

そのため不要なCOMMITをしないようにしたいのであれば、レコードの保存時に->save($entity, ['atomic' => false])と書けば良いことが分かりました。

実際にコードを変更してみたところ重かったCOMMITの実行ログがなくなり、スパイク時のCPU使用率が90% → 50%程度まで下がりました


平常時は30%以下に

SQLサーバーのスペックを上げるかどうか悩んでいたタイミングなので、結果的に上げる必要なく済んで良かったです。

最後に

トランザクション処理を外すときの注意点

とはいえデフォルトでトランザクション処理になっているのは理由があります。

一般的なフレームワークのORMでは、関連テーブルをまとめて保存する処理が用意されています

トランザクション処理をOFFにすることで、関連しているテーブルの保存時にトランザクションが走らないので

といったことが起こってしまう危険性があったり。

なので使い分けが大切です（今回のようにログとして受け取るだけの部分はトランザクションOFFで良いと思います）

今後の改善案

そもそもログ系のデータを保存するのであれば、MySQLよりBigQueryなどの方が良いですよね・・。

でも社内のリソースが足りずに手をつけられない状態です（泣）

まずはMySQLにデータを保存するようにして、BigQueryに移行したものから削除していく（BigQueryをバックアップ的に使う）方法であれば、DataStreamが使えそうな気がしました。

https://zenn.dev/openlogi/articles/survey-datastream-for-bigquery

Discussion