Identifying NHibernate-Related Bottlenecks through Performance Monitoring

• Download source - 18.08 KB

Overview

My work requires skills balanced somewhat evenly between a DBA and a software engineer. Through this position, I've learned some common gaps of misunderstanding between the two parties. NHibernate is great for the software engineer because it abstracts away most details of the data layer. However, much like any ORM framework, the caveat it carries is that you must pay attention to the way it interacts with your database.

This is the first part of two posts to help enable your NHibernate-based application meet your performance and scalability needs. This is a focus of effectively gathering data and understanding about your bottleneck. The second part can be found here.

First and foremost, it's important to correctly identify your performance issue before considering solutions because you need to be able to quantify the difference in performance your changes have made. Many books and applications already exist to help you load test applications. The following section suggests a distilled methodology using basic tools to identify data-layer bottlenecks.

Tools You Will Need

• SQL Server (Developer Edition Preferred): This is the same distribution of your (enterprise/standard) production server except it's built for an isolated development environment. The specific tools we will be using include:

  • SQL Profiler- The one and only! We use this extensively to view every database call NHibernate is sending to the database as well as the time and CPU cost each call creates. For those new to SQL profiler, make sure to learn the basics.

  • Query analyzer- We use SQL profiler to gather the collection of database calls in our troublesome areas. We use query analyzer to dissect and evaluate the performance of atomic database calls by viewing their compiled execution plans. Optimizing specific queries is an art in itself, reaching beyond the scope of this article. To learn the basics of how to properly read and interpret an execution plan through query analyzer, consider starting here for an introduction.

• .NET profiler: On a higher level, we need some program capable of reporting performance from the perspective of the .NET side of our application, preferably an execution trace showing us method calls and execution times in the form of an execution tree path. From my experience, I can recommend programs such as JetBrains DotTrace, AQTime, and ACT.

• Stress Generator: Feel free to select your favorite third party utility, but the desired functionality you will need is the ability to hammer your target use case or method a variable number of times with a variable number of concurrent requests. To maximize control and focus, consider writing some custom load-testing module if you don't already have one. I've attached an example project in Visual Studio 2005 that should help guide you in the right direction. This is a skeleton console application, complete with a command line, threadpool, and mock setup that we have used in the past. Hopefully, you'll spend less time building load testing code, and more time experimenting!

• Perfmon: An invaluable performance metrics tool already built into your Windows system. All kinds of great counters can yield information, but too much information can muddy the waters. Use the following performance counters when running your scalability tests:

  • SQLServer:Locks::Average wait time : The telltale sign of poor scalability, this shows how long a given thread is waiting for a lock to be released before it can complete its job.

  • Memory::Page Faults/sec: How often does the database have to read from the disk versus memory? This can give hints into improving your indexing.

Further Requirements

• Isolate your test environment: Performance testing is an experiment. Just like any proper experiment, we need to set up a controlled environment. It's important to eliminate any potential outside noise. Try to run the app as a standalone on your local machine if possible.

  • Unplug your Ethernet cable
  • Local SQL server
  • No unnecessary services or programs, especially anything that may create "noise" in SQL profiler.

Bottleneck Detection Procedure

1. General Walkthrough

   When you know the "use case" area you need to tune, but are not sure which specific methods are problematic, use the following steps to gather more specific data.

   1. Start your program, bring it to the beginning state of the use case in question.
   2. Start your .NET profiler, attach it to your application
   3. Run the use case.
   4. Once the use case is completed, stop the .NET profiler, view the reports.

   The data from this .NET profile will give us insight into which method calls stand out as sore thumbs. Once we've found the target methods, you can use the following tests to drill down to the real culprits.

2. Method-Specific (Atomic Test)

   Given a target method, it's time to find how this method impacts your database in a single call.

   1. Set a breakpoint on the start and end of the method, debug your program and reach the method call.
   2. Start SQL profiler, and profile the target database.
   3. Run the debugger, step-by-step and watch the sequential database interaction. See the commands NHibernate is generating? Note which .NET commands translate into which SQL commands.
   4. Save the profiler results in a database table.

   Is there a single database call that's taking a long time? Are there more database calls than you would expect? Take note, and try to find out why this is happening, but resist the temptation to jump to conclusions just yet.

