Performance Zone is brought to you in partnership with:

Jim spent years on the user side of APM solving problems, fighting fires, and trying to convince all of his APM vendors that they could (and should) do better. His passion for performance tuning and troubleshooting led him from systems and application administration to working as an APM Architect tasked with designing an integrated ecosystem capable of monitoring next generation data centers and the applications housed within. Jim never passed up an opportunity to test drive and provide feedback on (pick apart) an APM vendors offering so he has used most of the tools out there. Jim’s viewpoint is a result of work in a high pressure Financial Services environment but his methods and approach apply to any IT organization that strives for greatness. Jim is a DZone MVB and is not an employee of DZone and has posted 29 posts at DZone. You can read more from them at their website. View Full User Profile

Storage is Killing Your Database Performance

06.09.2013
| 5405 views |
  • submit to reddit

The other day I had the opportunity to speak with a good friend of mine who also happens to be a DBA at a global Financial Services company. We were discussing database performance and I was surprised when he told me that the most common cause of database performance issues (from his experience) was a direct result of contention on shared storage arrays.

After recovering from my initial surprise I had an opportunity to really think things through and realized that this makes a lot of sense. Storage requirements in most companies are growing at an ever increasing pace (big data anyone?). Storage teams have to rack, stack, allocate, and configure new storage quickly to meet demand and don’t have the time to do a detailed analysis on the anticipated workload of every application that will connect to and use the storage. And therein lies the problem.

Workloads can be really unpredictable and can change considerably over time within a given application. Databases that once played nicely together on the same spindles can become the worst of enemies and sink the performance of multiple applications at the same time. So what can you do about it? How can you know for sure if your storage array is the cause of your application/database performance issues? Well, if you use NetApp storage then you’re in luck!

AppDynamics for Databases remotely connects (i.e. no agent required) to your NetApp controllers and collects the performance and configuration information that you need to identify the root cause of performance issues. Before we take a look at the features, let’s look at how it gets set up.

The Config

Step 1: Prepare the remote user ID and privileges on the NetApp controller. The following commands are used for the configuration.

useradmin role add AppD_Role -a api-*,login-http-admin
useradmin group add AppD_Group -r AppD_Role
useradmin user add appd -g AppD_Group

Note: Make sure you set a password for the appd user.

Step 2: Configure AppDynamics to monitor the NetApp controller. Notice that we configure AppDynamics with the the username and password created in step 1.

Screen Shot 2013-04-11 at 4.14.33 PM

 Step 3: Enjoy your awesome new monitoring (yep, it’s that easy).

The Result

After an incredibly difficult 2 minutes of configuration work we are ready for the payoff. In the AppDynamics for Databases main menu you will see a section for all of your NetApp agents.

Screen Shot 2013-04-11 at 4.09.13 PM

Let’s do a “drill-up” from the NetApp controller to our impacted database. Clicking into our monitored instance we see the following activity screen.

Screen Shot 2013-04-11 at 4.29.28 PM

By clicking on the purple latency line inside of the red box in the image above we can drill into the volume that has the highest response time. Notice in the scree grab below that we have a link at the bottom of the page where we can drill-up into the database that is attached to this storage volume. This relationship is built automatically by AppDynamics for Databases.

Screen Shot 2013-04-11 at 4.31.56 PM

Clicking on the “Launch In Context” link we are immediately transfered to the Oracle instance activity page shown below.

Screen Shot 2013-04-11 at 4.34.21 PM

In just the same manner as we can drill-up from storage to database, we can also drill-down from database to storage. Notice the screen grab below from an Oracle instance activity screen. Clicking on the “View NetApp Volume Activity” link will launch the NetApp activity screen shown earlier for the volumes associated with this Oracle instance. It’s that easy to switch between the views you need to solve your applications performance issues.

Imagine being able to detect an end user problem, drill down through the code execution, identify the slow SQL query, and isolate the storage volume that is causing the poor performance. That’s exactly what you can do with AppDynamics.

natappdrilldown

Storage monitoring in AppDynamics for Databases is another powerful feature that enables application support, database support, and storage support to get on the same page and restore service as quickly as possible. If you have databases connected to NetApp storage you need to take a free trial of AppDynamics for Databases today.

Published at DZone with permission of Jim Hirschauer, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)