How to Improve and Optimize Controller Database Performance

Note: 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 readoptimized. As a result, the primary key changes as follows:

From To
ts_min, node/tier/app, metric_id metric_id, node/tier/app, ts_min

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.
    Note: 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.Controller pageThe 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: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.
    CODE 
    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;
Note: 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:Controller page

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


  1. Log in to the primary Controller host and enter:
    CODE 
    cd <controller_home>/bin directory
  2. Log in to the secondary Controller database and enter:
    CODE 
    ./controller.sh login-db
  3. Enter:
    CODE 
    SHOW SLAVE STATUS\G;
    This results in the following output:
    CODE 
    Seconds_Behind_Master: $Number_Of_Seconds_Behind_Master
    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: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.
    CODE 
    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;
    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: Controller page

Note: 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:

Errors or Conditions User Action
Job failed; Database replication is broken message displays. Re-establish database replication incrementally, then finalize replication.
Ran out of disk space while the database optimization job was running, and job stops processing. Free up disk space and restart database optimization job.