見出し画像
Photo by naseem_ou812

NewRelicで計測し、クエリを改善した話 -ｺﾞﾙﾌﾒﾄﾞﾚｰ開発日記-

宮水

こんにちは。ゴルフメドレーの開発をしている宮水です。

ゴルフメドレーとは、全国のゴルフ練習場を特徴（打ち放題 / 駅近 / アプローチ練習場...など）から検索できるサービスです。ゴルフ場コンサルタントのあべちゃんと、エンジニアのゆうすけと3人で開発しています。

地図検索をリリース！

さて、このゴルフメドレー ですが、最近新しく「地図検索機能」を導入しました。住所や地名などのキーワードを入力すると、そのキーワードの地点から20km圏内のゴルフ練習場施設が検索できるというものです。

画像1

地図検索のクエリが重たい

目玉機能をリリースできて喜んでいたものの、この地図検索のクエリがちょっと遅いことに気づきました。東京などの施設の件数が多いキーワードだと大体、800〜1700msくらいかかっています。

画像2

原因

NewRelicから該当するクエリをクリックしてより詳しい内訳をみてみると、TagとImageの処理が重いみたいです。

画像3

Railsのログをみてみると、ゴルフ場施設のモデルに紐づくtag（特徴）や画像のクエリが、検索結果の数と同じだけ走っていました。典型的なN+1問題でした。（開発環境では少ないクエリで実装していたので、全然気づきませんでした。）

