Skip navigation

Product Blog

5 Posts authored by: kathleen.gibbs

I am excited to say that Database Performance Analyzer 10.0, with MySQL support, is now available.  For the Orion users out there we have also extended the DPA data for MySQL into the integration. DPA 10.0 is now available in the customer portal to download for customers on active maintenance.  If you are new to DPA and want to try it, you can download an evaluation from the SolarWinds website.

New Features in 10.0

 

  • Support monitoring MySQL in DPA
    • Register and monitor your on-premise, cloud, and RDS MySQL instances.
    • Multi-Dimensional Monitoring of MySQL
    • Advisors for MySQL
    • Metrics for MySQL
    • Integration between DPA and Orion for MySQL instances
  • Baselines for Resource page
  • Updated Resource collection for SQL Server, No More WMI!!!

Note: DPA 9.5 was renamed to 10.0 before release.  If you are running the release candidate DPA 9.5, no need to rush to upgrade to 10.0.

 

 

Register MySQL Instances

 

Register MySQL on-premise and in the cloud (RDS &  EC2). Whether your MySQL instance is on RDS, EC2, or on-premise, the data shown in DPA is the same!  Register a MySQL instance the same as you would for any other supported database in DPA. Have several instances to register no problem, use the Mass Registration wizard that can be found in Options.

dpa95regi.png

 

 

Multi-dimensional Monitoring of MySQL

 

MySQL DBA's have never really had a tool that could show them their problem SQL Statements.  A lot of tuning work comes from the slow query log and monitoring metrics. While this can be important, this tuning path often misses the SQL that most effects the user.  You certainly can't find a query in the slow query log if it runs in .01 seconds.  However if that query is now running in .1 seconds and it runs thousands of times in an hour, it is most definitely the biggest pain point for your users.

 

In the screen capture below, you can see I have drilled into the familiar 'Time' dimension.  From here, you can see that  I can easily click to the Database tab to select and isolate SQLs that are coming from 1 specific database.  This isolation can be done the for any of the dimensions.

dpa95dimension.png

 

The new dimensions for MySQL are 'Wait Instruments' and 'Operations'.

  • Use the Wait Instruments dimension to drill into the granual detail of what a specific wait is doing, as an example I can drill into the 'updating' wait and then choose to find out just the query's that are in the 'io/file/innodb/innodb_log_file' wait instrument vs the 'lock/table/sql/handle' instrument.
    • Wait Instruments are exposed by MySQL if the MySQL Performance Schema is enabled. Wait Instruments are based on instrumented portions of the DB engine that you can enable at startup or during run-time via the Performance Schema configuration

dpa9.5waitinstrumentupdate.png

  • Using the example mentioned above, once I select the 'io/file/innodb/innodb_log_file' wait instrument, I can go to the Operations tab I can see the SQL that are either performing sync or write operations.
    • Operations are exposed by MySQL if the MySQL Performance Schema is enabled. Operations are based on instrumented portions of the DB engine that are enabled by enabling Wait Instruments.

dpa95operation.png

 

Advisors

 

You may say 'Ok Kathy, that is a lot of information and all of this data is great, but what do I do with it?'.  That is where the Advisors, Query Advice, and wait advice in general comes in.  Let's say we saw a lot of blocking with a SQL. I click on the Query Advice and select the SQL I am concerned with.

 

Below is an example of the Query Advisor in DPA.  You can see the highest hours that had blocking, an explanation of what Blocking is, and other areas to look in DPA to troubleshoot this problem further.

 

dpa95advisor.png

 

 

Resource Metrics

 

DPA has added more out of the box metrics for MySQL than we have for any other database we support.  The good news is you get all these metrics PLUS, you still can create a custom resource metric just like you can for the other monitored instances

 

DPA95metrics.png

Note:This is one area of DPA that provides more detail for InnoDB than other engines.

 

Integration with Orion

 

