Submit a ticketCall us

WebinarWebinar: A checklist for planning your Network Performance Monitor (NPM) upgrade

Are you ready for your next upgrade? To help you plan smoothly, join this webcast to learn more about, SolarWinds® Orion® Installer, SolarWinds Upgrade Advisor, Upgrades Guides, Training Videos, and other resources available. We’ll share key upgrade planning considerations, lessons learned from customers with practical advice from SolarWinds Product Experts. We’ll also give practical tips to identify the estimated time needed and resources, how to prepare the business and IT staff for changes, ways to plan for required system changes, and more.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > DPA Stats Poll Performance Recommendations

DPA Stats Poll Performance Recommendations

Updated January 15, 2018

Overview

Database Performance Analyzer (DPA) collects SQL statistics every 10 minutes for each monitored database instance to provide data such as executions and reads for each SQL statement. The "stats poll" is a multi-step process that involves querying the monitored database instance for raw statistics data and post-processing the data in the DPA Repository. Ideally, the entire process takes less than a couple of minutes, but for various reasons it can take longer than expected.

If a SolarWinds Support representative determines that the stats poll in your environment is running slowly, the rep can provide a link to a diagnostics page that lists potential issues. Refer to the following sections for background information and potential solutions for each issue:

Environment

  • DPA 12.0 and later

Query issue

The query to retrieve SQL statistics from the monitored instance is taking a long time. This is typically due to either:

  • A large number of statistics records retrieved from the monitored instance and sent across the network.
  • High network latency between DPA and the monitored instance. This is often the case when you are monitoring a cloud database instance from a DPA server running on premises (or vice versa).

Solution to consider

Enable Limited Stats in DPA for this monitored instance

Enabling Limited Stats will lower the number of queried records. This can help on slow networks by bringing fewer rows across the network. However, it can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries.

  1. Go to DPA's Advanced Options and click on the DB Instance Options tab.
  2. Select this monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Truncate issue

Each time the stats poll runs, a table in the repository is emptied to prepare for a new set of statistics records. By default, DPA truncates the table to empty it, and this operation is taking a long time.

The data kept in this table is temporary. It is used for intermediate calculations, and therefore does not need to be logged or backed up.

Solutions to consider

Lower the number of DPA permits

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

  1. On the DPA server, open the system.properties file.
  2. Uncomment or add a line like this:
    com.confio.idc.repositoryDataSourceSemaphore.permits=15
  3. Restart DPA.

Enable Limited Stats in DPA for this monitored instance

Enabling Limited Stats will lower the number of queried records. This can help on slow networks by bringing fewer rows across the network. However, it can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries.

  1. Go to DPA's Advanced Options and click on the DB Instance Options tab.
  2. Select this monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Force a Delete of the temp table

Truncate statements are typically very efficient, so it should not be common that a Delete runs faster. However, if the previous solutions do not resolve the issue, try deleting the temp table.

  1. Go to DPA's Advanced Options and click the System Options tab.
  2. Change the value of SQLSTATS_DELETE_FROM_TEMP_TABLES to true.
  3. Restart DPA.

Configure the repository

See other possible solutions in the Underpowered repository section.

Permit wait issue

Permits are a mechanism that DPA uses to limit simultaneous stats poll activity against the repository. The more permits you have, the more simultaneous activity can happen. The default permit count is 20.

Typically, each monitored instance's stats poll is either using the repository (in which case it has a permit), or is waiting to acquire a permit to use the repository. Significant time spent waiting for permits is a sign that the repository database cannot handle the amount of simultaneous activity that the stats polls are demanding.

Solutions to consider

Lower the number of DPA permits

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

  1. On the DPA server, open the system.properties file.
  2. Uncomment or add a line like this:
    com.confio.idc.repositoryDataSourceSemaphore.permits=15
  3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

  1. Go to DPA's Advanced Options and click the DB Instance Options tab.
  2. Select the monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository

See other possible solutions in the Underpowered repository section.

Insert issue

