Optimize index performance

OceanBase Database provides an automatic performance optimization mechanism for IVF indexes, which improves search performance through cache management and regular maintenance. This topic describes how to configure and monitor performance optimization tasks.

Optimization mechanism

Performance optimization for IVF series indexes includes two automatic tasks:

1. Cache warming task: The system periodically checks all IVF series indexes. If the cache for an index does not exist, the system automatically triggers cache warming to load the index data into memory. Additionally, when an IVF series index is created, the system automatically warms up the cache.
2. Cache cleanup task: The system periodically checks the caches of all IVF series indexes. If the cache corresponds to an index that has been deleted, the system automatically cleans up the invalid cache to release memory resources. Additionally, when an IVF series index is deleted, the system automatically cleans up its cache.

Configure the optimization task execution window

You can customize the time window for performance optimization tasks to avoid running them during peak business hours.

In the oceanbase database, set the execution window using the vector_index_optimize_duty_time parameter:

ALTER SYSTEM SET vector_index_optimize_duty_time='[23:00:00, 24:00:00]';

Configuration details:

• The time format is [start time, end time].
• The above configuration means optimization tasks are executed only between 23:00:00 and 24:00:00.
• Optimization tasks are not triggered at other times, avoiding impact on normal business operations.

Monitor optimization tasks

OceanBase Database provides monitoring capabilities for optimization tasks:

• Query the DBA_OB_VECTOR_INDEX_TASKS view to check tasks that are running or pending.
• Query the DBA_OB_VECTOR_INDEX_TASK_HISTORY view to check historical task records.

Examples:

1. Check current task status

   Query the DBA_OB_VECTOR_INDEX_TASKS view to see tasks that are running or waiting to run:

   SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS;
   

   Example output:

   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   | TABLE_ID | TABLET_ID           | TASK_ID | START_TIME                 | MODIFY_TIME                | TRIGGER_TYPE | STATUS   | TASK_TYPE | TASK_SCN         | RET_CODE | TRACE_ID                           |
   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   |   500020 | 1152921504606846990 | 2002281 | 1970-08-23 17:10:23.174127 | 1970-08-23 17:10:23.174137 | USER         | FINISHED |         2 | 1750671687770026 |        0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 |
   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   1 row in set
   

   Task status:

   • STATUS = 0: PREPARE – waiting to execute.
   • STATUS = 1: RUNNING – currently executing.
   • STATUS = 3: FINISHED – completed.

   Task type:

   • TASK_TYPE = 2: IVF cache warming task.
   • TASK_TYPE = 3: IVF cache cleanup task.

2. Check historical task records

   Completed tasks (STATUS = 3) are automatically archived in the history table every 10 seconds, regardless of success. Query the DBA_OB_VECTOR_INDEX_TASKS_HISTORY view to check historical records:

   -- Query historical records for a specific task ID.
   SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS_HISTORY WHERE TASK_ID=2002281;
   

   Example output:

   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   | TABLE_ID | TABLET_ID           | TASK_ID | START_TIME                 | MODIFY_TIME                | TRIGGER_TYPE | STATUS   | TASK_TYPE | TASK_SCN         | RET_CODE | TRACE_ID                           |
   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   |   500020 | 1152921504606846990 | 2002281 | 1970-08-23 17:10:23.174127 | 1970-08-23 17:10:23.174137 | AUTO         | FINISHED |         2 | 1750671687770026 |        0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 |
   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   1 row in set
   

Cancel optimization tasks

You can cancel a specific task with the following command:

-- Obtain trace_id from the DBA_OB_VECTOR_INDEX_TASKS_HISTORY view.
ALTER SYSTEM CANCEL TASK <trace_id>;

Example:

-- Log in to the system and obtain the trace_id of the specified task
SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASK_HISTORY WHERE TASK_ID=2037736;
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
| TABLE_ID | TABLET_ID           | TASK_ID | START_TIME                 | MODIFY_TIME                | TRIGGER_TYPE | STATUS   | TASK_TYPE | TASK_SCN         | RET_CODE | TRACE_ID                           |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
|   500041 | 1152921504606847008 | 2037736 | 1970-08-23 17:10:23.203821 | 1970-08-23 17:10:23.203821 | USER         | PREPARED |         2 | 1750682301145225 |       -1 | YAFF00B9E4D97-00063839E6BDDEE0-0-1 |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
1 row in set

-- Cancel the task.
ALTER SYSTEM CANCEL TASK "YAFF00B9E4D97-00063839E6BDDEE0-0-1";

After the task is canceled, its status changes to CANCELLED:

-- Log in to the user database and query the task status.
SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASK_HISTORY;
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
| TABLE_ID | TABLET_ID           | TASK_ID | START_TIME                 | MODIFY_TIME                | TRIGGER_TYPE | STATUS   | TASK_TYPE | TASK_SCN         | RET_CODE | TRACE_ID                           |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
|   500041 | 1152921504606847008 | 2037736 | 1970-08-23 17:10:23.203821 | 1970-08-23 17:10:23.203821 | USER         | FINISHED |         2 | 1750682301145225 |    -4072 | YAFF00B9E4D97-00063839E6BDDEE0-0-1 |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
1 row in set

References

• Maintain indexes