↳ app/services/map_search_practice_facility_service.rb:24:in `call'
  (1.8ms)  SELECT "tags"."name" FROM "tags" INNER JOIN "practice_facility_tags" ON "tags"."id" = "practice_facility_tags"."tag_id" WHERE "practice_facility_tags"."practice_facility_id" = $1  [["practice_facility_id", 225]]
↳ app/graphql/types/practice_facility_type.rb:293:in `tags'
 PracticeFacilityReviewImage Load (0.4ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" INNER JOIN "practice_facility_reviews" ON "practice_facility_review_images"."practice_facility_review_id" = "practice_facility_reviews"."id" WHERE "practice_facility_reviews"."practice_facility_id" = $1 ORDER BY "practice_facility_review_images"."id" DESC LIMIT $2  [["practice_facility_id", 225], ["LIMIT", 1]]

...
...
...
同じクエリが、ヒットした数だけ走っている

↳ app/models/practice_facility.rb:67:in `small_image'
  (2.3ms)  SELECT "tags"."name" FROM "tags" INNER JOIN "practice_facility_tags" ON "tags"."id" = "practice_facility_tags"."tag_id" WHERE "practice_facility_tags"."practice_facility_id" = $1  [["practice_facility_id", 214]]
↳ app/graphql/types/practice_facility_type.rb:293:in `tags'
 PracticeFacilityReviewImage Load (1.3ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" INNER JOIN "practice_facility_reviews" ON "practice_facility_review_images"."practice_facility_review_id" = "practice_facility_reviews"."id" WHERE "practice_facility_reviews"."practice_facility_id" = $1 ORDER BY "practice_facility_review_images"."id" DESC LIMIT $2  [["practice_facility_id", 214], ["LIMIT", 1]]
Completed 200 OK in 3321ms (Views: 18.9ms | ActiveRecord: 683.1ms | Allocations: 404008)

実装をみてみる

このクエリで関連するテーブルは4つです。

ゴルフ練習場：PracticeFacility
施設の特徴：Tag
施設のレビュー：PracticeFacilityReview
画像：PracticeFacilityReviewImage

画像4

class PracticeFacility < ApplicationRecord
 has_many :practice_facility_reviews, dependent: :destroy
 has_many :practice_facility_review_images, through: :practice_facility_reviews
 
 # 施設のレビューに添付されている画像の中で、TOPにしたいものはこちらに紐づいている
 belongs_to :practice_facility_review_image, optional: true
end

class PracticeFacilityReview < ApplicationRecord
 belongs_to :practice_facility
end

class PracticeFacilityReviewImage < ApplicationRecord
 belongs_to :practice_facility_review
 has_one :practice_facility
end

class PracticeFacilityTag < ApplicationRecord
 belongs_to :practice_facility
 belongs_to :tag
end

class Tag < ApplicationRecord
 has_many :practice_facility_tags, dependent: :destroy
 has_many :practice_facilities, through: :practice_facility_tags
end

N+1問題の解決

# 関係ないcodeは省略してます
def sort_by_distance
  PracticeFacility.all.order(:distance)
end

↓取得元のコードを、以下のように変更しました。

# 関係ないcodeは省略してます
def sort_by_distance
  PracticeFacility.all.includes(:tags).includes(:practice_facility_review_image).includes(:practice_facility_review_images).order(:distance)
end

発行されたクエリをみてみると、tagも画像もN+1が解消されました。

 # 施設に関するクエリ
 PracticeFacility Load (5.2ms)  SELECT practice_facilities.*, distance FROM "practice_facilities"
 
 # tagに関するクエリ
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityTag Load (7.6ms)  SELECT "practice_facility_tags".* FROM "practice_facility_tags" WHERE "practice_facility_tags"."practice_facility_id" IN ($1, $2, ... $126, $127)  [["practice_facility_id", 225],... ["practice_facility_id", 214]]
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 Tag Load (0.4ms)  SELECT "tags".* FROM "tags" WHERE "tags"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8)  [["id", 3], ["id", 4], ["id", 2], ["id", 1], ["id", 6], ["id", 7], ["id", 8], ["id", 5]]
 
  # 画像がある施設のクエリ
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReviewImage Load (0.6ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" WHERE "practice_facility_review_images"."id" IN ($1, $2, $3)  [["id", 22], ["id", 3], ["id", 12]]
 
 # レビューのクエリ
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReview Load (1.2ms)  SELECT "practice_facility_reviews".* FROM "practice_facility_reviews" WHERE "practice_facility_reviews"."practice_facility_id" IN ($1, $2, ... $126, $127)  [["practice_facility_id", 225], ["practice_facility_id", 233]...["practice_facility_id", 214]]
 
 # 画像がない施設のクエリ
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReviewImage Load (0.8ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" WHERE "practice_facility_review_images"."practice_facility_review_id" IN ($1, $2,... $74, $75)  [["practice_facility_review_id", 2]... ["practice_facility_review_id", 506]]
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'

ActiveRecordの欄がすごく早くなりました👏（683.1ms→36.0ms）

Completed 200 OK in 961ms (Views: 25.7ms | ActiveRecord: 36.0ms | Allocations: 282001)

必要ないクエリを叩きたくない

画像に関していうと、3種類の施設があります。

①投稿された画像が１枚もない施設
②投稿された画像がある施設
   　 施設→レビュー→画像で一番最新のものを出すようにしている
        practice_facility_review_images.last&.small_image
③投稿された画像があり、運営者によってTOP画像が選ばれている施設
        PracticeFacilityモデルに画像が一つ紐づけてある
        practice_facility_review_image&.small_image

地図検索画面の構成を見ると、このページには施設のTOP画像しか必要ありません。施設を検索する度に「この施設にはTOP画像があるか？」「TOP画像がなければ最新の画像を取得する」と判定しているのは無駄だと考えました。

画像5

 # 画像がある施設のクエリ
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReviewImage Load (0.6ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" WHERE "practice_facility_review_images"."id" IN ($1, $2, $3)  [["id", 22], ["id", 3], ["id", 12]]
 
 # レビューのクエリ => 必要ない
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReview Load (1.2ms)  SELECT "practice_facility_reviews".* FROM "practice_facility_reviews" WHERE "practice_facility_reviews"."practice_facility_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127)  [["practice_facility_id", 225], ["practice_facility_id", 233], ["practice_facility_id", 231], ["practice_facility_id", 166], ["practice_facility_id", 218], ["practice_facility_id", 142], ["practice_facility_id", 223], ["practice_facility_id", 201], ["practice_facility_id", 134], ["practice_facility_id", 194], ["practice_facility_id", 221], ["practice_facility_id", 133], ["practice_facility_id", 189], ["practice_facility_id", 213], ["practice_facility_id", 129], ["practice_facility_id", 191], ["practice_facility_id", 150], ["practice_facility_id", 234], ["practice_facility_id", 149], ["practice_facility_id", 238], ["practice_facility_id", 329], ["practice_facility_id", 236], ["practice_facility_id", 193], ["practice_facility_id", 139], ["practice_facility_id", 196], ["practice_facility_id", 114], ["practice_facility_id", 269], ["practice_facility_id", 175], ["practice_facility_id", 237], ["practice_facility_id", 184], ["practice_facility_id", 252], ["practice_facility_id", 118], ["practice_facility_id", 125], ["practice_facility_id", 143], ["practice_facility_id", 144], ["practice_facility_id", 303], ["practice_facility_id", 200], ["practice_facility_id", 253], ["practice_facility_id", 265], ["practice_facility_id", 128], ["practice_facility_id", 205], ["practice_facility_id", 348], ["practice_facility_id", 328], ["practice_facility_id", 350], ["practice_facility_id", 147], ["practice_facility_id", 122], ["practice_facility_id", 227], ["practice_facility_id", 199], ["practice_facility_id", 170], ["practice_facility_id", 168], ["practice_facility_id", 155], ["practice_facility_id", 230], ["practice_facility_id", 241], ["practice_facility_id", 311], ["practice_facility_id", 202], ["practice_facility_id", 154], ["practice_facility_id", 279], ["practice_facility_id", 120], ["practice_facility_id", 224], ["practice_facility_id", 156], ["practice_facility_id", 308], ["practice_facility_id", 222], ["practice_facility_id", 255], ["practice_facility_id", 174], ["practice_facility_id", 305], ["practice_facility_id", 331], ["practice_facility_id", 136], ["practice_facility_id", 162], ["practice_facility_id", 116], ["practice_facility_id", 278], ["practice_facility_id", 206], ["practice_facility_id", 172], ["practice_facility_id", 151], ["practice_facility_id", 341], ["practice_facility_id", 186], ["practice_facility_id", 135], ["practice_facility_id", 138], ["practice_facility_id", 277], ["practice_facility_id", 266], ["practice_facility_id", 300], ["practice_facility_id", 173], ["practice_facility_id", 239], ["practice_facility_id", 281], ["practice_facility_id", 207], ["practice_facility_id", 198], ["practice_facility_id", 299], ["practice_facility_id", 132], ["practice_facility_id", 324], ["practice_facility_id", 210], ["practice_facility_id", 489], ["practice_facility_id", 164], ["practice_facility_id", 319], ["practice_facility_id", 119], ["practice_facility_id", 197], ["practice_facility_id", 298], ["practice_facility_id", 339], ["practice_facility_id", 609], ["practice_facility_id", 141], ["practice_facility_id", 216], ["practice_facility_id", 211], ["practice_facility_id", 179], ["practice_facility_id", 297], ["practice_facility_id", 169], ["practice_facility_id", 217], ["practice_facility_id", 340], ["practice_facility_id", 244], ["practice_facility_id", 270], ["practice_facility_id", 195], ["practice_facility_id", 212], ["practice_facility_id", 215], ["practice_facility_id", 607], ["practice_facility_id", 1546], ["practice_facility_id", 187], ["practice_facility_id", 334], ["practice_facility_id", 335], ["practice_facility_id", 290], ["practice_facility_id", 506], ["practice_facility_id", 185], ["practice_facility_id", 322], ["practice_facility_id", 220], ["practice_facility_id", 203], ["practice_facility_id", 498], ["practice_facility_id", 229], ["practice_facility_id", 243], ["practice_facility_id", 610], ["practice_facility_id", 296], ["practice_facility_id", 214]]
 
 # 画像がない施設のクエリ => 必要ない
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'
 PracticeFacilityReviewImage Load (0.8ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" WHERE "practice_facility_review_images"."practice_facility_review_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75)  [["practice_facility_review_id", 2], ["practice_facility_review_id", 6], ["practice_facility_review_id", 7], ["practice_facility_review_id", 10], ["practice_facility_review_id", 18], ["practice_facility_review_id", 19], ["practice_facility_review_id", 20], ["practice_facility_review_id", 23], ["practice_facility_review_id", 24], ["practice_facility_review_id", 17], ["practice_facility_review_id", 26], ["practice_facility_review_id", 29], ["practice_facility_review_id", 38], ["practice_facility_review_id", 39], ["practice_facility_review_id", 53], ["practice_facility_review_id", 55], ["practice_facility_review_id", 57], ["practice_facility_review_id", 58], ["practice_facility_review_id", 61], ["practice_facility_review_id", 63], ["practice_facility_review_id", 65], ["practice_facility_review_id", 66], ["practice_facility_review_id", 73], ["practice_facility_review_id", 74], ["practice_facility_review_id", 75], ["practice_facility_review_id", 76], ["practice_facility_review_id", 77], ["practice_facility_review_id", 78], ["practice_facility_review_id", 83], ["practice_facility_review_id", 84], ["practice_facility_review_id", 117], ["practice_facility_review_id", 118], ["practice_facility_review_id", 119], ["practice_facility_review_id", 121], ["practice_facility_review_id", 123], ["practice_facility_review_id", 125], ["practice_facility_review_id", 126], ["practice_facility_review_id", 120], ["practice_facility_review_id", 122], ["practice_facility_review_id", 124], ["practice_facility_review_id", 127], ["practice_facility_review_id", 128], ["practice_facility_review_id", 129], ["practice_facility_review_id", 130], ["practice_facility_review_id", 131], ["practice_facility_review_id", 137], ["practice_facility_review_id", 169], ["practice_facility_review_id", 204], ["practice_facility_review_id", 206], ["practice_facility_review_id", 213], ["practice_facility_review_id", 228], ["practice_facility_review_id", 480], ["practice_facility_review_id", 239], ["practice_facility_review_id", 240], ["practice_facility_review_id", 241], ["practice_facility_review_id", 242], ["practice_facility_review_id", 243], ["practice_facility_review_id", 476], ["practice_facility_review_id", 274], ["practice_facility_review_id", 483], ["practice_facility_review_id", 489], ["practice_facility_review_id", 299], ["practice_facility_review_id", 300], ["practice_facility_review_id", 302], ["practice_facility_review_id", 356], ["practice_facility_review_id", 493], ["practice_facility_review_id", 494], ["practice_facility_review_id", 495], ["practice_facility_review_id", 496], ["practice_facility_review_id", 498], ["practice_facility_review_id", 499], ["practice_facility_review_id", 500], ["practice_facility_review_id", 503], ["practice_facility_review_id", 505], ["practice_facility_review_id", 506]]
 ↳ app/services/map_search_practice_facility_service.rb:22:in `call'

これに関しては、「レビューが投稿されたらTOP画像をデフォルトで作成する」という処理を追加しました。これにより、レビューのクエリと画像がない施設のクエリを毎回問い合わせなくなったので、さらに早くなりました。

# 施設に関するクエリ
PracticeFacility Load (5.2ms)  SELECT practice_facilities.*, distance FROM "practice_facilities" INNER JOIN (SELECT id, (
      6371 * acos(
          cos(radians(35.6414529))
          * cos(radians(latitude))
          * cos(radians(longitude) - radians(139.6980773))
          + sin(radians(35.6414529))
          * sin(radians(latitude))
      )) AS distance FROM practice_facilities) AS d ON practice_facilities.id = d.id WHERE (distance <= 20) ORDER BY "distance" ASC

# tagに関するクエリ
↳ app/services/map_search_practice_facility_service.rb:22:in `call'
PracticeFacilityTag Load (7.6ms)  SELECT "practice_facility_tags".* FROM "practice_facility_tags" WHERE "practice_facility_tags"."practice_facility_id" IN ($1, $2, ... $126, $127)  [["practice_facility_id", 225],... ["practice_facility_id", 214]]
↳ app/services/map_search_practice_facility_service.rb:22:in `call'
Tag Load (0.4ms)  SELECT "tags".* FROM "tags" WHERE "tags"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8)  [["id", 3], ["id", 4], ["id", 2], ["id", 1], ["id", 6], ["id", 7], ["id", 8], ["id", 5]]

 # 画像がある施設のクエリ
↳ app/services/map_search_practice_facility_service.rb:22:in `call'
PracticeFacilityReviewImage Load (0.6ms)  SELECT "practice_facility_review_images".* FROM "practice_facility_review_images" WHERE "practice_facility_review_images"."id" IN ($1, $2, $3)  [["id", 22], ["id", 3], ["id", 12]]

ちょっと早くなりました。

Completed 200 OK in 571ms (Views: 17.0ms | ActiveRecord: 32.2ms | Allocations: 175733)

終わりに

個人開発では、ちゃんとテスト・レビューせずにマージしてしまったりして、本番に出てからこういった初歩的なミスに気づいたり、設計に関する後悔に気づいたりしますね。新規機能開発もいいですが、既存の実装で怪しいところがないかチェックする日も定期的に確保していきたいと思いました。まだまだパフォーマンス改善できそうな項目があるので、頑張って調べたいと思います...。

以上です！お読みいただきありがとうございました。

よかったら覗いてみてください＼(^-^)／
https://golf-medley.com/

いいなと思ったら応援しよう！