- DBIx::TracerでN+1クエリを検知できるようにした - YAPC::Tokyo 2019 前夜祭 LTソン presented by 吉祥寺.pm
DBIx::TracerͰN+1ΫΤϦΛݕͰ͖ΔΑ͏ʹͨ͠ɻby @return520
View Slide
͘͡- ؆୯ࣗݾհ- 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.480.8 0.1 597798 132.84 16.61 116.230.8 0.4 1406491 312.55 156.28 156.270.45 0.2 5547284 693.41 308.18 385.230.2 0.09 1605089 89.17 40.13 49.040.2 0.1 8369158 464.95 232.48 232.47etc...
ϦϑΝΫλ݁ՌҰͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔ
ϦϑΝΫλ݁ՌҰͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53ʣݮͬͨɻʢଞʹͬͯΔͷͰɺͬͱͬͱݮͬͯΔϦϑΝΫλָ͍͠ʂʂʂ
·ͱΊ
·ͱΊ- DBIx::TracerΛ͑؆୯ʹ࡞ΕΔɻ- ϦϑΝΫλָ͕͘͠ͳΔɻ
͓·͚- caller ͏ͱݺͼग़͠ݩlogʹग़ͤΔɻ- ϦϑΝΫλָ͕͘͠ͳΔɻ
͓ΘΓ