Optimize the performance of vector search

OceanBase Database provides an automatic performance optimization mechanism for IVF indexes. It improves search performance by managing cache and performing regular maintenance. This topic describes how to configure and monitor performance optimization tasks.

Optimization mechanism

The performance optimization of IVF series indexes includes two automatic tasks:

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

Configure the execution cycle of optimization tasks

The system allows you to customize the time window for performance optimization tasks to avoid executing them during peak business hours, which could impact performance.

In the oceanbase database, you can set the execution cycle using the vector_index_optimize_duty_time parameter:

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

The configuration details are as follows:

• The time format is [start time, end time].
• The above configuration specifies that optimization tasks will only be executed from 23:00:00 to 24:00:00.
• Optimization tasks will not be initiated during other time periods to avoid affecting normal business operations.

Monitor optimization tasks

OceanBase Database provides the following features for monitoring optimization tasks:

• You can query the DBA_OB_VECTOR_INDEX_TASKS view to obtain information about tasks that are being executed or waiting to be executed.
• You can query the DBA_OB_VECTOR_INDEX_TASK_HISTORY view to obtain information about historical tasks.

Here are some usage examples:

1. View the status of current tasks

   You can query the DBA_OB_VECTOR_INDEX_TASKS view to obtain information about tasks that are being executed or waiting to be executed:

   SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS;
   

   Here is the sample 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
   

   The following table describes the task status.

   Status	Description
   STATUS = 0	PREPARE. The task is waiting to be executed.
   STATUS = 1	RUNNING. The task is being executed.
   STATUS = 3	FINISHED. The task has been completed.

   The following table describes the task type.

   Task Type	Description
   TASK_TYPE = 2	IVF cache preheating task.
   TASK_TYPE = 3	IVF cache cleanup task.

2. View the records of historical tasks

   Completed tasks (STATUS = 3) are automatically archived to the history table every 10 seconds, regardless of whether they were successful or not. You can query the DBA_OB_VECTOR_INDEX_TASKS_HISTORY view to view the history records:

   -- Query the history records of a specified task ID.
   SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS_HISTORY WHERE TASK_ID=2002281;
   

   Here is a sample of the return 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 |         2 | 1750671687770026 |        0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 |
   +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
   1 row in set
   

Cancel an optimization task

You can run the following command to cancel a specified task.

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

Here is an 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 you cancel a task, the status of the task 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

Related topics

• Monitor and maintain vector indexes