How to Improve and Optimize Controller Database Performance

The following procedure only applies to Controllers that have been upgraded from version 4.2.8 or earlier, to a later version. In such cases, after you upgrade to later version, you may experience performance issues with the Controller database. However, for all new Controller installations using version 4.2.9 or later, the metric data tables are optimized.

To improve database performance when querying metrics, the primary key used by the metric data tables is read optimized. As a result, the primary key changes as follows:

How to Run Database Optimization

You can use the Enterprise Console to run a database optimization job to optimize your database performance. To use this feature:

1. Upgrade your Enterprise Console to the latest version.

2. Upgrade your Controller to the latest version.

3. After the upgrade has completed, for any database table that can be optimized, you can run the database optimization job from the Enterprise Console.

   

   No downtime is required and the database optimization job runs automatically.

   Select Start Database Optimization from the Controller page to start a process that runs in the background on your primary Controller host. 

   
   The process performs several pre-checks to determine if there is enough disk space, and if any other database optimization process is running. The amount of disk space required is determined by the size of the tables to optimize. Based on the amount of Controller data, the database optimization job may take several hours to several days to complete.

4. Once all of the tables have been optimized successfully, the database optimization process completes and no longer displays on the page. To verify that all tables have been optimized, enter and run the following query:

   cd <controller_home>/bin directory
   ./controller.sh login-db
   	mysql>SELECT table_name
   	FROM   information_schema.key_column_usage
   	WHERE  table_name LIKE 'metricdata%'
        AND table_name != 'metricdata_min'
        AND table_name != 'metricdata_min_agg'
        AND column_name = 'ts_min'
        AND ordinal_position = 1; 

   CODE

   If the query returns any results, then those tables have not been optimized.
   If the query returns zero records, then all of the tables were optimized successfully. 

The database optimization job is supported on Linux OS only.

How to Run Database Optimization on a High Availability (HA) Controller Pair

Before you run database optimization on a HA Controller pair, you must ensure that the Controller database replication is in a healthy state.

• If both of the Controllers are onboarded into Enterprise Console, review the Controller page and note the following fields:
  

• If one of the Controllers is managed by HA toolkit (HATK):

1. 1. Log in to the primary Controller host and enter:
      

      cd <controller_home>/bin directory

      CODE

   2.  Log in to the secondary Controller database and enter:

      ./controller.sh login-db

      CODE

   3.  Enter:

      SHOW SLAVE STATUS\G;

      CODE

      This results in the following output:
      

      Seconds_Behind_Master: $Number_Of_Seconds_Behind_Master

      CODE

      If a non-zero number displays the output for this test, wait until the number changes to zero.

   4. After you ensure that replication is working as expected, you can run the database optimization job from the Enterprise Console. Select Start Database Optimization from the Controller page to start a process that runs in the background on your primary Controller host. 
      The process performs several pre-checks to determine if there is enough disk space, and if any other database optimization process is running. The amount of disk space required is determined by the size of the tables to optimize. Based on the amount of Controller data, the database optimization job may take several hours to several days to complete.

   5. Once all of the tables have been optimized successfully, the database optimization process completes and no longer displays on the page. To verify that all tables have been optimized, enter and run the following query:

      cd <controller_home>/bin directory
      ./controller.sh login-db
      	mysql>SELECT table_name
      	FROM   information_schema.key_column_usage
      	WHERE  table_name LIKE 'metricdata%'
           AND table_name != 'metricdata_min'
           AND table_name != 'metricdata_min_agg'
           AND column_name = 'ts_min'
           AND ordinal_position = 1; 

      CODE

      If the query returns any results, then those tables have not been optimized.
      If the query returns zero records, then all of the tables were optimized successfully.

How to Stop Database Optimization

After the database optimization job has completed successfully, you can stop the process. From the Enterprise Console, select Stop Database Optimization from the Controller page: 

You may need to stop the database optimization process if it is using too many resources and you notice a performance impact on the Controller, or if you decide to reschedule the process to run at a later date.

Troubleshooting Database Optimization

The following table describes possible conditions that may cause errors to occur and actions to take to mitigate them: