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
770
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
いかにして不足・不整合なくデータ移行したか
tjmtmmnk
1
910
ACES Meet におけるリリース作業改善の取り組み
fukucheee
0
150
Kotlin Multiplatform at Stable and Beyond (Kotlin Vienna, October 2024)
zsmb
2
410
Vaporモードを大規模サービスに最速導入して学びを共有する
kazukishimamoto
3
2.1k
書籍『LangChainとLangGraphによるRAG・AIエージェント[実践]入門』の紹介
os1ma
2
120
GPU Hash Table | レイトレ合宿10
yknishidate
0
330
Програмиране с Rust, ФМИ, 2024
nikolads
0
120
個人開発で使ってるやつを紹介する回
yohfee
1
730
学生の時に開催したPerl入学式をきっかけにエンジニアが組織に馴染むために勉強会を主催や仲間と参加して職能間の境界を越えていく
ohmori_yusuke
2
330
CSC509 Lecture 06
javiergs
PRO
0
130
Memory API: Patterns, Use Cases, and Performance
josepaumard
2
200
offers_20241022_imakiire.pdf
imakurusu
1
130
Featured
See All Featured
Fantastic passwords and where to find them - at NoRuKo
philnash
50
2.8k
Pencils Down: Stop Designing & Start Developing
hursman
119
11k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
191
16k
RailsConf 2023
tenderlove
28
860
We Have a Design System, Now What?
morganepeng
50
7.2k
Large-scale JavaScript Application Architecture
addyosmani
510
110k
Product Roadmaps are Hard
iamctodd
PRO
48
10k
The Invisible Side of Design
smashingmag
297
50k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
228
52k
Intergalactic Javascript Robots from Outer Space
tanoku
268
27k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
92
16k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
131
33k
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ʹग़ͤΔɻ - ϦϑΝΫλָ͕͘͠ͳΔɻ
͓ΘΓ