Best practices for creating indexes on large tables

Global index and local index are two common types of indexes in databases. A global index is built on the data of an entire table and impacts the overall query performance of the table, while a local index is created for specific partitions and only affects the query performance of those partitions. Creating indexes requires significant disk space, CPU, and memory resources. Global indexes process the data of the entire table, whereas local indexes only handle data from one or more partitions. In practice, indexes can greatly enhance the efficiency of complex queries, particularly when querying large tables frequently. During data analysis, indexes can also speed up data aggregation and processing.

In the distributed architecture of OceanBase Database, multiple OBServer nodes collaborate to build indexes. The system dynamically allocates resources based on data partitions and workload, ensuring efficient resource utilization. This flexibility allows you to create global or local indexes as needed to optimize performance.

Create indexes on large tables in OceanBase Database V4.x

In OceanBase Database V4.x, creating indexes on large tables can be resource-intensive, often consuming large amounts of disk space and CPU resources. These operations may lead to insufficient system resources if not properly planned. Additionally, you may encounter some limitations or known issues within OceanBase Database during this process. To avoid unnecessary complications, it is crucial to plan resources carefully in advance.

This section outlines key considerations and best practices for creating indexes on large tables, based on practical experience.

Considerations

When creating indexes on large tables, take note of the following considerations to optimize resource utilization and avoid potential issues.

Disk space

In OceanBase Database V4.2.2 and later, local indexes can be built using partition scheduling. This means the maximum temporary space required for index creation depends on the size of the largest partition.

In OceanBase Database V4.2.3 and later, if the degree of parallelism (DOP) for index creation is 8 or higher, the system automatically compresses the temporary files generated during the process to reduce disk space usage. If the DOP is lower than 8 and disk space is insufficient, you can enable compression manually by modifying the _ob_ddl_temp_file_compress_func parameter.

However, compression consumes additional CPU resources, so it’s important to evaluate the trade-off between available CPU and disk resources before enabling this feature.

# Enable compression and use the specified compression algorithm to compress temporary files generated during index creation.
alter system set _ob_ddl_temp_file_compress_func = 'ZSTD';
alter system set _ob_ddl_temp_file_compress_func = 'LZ4';
# Disable compression.
alter system set _ob_ddl_temp_file_compress_func = 'NONE’
# Determine whether to enable compression based on the DOP. If the DOP is equal to or higher than 8, compression is enabled and the compression algorithm of the primary table is used.
alter system set _ob_ddl_temp_file_compress_func = 'AUTO’

If you are using OceanBase Database V4.2.1 or earlier, refer to the Estimate the temporary space required for index creation section. If you are using OceanBase Database V4.2.2 or later, refer to the Estimate the temporary space required for local index creation section.

Estimate the temporary space required for index creation

Space amplification occurs during index creation. Given the worst situation, the required temporary space can be estimated as five times the original data size of the index table. The most challenging part is estimating the original data size of the index table.

You can estimate the original data size of an index table using either the existing statistics of the primary table or by performing a simple calculation based on internal tables.

Method 1: Estimate based on statistics

The procedure for estimating the required space based on statistics is as follows:

1. Table-level statistics include the number of rows (row_count) in the primary table and the average row length (avg_row_length). You can calculate the size of the primary table's data using the formula data_size = row_count x avg_row_length.
2. Column-level statistics provide the average column length (avg_column_length) for each column. The size of the index column data can be calculated by multiplying the primary table's data size by the ratio of the index column's average length to the average row length: index_size = data_size x (avg_column_length/avg_row_length).

The SQL syntaxes are as follows:

-- Retrieve table-level statistics
SELECT NUM_ROWS, AVG_ROW_LEN FROM ALL_TAB_STATISTICS WHERE OWNER = 'xxx' AND TABLE_NAME = 'xxx' AND OBJECT_TYPE = 'TABLE';

-- Retrieve column-level statistics
SELECT TABLE_NAME, COLUMN_NAME, AVG_COL_LEN FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'xxx' AND OWNER = 'xxx';

Method 2: Estimate based on an internal table

You can calculate the original data size of the primary table using an internal table. The SQL query is as follows:

SELECT SUM(original_size) FROM __all_virtual_tablet_sstable_macro_info WHERE tablet_id IN (SELECT tablet_id FROM __all_virtual_tablet_to_table_history WHERE table_id = xxx);

Divide the original data size of the primary table by the number of replicas to obtain the uncompressed data size of the table. Then, estimate the uncompressed column data size in the index table based on the uncompressed data size of the primary table and the percentage of data in each column of the index table relative to the corresponding column in the primary table.