We are building on what we did in the previous 9.2 release by giving SAM and Orion users the ability to see MySQL in Orion.

  • Dashboard views for NOC teams.
  • Publish response time analysis data to application monitors used by development and support teams.
  • See what is happening on your hosts and be able to correlate host activity to database activity

 

To see the full integration with Orion, go here Announcing DPA 9.2 GA : Is it the Application or the Database?

dpa95dpao.pngMy

 

Baselines for Metrics Page

 

Let's go back to that Resource (metrics) page for a moment.  you may notice something new.  Yep, that is the same 'Show Baselines' button that is on the Resources tab. When there is  a metric that is in alarm on the home page, here the Memory 'Warning alarm is circled', you want to click on that alarm to find out more details.

 

dpa95home.png

 

You can see that clicking on that warning icon brings you to the memory tab on the metrics page.  However once we got to the Resource Metric page, you noticed that there is a critical issue with Sorts and the Memory issue has resolved itself.  Here you can see a short snippet of what this Metric means as well as the Baseline for the metric. You can easily see that the Row Sort Rate is higher than the baseline for this hour.  This would call for more investigate in DPA.

dpa95baseline.png

 

So the obvious next question is 'How I can download the DPA 10'?

 

For current customers, just log into the Customer Portal to download DPA 10.0.

If you want to try out DPA for the first time, download it from the SolarWinds website

 

What's next for DPA?  You can review our What We Are Working on post  What We Are Working On for DPA (Updated April 7, 2017)

SolarWinds Database Performance Analyzer (DPA) has always been the way to quickly get to the root cause of application performance problems. Now with DPA 9.1, we have extended the same great functionality to databases hosted in the Amazon cloud.  From the same screens you use today, you can monitor SQL Server and Oracle RDS databases just like any other database.  Even better, you can monitor EC2, RDS, and on-premise databases from the same installation of DPA.

 

Now you can easily provision a DPA instance from the Amazon Marketplace using the DPA AMI, you won’t have to wait for a box to be provisioned, you are the master of your DPA install.  Once provisioned, it is just a couple of clicks and you are collecting data through DPAs agentless architecture.

 

Often, I have heard that the biggest fear when moving a database into the cloud is that you can’t see what is going on outside the database (server, storage, and infrastructure). Although you can’t monitor the servers that the database is on, you can use DPA to troubleshoot if a performance issue is likely related to a resource, such as Storage, or if queries need to be tuned.

 

DPA is a tool that pulls back the curtains, at least enough to be able to pinpoint or even eliminate a particular area.  One of our customer’s favorite features to use when you are monitoring a database in the cloud is DPA’s ‘Storage IO’ feature that was introduced in DPA 9.0.   As in the case of Storage, you can see if there is a high latency or prove that the work is being done in memory. DPA can give you that confidence to move forward with troubleshooting performance issues.  Without DPA, RDS databases still have a bit of a black box feel to them.

 

DPA 9.1 can help ease your mind as you leverage the Amazon Cloud for your databases.

 

To see all the features of Database Performance Analyzer 9.1, see Database Performance Analyzer 9.1 Release Candidate is available now!!!!.

The Database Performance Analyzer team has been very hard at work to provide you with some great features that will not only help you with your current monitoring; Database Performance Analyzer is now in the Amazon cloud.  This RC for DPA 9.1 is now available in the Customer Portal to download for customers on active maintenance through March 31, 2015.  The RC can be installed on your production system.  For any questions or comments, we'd love to hear your feedback at Database Performance Analyzer RC

 

What are these New Features, you ask?

  • DPA is in the Amazon Cloud
    • Amazon Machine Image (AMI) for DPA
    • Monitor Amazon RDS instances for Oracle and SQL Server
  • New Features
    • Annotations
    • Updated Oracle Plans
    • Exclude SQL statements
    • 64 bit installation
  • Updated Licensing - everything is instance based


DPA Available in Amazon

