Ways To Measure Your ORM's Cost

Ways To Measure Your ORM's Cost

Your project has adopted an ORM such as Doctrine because it's the quickest way to hook your code up to your database. Things are looking up, and your website is about to take off. Aaaand then the first complaints start to come in that your site isn't coping with the new customers. You can't find a problem in your own code. Could it be the ORM that's the problem? How can you tell?

In this talk, Stuart will show you how you can use off-the-shelf open-source tools to work out what your ORM is doing, and whether or not it is the cause of your performance problems. He'll show you how to measure the database itself and the ORM code, as well as providing useful strategies to reduce the cost of your ORM without having to abandon the ORM altogether. Finally, he'll show you how you can extend these techniques to other parts of your application, so that you're never in the dark again.

Presented at #PHPNW15 in October, 2015.

2c1dc90ff7bf69097a151677624777d2?s=128

Stuart Herbert

October 04, 2015
Tweet

Transcript

  1. A presentation by @stuherbert
 for @GanbaroDigital Ways To Measure Your

    ORM’s Cost
  2. @GanbaroDigital Today’s Talk

  3. @GanbaroDigital 1. Why Measure

  4. @GanbaroDigital 2. What To Measure

  5. @GanbaroDigital 3. How To Measure

  6. @GanbaroDigital You’ll take away practical advice that you can apply

    straight away.
  7. @GanbaroDigital Accepted Wisdom? “PHP is rarely the bottleneck”
 - Rasmus

    Lerdorf, 2010 http://talks.php.net/show/ digg/7
  8. @GanbaroDigital agree? Do you

  9. @GanbaroDigital Today, using an ORM is common practice.

  10. @GanbaroDigital When you use an ORM, you are hiding persistent

    data storage and retrieval behind an abstraction.
  11. @GanbaroDigital Abstractions trade dev time for runtime

  12. @GanbaroDigital is slow PHP’s runtime

  13. @GanbaroDigital The current fashion is to write Java-style code for

    a language that doesn’t have JVM-style performance.
  14. @GanbaroDigital a free lunch Your ORM of choice is not

  15. @GanbaroDigital Used wisely, an ORM can be a good thing.

  16. @GanbaroDigital Used blindly, an ORM can cripple your Production performance.

  17. @GanbaroDigital what your ORM costs? Do you know

  18. @GanbaroDigital to know? Do you need

  19. @GanbaroDigital Need To Measure? Your App MySQL ORM Single Server

  20. @GanbaroDigital Need To Measure? Your App MySQL ORM Separate Tiers

  21. @GanbaroDigital Need To Measure? Your App MySQL ORM Your App

    ORM Scale Out Architecture
  22. @GanbaroDigital Need To Measure? Your App MySQL ORM Your App

    ORM Cloud Architecture MySQL
  23. @GanbaroDigital You need to measure the costs of how you’re

    using the ORM.
  24. @GanbaroDigital How you use the ORM determines what the costs

    are.
  25. @GanbaroDigital It isn’t the ORM that is slow. It only

    does what you ask it to.
  26. @GanbaroDigital Measure to improve your understanding

  27. @GanbaroDigital Measure to prevent problems reaching Production

  28. @GanbaroDigital What would YOU measure?

  29. @GanbaroDigital What To Measure? Your App MySQL ORM Your App

    ORM
  30. @GanbaroDigital What To Measure? Your App ORM MySQL Storage Network

    Network
  31. @GanbaroDigital What To Measure • Storage IOPS • Demands on

    the network • Efficiency of data requests • Where the time goes
  32. @GanbaroDigital Storage IOPS Your App ORM MySQL Storage Network Network

  33. @GanbaroDigital What To Measure • Storage IOPS • Demands on

    the network • Efficiency of data requests • Where the time goes
  34. @GanbaroDigital Network Demand Your App ORM MySQL Storage Network Network

  35. @GanbaroDigital What To Measure • Storage IOPS • Demands on

    the network • Efficiency of data requests • Where the time goes
  36. @GanbaroDigital Data Efficiency Your App ORM MySQL Storage Network Network

  37. @GanbaroDigital What To Measure • Storage IOPS • Demands on

    the network • Efficiency of data requests • Where the time goes
  38. @GanbaroDigital Where The Time Goes Your App ORM MySQL Storage

    Network Network
  39. @GanbaroDigital Storage IOPS? Why measure

  40. None
  41. @GanbaroDigital Dev Environments • Dev laptops use PCIe SSDs •

    Typically perform around 100K IOPS • Cheap SATA SSDs can do 50K+ IOPS
  42. @GanbaroDigital Dev Environments • Dev laptops use PCIe SSDs •

    Typically perform around 100K IOPS • Cheap SATA SSDs can do 50K+ IOPS
  43. @GanbaroDigital Dev Environments • Dev laptops use PCIe SSDs •

    Typically perform around 100K IOPS • Cheap SATA SSDs can do 50K+ IOPS
  44. @GanbaroDigital Servers • Servers typically use SAS RAID arrays •

    Yields 0.4 - 2K IOPS • SATA-equipped servers are even slower!
  45. @GanbaroDigital Servers • Servers typically use SAS RAID arrays •

    Yields 0.4 - 2K IOPS • SATA-equipped servers are even slower!
  46. @GanbaroDigital Servers • Servers typically use SAS RAID arrays •

    Yields 0.4 - 2K IOPS • SATA-equipped servers are even slower!
  47. @GanbaroDigital In The Cloud • Servers typically over-subscribed • Need

    large, slow disks to support all the customers and their VMs • Maybe 1K IOPS shared between many VMs!
  48. @GanbaroDigital In The Cloud • Servers typically over-subscribed • Need

    large, slow disks to support all the customers and their VMs • Maybe 1K IOPS shared between many VMs!
  49. @GanbaroDigital In The Cloud • Servers typically over-subscribed • Need

    large, slow disks to support all the customers and their VMs • Maybe 1K IOPS shared between many VMs!
  50. @GanbaroDigital Storage Comparison Dev
 Laptop Dedicated
 Server Cloud 
 Server

    50-100K 0.4-2K 1K (shared)
  51. @GanbaroDigital Storage Comparison Dev Laptop Dedicated Server Cloud Server 0

    25 50 75 100
  52. @GanbaroDigital Storage Comparison Dev Laptop Dedicated Server Cloud Server 0

    25 50 75 100
  53. @GanbaroDigital Storage Comparison Dev Laptop Dedicated Server Cloud Server 0

    25 50 75 100
  54. @GanbaroDigital For the first time in the 
 history of

    web development, your dev machine’s storage is faster than Production’s storage.
  55. @GanbaroDigital Faster?!? It stomps all over Production!

  56. @GanbaroDigital Storage IOPS How to measure

  57. @GanbaroDigital iostat -d -x 5

  58. @GanbaroDigital Two Key Columns • r/s • w/s

  59. @GanbaroDigital Other Tips • Move your database onto its own

    filesystem • Don’t forget to monitor how much IOPS your logging causes
  60. @GanbaroDigital Other Tips • Move your database onto its own

    filesystem • Don’t forget to monitor how much IOPS your logging causes
  61. @GanbaroDigital Storage IOPS Your App ORM MySQL Storage Network Network

  62. @GanbaroDigital Network Demands? Why measure

  63. Stopwatch - William Warby - https://flic.kr/p/62n3eY

  64. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  65. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  66. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  67. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  68. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  69. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  70. @GanbaroDigital Your app is paused until the ORM has finished

    processing your request.
  71. @GanbaroDigital In the meantime, your customer is kept waiting for

    their webpage.
  72. @GanbaroDigital How many SQL requests does your ORM actually make?

  73. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  74. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  75. @GanbaroDigital Your app can make requests without you realising.

  76. @GanbaroDigital The ORM can make multiple requests without you realising.

  77. @GanbaroDigital Solve By Scaling Out? Your App MySQL ORM Your

    App ORM Scale Out Architecture
  78. @GanbaroDigital Scaling out allows you to serve more customers at

    once. It does not help you to serve a single customer faster.
  79. @GanbaroDigital Network Demand How to measure

  80. @GanbaroDigital mysql-proxy Completely independent logging

  81. @GanbaroDigital MySQL Proxy • Point your app at mysql-proxy •

    Use the ‘debug-blind.lua’ proxy plugin
  82. @GanbaroDigital MySQL Proxy • Point your app at mysql-proxy •

    Use the ‘debug-blind.lua’ proxy plugin
  83. @GanbaroDigital What It Tells Us • Complete log of all

    SQL requests • How long each request takes • Number of rows returned • Whether or not an index was used
  84. @GanbaroDigital What It Tells Us • Complete log of all

    SQL requests • How long each request takes • Number of rows returned • Whether or not an index was used
  85. @GanbaroDigital What It Tells Us • Complete log of all

    SQL requests • How long each request takes • Number of rows returned • Whether or not an index was used
  86. @GanbaroDigital What It Tells Us • Complete log of all

    SQL requests • How long each request takes • Number of rows returned • Whether or not an index was used
  87. @GanbaroDigital Network Demand Your App ORM MySQL Storage Network Network

  88. @GanbaroDigital Data Efficiency? Why measure

  89. Junk Mail - Judith E. Bell - https://flic.kr/p/pgMxPA

  90. @GanbaroDigital Your ORM can request lots of data that your

    app’s code never sees.
  91. @GanbaroDigital Why? Hydration of complex relationships.

  92. @GanbaroDigital http://ocramius.github.io/blog/ doctrine-orm-optimization-hydration/

  93. @GanbaroDigital Your app may use less than 10% of the

    data retrieved from the database.
  94. @GanbaroDigital Costs Twice • Database load • Application server load

  95. @GanbaroDigital Costs Twice • Database load • Application server load

  96. @GanbaroDigital Puts Pressure On • CPU • RAM • Storage

    IOPS
  97. @GanbaroDigital Puts Pressure On • CPU • RAM • Storage

    IOPS
  98. @GanbaroDigital Puts Pressure On • CPU • RAM • Storage

    IOPS
  99. @GanbaroDigital Hurts • Response times • Simultaneous requests • Database

    replication
  100. @GanbaroDigital Hurts • Response times • Simultaneous requests • Database

    replication
  101. @GanbaroDigital Hurts • Response times • Simultaneous requests • Database

    replication
  102. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  103. @GanbaroDigital Data Efficiency How to measure

  104. @GanbaroDigital manual hacks required

  105. @GanbaroDigital The Approach 1. Modify your ORM’s database driver to

    log fields from the database. 2. Modify your code to count the size of your entities. 3. Efficiency = entity size / fields from DB
  106. @GanbaroDigital The Approach 1. Modify your ORM’s database driver to

    log fields from the database. 2. Modify your code to count the size of your entities. 3. Efficiency = entity size / fields from DB
  107. @GanbaroDigital The Approach 1. Modify your ORM’s database driver to

    log fields from the database. 2. Modify your code to count the size of your entities. 3. Efficiency = entity size / fields from DB
  108. @GanbaroDigital The lower the efficiency, the more time is being

    wasted.
  109. @GanbaroDigital Data Efficiency Your App ORM MySQL Storage Network Network

  110. @GanbaroDigital where the time goes? Why measure

  111. Wait - Hernán Piñera - https://flic.kr/p/o1mSAi

  112. @GanbaroDigital Your app can spend up to 80% of its

    total time waiting for the ORM.
  113. @GanbaroDigital Request Timeline Your App ORM MySQL Network

  114. @GanbaroDigital where the time goes? How to measure

  115. @GanbaroDigital Profile your app

  116. @GanbaroDigital • Xdebug • XHProf • Blackfire.IO • Zend Z-Ray

    • AppDynamics • or build your own instrumentation using Graphite Profilers For PHP
  117. @GanbaroDigital Where The Time Goes Your App ORM MySQL Storage

    Network Network
  118. @GanbaroDigital Plenty of existing conference talks about using a profiler.

    Go and Google for them.
  119. @GanbaroDigital Profile your app

  120. @GanbaroDigital said than done Much easier

  121. @GanbaroDigital Profile your app under realistic load

  122. @GanbaroDigital Profile your app under realistic load in a representative

    environment.
  123. @GanbaroDigital Profile your app with production data under realistic load

    in a representative environment.
  124. Stuart Herbert ~ @stuherbert Founder @GanbaroDigital