SolarWinds Database Performance Analyzer (DPA) enables deep visibility into database performance and expert advice for performance optimization and tuning. The software is suitable for database administrators (DBAs), developers, system admins and DBA managers. Teams can collaborate in the system to monitor performance with read-only options to maintain security. Anomaly detection powered by machine learning gets smarter over time to help identify the bottlenecks slowing down the applications.
Database Performance Analyzer enables users to monitor databases irrespective of their environment including on-premises (Oracle, SQl server, DB2, MySQL, and SAP SE) and cloud (virtual machine offerings and Database-as-a-Service). Users can utilize SQL response time as a key metric for contrasting performance between platforms. The system uses multi-dimensional analysis for analyzing and tuning AWS, EC2® instance performance, and Amazon Relational Database Service (RDS). Down-to-the-second data collection with both real-time and historical analysis allows you to pinpoint problems to find the root cause of performance issues.
Features
Database Anomaly Detection Powered by Machine Learning
Learn Wait Behavior Automatically
Extend Anomaly Detection Beyond Spikes
Alert on Significant Behavior Changes
Database Monitoring Tools
Deliver critical metrics and automated alerts to better manage database management system (DBMS) performance
To fully understand DBMS, it’s important to understand the definition of a database management system. For data-driven companies, a DBMS is the preferred solution for mission-critical applications.
There are several types of DBMS software, each with their own way of structuring and using data:
-
- Relational databases refer to data organized in tables in which there are relationships between tables. Querying occurs in Structured Query Language, or SQL. Examples of RDBMSs include Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.
-
- NoSQL databases are schema-free. This form of database allows more data variety than RDBMSs. Examples include Cassandra, MongoDB, and Oracle NoSQL.
-
- Columnar databases are a useful form for data warehouses with vast amounts of similar data. This data can be highly compressed.
-
- Cloud-based databases refers to either PaaS/DBaaS solutions such as Azure SQL Database, Amazon RDS, and Aurora. Cloud hosted or IaaS examples include SQL Server in Azure VM and Amazon EC2.
Popular DBMS types include:
-
- MySQL
-
- Oracle
-
- Microsoft SQL Server
-
- SAP ASE
-
- IBM DB2 LUW
-
- Aurora
-
- MariaDB
-
- PostgreSQL
There are also three types of database architecture:
-
- One-tier architecture is where the client and server reside on the same machine as the database. This occurs when you install a database directly onto your system, typically for SQL query practice.
- Two-tier architecture is where data is stored on a server and clients (like PCs) run the presentation layer. The DBMS is not directly exposed to the end user but can be called by an API.
- Three-tier architecture is where the application layer exists between the database and the client, so a server controls the user requests and DBMS responses based on functional logic and rules. This is the most common architecture for web applications.
The type of DBMS and architecture of your database environment is important because they can affect your ability to scale up effectively, remove redundancies, allow multi-user access, and how you handle complicated transactions. Not every organization recognizes its DBMS—whether MySQL, Oracle, or another program—may require performance management. In fact, poor database performance and slow response times can lead to high costs for an organization. Manual troubleshooting can require hours or days of admin time to resolve an issue, while customers and employees may be affected by the failure in business function.
Database performance management software is designed to help admins more easily troubleshoot and resolve DBMS performance issues by monitoring performance and providing root-cause analysis of your database using multi-dimensional views to answer the who, what, when, where, and why of performance issues. These database monitoring tools can also help you use best practice methods to tune your databases and SQL queries to improve database performance.
Database Performance Tuning to Improve DBMS Performance
Use response time analysis to make database tuning more precise
Response time analysis is a pragmatic approach to tuning and optimizing database performance, allowing users to more easily identify issues and deliver measurable results. With response time analysis, you can optimize database tuning in your DBMS by identifying bottlenecks, pinpointing root causes, and prioritizing actions based on the impact poor database performance has on end users.
SolarWinds® Database Performance Analyzer (DPA) is built to collate SQL statement data every second and can help you identify which SQL queries to focus on.
Isolate and remediate poor performing SQL statements with database tuning in SQL Server
SolarWinds DPA includes tools and features specially designed to handle database tuning in SQL Server and offer insights into critical metrics.
Intuitive graphs in DPA are built to display poor performing SQL statements, application wait times, and specific wait types/events that could be causing bottlenecks. You can also click on sections of the graph to drill down on specific issues to troubleshoot quickly.
Take database tuning to the next level with table tuning advisors
DPA’s table tuning advisor is designed to make it easier to jump into complex database tuning by collating historical data and presenting the information in bar graphs.
The tuning advisors in DPA can help point DBAs toward problems in need of immediate focus by providing clear, actionable advice. You can also leverage DPA’s alarms to drill down into detailed data surrounding an issue in real time.
Follow database performance tuning steps to beat roadblocks
The first step in database performance tuning is locating the problem. With SolarWinds DPA database anomaly detection feature powered by machine learning, you can analyze your instance wait behavior profile to more easily identify what needs fixing. You can then leverage the SQL tuning advisors to analyze existing SQL statements and gain recommendations on how to adjust queries.
By following the recommendations provided by DPA, you can better troubleshoot database performance tuning issues like bad SQL or poor connection management.
Monitor and Improve SQL Server Performance on Microsoft Azure Virtual Machines and SQL Databases
Find what’s bad in your database environment
Analyze performance for root cause
Optimize your databases
Table tuning best practices
Database Maintenance Software for SQL and MySQL
SQL query optimization
Response time analysis
Unified dashboard
Actionable advice
Database Maintenance Automation
Benefits
- Easy to install and quick time-to-value, low overhead of 1% or less
- Goes beyond problem analysis with expert advice with table tuning and query advisors
- Get the complete picture of all database instance resources spanning CPU to storage I/O
- Granularity to drill down to even the SQL Text level
- Create custom reports and automated email alerts
- Integrate into your DevOps automation routine with RESTful API
- Correlate resource metrics of hardware constraint impacts on end users
- Scalability to support large and/or dynamic environments
- End-to-end visibility with SolarWinds Orion® Platform, Perfstack™, and Appstack™ integration
New features and improvements in DPA
Release date: November 5, 2019
Last updated: April 1, 2020
DPA 2019.4 offers new features and improvements compared to previous releases of DPA.
Custom email templates for alert notifications
When an alert is triggered, DPA sends an email to notify the designated recipients. DPA 2019.4 introduces custom templates that you can use to tailor the contents of alert notification emails. You can create multiple custom email templates for different types of alerts.
Creating a custom email template requires no coding. Specify the email subject line and content by dragging and dropping variables and by entering static text.
After you create one or more custom email templates, you can designate one of your custom templates as the default template.
Additions to the REST API
The DPA REST API now includes additional endpoints that you can use to:
- Retrieve, update, and reset values for DPA advanced options.
- Retrieve and manage custom alert email templates.
- Retrieve alert groups and assign or remove database instances.
- Retrieve alerts and alert statuses, assign or remove database instances, and assign or remove an email template.
- Create and manage custom properties that can be used in custom email templates.
- Create and manage database groups.
- Change the logging level for the DPA REST API.
- Get information about vSphere servers and VMware events.
Table tuning best practices
When DPA generates a table tuning advisor, it evaluates the table and its indexes against a set of best practices. The table tuning advisor reports any violations, and DPA provides recommendations for resolving violations.
You can use DPA’s advanced configuration options to customize the best practice checks for your environment.
VM co-stop metrics
For database instances that run on a virtual machine (VM), the Resources tab includes two new CPU metrics to track co-stop wait time. Co-stop wait time occurs on VMs configured to use multiple virtual CPUs (vCPUs). It measures the time that the VM is ready to execute but has to wait on vCPU resources to be freed from other VMs contending for those vCPUs.
DPA collects VM metrics only if you register the VM for monitoring. Monitoring a VM requires a VM license.
DPA provides the following co-stop metric resources:
- VM Total Co-Stop Time is the total amount of time (in milliseconds) that the VM was ready to execute but had to wait.
- VM Co-Stop is the percentage of time that the VM was ready to execute but had to wait.
If SQL statements are experiencing high CPU/Memory waits, check the co-stop metrics. If they are not near 0, co-stop could be contributing to the CPU/Memory waits. To reduce co-stop, you can:
- Decrease the number of vCPUs on the VM.
- Add additional CPUs to the pool available to the VMs.
- Use vMotion to migrate other VMs to a different host to reduce contention.
Additional VM information on the Query Details page
For database instances that run on a VM, resource metrics charts on the Query Details page now show the following additional information:
- VMware events are shown as annotations. Hover over a dot at the top of the chart to see the events.
- When a VM is migrated to another host, the chart lines distinguish between the hosts.
New version numbering system
DPA now uses same version numbering system that is used by SolarWinds Orion Platform products. The version number consists of the four-digit year of the release followed by the quarter of the release. If there is a service release, it appears after the quarter:
YYYY.Q.SR
Changes to system requirements
DPA 2019.4 adds support for the following versions:
Monitored database instances |
|
Repository database versions |
|
DPA 2019.4 removes support for the following versions:
Monitored database instances |
|
Repository database version |
|
New features and improvements in DPAIM 2019.4
Release date: November 5, 2019
New version numbering for DPAIM and other Orion Platform products
All Orion Platform product version numbers now consist of the four-digit year in which they were released, followed by the quarter of release. If there is a service release, it appears after the quarter.
[YYYY.Q.SR]
It’s rather arbitrary that 6.9 is the Server & Application Monitor (SAM) version compatible with Network Performance Monitor (NPM) 12.5. Rather than require users have a Ph.D. in SolarWinds Orion Platform product module versioning, wouldn’t it be easier if those product modules compatible with each other all shared the same version number? Then it would be downright simple to identify IP Address Manager vX.XX wasn’t compatible with User Device Tracker vY.YY or Network Configuration Manager vZ.ZZ.
Simplifying and consolidating our product module versioning is precisely what we aim to do in our next Orion Platform module releases. As you can imagine, this might come as a big surprise to many, which is why we’ve decided to notify the community in advance.
New releases for every Orion Platform product module going forward will now use the same versioning as the Orion Platform itself. This means the next release of Network Performance Monitor will not be v12.6 or v13.0, nor will any of the other Orion Platform product modules bear a resemblance to their current versioning. Instead, Orion Platform product module versions will be the four-digit year in which they were released, followed by the quarter of release. If there is a Service Release for a given module, it will appear in the third position following the quarter.
[YYYY.Q.SR]
If this all seems a bit confusing, fret not. You’re probably already familiar with this versioning, as it’s been the basis of the Orion Platform version for nearly a decade. This is also the same versioning used for Network Automation Manager.
Orion Platform 2019.4 offers new features and improvements compared to previous releases of Orion Platform.
Native SolarWinds Service Desk Integration
Generate Service Desk incidents from Orion Alerts.
Orion Maps enhancements
Entity Library enhancements
- Filter and refine your entity list based on any property.
- Bulk-select entities to add them to the canvas.
- Quickly identify contextual relationships through the entity library without leaving the editor.
Bulk Administration
- Multi-select from the canvas to move or delete multiple objects in groups.
- Undo and redo options within the Editor.
Custom Images
- Add custom images and backgrounds to enhance the map.
Manual Topology Connections
- Define topology between any two entities directly from the Map Editor.
Customizable map refresh rate
From the Advanced Configuration Settings, specify the map refresh rate – in minutes – for the Orion Maps Viewer and Widgets.
Installation improvements
In-Product evaluations
Add or evaluate additional Orion Platform products from within the Orion Web Console.
Install new products for evaluation even if you are not ready to upgrade your existing Orion Platform products to the latest version. Compatible versions of the new products are installed.
Microsoft Azure
- Deploy the Orion Platform in the cloud using Azure SQL Database managed instance as an alternative to MS SQL.
- Deploy the Orion Platform directly from Azure Marketplace
.NET 4.8 support
- All Orion Platform products now use .NET 4.8.
- .NET 4.8 is deployed automatically to all Orion Agents for Windows after the upgrade.
- All Windows Agent plugins for supported operating systems were migrated.
Make sure that the operating system of your Orion Platform supports .NET 4.8. See .NET Framework system requirements (© 2019 Microsoft, available at https://dotnet.microsoft.com, obtained on October 3, 2019).
If your Orion Platform runs on an operating system that does not support .NET 4.8, consider upgrading your environment to be able to use the new features provided by Orion Platform 2019.4.
Other improvements
- Website performance improvements
- Scalability Engines Installer download performance improvements
- Legacy Syslog & Traps replacement: Log Viewer replaces legacy Syslog & Traps with an instant evaluation of Log Analyzer
- Updated and improved localization of Orion Platform product UI for German and Japanese.
- Versioning is now consistent across all Orion Platform products; products follow the Orion Platform versioning convention.
- Orion Platform out-of-the-box reports were migrated to web-based Report Manager
- Orion SDK enhancements: Automate ‘List Resources’ and import results
- Review the list of newly supported devices
System Requirements
DPAIM compatibility
DPA 2019.4 is compatible with DPAIM 11.1 or later.
Port requirements
Review and open ports on the DPA server to support communication to and from DPA.
No additional ports are required for DPA Central.
Port | Protocol | Service or Process | Direction | Encryption | Description |
---|---|---|---|---|---|
8123 | HTTP | Windows: Ignite PI Service
Linux: java/tomcat |
Inbound Outbound |
Default HTTP port for web server | |
8124 | HTTPS | Windows: Ignite PI Service
Linux: java/tomcat |
Inbound Outbound |
TLS 1.0 TLS 1.1 TLS 1.2 |
Default HTTPS port for web server |
8127 | TCP | Windows: Ignite PI Service
Linux: java/tomcat |
Internal | Internal Tomcat shutdown port | |
80 | HTTP | Windows: Ignite PI Service
Linux: java/tomcat |
Inbound Outbound |
Default HTTP port for web server (Amazon Machine Images only) | |
443 | HTTPS | Windows: Ignite PI Service
Linux: java/tomcat |
Inbound Outbound |
TLS 1.0 TLS 1.1 TLS 1.2 |
Default HTTPS port for web server (Amazon Machine Images only) |
DPA server requirements
You can install SolarWinds DPA on any physical or virtual Windows or Linux server that supports the Java Runtime Environment (JRE) version 11.
You can also launch DPA in the cloud:
- In the Amazon Web Services (AWS) Marketplace from an Amazon Machine Image (AMI).
- The AMI contains a DPA server on Linux with no repository.
- Subscription licensing is available.
- In the Azure Marketplace.
- The DPA virtual machine contains a DPA server on Windows and a built-in Microsoft SQL Server database instance configured as the DPA repository.
- Individual licenses must be purchased.
Self-managed DPA server requirements
The CPU, RAM, and disk space requirements depend on the number of database instances you plan to monitor.
If you register VMware, increase the CPU and RAM requirements by 50%.
These are estimates. They are based on testing done with an average of 2-3 active sessions per monitored instance. If you are monitoring busy instances with an average of more than 2-3 active sessions, please adjust accordingly.
Hardware / Software | 1 – 20 Monitored DB Instances | 21 – 50 Monitored DB Instances | 51 – 100 Monitored DB Instances | 101 – 250 Monitored DB Instances* |
---|---|---|---|---|
CPUs | 1 | 2 | 4 | 4 |
RAM dedicated to DPA | 1 GB | 2 GB | 4 GB | 8 GB |
Disk space | 2 GB minimum 4 GB recommended |
|||
Operating System |
|
|||
Operating System Architecture | 64-bit | |||
Character sets | To support a multibyte language, the DPA server, repository, and monitored instances must be configured with the same character set. |
* If you plan to monitor more than 250 database instances, consider using more than one DPA server and linking the servers together.
Requirements for an AWS DPA server
The minimum required AWS instance type for the DPA server AMI is t2.medium. Smaller instance types are not supported.
A t2.medium size instance is typically powerful enough to monitor 20 database instances. You might need a larger instance type to reliably monitor more than 20 database instances.
Requirements for an Azure DPA server
SolarWinds recommends two or more database throughput units (DTUs) per monitored database instance. See the Azure SQL Database DTU Calculator for more information.
The minimum required Azure tier is standard s0. DPA repository database requirements
The repository database stores the data collected by DPA. A supported database instance must be installed on the database server.
Do not host the repository on a database instance that you plan to monitor, because this affects the performance of that instance.
Supported database versions
Database | Edition | Version |
---|---|---|
Microsoft SQL Server |
|
The latest Service Pack is supported unless otherwise noted. |
Azure SQL | Standard Service Tier or higher | V12 |
MySQL |
|
Note: MySQL 8.0 is not supported for use as a DPA repository. |
Oracle |
|
|
Notes:
- Although DPA will work with the Express editions of Oracle and SQL Server, SolarWinds does not officially support these editions for the repository because of the database size limits. If you need a free database for an evaluation, SolarWinds recommends using a MySQL database.
- You can host a self-managed Oracle, SQL Server, or MySQL database on Amazon EC2 to use as your repository.
- If you choose Azure SQL as your repository, SolarWinds recommends two or more database throughput units (DTU) per monitored database instance.
Self-managed repository database server requirements
If you install DPA on the same server as the repository database, the server must meet these requirements in addition to the DPA server requirements. If you register VMware, increase the CPU and RAM requirements by 50%.
These are estimates. They are based on testing done with an average of 2-3 active sessions per monitored instance. If you are monitoring busy instances with an average of more than 2-3 active sessions, please adjust accordingly.
Hardware / Software | 1 – 20 Monitored DB Instances | 21 – 50 Monitored DB Instances | 51 – 100 Monitored DB Instances | 101 – 250 Monitored DB Instances |
---|---|---|---|---|
CPUs | 2 | 2 | 4 | 4 |
Database cache available for DPA | 4 GB | 8 GB | 8 GB | 16 GB |
Disk space | The amount of disk space your repository uses is determined by the number of database instances you are monitoring and the activity level of each instance:
Example: You are monitoring five low, three medium, and two high activity database instances. (5 × 1 GB) + (3 × 3 GB) + (2 × 5 GB) = 24 GB Reserve at least 24 GB to provide adequate disk space for this repository database. |
Repository scalability depends on many things, including the database vendor and configuration, the specifications of the repository server, other activity on the repository server, and the activity levels of the monitored database instances.
Requirements for an AWS DPA repository database server
The following RDS instance types are recommended for AWS deployments.
Hardware / Software | 1 – 20 Monitored DB Instances | 21 – 50 Monitored DB Instances | 51 – 100 Monitored DB Instances | 101 – 250 Monitored DB Instances |
---|---|---|---|---|
RDS instance type | db.m5.large | db.m5.large | db.m5.xlarge | db.m5.xlarge |
CPUs | 2 | 2 | 4 | 4 |
Database cache available for DPA | 8 GB | 8 GB | 16 GB | 16 GB |
Required administrator credentials
You must know the following credentials for the database instance hosting your DPA repository.
Repository database type | Credentials |
---|---|
SQL Server | SYSADMIN |
Oracle | database administrator (DBA) |
MySQL | repository administrator
Alternatively, you can:
|
Azure SQL Database | repository administrator
Alternatively, you can:
|
Virtual environment requirements
In a virtual environment, DPA can remotely connect to the following to monitor the virtual environment that virtualized database instances are running on. The virtualized database instances must be registered separately from the virtual environment.
Software | Version |
---|---|
VMware vCenter Server |
|
VMware ESX/ESXi Host |
|
Web browsers
You can use the following browsers to access the DPA web interface:
You can use the following browsers to access the DPA web interface:
- Microsoft Edge
- Microsoft Internet Explorer 11
Do not use IE compatibility view. It can cause DPA to function incorrectly.
- Mozilla Firefox: latest stable version
- Google Chrome: latest stable version
MySQL requirements
SolarWinds recommends the following settings to optimize reporting capabilities for a MySQL database instance.
MySQL Performance Schema
The Performance Schema monitors server events and collects performance data. If the Performance Schema is not enabled, DPA provides limited data. Monitoring with the Performance Schema disabled excludes the following data:
- All instrumented wait events
- All wait operations
- All file wait time, broken out by file
- All object wait time, broken out by index and table
- SQL statistics
- Performance-schema dependent alerts
The Performance Schema must be enabled at server startup. In MySQL versions 5.6.6 and later, the Performance Schema is enabled by default.
Global Instrumentation and Thread Instrumentation
Global Instrumentation and Thread Instrumentation must be enabled in the Performance Schema configuration. Disabling these instruments has the same effect as disabling the Performance Schema.
By default, DPA enables these instruments in the configuration. However, if you select the Leave As Is option for Performance Schema setup, you must verify that Global Instrumentation and Thread Instrumentation are enabled in the existing Performance Schema configuration.
show_compatibility_56 system variable
If the monitored database instance is MySQL 5.7.6 or later, SolarWinds recommends turning on the show_compatibility_56
system variable. If this variable is on, DPA can collect data for all metrics.
If this variable is off and the Performance Schema is enabled, DPA cannot collect data for the following metrics:
- Selects
- Inserts
- Updates
- Deletes
Java requirements
DPA requires JDK 13 on the DPA server, and DPA ships with this version of Java for Windows and Linux. If you are installing DPA on a supported operating system, no action is required.
For unsupported operating systems, you must ensure that JDK 13 is installed on the DPA server. If you need to upgrade the JDK:
- Download and install JDK 13.
- Remove old Java information by deleting the
cat.txt
andcat.end
files from the following directory:<DPA_Home>/iwc/tomcat/ignite_config/
- At a command line, go to the DPA installation directory.
- Enter the following command:
./startup.sh