Now if you are thinking of moving to the cloud, DPA can move with you.

    • Deploy DPA in less than 2 minutes and you are ready to start monitoring.
    • You can monitor database instances that are both on the cloud and on-premise.
    • Monitor MSSQL Server, Oracle, DB2, SAP ASE (formally Sybase) running on Amazon's EC2 instances as well!!

 

You will have to wait a bit to see and enjoy this feature, it won't be available initially for RC, stayed tuned for dates.

 

Register Amazon RDS instances

You can now register a RDS instance, SQL Server and Oracle, just like on-premise.

register.png

RDS is Amazons version of a DBaaS or database as a service.  Because it is a service, we have had to adjust DPA so we can collect as much as the same as we do with on-premise instances.  There are a few items not available, mostly around metrics and alerts.  You can always explore and add additional metrics through Custom Metrics and additional alerts through Custom Alerts.  See the Administrators guide for more detail.

 

Annotations for Database Events

Have you ever wanted to track performance before and after you added an index or deployed code?  Now you can!  Add comments directly in the timeline within DPA, providing an easy method log changes to  SQL code, database configuration, and server resources for future reference. You can choose to have these annotations included when you select to mail the screen making it easy to share your findings.

annotations.png


Detailed Oracle Plan Display

The new Oracle plan display from the Plan text tab enhances your insights into the inner workings of a plan including Predicates and an updated tree display.  You can see that there are magnifying glass icons on the screen.  When that icon is selected, you will see the associated predicates as well as the type of predicate (access or filter).

 

oracleplan.png

 

Exclude SQL Display from Trend Charts

We are happy to bring our number one customer requested enhancement to a feature in DPA! With the Exclude SQL feature, you can mask SQL statements from the month and day trend views, letting you focus on less significant statements in the vertical bar graphs, that were previously dwarfed by one or more statements that take significantly more time.

Now that runaway SQL won't dominate your trend screen all you have to do is select a SQL from the legend on the right of the graph

exclude.png

Click the check box 'Exclude this SQL from Performance-side Trend charts' and the graph is refreshed without that pesky SQL.

sqlpropertiesexclude.png

Once one SQL is excluded you will know by looking at the bottom of the trend graph and seeing 'There are SQL Statements that may have been excluded from this chart. View Excluded SQL'


viewexcludedsqlstatement.png


 

Default Install is 64 Bit Software

I am very happy to announce that DPA now defaults to a 64 bit installation.  Never fear, if you still need a 32 bit installer, you just need to contact our support team!

 

Updated Licensing

Many of you have had to go through new processes that in some cases were a bit odd and confusing before DPA 9.1. We have addressed all this confusion by fully going to instance based licensing in DPA 9.1.  You will need to go to your portal to download new keys for the RC and later for GA.  However, all these keys are now in the portal which means no more calls to support to help find the key(s).

 

So the obvious next question is 'How I can download the RC'?

 

To download the DPA 9.1 RC, just log into the Portal to get your DPA RC 9.1 bits and your RC license keys here.

 

Again, if you have any questions or just want to chat about how great this RC is, head over to Database Performance Analyzer RC area.

 

Thank you very much for your participation.  We are looking forward to hearing from you!!

 

What's next for DPA?  You can review our What We Are Working on post AND this Beta blog post on DPA with Orion integration work.

It is our extreme pleasure to announce Database Performance Analyzer (DPA) 9.0 is generally availability.

 

Below is a quick summary of the features, but check out these two blog posts for all the details:

 

Top features in this release include:

  • Storage I/O Analysis

o   Determine if your slow queries are related to storage

o    See latency and throughput of specific files and correlate these to SQL statements

o   Clear visibility of storage performance and its effect on SQL response time

  • Resource Metrics Baselines

o   Determine if your resources are behaving abnormally and correlate them to SQL statements.

  • SQL Statement Analysis and Advice