3. Method-Specific (Scalability Test):

   Make a few graphs that display execution time (in terms of the .NET profile or query profile) versus thread count. Each graph has a certain number of method calls (a graph for 1, 10, 100, 1000, etc.):

   If scalability is your problem, your graphs will look somewhat like this:

   

   Good scalability is indicated by a reduction in execution time as threads are introduced. Of course, there is a threshold to the number of threads you can add before "context-switching" overhead hinders your performance (aka "thrashing"), but this is natural, and dependent on the hardware and OS. The following is an example of good scaling up to 5 threads:

   

   How many concurrent threads can your app manage before it starts to suffer? Is this threshold acceptable enough to meet your requirements?

   At this point, save your .NET traces, your profiler traces, and your scalability results. Be sure you can re-run these tests in the future to accurately quantify your gains in performance once you're done adding turbo boosts to your app!

Sift Through Your Traces to Find Out Point Sources of your Problems

The following SQL will help you find the most frequently used queries:

SELECT
DISTINCT cast(textdata as varchar(150)) as textdata,
avg(duration) as avg_duration,
count(duration) as Occurences 
FROM
[<yourTraceTableHere>] 
GROUP BY
Cast(textdata as VarChar(150))
ORDER BY
count(duration)desc

And this will help you find your more inefficient queries:

SELECT
DISTINCT cast(textdata as varchar(150)) as textdata,
avg(duration) as avg_duration,
count(duration) as Occurences
FROM
[<yourTraceTableHere>] 
GROUP BY
Cast(textdata as VarChar(150))
ORDER BY 
Avg(duration)desc 

• Sort your SQL profile traces by CPU cost, execution time to find expensive queries. Note any of the following:

  • Is there a similar query that's happening all too frequently?
  • Can they be bypassed by caching of some sort?
  • Do you wish you had a stored procedure to bypass some expensive and frequently called statement?
  • Is a single table getting inserts/updates repeatedly?
  • Is a single relatively static table getting queried repeatedly, making you wish you could cache it?



• Are you loading more data than you really need in some queries?

• Are you calling the database more than necessary?

List out what problems you suspect and order them by severity. At this point, it's tempting to implement solutions, but make sure you have a good grasp on precisely where your problem lies as well as the overall time cost it poses before you begin.

Common ORM-Related Performance Problems

What kind of common problems can ORM introduce into your system?

The "Chatty" Application Server Problem

All too often, there's an excessive amount of I/O "chat" between your .NET and database tiers. You call for an entity layer object, and NHibernate automatically "hydrates" this object for you from values in your database. What kind of work actually goes in to hydrating an object?

• NHibernate uses your chosen dialect to generate SQL
• NHibernate allocates a connection to the database
• The database server parses, validates, and compiles the SQL into a query plan
• The database server broadcasts the interaction to any listeners
• The database executes the compiled query plan and stores the results in a database-native recordset
• The recordset is converted to a DBLib-specific data structure (such as an ODBC recordset)
• The recordset is transported back to the NHibernate layer
• The recordset is converted into an SQL Data Reader
• NHibernate converts the data from the SQL data reader into the requested object(s).

Now that's a non-trivial amount of I/O work! Any good DBA will preach to you that it's bad practice to load more data than you really need. Think about this the next time you make a DAO call.

Is there a roundtrip to the database for every single object you load? If so, it brings forth some considerable overhead cost in the form of database-layer communication. The end result? Things look good in your initial tests, but later you will find the scalability is abysmal. The solution? Decrease the "chat" between your tiers by increasing the granularity of your database calls. The next chapter will explain how to manage this problem.

The "Greedy" Loading Problem

Sometimes, you may want a specific object, but due to your mapping definitions, you always get more data than you really need. I've seen the "Greedy" loading problem come about in three different ways:

1. Lack of use of the "lazy-loading" relational attribute. In NHibertate 1.0.3.0, no child collections are lazy by default. In NHibernate 1.2 all child collections are lazy by default. Are you missing a lazy attribute where you could use one, or are you better off with an eager fetching plan?
2. Loading a collection of objects when you only need one element of the collection. Have you ever iterated through a huge "lazy" collection to retrieve a single object and leave the rest to the garbage collector? Consider how big this collection can become, and consider accessing the single object through some more efficient means.
3. Loading a child object (an object with a many-to-one association) implicitly forces the loading of the parent object, as well as all of the things that come along with loading the parent object. This means that if you load a child object with a long ancestry of many-to-one associations, you're implicitly loading all of the parents, grandparents, etc. up to the top of the tree and any associated data at any level. The next chapter will explain how to manage this problem.

My next section will finally get into the details of how to control your NHibernate data layer access for better granularity, more efficient locking, caching schemes, isolated searches, and yes, stored procedures/functions.

Part 2: Performance Enhancements

Can be found here.