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
Azure AI Foundryではじめてのマルチエージェントワークフロー
seosoft
0
150
新メンバーも今日から大活躍!SREが支えるスケールし続ける組織のオンボーディング
honmarkhunt
3
4k
deno-redisの紹介とJSRパッケージの運用について (toranoana.deno #21)
uki00a
0
180
NPOでのDevinの活用
codeforeveryone
0
740
CursorはMCPを使った方が良いぞ
taigakono
1
220
Team operations that are not burdened by SRE
kazatohiei
1
300
Blazing Fast UI Development with Compose Hot Reload (droidcon New York 2025)
zsmb
1
280
既存デザインを変更せずにタップ領域を広げる方法
tahia910
1
270
なぜ適用するか、移行して理解するClean Architecture 〜構造を超えて設計を継承する〜 / Why Apply, Migrate and Understand Clean Architecture - Inherit Design Beyond Structure
seike460
PRO
3
740
ふつうの技術スタックでアート作品を作ってみる
akira888
0
400
VS Code Update for GitHub Copilot
74th
2
600
20250704_教育事業におけるアジャイルなデータ基盤構築
hanon52_
5
560
Featured
See All Featured
Large-scale JavaScript Application Architecture
addyosmani
512
110k
Raft: Consensus for Rubyists
vanstee
140
7k
The Language of Interfaces
destraynor
158
25k
Navigating Team Friction
lara
187
15k
Scaling GitHub
holman
459
140k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
20
1.3k
Measuring & Analyzing Core Web Vitals
bluesmoon
7
500
The Invisible Side of Design
smashingmag
301
51k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.7k
Making the Leap to Tech Lead
cromwellryan
134
9.4k
Optimising Largest Contentful Paint
csswizardry
37
3.3k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
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ʹग़ͤΔɻ - ϦϑΝΫλָ͕͘͠ͳΔɻ
͓ΘΓ