o    Get expert advice on specific SQL statements

  • Database File/Drive tab for SQL Server (with continued support for Oracle)

o   Use the File and Drive dimensions to isolate poor performance

  • Alerting Improvements

o   Resource Metric Alerts

o   Alerting Blackouts

  • New Version Support
    • MS SQL Server 2014
    • Oracle 12c (single-tenant)
    • DB2 10.5

 

 

Want more information on DPA and this release?


We are very excited to talk to you about this feature packed Beta of Database Performance Analyzer (DPA). Since there is so much in this Beta, we will be covering the features in multiple blog posts.  In this post I get the privilege of introducing.

If you are interested in being a part of the Beta program, please click the link below. The only requirement is that you must be an existing DPA/Ignite product owner under active maintenance.

DPA9.0Survey.png

 

WHAT IS DPA?

At this point you may be thinking, 'but what is this DPA of which you speak'.  Database Performance Analyzer (formerly Confio Ignite) is a database focused product that uses 'Response Time Analysis' to quickly decompose your queries and identify why they are slow - and then helps you fix them.  How does this fit in with the SolarWinds products you already own and love?  See this post to find out more detail: SQL Server Two Ways - SAM AppInsight for SQL and Database Performance Analyzer

Oh, and yes, we plan on adding Orion integration in future releases of the tool!  Check out the What we are working on now post.


Performance I/O - Drill Down Into I/O Bottlenecks at the File and Drive level

‘It’s slow’ is often all a DBA ever hears when there are performance problems in the database.  Slow queries can be attributed to many things in the environment.  DBAs in the past have always used wait events to detect I/O problems, now we have another wait to detect these I/O problems!!!  

Use CaseDisplay

Let's say it is a 'normal' day in the life as a DBA and Performance Tuning.  The customers have complained that the system 'Was Slow' on April 7th sometime in their nightly load, from 1am to 2am.  You pull up DPA to see if you see anything that jumps out at you drilling into this time period

pic1.png
However nothing jumps out at you, let's look at the wait events during this time frame. By clicking on the bar for the 1am to 2am time frame I can see that there is wait that is I/O related here (PAGEIOLATCH_SH, WRITELOG, LOGBUFFER, and PAGEIOLATCH_EX)pic2.png

Happily we can now dive further in with Performance I/O.  We now have another way to look at this data to let me know if this was an I/O Problem.


Note: From this screen or the main Trend screen you will see that we now have 'Storage I/O'.


At this point, you may be thinking, ‘How are the Top 15 Data Files ranked’. Well we have come up with an 'I/O Rating' that rates the data files for the instance. The I/O Rating behind the scenes of DPA does the same thing you would do when determining which data file should be looked at.

 

First, DPA will find the files that are in a 'Critical' or 'Warning' state, based on industry standards by default. Then for these files we perform a calculation based on the latency (either Read or Write depending on which tab you are on), the throughput and most importantly the duration the files have been in the 'Critical' or 'Warning' state. This calculation shows you the files you should review first.

 

If there are no files that show up here, that doesn't mean there was no I/O against your database, it just means that the I/O related activity was never in the 'Critical' or 'Warning' state

pic3.png
As an added troubleshooting feature, you can compare these files with what the wait is in the database outside of I/O Wait. This is important because you want to know what your aggregate I/O wait, however you also will want to know if this wait is the largest type of wait or if it is just a small percentage of wait (could we have high Memory/CPU wait, or high lock wait). In our example, over 90% of the wait occurring in this instance is I/O related wait.  Because of this, we will want to drill in further to get more details.pic4v2.png

Let's continue our troubleshooting for April 7th from 1am to 2am.  Just like with the Trending data, you can drill into the 'Storage I/O' data.  Click on April 7th and you will see that day's data, then as we did previously, click on the 1am bar and we will see the detailed hour graph.  As we can see, there is a lot of activity in the early morning hours. Specifically I see a lot of activity on the 'C:\..ignite_ignite_depas13.mdf'' file during our 1am to 2am time frame.