Estimate the temporary space required for local index table creation

The temporary space required for creating a local index table depends on the total data size of the index partitions. Typically, all index-building threads for a single partition are fully utilized. Therefore, you only need to consider the disk space occupied by the largest partition. For a single partition, you can refer to the methods described in the Estimate the temporary space required for index creation section, but limit the calculation to the data size of the largest partition.

Disable major compactions

To avoid space amplification in the primary table caused by the retention of snapshot points, we recommend that you disable major compactions by performing the following steps:

1. Set the minor_freeze_times parameter to a large value, such as 500.
2. Set the major_freeze_duty_time parameter to disable.

CPU

If N CPU cores in the current cluster can be allocated for index creation, you can set the DOP for index creation to N.

In OceanBase Database V4.0 and later, DDL operations are executed in series by default. You can specify the DOP by using a session-level parameter. The rules for determining the DOP are as follows:

1. The total DOP of all DDL operations does not exceed the value of the tenant's max_cpu parameter.
2. In OceanBase Database V4.0 and V4.1, the space occupied by temporary files is limited. We recommend that the total DOP of all DDL operations do not exceed 64. For more information, see the index building performance test.

You can set the DOP as follows:

1. MySQL mode: SET SESSION _FORCE_PARALLEL_DDL_DOP = 8;
2. Oracle mode: ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
3. In OceanBase Database V4.3.0 and later, you can use a hint to specify the DOP of DDL operations. The syntax is as follows: ALTER /*+ PARALLEL(2) */ TABLE XXX;.

After the DOP is adjusted, you also need to modify the value of the parallel_servers_target parameter.

SET GLOBAL parallel_servers_target = 100; -- Set this parameter to a value greater than the total DOP.

I/O

Generally, I/O isolation is not required if there is no I/O bottleneck.

However, you can enable I/O isolation to limit the I/O traffic of DDL operations, such as index creation, to prevent these operations from affecting foreground business traffic.

A DDL task can perform data completion through direct acyclic graph (DAG) tasks such as column deletion, or internal SQL statements such as CREATE INDEX. If data completion is performed through a DAG task, throttling can be implemented based on the function-level isolation relationship, namely, the DDL group bound to the function. If data completion is performed through an internal SQL statement, resource isolation is implemented based on the resource consumer group to which the user is bound, because this internal SQL statement is still executed by a parallel eXecution (PX) thread.

The following example demonstrates how to create a resource consumer group named DDL_GROUP, map the current user (root) to the group, and specify the resources available for the group to limit resource usage during index creation tasks:

/* Create a resource management plan */
CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('DDL_PLAN','plan for ddl');
/* Create a resource consumer group */
CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'DDL_GROUP', COMMENT => 'DDL');
/* Create a plan directive */
CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'DDL_PLAN', GROUP_OR_SUBPLAN => 'DDL_GROUP', COMMENT => 'DDL', MAX_IOPS => 60);
/* Create mapping */
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('USER', 'root', 'DDL_GROUP');
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('FUNCTION', 'DDL', 'DDL_GROUP');
/* Enable the resource management plan */
SET GLOBAL resource_manager_plan = 'DDL_PLAN';

In this example, the root user and the DDL function for executing DDL tasks are mapped to the DDL_GROUP resource consumer group. The max_iops=60 configuration specifies that the maximum IOPS resources available for DDL_GROUP are limited to 60% of the current unit. You can use the I/O diagnostics table to observe the isolation effect.

Memory

The following modules consume significant memory during index creation:

1. Metadata memory of temporary files
2. Range information for channels during execution
3. Buffers for the data transmission layer (DTL)

For OceanBase Database V4.2.1 and earlier, it is known that the preceding modules consume many memory resources. For versions later than V4.2.1, we recommend that you check the memory usage of these modules as well to ensure optimal performance.

Estimate the metadata memory required for temporary files

Each 1 GB of temporary file space consumes 3.75 MB of metadata memory. In OceanBase Database V4.2.1, this module can use a maximum of 70% of the tenant memory. If the module runs out of memory, we recommend that you increase the tenant memory.

Calculate the memory required for the range information of the channel

In OceanBase Database V4.2.1 and earlier, the memory required for the range information of the channel is calculated as follows:

• DOP x DOP x Number of partitions x 1.5 KB

In OceanBase Database of a version later than V4.2.1, the memory required for the range information of the channel is calculated as follows:

• DOP x Number of partitions x 1.5 KB

Calculate the size of memory for the buffer of the data transmission layer

The calculation formula is as follows: DOP x DOP x 64 KB x 3

