Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DBIx::TracerでN+1クエリを検知できるようにした

return520
January 25, 2019

 DBIx::TracerでN+1クエリを検知できるようにした

- DBIx::TracerでN+1クエリを検知できるようにした
- YAPC::Tokyo 2019 前夜祭 LTソン presented by 吉祥寺.pm

return520

January 25, 2019
Tweet

Other Decks in Programming

Transcript

  1. DBIx::TracerͰN+1ΫΤϦΛݕ
    ஌Ͱ͖ΔΑ͏ʹͨ͠ɻ
    by @return520

    View Slide

  2. ΋͘͡
    - ؆୯ࣗݾ঺հ
    - N+1ͱ͸
    - ࣮࿩
    - ࣮૷
    - ݁Ռ
    - ·ͱΊ

    View Slide

  3. ؆୯ࣗݾ঺հ

    View Slide

  4. ࣗݾ঺հ
    - େੲʹڝϓϩ΍ͬͯͨɻ
    - ύιίϯߕࢠԂͱ͔ɻ
    - U-20ϓϩίϯͰ৆ͱ͔΋Βͬͨɻ
    - ͜͜͠͹Β͘͸ɺޒ൓ాͰPerlॻ͍ͯ·͢ɻ
    - ͋ͱAWSͱ͔jsͱ͔΋ɻ
    @return520

    View Slide

  5. ࣗݾ঺հ
    - YAPCॳࢀՃͰ͢ɻ
    - PerlͰN+1Λ؆୯ʹݕ஌͢Δ࿩Λ͠·͢ɻ
    @return520

    View Slide

  6. N+1ͱ͸

    View Slide

  7. N+1ͱ͸
    SQLͷΫΤϦ͕ͨ͘͞Μൃߦ͞Εͯɺ
    RDBMS͕ͭΒ͍ࢥ͍Λ͢Δ͜ͱɻ
    ʻͻ͌ʂ
    ʻͻ͌ʂ

    View Slide

  8. ͭΒ͍ͷ͸஌ͬͯΔ

    View Slide

  9. Ͱ΋ੜΈग़ͯ͠͠·͏

    View Slide

  10. ੜΈग़͞ΕΔN+1
    - ॳ৺ऀ͸஌ΒͣʹੜΈग़ͯ͠͠·͏ɻ
    - ஌ͬͯͯ΋ؾ͔ͮͣੜΈग़ͯ͠͠·͏ɻ
    - ͦ΋ͦ΋ͳΜ͔͋ͬͨɻ

    View Slide

  11. ࣮࿩...

    View Slide

  12. ࣮࿩̍
    - ৽ػೳϦϦʔεͨ͠ΒN+1ͰΞϓϦ͕ܹॏʹͳͬͨ
    - ϦϦʔεͨ͠ॠؒɺRDS͕ߴෛՙ
    - ͙͢ʹrevert
    - Α͘ݟͨΒN+1Ͱselect͕80ճ͞ΕͯΔ

    View Slide

  13. ࣮࿩̎
    - ͦ΋ͦ΋ͳΜ͔͋Δ
    - ಈ͍ͯΔ͚Ͳ์ஔ͞ΕͯΔAPI
    - ਺೥લʹ࡞ΒΕͯ࡞ͬͨਓ͕͍ͳ͍
    - ίʔυ΋࢓༷΋ෳࡶͰख͕ग़ͮ͠Β͍

    View Slide

  14. ݟ͚ͭͯ͸मਖ਼ͯ͠Δ

    View Slide

  15. ݟ͚ͭͯ͸मਖ਼͍ͯ͠Δ
    - slow_query_logͰॏ͍ͷ࣏͢ɻ
    - apiͷ଎౓ܭଌͯ͠ɺ஗͍apiΛվળ͢Δɻ
    - ॲཧ·Δ·Δॻ͖௚͢ɻ

    View Slide

  16. ݟ͚ͭͯ͸मਖ਼ͯ͠Δ͚Ͳ
    ະવʹ๷͍͗ͨ

    View Slide

  17. ΋ͬͱָʹϘτϧωοΫΛ
    ݟ͚͍ͭͨ

    View Slide

  18. ݕ஌Ͱ͖ΔΑ͏ʹ͠Α͏ʂ

    View Slide

  19. ࠓճͷΞʔΩςΫνϟ
    - Perl5
    - Amon2
    - ORM Aniki + α
    - AWS (EC2 + Aurora)

    View Slide

  20. ݱঢ়੔ཧ
    - ORMΛ࢖༻͍ͯ͠ΔͷͰɺqueryͷ͹Β͖ͭ͸ଟ෼ແ͍ɻ
    - SELECT͕େจࣈͩͬͨΓখจࣈͩͬͨΓ͸͠ͳ͍͸ͣɻ
    - ΄΅΄΅ಉ͡query͕࣮ߦ͞ΕΔ͸ͣɻ
    => data bind͞Ε͍ͯΔ෦෼Λ͍͍ײ͡ʹڞ௨ԽͰ͖Ε͹ಉҰqueryͷ࣮ߦΛ
    ݕ஌Ͱ͖ͦ͏ɻ

    View Slide

  21. ΞΠσΟΞ
    - API͕ୟ͔ΕΔʢAmon2ͷBEFORE_DISPATCHʣ
    - SQLͷ࣮ߦΛϑοΫ͢Δɻ
    - ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ
    - ಉҰqueryΛcount͢Δ
    - count͕Ұఆ਺Λ௒͑ͨΒɺlogʹు͖ग़͢ɻ
    - ॲཧऴྃʢAmon2ͷAFTER_DISPATCHʣ

    View Slide

  22. SQLͷ࣮ߦΛϑοΫ͢Δ

    View Slide

  23. SQLͷ࣮ߦΛϑοΫ͢Δɻ
    - DBIx::Tracer͕͋Δ͡Όͳ͍͔ʂʂ
    - https://metacpan.org/pod/DBIx::Tracer

    View Slide

  24. SQLͷ࣮ߦΛϑοΫ͢Δɻ
    __PACKAGE__->add_trigger(
    BEFORE_DISPATCH => sub {
    my ($c) = @_;
    $c->{__dbix_tracer} = DBIx::Tracer->new(sub {
    my %args = @_;
    my $sql = $args{sql}; # ࣮ߦ͞ΕΔSQL
    });
    }
    )

    View Slide

  25. ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ

    View Slide

  26. ϑοΫͨ͠SQL͸͜Μͳײͩͬͨ͡
    SELECT `id`, `fuga_id`, `read_fg`, `created_at`, `updated_at`
    FROM `hoge`
    WHERE (`read_fg` = ?) AND (`fuga_id` = ?)
    վߦফ͚ͩ͢Ͱେৎ෉ͦ͏ɻ
    ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ

    View Slide

  27. sub normaliz_sql{
    my ($sql) = shift;
    $sql =~ s/\r?\n?//g;
    return $sql;
    }
    ྲྀΕ͍ͯΔSQLΛਖ਼نԽ͢Δ

    View Slide

  28. ಉҰqueryΛcount͢Δ

    View Slide

  29. ಉҰqueryΛcount͢Δ

    my %query_count;
    sub count_query {
    my ($sql) = shift;
    $query_count{$sql}++;
    }

    View Slide

  30. count͕Ұఆ਺Λ௒͑ͨΒɺlogʹు͖ग़͢ɻ


    View Slide

  31. 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);
    }
    }

    View Slide

  32. ؆୯ʹͰ͖·ͨ͠ɻ

    View Slide

  33. ͋Γ͕ͱ͏ʂʂ
    DBIx::Tracerʂʂ

    View Slide

  34. ͜ͷ͋ͱ໓஡ۤ஡
    ϦϑΝΫλͨ͠

    View Slide

  35. ϦϑΝΫλ݁Ռ
    લʢඵʣ ޙʢඵʣ ճ਺ લʢ࣌ؒʣ ޙʢ࣌ؒʣ ࠩʢ࣌ؒʣ
    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...

    View Slide

  36. ϦϑΝΫλ݁Ռ
    Ұ೔ͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53೔ʣݮͬͨɻ
    ʢଞʹ΋΍ͬͯΔͷͰɺ΋ͬͱ΋ͬͱݮͬͯΔ

    View Slide

  37. ϦϑΝΫλ݁Ռ
    Ұ೔ͷॲཧ͕࣌ؒ 1356.72࣌ؒʢ56.53೔ʣݮͬͨɻ
    ʢଞʹ΋΍ͬͯΔͷͰɺ΋ͬͱ΋ͬͱݮͬͯΔ
    ϦϑΝΫλָ͍͠ʂʂʂ

    View Slide

  38. ·ͱΊ

    View Slide

  39. ·ͱΊ
    - DBIx::TracerΛ࢖͑͹؆୯ʹ࡞ΕΔɻ
    - ϦϑΝΫλָ͕͘͠ͳΔɻ

    View Slide

  40. ͓·͚
    - caller ࢖͏ͱݺͼग़͠ݩ΋logʹग़ͤΔɻ
    - ϦϑΝΫλָ͕͘͠ͳΔɻ

    View Slide

  41. ͓ΘΓ

    View Slide