Select this file from the drop down list.

pic5v2.png

Once you select this file, NOW you have hit the MOTHER LOAD of data!!! Not only are there several ways to view I/O Performance for this file, we can also look at the SQL statements being run contributing to the I/O wait (this tab is highlighted on the graph), against this particular file, the Programs this SQL is being generated from, the DB Users that ran the SQL, and the Machines this SQL was sent from.

pic6.png

As you click through the different links at the top of the page,DPA gives you different ways to slice the data to assist in troubleshooting. For our example, we can see a spike that is out of the norm or 'Typical Hour' for our file.  This leads us to believe there may have in fact been an issue with this file.

 

Let's look a little further though.

pic7.png

I click on the 'Comparison over Files' link and I can see that in comparing my file, I see the average for the other files (both on the same drive and for the instance I am on) still have much lower latency.  Yep, it sure continues to point to my file is isolated with this high latency.

 

What if the problem is that I just had higher throughput, more records, than normal.  This could be a good thing, maybe more product is being sold!!!  Let's click on the last tab to find out.

pic8.png

Clicking on 'Throughput Analysis' I can see clearly, I need to talk to my Storage Admins.  My throughput is actually lower than at other times though my latency is high.  This indicates a Hardware issue vs a Software (more product clogging up the latency).  If throughput were much higher, we might surmise that the high I/O was due to more data.  Looks to me the problem happened right after 1am.

 

I can now confidently take my findings to my customers identifying when the problem occurred specifically.  I can then talk to my Storage Admins to figure out the best fix so that this peak is handled in the appropriate manner.

 

AND.. the BEST part is it just took a couple of clicks!!!

pic9.png

I/O Performance is a great feature, but WAIT, there’s more …..


Mass Registration Wizard - Quickly add all your databases into DPA

If you have a large environment, today you have to go through a tedious process to manually register each database instance. In this beta you can try out the Mass Registration Wizard which will make it much easier to load multiple instances into DPA.  What a great feature to use for testing the beta!!!    

Use CaseDisplay

For either Oracle or MS SQL Server Repositories, the steps are the same.  You go to Options and select the Mass Registration button which brings you to the initial screen in a 3 step process.

 

The templates you will need come with the product and are located where you have DPA installed <install directory>/iwc/tomcat/ignite_config/registration.

 

Just take this csv file, update it with your information and select to load it into your respository

pic9v2.PNG

When you select to load the file, DPA goes through an initial check to make sure all the fields are filled in properly and that we can connect with the credentials provided.

 

Click on the 'Register Valid Databases'

pic10v2.PNG

After clicking on the 'Register Valid Databases' DPA will go through the list and try to connect to the desired monitored instance and load the required information into the repository.

 

If there are instances that didn't get registered, DPA will show in this screen  a red 'Invalid' icon with detailed information on why the instance could not be registered.

 

If a monitored instance already exists, you will get a red 'Invalid' and a comment in the status column stating it is already registered

pic11v2.PNG

 


GUI Improvements - Its not always just about the look

Many of the screens you’ll see, and have seen in this blog, during the Beta process will just have a cleaner, crisper and much more SolarWinds-ier feel.  Yes, we are rebranding the product for SolarWinds, but this is more than skin deep - we have also spent a lot of time improving usability and the speed of the product.  We have also spent a lot of making GUI enhancements that improve usability and give it more modern aesthetic.

Is that all you say? Not by a longshot. There will be more blog posts coming to continue describing the new features of DPA including:

  • Resource Metrics Baselines
  • Alert Blackouts
  • Custom Metric Alerts.

 

So, stay at the edge of your seat, the edge of Thwack and STAY TUNED!!!

P.S. Don’t forget to sign up for the Beta

   DPA9.0Survey.png

Filter Blog

By date: By tag: