Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
DBIx::TracerでN+1クエリを検知できるようにした
Search
return520
January 25, 2019
Programming
0
830
DBIx::TracerでN+1クエリを検知できるようにした
- DBIx::TracerでN+1クエリを検知できるようにした
- YAPC::Tokyo 2019 前夜祭 LTソン presented by 吉祥寺.pm
return520
January 25, 2019
Tweet
Share
Other Decks in Programming
See All in Programming
MDN Web Docs に日本語翻訳でコントリビュートしたくなる
ohmori_yusuke
1
130
Rubyでやりたい駆動開発 / Ruby driven development
chobishiba
1
740
Advanced Micro Frontends: Multi Version/ Framework Scenarios @WAD 2025, Berlin
manfredsteyer
PRO
0
290
MCPを使ってイベントソーシングのAIコーディングを効率化する / Streamlining Event Sourcing AI Coding with MCP
tomohisa
0
120
PHPでWebSocketサーバーを実装しよう2025
kubotak
0
290
Deep Dive into ~/.claude/projects
hiragram
14
2.6k
「テストは愚直&&網羅的に書くほどよい」という誤解 / Test Smarter, Not Harder
munetoshi
0
180
チームで開発し事業を加速するための"良い"設計の考え方 @ サポーターズCoLab 2025-07-08
agatan
1
440
Result型で“失敗”を型にするPHPコードの書き方
kajitack
5
930
20250628_非エンジニアがバイブコーディングしてみた
ponponmikankan
0
700
GPUを計算資源として使おう!
primenumber
1
170
たった 1 枚の PHP ファイルで実装する MCP サーバ / MCP Server with Vanilla PHP
okashoi
1
270
Featured
See All Featured
Building a Modern Day E-commerce SEO Strategy
aleyda
42
7.4k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
34
5.9k
Adopting Sorbet at Scale
ufuk
77
9.5k
Building Adaptive Systems
keathley
43
2.7k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
130
19k
GraphQLの誤解/rethinking-graphql
sonatard
71
11k
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.4k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.7k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
30
2.1k
A Modern Web Designer's Workflow
chriscoyier
695
190k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
48
2.9k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Transcript
DBIx::TracerͰN+1ΫΤϦΛݕ Ͱ͖ΔΑ͏ʹͨ͠ɻ by @return520
͘͡ - ؆୯ࣗݾհ - N+1ͱ - ࣮ - ࣮ -
݁Ռ - ·ͱΊ
؆୯ࣗݾհ
ࣗݾհ - େੲʹڝϓϩͬͯͨɻ - ύιίϯߕࢠԂͱ͔ɻ - U-20ϓϩίϯͰͱ͔Βͬͨɻ - ͜͜͠Β͘ɺޒాͰPerlॻ͍ͯ·͢ɻ -
͋ͱAWSͱ͔jsͱ͔ɻ @return520
ࣗݾհ - YAPCॳࢀՃͰ͢ɻ - PerlͰN+1Λ؆୯ʹݕ͢ΔΛ͠·͢ɻ @return520
N+1ͱ
N+1ͱ SQLͷΫΤϦ͕ͨ͘͞Μൃߦ͞Εͯɺ RDBMS͕ͭΒ͍ࢥ͍Λ͢Δ͜ͱɻ ʻͻ͌ʂ ʻͻ͌ʂ
ͭΒ͍ͷͬͯΔ
ͰੜΈग़ͯ͠͠·͏
ੜΈग़͞ΕΔN+1 - ॳ৺ऀΒͣʹੜΈग़ͯ͠͠·͏ɻ - ͬͯͯؾ͔ͮͣੜΈग़ͯ͠͠·͏ɻ - ͦͦͳΜ͔͋ͬͨɻ
࣮... <ॿ͚ͯʂ
࣮̍ - ৽ػೳϦϦʔεͨ͠ΒN+1ͰΞϓϦ͕ܹॏʹͳͬͨ - ϦϦʔεͨ͠ॠؒɺRDS͕ߴෛՙ - ͙͢ʹrevert - Α͘ݟͨΒN+1Ͱselect͕80ճ͞ΕͯΔ
࣮̎ - ͦͦͳΜ͔͋Δ - ಈ͍ͯΔ͚Ͳ์ஔ͞ΕͯΔAPI - લʹ࡞ΒΕͯ࡞ͬͨਓ͕͍ͳ͍ - ίʔυ༷ෳࡶͰख͕ग़ͮ͠Β͍
ݟ͚ͭͯमਖ਼ͯ͠Δ
ݟ͚ͭͯमਖ਼͍ͯ͠Δ - slow_query_logͰॏ͍ͷ࣏͢ɻ - apiͷܭଌͯ͠ɺ͍apiΛվળ͢Δɻ - ॲཧ·Δ·Δॻ͖͢ɻ
ݟ͚ͭͯमਖ਼ͯ͠Δ͚Ͳ ະવʹ͍͗ͨ
ͬͱָʹϘτϧωοΫΛ ݟ͚͍ͭͨ
ݕͰ͖ΔΑ͏ʹ͠Α͏ʂ
ࠓճͷΞʔΩςΫνϟ - Perl5 - Amon2 - ORM Aniki + α
- AWS (EC2 + Aurora)
ݱঢ়ཧ - ORMΛ༻͍ͯ͠ΔͷͰɺqueryͷΒ͖ͭଟແ͍ɻ - SELECT͕େจࣈͩͬͨΓখจࣈͩͬͨΓ͠ͳ͍ͣɻ - ΄΅΄΅ಉ͡query͕࣮ߦ͞ΕΔͣɻ => data bind͞Ε͍ͯΔ෦Λ͍͍ײ͡ʹڞ௨ԽͰ͖ΕಉҰqueryͷ࣮ߦΛ
ݕͰ͖ͦ͏ɻ
ΞΠσΟΞ - API͕ୟ͔ΕΔʢAmon2ͷBEFORE_DISPATCHʣ - SQLͷ࣮ߦΛϑοΫ͢Δɻ - ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ - ಉҰqueryΛcount͢Δ -
count͕ҰఆΛ͑ͨΒɺlogʹు͖ग़͢ɻ - ॲཧऴྃʢAmon2ͷAFTER_DISPATCHʣ
SQLͷ࣮ߦΛϑοΫ͢Δ
SQLͷ࣮ߦΛϑοΫ͢Δɻ - DBIx::Tracer͕͋Δ͡Όͳ͍͔ʂʂ - https://metacpan.org/pod/DBIx::Tracer
SQLͷ࣮ߦΛϑοΫ͢Δɻ __PACKAGE__->add_trigger( BEFORE_DISPATCH => sub { my ($c) = @_;
$c->{__dbix_tracer} = DBIx::Tracer->new(sub { my %args = @_; my $sql = $args{sql}; # ࣮ߦ͞ΕΔSQL }); } )
ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
ϑοΫͨ͠SQL͜Μͳײͩͬͨ͡ SELECT `id`, `fuga_id`, `read_fg`, `created_at`, `updated_at` FROM `hoge` WHERE
(`read_fg` = ?) AND (`fuga_id` = ?) վߦফ͚ͩ͢Ͱେৎͦ͏ɻ ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
sub normaliz_sql{ my ($sql) = shift; $sql =~ s/\r?\n?//g; return
$sql; } ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
ಉҰqueryΛcount͢Δ
ಉҰqueryΛcount͢Δ my %query_count; sub count_query { my ($sql) = shift;
$query_count{$sql}++; }
count͕ҰఆΛ͑ͨΒɺlogʹు͖ग़͢ɻ
count͕ҰఆΛ͑ͨΒɺlogʹు͖ग़͢ɻ sub check_n1_query { my ($class, $query_count) = @_;
foreach my $sql (keys %$query_count) { my $count = $query_count{$sql}; warn “warning n+1 query count:[$count] SQL:[$sql]” if ($query_count >= 5); } }
؆୯ʹͰ͖·ͨ͠ɻ
͋Γ͕ͱ͏ʂʂ DBIx::Tracerʂʂ
͜ͷ͋ͱ໓ۤ ϦϑΝΫλͨ͠
ϦϑΝΫλ݁Ռ લʢඵʣ ޙʢඵʣ ճ લʢ࣌ؒʣ ޙʢ࣌ؒʣ ࠩʢ࣌ؒʣ 1.3 0.7 2504868
904.54 487.06 417.48 0.8 0.1 597798 132.84 16.61 116.23 0.8 0.4 1406491 312.55 156.28 156.27 0.45 0.2 5547284 693.41 308.18 385.23 0.2 0.09 1605089 89.17 40.13 49.04 0.2 0.1 8369158 464.95 232.48 232.47 etc...
ϦϑΝΫλ݁Ռ Ұͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻ ʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔ
ϦϑΝΫλ݁Ռ Ұͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻ ʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔ ϦϑΝΫλָ͍͠ʂʂʂ
·ͱΊ
·ͱΊ - DBIx::TracerΛ͑؆୯ʹ࡞ΕΔɻ - ϦϑΝΫλָ͕͘͠ͳΔɻ
͓·͚ - caller ͏ͱݺͼग़͠ݩlogʹग़ͤΔɻ - ϦϑΝΫλָ͕͘͠ͳΔɻ
͓ΘΓ