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.