Skip to main content

Index performance optimization

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

tip

Currently, only IVF series indexes are supported.

Optimization mechanism​

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

  1. Cache warming task: This task regularly checks all IVF series indexes. If the cache corresponding to an index does not exist, the cache is warmed by loading the index data into memory. Additionally, when an IVF series index is created, the cache is automatically warmed.
  2. Cache cleanup task: This task regularly checks all IVF series index caches. If the cache corresponds to an index that has been deleted, the cache is automatically cleaned up to release memory resources. Additionally, when an IVF series index is deleted, the cache is automatically cleaned up.

Configure the execution cycle of the optimization task​

The system allows you to customize the time window for executing performance optimization tasks to avoid impacting performance during peak business hours.

In the oceanbase database, you can set the execution cycle by configuring 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 indicates that the optimization task is executed only between 23:00:00 and 24:00:00.
  • Optimization tasks are not initiated during other time periods to avoid affecting normal business operations.

Monitor the optimization task​

The seekdb vector index provides the capability to monitor optimization tasks:

Here are some usage examples:

  1. View the current task status

    You can query the DBA_OB_VECTOR_INDEX_TASKS view to view the status of running or pending tasks:

    SELECT * FROM oceanbase.DBA_OB_VECTOR_INDEX_TASKS;

    Example 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 | USER         | FINISHED |         2 | 1750671687770026 |        0 | YAFF00B9E4D97-00063839E6BD9BBC-0-1 |
    +----------+---------------------+---------+----------------------------+----------------------------+--------------+----------+-----------+------------------+----------+------------------------------------+
    1 row in set

    The task status is described as follows:

    • 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 task type is described as follows:

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

  2. View the history of completed tasks

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

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

    Example 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>;
tip

You can cancel a task only during the task's retry phase after a failure. If a background task is stuck in an execution phase, you cannot cancel it by running this command.

Here is an example:

-- Log in to the database 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, the task status changes to CANCELLED.

-- Log in to the 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​

Contents

Edit this page