Documentation forDatabase Performance Analyzer

Move the Find SQL indexes

DPA indexes the SQL statements that it monitors on each database instance. The Find SQL feature uses this indexed data. By default, the indexes are created in the DPA directory.

DPA keeps indexed data for 30 days. In a large or busy DPA deployment, the Find SQL indexes can become large. In some cases, large indexes can affect DPA performance. If the Find SQL indexes are in the default location and the storage requirement reaches 5 GB, DPA displays a message. If you receive this message, you have the following options:

  • If the DPA server has enough storage capacity and DPA is not experiencing performance problems, you can increase the recommended storage limit. To do this, edit the advanced Support option FIND_SQL_INDEX_SIZE_LIMIT.

  • Move the indexes out of the DPA directory to a location with enough storage capacity for your indexed data.

See the following sections:

Space requirements for the Find SQL indexes

The amount of storage space required for the indexed search data is determined by the number of monitored database instances with Find SQL enabled. Index files for one instance can take up to 300 MB. So, for example, if you are monitoring 120 instances, reserve at least 36 GB to provide adequate disk space for the Find SQL indexes:

120 x 0.3 GB = 36 GB

Move the indexes to a custom location

  1. Stop DPA.
  2. Create a folder for the indexes in the new location, and set the privileges to allow the DPA service to read and write to the folder.
  3. Configure DPA to write index data to this folder:

    1. Open the system.properties file in a text editor. This file is located in the following directory:

      DPA-install-dir\iwc\tomcat\ignite_config\idc

      In Windows, the default location is:

      C:\Program Files\SolarWinds\DPA\iwc\tomcat\ignite_config\idc

      In a Linux-based OS, the default location is:

      /home/solarwinds/dpa_V_v/DPA/iwc/tomcat/ignite_config/idc

    2. Add one of the following lines:

      • If you are going to move all indexes to the custom location, enter:

        com.solarwinds.dpa.findSql.index.location=your_custom_location

      • If you are going to move only indexes for specific database instances to the custom location, enter a line for each database instance, where db_id is the database ID:

        com.solarwinds.dpa.findSql.index.location.db_id=your_custom_location

        For example:

        com.solarwinds.dpa.findSql.index.location.5=/home/dpa/custom_location

  4. Move indexes from the default location to your custom location. The default location is:

    DPA-install-dir/iwc/tomcat/ignite_config/lucene-index/RepoID/db_id

    where:

    • RepoID is the DPA repository ID.

      If you have more than one folder in this path (for example, because you changed the database used as the repository), open the DPA-Install-Dir/iwc/tomcat/ignite_config/idc/repo.properties file. The value of the repo.guid.hash property is the currently used repository ID.

    • db_id is the database ID.

    An example of the command to move all indexes is:

    mv DPA-install-dir/iwc/tomcat/ignite_config/lucene-index/ /home/dpa/custom_location

    An example of the command to move an index for a specific database instance is:

    mv DPA-install-dir/iwc/tomcat/ignite_config/lucene-index/<RepoID>/5 /home/dpa/custom_location/RepoID/

  5. Start DPA.

Troubleshooting tip

If you followed the instructions to move the Find SQL indexes but DPA still displays a yellow banner with the message about the index size:

  1. Verify that the indexes were moved to the custom location and that the system.properties file has the correct location.

  2. Check the default index location in the DPA directory and verify that the indexes were removed. The indexes might have been copied instead of being moved. If the custom location is configured and the indexes were copied there, remove the index folders from the default location.

    For example, if the index for the database instance with the ID of 5 is configured in system.properties, remove the following folder:

    DPA-install-dir/iwc/tomcat/ignite_config/lucene-index/RepoID/5

    If all indexes are configured in system.properties, remove the following folder:

    DPA-install-dir/iwc/tomcat/ignite_config/lucene-index