Vector search in OceanBase AI Database provides automatic performance optimization mechanisms for IVF indexes, enhancing search performance through cache management and periodic maintenance. This topic describes how to configure and monitor performance optimization tasks.
Notice
Currently, only IVF series indexes are supported.
Optimization mechanisms
Performance optimization for IVF series indexes includes two automatic tasks:
- Cache warming task: Regularly checks all IVF series indexes. If a cache corresponding to an index does not exist, it automatically triggers cache warming to load the index data into memory. Additionally, the cache is automatically warmed up when an IVF series index is created.
- Cache cleanup task: Regularly checks the caches of all IVF series indexes. If an index corresponding to a cache has been deleted, it automatically cleans up the invalid cache to free up memory resources. Additionally, the cache is automatically cleaned up when an IVF series index is deleted.
Configure the optimization execution cycle
The system supports customizing the execution time window for performance optimization tasks to avoid impacting performance during business peak hours.
Under the oceanbase database, set the execution cycle using the parameter vector_index_optimize_duty_time:
ALTER SYSTEM SET vector_index_optimize_duty_time='[23:00:00, 24:00:00]';
The configuration is as follows:
- The time format is
[start time, end time]. - The above configuration means the optimization task is executed only during the 23:00:00 to 24:00:00 period.
- Optimization tasks are not initiated during other periods to avoid affecting normal business operations.
Monitor optimization tasks
Vector indexes in OceanBase AI Database provide functionality to monitor optimization tasks:
- You can view tasks that are being executed or waiting to be executed through the DBA_OB_VECTOR_INDEX_TASKS view.
- You can view historical task records through the DBA_OB_VECTOR_INDEX_TASK_HISTORY view.
A specific usage example is as follows:
View the current task status
View tasks that are being executed or waiting to be executed through the
DBA_OB_VECTOR_INDEX_TASKSview:SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS;A sample result is as follows:
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+ | 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 | IVF_CACHE_LOAD | 1750671687770026 | 0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 | +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+ 1 row in setRelated task status descriptions:
PREPARE: The task is waiting to be executed.RUNNING: The task is in progress.FINISHED: The task has been completed.STANDBY: A manually triggered task is waiting for a node to claim and execute it.QUEUE: The task is queued in a multi-priority queue.
Related task type descriptions:
IVF_CACHE_LOAD: An IVF cluster center load task.IVF_CACHE_CLEAN: An IVF cluster center cleanup task.
View historical task records
Completed tasks (
STATUS = 'FINISHED') are automatically archived to the history table, regardless of success or failure, every 10 seconds. You can view historical records through theDBA_OB_VECTOR_INDEX_TASK_HISTORYview:-- Query the history of a specified task ID SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASK_HISTORY WHERE TASK_ID=2002281;Sample result:
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+ | 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 | IVF_CACHE_LOAD | 1750671687770026 | 0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 | +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+ 1 row in set
Cancel an optimization task
You can cancel a specified task using the following command.
-- The trace_id is obtained from the DBA_OB_VECTOR_INDEX_TASK_HISTORY view mentioned above.
ALTER SYSTEM CANCEL TASK <trace_id>;
Note
Tasks can only be canceled during their failed retry phase, and only by executing the ALTER SYSTEM CANCEL TASK command. If a background task gets stuck at a certain execution stage, it cannot be canceled using this command.
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 | PREPARE | IVF_CACHE_LOAD | 1750682301145225 | -1 | YAFF00B9E4D97-00063839E6BDDEE0-0-1 |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
1 row in set
-- Cancel Task
ALTER SYSTEM CANCEL TASK "YAFF00B9E4D97-00063839E6BDDEE0-0-1";
After a 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 | IVF_CACHE_LOAD | 1750682301145225 | -4072 | YAFF00B9E4D97-00063839E6BDDEE0-0-1 |
+----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
1 row in set