はじめに
私が所属するチームでは、データ管理においてCloud SQL（PostgreSQL14）を使っています。
本資料ではCloud SQLおよびPostgreSQL14を利用してクエリ改善に取り掛かり一定の効果が得られたのでその手順を公開・共有しようと思います。
課題
そもそも、なぜCloud SQLの負荷を下げる必要があったのでしょうか？
我々のチームは日々の業務において3つの課題を見つけていました。
課題:1 レプリケーションラグ
我々のサービスにおいては負荷分散を目的としてリードレプリカを複数台利用しています。
しかし、リードレプリカの利用では「レプリケーションラグ」を考慮する必要があります。
レプリケーションラグとはリードレプリカ（レプリカインスタンス）の状態がプライマリインスタンスの状態よりも遅れている秒数のことを示します。
データの読み込みに対してリードレプリカを参照するように実装しているとレプリケーションラグの影響で書き込んだはずのデータが直後に取得できないということが発生します。
この問題に対してはデータフェッチ時にリトライ処理を施す必要があります。
しかし、レプリケーションラグの影響が大きい場合には1度のリトライでは取得できず、複数回のリトライ処理のが発生することになります。
システムの過負荷を避けるために効率的なリトライ（参考：Exponential backoff）をしてもらうなど工夫を行う必要がありますが、システムとしてもレプリケーションラグが小さくなればリトライ処理によるアクセス増大を減らすことにもつながります。
課題:2 スロークエリ
スロークエリはアプリケーションのボトルネックとなる、処理速度が多いSQLのことを示します。
スロークエリはAPIレイテンシの悪化にも繋がります。
また、我々のチームではサービスレベル指標としてAPIのQuery(GET,HEAD)を 1500[ms]、Command(POST,PUT,PATCH,DELETE)を 3000[ms] で測定しています。スロークエリはサービスレベル目標を達成する上でも解決すべき課題となります。
課題:3 運用コスト
我々のチームにおいてCloud SQLは全体の3~4割を占め最も運用コストがかかるものでした。そのためCloud SQLの運用コスト改善に取り掛かることが最も効率の良い試みでした。
実践：クエリ改善方法
我々が取り組んだクエリ改善の方法をステップを踏んでご紹介します。
Step1: 発見
Query Insightsを活用してスロークエリを見つけます。
ただ、Query Insightsではサンプリングした結果しかわからないので測定のために実際の処理を特定します。ログエクスプローラやBigQueryを活用してスロークエリの実データを手に入れます。
Step2: 測定
Step1で手に入れた実データを利用してEXPLAINおよびEXPLAIN ANALYZEを用いて測定します。
(私はまだまだ慣れていないので)EXPLAIN ANALYZEを活用して "actual time" 値の実時間を確認していました。
クエリ改善なので取得した結果が変わってしまうことは避けたいですが、この段階で取得できる結果についてはそこまで意識しなくて問題ないです。当たり前ですが、実データの場合は更新が発生し結果が変わる可能性があります。そのため、改善後にもう一度実行するのがよいです。
Step3: 改善
SQLを分解します。アプリケーションとして必要な要素はなにか？（不要な要素は取得していないか）を精査します。実装してから時間が経っているものだと、データ量の増加やSQLとしては取得しているけどアプリケーション側では使っていない要素などがあります。使っていない要素なのにJOINなどをしていると無駄なコストが発生することになります。
Step4: 適用
アプリケーションコードに組み込みます。我々のシステムはGo言語による開発およびORMとしてGORMを活用しています。Raw SQLを活用し改善して改善したSQLを組み込みました。また、必要ならばINDEX作成のマイグレーションをプロダクトへ適用します。
Step5: 確認
適用後、クエリインサイトを確認し改善対象のクエリが上位に入らないことを確認します。また、必要であえればCloud SQLのCPU負荷率を確認し負荷が下がっていることを確認します。
実例紹介
実例と言いつつ業務で利用しているデータ・クエリをそのまま公開する訳には行かないので同じようなモデリングとなるようにSQLを用いて説明しようと思います。
今回改善に至ったSQLは検索していたカラムに対してINDEXは貼られていたもののVIEWを組み立てる際には無効になっているものだったので解体して組み立て直し対応しました。
テーブル定義およびビューの定義は以下のようなものです。
CREATE TABLE a_table (
id VARCHAR(128) PRIMARY KEY NOT NULL,
name VARCHAR(128) NOT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE b_table (
id VARCHAR(128) PRIMARY KEY NOT NULL,
name varchar(128),
a_id VARCHAR(128) NOT NULL,
type smallint,
created_at timestamp DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX b_table_name_idx ON b_table (
name
);
CREATE VIEW view (
id,
name,
created_at,
updated_at,
type_1_name,
type_2_name
) AS SELECT
a_table.id AS id,
a_table.name AS name,
a_table.created_at AS created_at,
a_table.updated_at AS updated_at,
(SELECT name FROM b_table WHERE a_id = a_table.id AND type = 1) AS type_1_name,
(SELECT name FROM b_table WHERE a_id = a_table.id AND type = 2) AS type_2_name
FROM a_table;
こちらに対して以下のようなSQLを用いてデータ取得を行なっていました。
SELECT * FROM view WHERE type_1_name = 'foo';
検索条件のtype_1_nameはb_tableにおいてはnameに対してINDEXは貼られていましたが、今回のようなVIEWではINDEXの効果を得られることができていませんでした。
こちらに対しては以下のようなSQLを新たに作成してデータ取得を実施するように修正しました。
SELECT
a_table.id as id,
a_table.name as name,
a_table.created_at as created_at,
a_table.updated_at as updated_at,
b_table.name as type_1_name,
(SELECT name FROM b_table WHERE a_id = a_table.id AND type = 2) as type_2_name
FROM b_table
INNER JOIN a_table ON a_table.id = b_table.a_id
WHERE b_table.name = 'foo';
検索条件となるb_tableでまずデータを取得してそこに対して必要なa_tableの値をJOINする形式へと修正しました。
（typeが2で絞るパターンも同様のSQLを追加することになります。）
修正した結果、実際に利用していたクエリでは
- cost=3.93..3553085.93 rows=9291 width=1313 → cost=1003.91..5571.41 rows=15 width=781
- Execution Time: 4133.716 ms → Execution Time: 19.681 ms (約200倍の改善)
と効果が確認できました。
結果
今回ご紹介していませんが、紹介した事例のほかにもクエリ改善は実施しています。その結果当初課題として挙げていたものに対して下記の結果を得ることができました。
課題1: レプリケーションラグ
Cloud SQLのCPU使用率は適用前では10%~15%で推移していたのですが、適用後は2%~5%で推移するようになりました。
また、レプリケーションラグについても最大で1[s]程度の遅れが発生していたのが0.2[s]未満へと改善したことが確認できました。
GCPのドキュメントにあるようにリードレプリカでの長時間実行クエリが改善したことによりレプリケーションラグも小さくなったのかと考えます。
CPU使用率が改善できたのでCloud SQLのスケールダウンも視野に入ってきます。
しかし、公式ドキュメントのよくある質問に記載があるようにスケールダウン（マシンタイプの更新）を実施するには再起動が必要となりダウンタイムが発生します。今後のビジネスの拡大や、ダウンタイムを設けてまでの更新を考えると費用対効果が少ないと判断し今回は見送りました。
課題2: スロークエリ
実例を利用するエンドポイントにおいては avg 4253[ms] -> avg 262[ms] へと改善しました。
実例のクエリはCommandのエンドポイントにおいて利用されており目標値である3000[ms]以内へと改善されました。
課題3: 運用コスト
レプリカインスタンスにおけるCPU使用率の削減を確認できたので、負荷分散を目的として横に並べていたレプリカインスタンスの数を少なくすることにしました。
最終的には横に並べていたレプリカインスタンスの数を半分に減らし、Cloud SQLの運用コストは4割ほど削減できました。
おわりに
Cloud SQLを利用したクエリ改善について、改善方法と実例2種を紹介してみました。
今回クエリ改善を実施した中で以下の要因が大きく貢献したと考えています。
- デイリースクラムにてクエリインサイトを確認していました。
- クエリ改善に対する課題感をチーム内で共有できていました。
- ここでの確認は大雑把にどのクエリが高いかを確認するくらいで詳細は追いません。
- クエリ改善後も効果があったのかどうか、次のスロークエリがあるかどうかなどが確認できています。
- スプリントレトロスペクティブにて運用コストを確認していました。
- クエリ改善が運用コスト改善に直結して効果として現れたことを体感できました。
また、SQLをGORMを用いて実装する際に複雑なSQLを組み立てるのであればORMってあまり必要ないのではないかと思いました。ところが、先輩にこの話をしたところそれは「ORMが必要かどうかというよりそもそものモデリングが間違っている気がする」とお話しを伺いました。確かにモデリングが正しければORMを用いた実装で事足りるようになる気がして納得しました。モデリングをもっと勉強しようと思います。
みなさまの環境で同じような課題を抱えていて、Cloud SQLやPostgreSQLを利用しているのであれば今回紹介した方法で解決できるかもしれません。ぜひ試してみてください。