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

ActiveRecord::PostgreSQLAnalyzer gem を作った話 #m3dev

ActiveRecord::PostgreSQLAnalyzer gem を作った話 #m3dev

M3 Tech Talk で「ActiveRecord::PostgreSQLAnalyzer gem を作った話」というタイトルで LT したときの資料です。
#m3dev

Takayuki Matsubara

December 22, 2015
Tweet

More Decks by Takayuki Matsubara

Other Decks in Programming

Transcript

  1. ActiveRecord::Postgr
    eSQLAnalyzer
    gem Λ࡞ͬͨ࿩ !

    View Slide

  2. ࣗݾ঺հ

    View Slide

  3. TODO: !

    View Slide


  4. github.com/ma2gedev/
    power_assert_ex

    View Slide

  5. ActiveRecord::PostgreSQLAnalyzer
    https://github.com/m3dev/
    active_record-
    postgresql_analyzer

    View Slide

  6. ͖͔͚ͬ

    View Slide

  7. m3 advent calendar

    View Slide

  8. SAStruts + S2JDBC ͷࢥ͍ग़ʹ͍ͭͯ
    ଘ෼ʹޠΔ #m3dev
    — @seratch
    http://qiita.com/
    [email protected]/items/
    826f1b4a89993d3b0804

    View Slide

  9. Sequential Scan ݕ

    View Slide

  10. ศརͦ͏
    ͪͳΈʹ͜ͷΞΠσΞࣗମ͸ S2JDBC
    ʹݶఆ͞Ε·ͤΜɻࠓ೥΍ͬͨϓϩδ
    ΣΫτͰ͸ Scala ͷΞϓϦέʔγϣϯ
    Ͱ΋ಉ͡Α͏ͳίʔυΛॻ͍ͨͷͰ͢
    ͕ɺඇৗʹ༗༻Ͱͨ͠ɻ
    — @seratch

    View Slide

  11. Ruby(RoR)͸ʁ

    View Slide

  12. ͳ͚Ε͹࡞Ζ
    ͏ OSS

    View Slide

  13. ActiveRecord::PostgreSQLAnalyzer
    https://github.com/m3dev/
    active_record-
    postgresql_analyzer

    View Slide

  14. Rails ͰγʔέϯγϟϧεΩ
    ϟϯ͍ͯ͠ΔΫΤϦΛݟ
    ͚ͭͯḿΓ͍ͨ - PostgreSQL ݶ

    http://qiita.com/ma2ge/
    items/0df91b0fbd90808f40d5

    View Slide

  15. όζͬ
    ͨʂʁ

    View Slide

  16. όζͬͯͳ
    ͍

    View Slide

  17. ͩ͞·͞͠ऑऀ͗ͯ͢ࠔͬͨͷͰ
    Scala ͰίϯύΠϧ͢Δ͜ͱʹͨ͠࿩
    — @seratch
    http://qiita.com/
    [email protected]/items/
    3be26b584f3d74aa1150

    View Slide

  18. ࢖͍ํ

    View Slide

  19. Gemfile
    gem 'active_record-postgresql_analyzer', group: :development

    View Slide

  20. Output logfile if sequential scan is detected
    ------------ find Seq Scan query ------------
    SELECT "todo".* FROM "todo" WHERE "todo"."user_id" = $1 LIMIT 1
    QUERY PLAN
    ---------------------------------------------------------------------------------------------
    Limit (cost=10000000000.00..10000000001.67 rows=1 width=81)
    -> Seq Scan on todo (cost=10000000000.00..10000000001.67 rows=1 width=81)
    Filter: (user_id = 13)
    (3 rows)

    View Slide

  21. ࢓૊Έ

    View Slide

  22. ActiveSupport::Notifications
    ActiveRecord::PostgreSQLAnalyzer::LogSubscriber.attach_to :active_record
    https://github.com/m3dev/
    active_record-postgresql_analyzer/
    blob/master/lib/active_record/
    postgresql_analyzer.rb#L42

    View Slide

  23. Explain
    ActiveRecord::Base.connection.explain(payload[:sql], payload[:binds])
    https://github.com/m3dev/
    active_record-postgresql_analyzer/
    blob/master/lib/active_record/
    postgresql_analyzer.rb#L22

    View Slide

  24. Detect Sequential Scan
    SELECT "todo".* FROM "todo" WHERE "todo"."user_id" = $1 LIMIT 1
    QUERY PLAN
    ---------------------------------------------------------------------------------------------
    Limit (cost=10000000000.00..10000000001.67 rows=1 width=81)
    -> Seq Scan on todo (cost=10000000000.00..10000000001.67 rows=1 width=81)
    Filter: (user_id = 13)
    (3 rows)
    https://github.com/m3dev/
    active_record-postgresql_analyzer/
    blob/master/lib/active_record/
    postgresql_analyzer.rb#L23

    View Slide

  25. index షΓ๨Ε๷

    View Slide

  26. ActiveRecord::PostgreSQLAnalyzer
    https://github.com/m3dev/
    active_record-
    postgresql_analyzer

    View Slide


  27. github.com/ma2gedev/
    power_assert_ex

    View Slide

  28. end

    View Slide