Using Skylight to Solve Real-World Performance Problems

Using Skylight to Solve Real-World Performance Problems

22bb3e56828870ee9a0dd93aeadbe04a?s=128

Godfrey Chan

April 18, 2018
Tweet

Transcript

  1. 1.
  2. 2.
  3. 4.
  4. 5.

    An Open Source Event Manager tailored to
 Free and Open

    Source Software conferences. openSUSE/osem
  5. 6.
  6. 7.
  7. 8.
  8. 9.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD
  9. 10.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD Not a real response time distribution.
  10. 11.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD Average! Not a real response time distribution.
  11. 12.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD 95% requests Not a real response time distribution.
  12. 13.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD
  13. 14.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD Average…?
  14. 15.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD 50% requests
  15. 16.

    A B C D E F G H I J

    K L M N O P Q R S T U V W X Y Z AA BB CC DD 95% requests
  16. 17.
  17. 18.
  18. 21.

    <% @posts.each do |post| %> <!-- ... --> <h2>By <%=

    post.author.name %></h2> <!-- ... --> <% end %>
  19. 22.

    <% @posts.each do |post| %> <!-- ... --> <h2>By <%=

    post.author.name %></h2> <!-- ... --> <% end %> @posts post.author SELECT * FROM authors WHERE id = 13; SELECT * FROM authors WHERE id = 15; SELECT * FROM authors WHERE id = 21; SELECT * FROM authors WHERE id = 33; SELECT * FROM authors WHERE id = 37; SELECT * FROM posts LIMIT 10;
  20. 23.

    class PostsController < ApplicationController def index @posts = Post.limit(10) end

    end @posts = Post.limit(10).includes(:author) SELECT * FROM posts LIMIT 10; SELECT * FROM authors WHERE id IN (13, 15, 21, 33, 37, 42, 46, 47, 53, 55);
  21. 24.
  22. 26.
  23. 29.

    PERCENTAGE = COURSES COMPLETED ⨉ 100% LESSONS AVAILABLE LESSONS COMPLETED

    SELECT * FROM lessons WHERE course_id = 1; SELECT * FROM lesson_completions WHERE student_id = 13;
  24. 30.

    SELECT * FROM lesson_completions WHERE student_id = 13; create_table "lesson_completions"

    do |t| t.integer "lesson_id" t.integer "student_id" t.t.timestamps t.index ["lesson_id", "student_id"], unique: true, using: :btree end PERCENTAGE = COURSES COMPLETED ⨉ 100% LESSONS AVAILABLE LESSONS COMPLETED SELECT * FROM lessons WHERE course_id = 1;
  25. 31.

    SELECT * FROM lesson_completions WHERE student_id = 13; create_table "lesson_completions"

    do |t| t.integer "lesson_id" t.integer "student_id" t.t.timestamps t.index ["lesson_id", "student_id"], unique: true, using: :btree end
  26. 32.
  27. 34.

    LessonCompletion Load (57.2ms) SELECT "lesson_completions".* FROM ... => EXPLAIN for:

    SELECT "lesson_completions".* FROM "lesson_completions" ... ----------------------------------------------------------------------- Seq Scan on lesson_completions (cost=0.00..4063.50 rows=49 width=28) Filter: (student_id = 3074) (2 rows)
  28. 35.

    LessonCompletion Load (57.2ms) SELECT "lesson_completions".* FROM ... => EXPLAIN for:

    SELECT "lesson_completions".* FROM "lesson_completions" ... ----------------------------------------------------------------------- Seq Scan on lesson_completions (cost=0.00..4063.50 rows=49 width=28) Filter: (student_id = 3074) (2 rows) 57.2ms Seq Scan on lesson_completions
  29. 36.

    LessonCompletion Load (9.8ms) SELECT "lesson_completions".* FROM ... => EXPLAIN for:

    SELECT "lesson_completions".* FROM "lesson_completions" ... ----------------------------------------------------------------------- Index Scan using index_lesson_completions_on_lesson_id_and_student_id ... Filter: (student_id = 3074) (2 rows) 9.8ms 
 Index Scan using index_lesson_completions_on_lesson_id_and_student_id

  30. 37.

    LessonCompletion Load (1.6ms) SELECT "lesson_completions".* FROM ... => EXPLAIN for:

    SELECT "lesson_completions".* FROM "lesson_completions" ... ----------------------------------------------------------------------- Index Scan using index_lesson_completions_on_student_id on ... Filter: (student_id = 3074) (2 rows) 1.6ms Index Scan using index_lesson_completions_on_student_id
  31. 38.

    SELECT * FROM lesson_completions WHERE ... lesson_id = ? student_id

    = ? lesson_id = ? AND
 student_id = ? student_id = ? AND
 lesson_id = ? Dedicated Indexes (lesson_id) (student_id) Compound Index (lesson_id, student_id) Compound Index (student_id, lesson_id) ! ! ! ! ! ! ! " " ! ! !
  32. 39.
  33. 40.
  34. 41.
  35. 42.

    Help out your favorite open source projects and become a

    better developer while doing it. codetriage/codetriage
  36. 43.
  37. 44.
  38. 45.
  39. 46.
  40. 47.
  41. 48.
  42. 49.
  43. 50.

    <% @repos.each do |repo| %> <% cache repo do %>

    <!-- ... --> <% end %> <% end %>
  44. 51.

    <% @repos.each do |repo| %> <% cache repo do %>

    <!-- ... --> <% end %> <% end %> <% cache repo do %> READ views/repos/1894-20180413141703167092/b20 READ views/repos/1245-20180413141214948480/b20 READ views/repos/3353-20180413142449772302/b20 READ views/repos/987-20180413140921805080/b201 READ views/repos/1240-20180413141121053193/b20 READ views/repos/2193-20180413141853545411/b20 READ views/repos/1055-20180413141006040871/b20 READ views/repos/1332-20180413141232839666/b20
  45. 52.

    <%= render partial: 'repo', collection: @repos, cached: true %> READ

    MULTI views/repos/1894-2018041 views/repos/1245-2018041 views/repos/3353-2018041 views/repos/987-20180413 views/repos/1240-2018041 views/repos/2193-2018041 views/repos/1055-2018041 views/repos/1332-2018041 views/repos/1258-2018041
  46. 53.
  47. 54.

    Cache write: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/863-20180413140900779427/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/386-20180413140542430556/b201d971de1a77b9f19a62934e479a96 Cache

    write: views/repos/1996-20180413141654477686/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/528-20180413140609506027/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3072-20180413142336550445/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1421-20180413141154176342/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2216-20180413141847819637/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3916-20180413142706315602/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3793-20180413142632521852/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/696-20180413140702557180/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1521-20180413141315869506/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/781-20180413140752378480/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/297-20180413140459380343/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/764-20180413140728504164/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2188-20180413141834504728/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1224-20180413141032106598/b201d971de1a77b9f19a62934e479a96 Rendered collection of repos/_repo.html.slim [23 / 50 cache hits] (135.1ms) Rendered pages/_repos_with_pagination.html.slim (148.7ms) Rendered pages/index.html.slim within layouts/application (166.9ms) Rendered application/_head.html.slim (1.0ms) Rendered application/_flashes.html.slim (0.2ms) Rendered application/_nav.html.slim (1.0ms) Rendered application/_footer.html.slim (1.0ms) Rendered layouts/application.html.slim (172.2ms) Completed 200 OK in 183ms (Views: 166.3ms | ActiveRecord: 7.9ms) Rendered collection of repos/_repo.html.slim [23 / 50 cache hits] (135.1ms)
  48. 55.
  49. 56.
  50. 57.
  51. 58.

    STAT items:0:chunk_size 64 STAT items:0:evicted 0 STAT items:0:evicted_nonzero 0 STAT

    items:0:number 0 STAT items:0:outofmemory 0 STAT items:1:chunk_size 128 STAT items:1:evicted 94 STAT items:1:evicted_nonzero 4 STAT items:1:number 18 STAT items:1:outofmemory 5 STAT items:2:chunk_size 256 STAT items:2:evicted 106659 STAT items:2:evicted_nonzero 0 STAT items:2:number 9040 STAT items:2:outofmemory 0 STAT items:3:chunk_size 512 STAT items:3:evicted 5356 STAT items:3:evicted_nonzero 981 STAT items:3:number 2 STAT items:3:outofmemory 57855 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 STAT items:5:chunk_size 2048
  52. 59.

    STAT items:1:number 18 STAT items:1:outofmemory 5 STAT items:2:chunk_size 256 STAT

    items:2:evicted 106659 STAT items:2:evicted_nonzero 0 STAT items:2:number 9040 STAT items:2:outofmemory 0 STAT items:3:chunk_size 512 STAT items:3:evicted 5356 STAT items:3:evicted_nonzero 981 STAT items:3:number 2 STAT items:3:outofmemory 57855 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 STAT items:5:chunk_size 2048 STAT items:5:evicted 67687 STAT items:5:evicted_nonzero 0 STAT items:5:number 5605 STAT items:5:outofmemory 0 STAT items:6:chunk_size 4096 STAT items:6:evicted 135319 STAT items:6:evicted_nonzero 0 STAT items:6:number 11126 STAT items:6:outofmemory 0 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 1 KB 4982 items
  53. 60.

    STAT items:1:number 18 STAT items:1:outofmemory 5 STAT items:2:chunk_size 256 STAT

    items:2:evicted 106659 STAT items:2:evicted_nonzero 0 STAT items:2:number 9040 STAT items:2:outofmemory 0 STAT items:3:chunk_size 512 STAT items:3:evicted 5356 STAT items:3:evicted_nonzero 981 STAT items:3:number 2 STAT items:3:outofmemory 57855 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 STAT items:5:chunk_size 2048 STAT items:5:evicted 67687 STAT items:5:evicted_nonzero 0 STAT items:5:number 5605 STAT items:5:outofmemory 0 STAT items:6:chunk_size 4096 STAT items:6:evicted 135319 STAT items:6:evicted_nonzero 0 STAT items:6:number 11126 STAT items:6:outofmemory 0 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 5 MB
  54. 61.
  55. 63.

    STAT items:1:number 18 STAT items:1:outofmemory 5 STAT items:2:chunk_size 256 STAT

    items:2:evicted 106659 STAT items:2:evicted_nonzero 0 STAT items:2:number 9040 STAT items:2:outofmemory 0 STAT items:3:chunk_size 512 STAT items:3:evicted 5356 STAT items:3:evicted_nonzero 981 STAT items:3:number 2 STAT items:3:outofmemory 57855 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 STAT items:5:chunk_size 2048 STAT items:5:evicted 67687 STAT items:5:evicted_nonzero 0 STAT items:5:number 5605 STAT items:5:outofmemory 0 STAT items:6:chunk_size 4096 STAT items:6:evicted 135319 STAT items:6:evicted_nonzero 0 STAT items:6:number 11126 STAT items:6:outofmemory 0 STAT items:4:chunk_size 1024 STAT items:4:evicted 1880488 STAT items:4:evicted_nonzero 0 STAT items:4:number 4982 STAT items:4:outofmemory 85445 !!!
  56. 64.
  57. 65.
  58. 66.

    Rendered application/_down.html.slim (0.1ms) Cache read: views/language_list/05660542d890bbeab574f26e0cbbd974 ({:expires_in=>1 hour}) Repo Load

    (1.7ms) SELECT "repos"."id", "repos"."updated_at", "repos"."issues_count", " (2.7ms) SELECT COUNT(*) FROM "repos" WHERE (issues_count > 0) AND ("repos"."id" NOT IN Cache read_multi: ["views/repos/1894-20180413141703167092/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache read_multi: ["views/repos/1894-20180413141703167092/b201d971de1a77b9f19a62934e479a96
  59. 67.

    Rendered application/_down.html.slim (0.1ms) Cache read: views/language_list/05660542d890bbeab574f26e0cbbd974 ({:expires_in=>1 hour}) Repo Load

    (1.7ms) SELECT "repos"."id", "repos"."updated_at", "repos"."issues_count", " (2.7ms) SELECT COUNT(*) FROM "repos" WHERE (issues_count > 0) AND ("repos"."id" NOT IN Cache read_multi: ["views/repos/1894-20180413141703167092/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96
  60. 68.
  61. 69.

    Cache write: views/repos/696-20180413140702557180/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1521-20180413141315869506/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1521-20180413141315869506/b201d971de1a77b9f19a62934e479a96 Cache

    read: views/repos/781-20180413140752378480/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/781-20180413140752378480/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/297-20180413140459380343/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/297-20180413140459380343/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/764-20180413140728504164/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/764-20180413140728504164/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/2188-20180413141834504728/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2188-20180413141834504728/b201d971de1a77b9f19a62934e479a96 Cache read: views/repos/1224-20180413141032106598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1224-20180413141032106598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/863-20180413140900779427/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/386-20180413140542430556/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1996-20180413141654477686/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/3353-20180413142449772302/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1055-20180413141006040871/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1332-20180413141232839666/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/2151-20180413141907871737/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1082-20180413140958427040/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1524-20180413141412726203/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1916-20180413141546875915/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1348-20180413141215739127/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/177-20180413140322053324/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1824-20180413141520630598/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1901-20180413141634501842/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/863-20180413140900779427/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/386-20180413140542430556/b201d971de1a77b9f19a62934e479a96 Cache write: views/repos/1996-20180413141654477686/b201d971de1a77b9f19a62934e479a96
  62. 70.
  63. 71.
  64. 72.

    DO THE ANSWER DANCE. Don’t struggle to learn why your

    app is slow. Get answers with Skylight. SKYLIGHT