第2回のお題

第1回に引き続き、隔週で行っているパフォーマンスMTGで見つかったIssueを選びました。

パフォーマンスMTGでは以下のようにRDSのPerformance Insightsという機能を使い、RDSのCPUをモニタリングして負荷が高いものをIssue化しています。今回はそのIssueのうち、00:30頃から02:00頃まで負荷が高かったクエリの改善をお題として選びました。

B/43では口座残高を日々集計し、資金移動業者として履行保証金として保全する金額を計算しています。履行保証金とは、仮にスマートバンクが破産した際でもユーザーに対して優先的に弁済を行う原資となるものです。今回のお題となったクエリは、この履行保証金を計算するシステムの一部分で実行されていました。このクエリはユーザーの口座残高を集計するため、サービスの成長とともにレコード数が増え、徐々に問題が顕在化してきたものでした。

レギュレーション

2人1組でチームを組み、制限時間は1時間30分
終了後、各チームの改善案を発表し、最も良いものをPRとして出す

イベントの様子

今回のお題はバッチシステムから発行される一部のクエリだったため、Performance Insightsに表示されているクエリが実行されているコード箇所を特定して、そのバッチの仕様を把握するところから始まりました。

第1回の反省点として、パフォーマンスチューニングに使う本番のコピーDBの準備に時間がかかっていたため、今回は前日に準備しておくことでスムーズに作業に着手できました。

今回のバッチはリリース当初から存在するもので、バッチの仕様だったり集計対象のテーブルに普段触れていないメンバーも多かったです。そのため、チーム横断して質問しあいながら仕様を把握し、改善案については各チーム内で考えるという形で進めました。

ボトルネックとなっていた箇所

調査を進める中で、以下のようなコードがボトルネックになっていることが判明しました。（実際のコードをぼかして書いています）

# Point 1
records = TableA.where(
    foreign_table_id: user.table_a
    .joins(:foreign_table)
    .where(id: ...id)
    .group(:user)
    .select('MAX(foreign_tables.id)')
)

# Point 2
return if records.empty?

other_table.update!(amount: records.sum(&:amount))

Point 1

サブクエリでuserごとのidの最大値を取得し、foreign_table_idで絞り込みをしているクエリです。TableAのforeign_table_idは外部キーだったためindexは作成されているのですが、実行計画を見るとtype=ALLとなっており全件走査が起きていました。ここがCPU負荷を高めている要因だと思われます。

実行計画（実際のものから重要な箇所のみピックアップ）

id	select_type	table	type	rows	filtered	Extra
1	PRIMARY	table_a	ALL	289879	100	Using where
2	SUBQUERY	foreign_tables	ref	1	100	Using index; Using temporary
2	SUBQUERY	table_a	eq_ref	1	100	Using index

Point 2

ここの empty? はActiveRecordに生えているものなので、SELECT 1 AS one FROM … というクエリが発行されます。ただやりたいことはrecordsが1件以上あるかどうかのチェックなため、クエリを発行しなくても判定できるものでした。

結果発表

第1回とは違って、全てのチームで同じ解決策に至ったため同率優勝となりました 👏

どのチームもtable_aにindexが効かずに全件走査されている点を問題視し、indexが効くようにクエリを変えるアプローチを取りました。具体的には、以下のようにIN句の中で集計関数を使うとindexが使われず、

SELECT *
FROM table_a
WHERE foreign_table_id IN (
  SELECT MAX(id)
    FROM table_a
   INNER JOIN foreign_tables ON table_a.foreign_table_id = foreign_tables.id
   WHERE id > xxx
   GROUP BY user_id
)

以下のようにIDを直に指定するとindexが使われるため、IN句に指定するIDを取得する処理とその後の処理を分けるように修正しました。

SELECT *
FROM table_a
WHERE foreign_table_id IN (1,2,3)

なお、同じようにサブクエリにしたままでも集約関数を使わなければindexを効かせることができます。これにはMySQLのクエリーオプティマイザによるセミジョイン最適化の仕組みが働いているようです。

https://dev.mysql.com/doc/refman/8.0/ja/semijoins.html

修正コードイメージ

ids = user.table_a
        .joins(:foreign_table)
        .where(id: ...id)
        .group(:user)
        .pluck('MAX(foreign_tables.id)')

return if ids.empty?

records = TableA.where(foreign_table_id: ids)
other_table.update!(amount: records.sum(&:amount))