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

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

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/
   seratch@github/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/
    seratch@github/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