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
850
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
Web Components で実現する Hotwire とフロントエンドフレームワークの橋渡し / Bridging with Web Components
da1chi
3
2k
詳しくない分野でのVibe Codingで困ったことと学び/vibe-coding-in-unfamiliar-area
shibayu36
3
4.8k
バッチ処理を「状態の記録」から「事実の記録」へ
panda728
PRO
0
140
Devoxx BE - Local Development in the AI Era
kdubois
0
110
CSC509 Lecture 03
javiergs
PRO
0
330
Advance Your Career with Open Source
ivargrimstad
0
450
Swift Concurrency - 状態監視の罠
objectiveaudio
2
490
After go func(): Goroutines Through a Beginner’s Eye
97vaibhav
0
310
iOSエンジニア向けの英語学習アプリを作る!
yukawashouhei
0
190
登壇は dynamic! な営みである / speech is dynamic
da1chi
0
230
Back to the Future: Let me tell you about the ACP protocol
terhechte
0
140
Go Conference 2025: Goで体感するMultipath TCP ― Go 1.24 時代の MPTCP Listener を理解する
takehaya
8
1.6k
Featured
See All Featured
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
285
14k
A better future with KSS
kneath
239
18k
Connecting the Dots Between Site Speed, User Experience & Your Business [WebExpo 2025]
tammyeverts
9
580
Git: the NoSQL Database
bkeepers
PRO
431
66k
Music & Morning Musume
bryan
46
6.8k
Fantastic passwords and where to find them - at NoRuKo
philnash
52
3.4k
Docker and Python
trallard
46
3.6k
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
358
30k
Designing for humans not robots
tammielis
254
26k
Fireside Chat
paigeccino
40
3.7k
The Straight Up "How To Draw Better" Workshop
denniskardys
237
140k
Building Flexible Design Systems
yeseniaperezcruz
329
39k
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ʹग़ͤΔɻ - ϦϑΝΫλָ͕͘͠ͳΔɻ
͓ΘΓ