Procedure

To ensure successful index creation on a large table, perform the following steps:

1. Evaluate the existing statistics of the table: Run an SQL query to obtain the number of rows and average row length of the table. This is to ensure that the latest statistics are obtained.

   -- Retrieve table-level statistics
   SELECT NUM_ROWS, AVG_ROW_LEN
   FROM ALL_TAB_STATISTICS
   WHERE OWNER = 'xxx' AND TABLE_NAME = 'xxx' AND OBJECT_TYPE = 'TABLE';
   

2. Estimate the temporary space required: Estimate the space required for index creation based on statistics or an internal table. This is to ensure that sufficient temporary space is reserved.

   • Formula: index_size = data_size x (avg_column_length/avg_row_length)

   -- Retrieve column-level statistics
   SELECT TABLE_NAME, COLUMN_NAME, AVG_COL_LEN
   FROM ALL_TAB_COL_STATISTICS
   WHERE TABLE_NAME = 'xxx' AND OWNER = 'xxx';
   

   • Estimate based on an internal table

   SELECT SUM(original_size)
   FROM __all_virtual_tablet_sstable_macro_info
   WHERE tablet_id IN (SELECT tablet_id FROM __all_virtual_tablet_to_table_history WHERE table_id = xxx);
   

3. Plan resources: Adjust the memory settings of the OBServer node based on the estimated space and memory required.

   This is to ensure that the CPU and I/O resources can meet the DOP requirements.

   Calculate the memory required:

   • Metadata memory of temporary files: Temporary space (GB) x 3.75 MB
   • Memory for the range information of the channel: DOP x Number of partitions x 1.5 KB
   • Memory for the buffer of the data transmission layer: DOP x DOP x 64 KB x 3

   Compression settings for temporary files: In OceanBase Database V4.2.3 and later, if the DOP for index creation is equal to or higher than 8, the system compresses the temporary files generated during index creation by default.

   If the DOP is lower than 8 and the disk space is insufficient, you can enable compression to reduce the disk space occupied.

   -- Enable compression and use the specified compression algorithm.
   ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'ZSTD'; -- or 'LZ4'
   
   -- Disable compression.
   ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'NONE';
   
   -- Determine whether to enable compression based on the DOP. If the DOP is equal to or higher than 8, enable compression and use the compression algorithm of the primary table.
   ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'AUTO';
   

4. Configure parameters: Set a DOP that does not exceed the CPU capacity of the tenant.

   -- MySQL mode
   SET SESSION _FORCE_PARALLEL_DDL_DOP = 8;
   
   -- Oracle mode
   ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
   
   -- Set a DOP for DDL operations in OceanBase Database V4.3.0 and later.
   ALTER /*+ PARALLEL(2) */ TABLE xxx;
   

5. Disable major compactions: Before index creation, adjust the major compaction parameters to avoid space amplification of the primary table caused by the retention of snapshot points.

   ALTER SYSTEM SET minor_freeze_times = 500;
   ALTER SYSTEM SET major_freeze_duty_time = 'disable';
   

Verify the result

• Execute a query to verify whether the index has been created and whether the query performance has been significantly improved.
• Monitor the usage of resources to ensure that the disk, CPU, and memory resources are properly used without overloads.
• Check the logs to ensure that no error or warning is generated.

Create indexes on large tables in OceanBase Database V3.x

In OceanBase Database V3.x, creating indexes on large tables can be resource-intensive, often consuming large amounts of disk space and CPU resources. These operations may lead to insufficient system resources if not properly planned. Additionally, you may encounter some limitations or known issues within OceanBase Database during this process. To avoid unnecessary complications, it is crucial to plan resources carefully in advance. This section outlines key considerations and best practices for creating indexes on large tables, based on practical experience.

Considerations

When creating indexes on large tables, take note of the following considerations to optimize resource utilization and avoid potential issues.

Adjust the timeout period

It takes a very long time to create an index on a large table. Therefore, you need to adjust the timeout period to avoid index creation failure due to timeout.

Here is an example:

alter system set global_index_build_single_replica_timeout = '168h';

Notice: Set the timeout period based on your business scenario.

Configure the storage space

The storage space comprises two parts: disk and memory.

Configure the disk space

In OceanBase Database V3.x, a global index is created based on the data of an entire table, and a local index is created based on the data of multiple partitions. The temporary space required for creating a global index is subject to the data size in the entire table, and the temporary space required for creating a local index is subject to the total amount of data in the index partitions.

If you want to build a global or local index table, you need to reserve sufficient disk space.

Estimate the temporary space required for a global index table

