Slide 1
Slide 1 text
DBIx::TracerͰN+1ΫΤϦΛݕ
Ͱ͖ΔΑ͏ʹͨ͠ɻ
by @return520
Slide 2
Slide 2 text
͘͡
- ؆୯ࣗݾհ
- N+1ͱ
- ࣮
- ࣮
- ݁Ռ
- ·ͱΊ
Slide 3
Slide 3 text
؆୯ࣗݾհ
Slide 4
Slide 4 text
ࣗݾհ
- େੲʹڝϓϩͬͯͨɻ
- ύιίϯߕࢠԂͱ͔ɻ
- U-20ϓϩίϯͰͱ͔Βͬͨɻ
- ͜͜͠Β͘ɺޒాͰPerlॻ͍ͯ·͢ɻ
- ͋ͱAWSͱ͔jsͱ͔ɻ
@return520
Slide 5
Slide 5 text
ࣗݾհ
- YAPCॳࢀՃͰ͢ɻ
- PerlͰN+1Λ؆୯ʹݕ͢ΔΛ͠·͢ɻ
@return520
Slide 6
Slide 6 text
N+1ͱ
Slide 7
Slide 7 text
N+1ͱ
SQLͷΫΤϦ͕ͨ͘͞Μൃߦ͞Εͯɺ
RDBMS͕ͭΒ͍ࢥ͍Λ͢Δ͜ͱɻ
ʻͻ͌ʂ
ʻͻ͌ʂ
Slide 8
Slide 8 text
ͭΒ͍ͷͬͯΔ
Slide 9
Slide 9 text
ͰੜΈग़ͯ͠͠·͏
Slide 10
Slide 10 text
ੜΈग़͞ΕΔN+1
- ॳ৺ऀΒͣʹੜΈग़ͯ͠͠·͏ɻ
- ͬͯͯؾ͔ͮͣੜΈग़ͯ͠͠·͏ɻ
- ͦͦͳΜ͔͋ͬͨɻ
Slide 11
Slide 11 text
࣮...
<ॿ͚ͯʂ
Slide 12
Slide 12 text
࣮̍
- ৽ػೳϦϦʔεͨ͠ΒN+1ͰΞϓϦ͕ܹॏʹͳͬͨ
- ϦϦʔεͨ͠ॠؒɺRDS͕ߴෛՙ
- ͙͢ʹrevert
- Α͘ݟͨΒN+1Ͱselect͕80ճ͞ΕͯΔ
Slide 13
Slide 13 text
࣮̎
- ͦͦͳΜ͔͋Δ
- ಈ͍ͯΔ͚Ͳ์ஔ͞ΕͯΔAPI
- લʹ࡞ΒΕͯ࡞ͬͨਓ͕͍ͳ͍
- ίʔυ༷ෳࡶͰख͕ग़ͮ͠Β͍
Slide 14
Slide 14 text
ݟ͚ͭͯमਖ਼ͯ͠Δ
Slide 15
Slide 15 text
ݟ͚ͭͯमਖ਼͍ͯ͠Δ
- slow_query_logͰॏ͍ͷ࣏͢ɻ
- apiͷܭଌͯ͠ɺ͍apiΛվળ͢Δɻ
- ॲཧ·Δ·Δॻ͖͢ɻ
Slide 16
Slide 16 text
ݟ͚ͭͯमਖ਼ͯ͠Δ͚Ͳ
ະવʹ͍͗ͨ
Slide 17
Slide 17 text
ͬͱָʹϘτϧωοΫΛ
ݟ͚͍ͭͨ
Slide 18
Slide 18 text
ݕͰ͖ΔΑ͏ʹ͠Α͏ʂ
Slide 19
Slide 19 text
ࠓճͷΞʔΩςΫνϟ
- Perl5
- Amon2
- ORM Aniki + α
- AWS (EC2 + Aurora)
Slide 20
Slide 20 text
ݱঢ়ཧ
- ORMΛ༻͍ͯ͠ΔͷͰɺqueryͷΒ͖ͭଟແ͍ɻ
- SELECT͕େจࣈͩͬͨΓখจࣈͩͬͨΓ͠ͳ͍ͣɻ
- ΄΅΄΅ಉ͡query͕࣮ߦ͞ΕΔͣɻ
=> data bind͞Ε͍ͯΔ෦Λ͍͍ײ͡ʹڞ௨ԽͰ͖ΕಉҰqueryͷ࣮ߦΛ
ݕͰ͖ͦ͏ɻ
Slide 21
Slide 21 text
ΞΠσΟΞ
- API͕ୟ͔ΕΔʢAmon2ͷBEFORE_DISPATCHʣ
- SQLͷ࣮ߦΛϑοΫ͢Δɻ
- ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
- ಉҰqueryΛcount͢Δ
- count͕ҰఆΛ͑ͨΒɺlogʹు͖ग़͢ɻ
- ॲཧऴྃʢAmon2ͷAFTER_DISPATCHʣ
Slide 22
Slide 22 text
SQLͷ࣮ߦΛϑοΫ͢Δ
Slide 23
Slide 23 text
SQLͷ࣮ߦΛϑοΫ͢Δɻ
- DBIx::Tracer͕͋Δ͡Όͳ͍͔ʂʂ
- https://metacpan.org/pod/DBIx::Tracer
Slide 24
Slide 24 text
SQLͷ࣮ߦΛϑοΫ͢Δɻ
__PACKAGE__->add_trigger(
BEFORE_DISPATCH => sub {
my ($c) = @_;
$c->{__dbix_tracer} = DBIx::Tracer->new(sub {
my %args = @_;
my $sql = $args{sql}; # ࣮ߦ͞ΕΔSQL
});
}
)
Slide 25
Slide 25 text
ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
Slide 26
Slide 26 text
ϑοΫͨ͠SQL͜Μͳײͩͬͨ͡
SELECT `id`, `fuga_id`, `read_fg`, `created_at`, `updated_at`
FROM `hoge`
WHERE (`read_fg` = ?) AND (`fuga_id` = ?)
վߦফ͚ͩ͢Ͱେৎͦ͏ɻ
ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
Slide 27
Slide 27 text
sub normaliz_sql{
my ($sql) = shift;
$sql =~ s/\r?\n?//g;
return $sql;
}
ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
Slide 28
Slide 28 text
ಉҰqueryΛcount͢Δ
Slide 29
Slide 29 text
ಉҰqueryΛcount͢Δ
my %query_count;
sub count_query {
my ($sql) = shift;
$query_count{$sql}++;
}
Slide 30
Slide 30 text
count͕ҰఆΛ͑ͨΒɺlogʹు͖ग़͢ɻ
Slide 31
Slide 31 text
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);
}
}
Slide 32
Slide 32 text
؆୯ʹͰ͖·ͨ͠ɻ
Slide 33
Slide 33 text
͋Γ͕ͱ͏ʂʂ
DBIx::Tracerʂʂ
Slide 34
Slide 34 text
͜ͷ͋ͱ໓ۤ
ϦϑΝΫλͨ͠
Slide 35
Slide 35 text
ϦϑΝΫλ݁Ռ
લʢඵʣ ޙʢඵʣ ճ લʢ࣌ؒʣ ޙʢ࣌ؒʣ ࠩʢ࣌ؒʣ
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...
Slide 36
Slide 36 text
ϦϑΝΫλ݁Ռ
Ұͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻ
ʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔ
Slide 37
Slide 37 text
ϦϑΝΫλ݁Ռ
Ұͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻ
ʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔ
ϦϑΝΫλָ͍͠ʂʂʂ
Slide 38
Slide 38 text
·ͱΊ
Slide 39
Slide 39 text
·ͱΊ
- DBIx::TracerΛ͑؆୯ʹ࡞ΕΔɻ
- ϦϑΝΫλָ͕͘͠ͳΔɻ
Slide 40
Slide 40 text
͓·͚
- caller ͏ͱݺͼग़͠ݩlogʹग़ͤΔɻ
- ϦϑΝΫλָ͕͘͠ͳΔɻ
Slide 41
Slide 41 text
͓ΘΓ