The Nastiest Performance Bottleneck Is Often The Database

Some of the most joyful technical challenges I experienced as a developer were solving application performance problems. Isn't it fun. You are Sherlock Holmes - examining the architecture, diving into the code for clues, and scouring through logs files to find the bottlenecks that are responsible for snail's pace. However, this job is a lot harder than Sherlock Holmes or CSI. It is more like Dr. Gregory House, because you are racing against the clock. For every minute of sluggish performance, you could be losing eyeballs and therefore revenue. Worst case: the patient, i.e., your website, dies.

Performance Problems Are Usually Elevated Because Of A Crisis

Your business just launched a Super Bowl commercial that confidently directed people to your website - #fail. More likely, a new release of software performs like a dog (with apologies to Greyhounds) because of lame coding and nonexistent performance testing.

 You Need A Clever Solution, Fast

Since crisis performance problems are often caused by an unexpected influx of users or transactions from another application, you need to find a solution fast. If you are lucky, the solution to the bottle will be something easy like ordering more bandwidth or adding more web/application servers. Maybe adding a content delivery network such as Akamai, EdgeCast, Cotendo, or Limelight if you don't already have one. Not too often you will find a dumb coding mistake that can easily be fixed. Many performance problems can be solved by these aforementioned methods. But, the hardest and therefore nastiest problem is when your database is the bottleneck.

You Can't Easily Horizontally Scale A Database

Many applications access a relational database such as DB2, MySQL, Oracle, or SQL Server. More often than not, I have found that the hardest performance problem to solve is the database bottleneck. Application performance is often governed by how quickly the database can perform queries, inserts, and updates. The problem with relational database is that you can't just add another server and double your pleasure. The reason: Relational databases do not scale horizontally.

Elastic Caching Platforms Can Help

One way to boost performance of relational databases is to add an elastic caching platform (ECP) layer between your application and the database. Elastic caching platforms offer an in-memory cache that can scale horizontally. This can give you the immediate benefit of in-memory distributed caching to boost performance and eliminate the database bottleneck. Forrester evaluated seven elastic caching platforms: Alachisoft NCache, VMware GemFire, GigaSpaces XAP Elastic Application Platform, IBM WebSphere eXtreme Scale, Infinispan, ScaleOut StateServer, and Terracotta. Additional elastic caching platforms not evaluated in the Forrester Wave™ include TIBCO ActiveSpaces and Microsoft Project Codename Velocity that is now part of Azure AppFabric.

Find The Most Heinous Bottleneck And Mitigate It

The database bottleneck is often the nastiest to solve. Hopefully, the cause of your performance problem is just an ignorantly formed SQL statement that is easily fixed. But, with increasing scale, the solution to bottlenecks becomes your ability to scale horizontally.

Comments

Like all performance problems - it depends

Tom -

I have to disagree with you on two counts. First of all, Real Application Clusters, an option with the Oracle database, DO indeed scale horizontally - it's a key part of the value proposition for this option.

As you scale out nodes with RAC, you also scale out cache, and with in-memory parallel processing, you can use that distributed cache as a shared pool, in some sense.

Secondly, I think it is always a mistake to call out a single solution for a performance problem based on a single symptom. A lack of high speed access could be the issue with less than optimal database performance - or it might not. You could be CPU-bound on the database servers, depending on the specifics of the workload.

It's pretty easy to see if the bottleneck is in I/O operations, so some rudimentary checking would be in order before any solution is prescribed. And although the database may be the problem, caching is not necessarily the solution.

Single solution

Hi Tom, I agree that a single solution is not always appropriate. I try to find any and all bottlenecks and then find solutions to mitigate them. My point is that I often find the database to be one of the key bottlenecks because it can't easily scale horizonatlly. I agree Oracle RAC is one solution, but I think elastic caching is a less costly solution for applications that use objects as transactions. If the application is doing arbitrary queries then ECP won't help much.
I

With scaling and distributed

With scaling and distributed data platforms come trade-offs, and things just got that much harder management wise. Each one of these caching platforms comes with its own overhead, and not having a complete understanding of how much data are you working with and what are you trying to do with it, will lead you down the path of nowhere.

Those ill designed SQL may not perform any better in a scaled out environment with growth. Once you scale the data, the application requires a top down redesign of how it will interact with this layer. Again no easy scaling here