Database Network Performance

Database performance issues are the scourge of any large application, especially as load increases. They can be hard to understand, happen at times that aren’t particularly predictable, and difficult to replicate in a development or QA environment. This can make them very difficult to mitigate. The problem is compounded by the fact that many database performance issues are actually side effects of so-called “proper” code reuse and abstraction. As such, fixing them in a codebase can quickly become a problem on a team; many people have read books on the “right” way to do things, but don’t understand when those rules don’t apply. Given this, the process of troubleshooting database network performance issues in your application is really the process of piercing through a pile of abstractions to whatever depth is necessary to see what is going on. Really shallow bugs may be as obvious as a query that brings back thousands of records and doesn’t need to. Deep bugs may force you to have to dig deeply into how your ORM builds queries and how data is actually transmitted over the wire. Fixing these bugs may be as simple as caching frequently used data for a few minutes instead of constantly retrieving it from the database, while more complex issues may require major reworking of your codebase. The latter can be especially fun, as it often has to be accomplished piecemeal while still allowing feature development to continue. Database networking issues can often be fairly counter-intuitive to solve. While we like to believe that we can know what our code is doing by simply reading it, the truth is that we live atop a huge pile of abstractions that we probably didn’t create. And it gets even more interesting when our own abstractions work against us, or when our coworkers (or ourselves in six months) slightly misinterpret an abstraction and create unintended side effects in application performance. Worse still, there are operations and infrastructure issues that can manifest in slow application performance, while appearing to be problems with our application. While database network performance issues are complex, many of them can be traced down to incorrect usage of underlying infrastructure. In essence, these are physics problems manifesting as programming problems. Many of these problems occur as a result of abstraction hiding what’s really going on under the hood. Once you get past the abstractions of your application and see what is really happening at the network and database level, the source of the problem is usually pretty easy to find. Solving them can be more difficult, however, as doing so often requires rethinking the way that you use abstraction within your application and may require that you adopt different coding patterns entirely. Episode Breakdown Select N+1 This occurs frequently when you load a top level record and then individually load records underneath that record. This is common when (mis)using an ORM. In many ORMs, child objects can be retrieved transparently as required (lazy loading). While convenient, this approach doesn’t scale and results in counter-intuitive behavior. To avoid this, it is generally best to avoid lazy loading, as well as loading child records within the body of a loop. To fix this problem, you need to load the relevant child records before you need them, preferably along with the parent record. Filtering data on the wrong end of the wire. This frequently occurs when filtering criteria are complex or when an ORM exhibits counter-intuitive behavior that means that a filtering statement is not applied at the database. There isn’t a great way to avoid this at the team level. Instead, you will want to monitor your system to look for sudden changes in performance. Fixing this problem is going to vary depending ...