DPA is spending a significant amount of time inserting statistics into the DPA repository. Monitors take turns (based on permits) inserting batches of raw statistics records. Each monitor's raw statistics data is held in its own pair of tables (CONTSS1_<dbid> and CONTSS2_<dbid>).

Solutions to consider

Lower the number of DPA permits

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

  1. On the DPA server, open the system.properties file.
  2. Uncomment or add a line like this:
    com.confio.idc.repositoryDataSourceSemaphore.permits=15
  3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

  1. Go to DPA's Advanced Options and click the DB Instance Options tab.
  2. Select the monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository

See other possible solutions in the Underpowered repository section.

Diff issue

The statistics values kept by the monitored instance are counters that increase from the time the query first runs or when it re-enters the monitored instance's cache. To calculate that statistics during a period of time, DPA takes a delta of statistics samples taken at the beginning and end of that time period. In this case, the delta is taking a long time to run.

Solutions to consider

Lower the number of DPA permits

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

  1. On the DPA server, open the system.properties file.
  2. Uncomment or add a line like this:
    com.confio.idc.repositoryDataSourceSemaphore.permits=15
  3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

  1. Go to DPA's Advanced Options and click the DB Instance Options tab.
  2. Select the monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Configure the repository

See other possible solutions in the Underpowered repository section.

Underpowered repository

Stats polls can take a long time simply because the repository is underpowered. The solutions below offer a variety of ways to decrease stats poll load and to increase the power/efficiency of the repository configuration. Some overlap with solutions from other, more specific, issues.

Solutions to consider

Ensure that the repository has sufficient resources

  • Increase memory and CPU allocation to the repository.

  • Use faster drives for the repository.

  • Do not co-locate DPA and the repository. If the DPA application and the repository are running on the same server, put them on separate servers so they are not competing for the same computing resources.

  • If DPA and the repository are not co-located, make sure there is a fast network between them.

  • Limit or eliminate other activity on the repository instance. If other applications are using the repository's database instance, they may be competing with DPA for resources. If possible, separate out the repository into an instance on its own server.

  • For Oracle repositories, configure redo logs files. Make sure the redo log files are large enough, that there are enough of them, and they are not on same drives as data files. Look at disk device latencies.

  • Register the repository to see why it is slow. Make sure you set MONITOR_IGNITE_MONITORING so you can see DPA query activity.

Lower the number of DPA permits

On underpowered repositories, too many permits can essentially bottleneck the repository instance because DPA is asking it to do too much at one time.

  1. On the DPA server, open the system.properties file.
  2. Uncomment or add a line like this:
    com.confio.idc.repositoryDataSourceSemaphore.permits=15
  3. Restart DPA.

Enable Limited Stats on the monitored instances with the most stats rows

The goal here is to decrease the overall stats poll load against the repository. Enabling Limited Stats for a monitored instance will lower the number of queried records, which will in turn limit the processing that the repository needs to do. Note, however, that this can result in slower query time on underpowered monitored instances because the limited stats query makes the monitored instance determine the top N queries. If this happens for a given monitored instance, then you might want to consider disabling Limited Stats for that monitored instance.

For each monitored instance that is processing a lot of stats rows:

  1. Go to DPA's Advanced Options and click the DB Instance Options tab.
  2. Select the monitored instance.
  3. Change the value of SQLSTATS_LIMITED to true.
  4. (Optional) Change the value of SQLSTATS_LIMITED_ROWS to alter the number of statistics rows to retrieve from the monitored instance.

Missing statistics notification

You will see this notification if, due to the issue(s) that caused the Stats Poll to take a long time, there was not enough time to insert and process all statistics records in the repository.

  • This results in some queries with no statistics for some 10 minute intervals.
  • You can also see some queries with inaccurate statistics values, sometimes lower than expected and sometimes higher than expected.

It is important to act on some of the solutions suggested to resolve the underlying issues so that the stats polls complete quickly, enabling them to collect all statistics information.

 

Last modified

Tags

Classifications

Public