The temporary space occupied by a global index table is subject to the data size in the entire table. You can estimate the required temporary space by using the following formulas:

1. Sum of the lengths of all index columns x Number of table rows x 5 Here is a sample statement for querying the average length of an index column:

   select sum(length(c1)) / count(1) from t1;
   

2. Total column length of the index table/Total column length of the primary table x Amount of compressed table data x Compression ratio (empirical value: 4)

   -- Query the data size in the primary table.
   select sum(data_size) from __all_virtual_meta_table where table_id = xxx and role = 1;
   

Estimate the temporary space required for a local index table

The temporary space occupied by a local index table is subject to the total amount of data in the index partitions. Generally, you only need to estimate the disk space occupied by the largest partition.

Notice

1. Reserve 10% of the total disk space on each OBServer node.
2. In a public cloud, we recommend that you ensure sufficient disk space. The disk space will be insufficient if the disk space is not scaled out in a timely manner. At present, the scaling step is 50 GB.

Configure the memory space

The memory space required for an index creation task is subject to the number of parallel threads running the task. The calculation method varies based on the index type.

You need to adjust the memory parameters to support data sorting. Here is an example:

ob_sql_work_area_percentage x Tenant memory size = Number of partitions in the primary table x Number of partitions in the index table x 16 MB

Estimate the memory space required for a global index

The memory space required for a global index creation task is subject to the number of partitions. You can calculate the required memory space by using the following formula:

Required memory space = Number of partitions in the primary table x Number of partitions in the index table x 8 MB

Estimate the memory space required for a local index

The formula for estimating the memory space required for a local index creation task is as follows:

Required memory space = Number of threads x 128 MB

The memory space occupied by local indexes is allocated from the sys500 tenant. You do not need to adjust related parameters.

Disable major compactions

You need to disable major compactions to avoid space amplification of tables caused by the retention of snapshot points. Configure the following parameters:

-- Set the `minor_freeze_times` parameter to a large value, such as `500`.
alter system set minor_freeze_times = 500;
-- Set the `major_freeze_duty_time` parameter to `disable`.
alter system set major_freeze_duty_time = disable;

Configure CPU resources

To avoid affecting business, you need to optimize the CPU utilization of the index creation task by using either of the following methods:

1. Reserve sufficient CPU resources for business. Here is an example:

   -- Reserve half of the remaining CPU resources for index creation.
   parallel_servers_target = (Number of physical CPU cores - Number of CPU cores required during peak business hours)/n
   

   Notice: Calculate the CPU resources based on your business scenario.

2. Transfer the index creation task on a follower to reduce the load on the leader. Here is an example:

   select svr_ip, count(1) from __all_virtual_sys_task_status where comment like 'build index task%' group by svr_ip;
   

   In the __all_virtual_sys_task_status table, job=3 means that a task is in the sorting phase.

   Find the server where the index creation task (task_type = 'create index') on the table is running and switch the business traffic from this server to another server.

Notice: The maximum DOP for global index creation tasks is 100.

Configure I/O resources

In OceanBase Database V3.x, the I/O resources for index creation are automatically adjusted based on the response time of user requests, and no special configuration is required.

Procedure

You can perform the following steps:

1. Adjust the timeout period.

   alter system set global_index_build_single_replica_timeout = '168h';
   

2. Adjust the disk space and reserved space.

3. Disable major compactions.

   alter system set minor_freeze_times = 500;
   alter system set major_freeze_duty_time = disable;
   

4. Optimize CPU configurations to ensure sufficient CPU resources for business.

5. Adjust memory parameters.

   ob_sql_work_area_percentage x Tenant memory size = Number of partitions in the primary table x Number of partitions in the index table x 16 MB
   

6. Start the index creation task.

Verify the result

1. Execute a query to verify whether the index has been created and whether the query performance has been significantly improved.
2. Monitor the usage of resources to ensure that the disk, CPU, and memory resources are properly used without overloads.
3. Check the logs to ensure that no error or warning is generated.

With the preceding measures, you can efficiently create indexes on large tables in OceanBase Database V3.x to improve the system performance and resource utilization.

References

OceanBase Database V4.x

Take OceanBase Database V4.3.3 as an example:

• Create an index in Oracle mode
• About indexes in Oracle mode
• Create an index in MySQL mode
• About indexes in MySQL mode

OceanBase Database V3.x

OceanBase Database V3.2.x (Take OceanBase Database V3.2.4 as an example):

• Create indexes in Oracle mode
• About indexes in Oracle mode
• Create indexes in MySQL mode
• About indexes in MySQL mode