Description
This alert is triggered when a table with an indexing failure exists in the OceanBase cluster.
Principle
The following table describes the key parameters that are involved in the monitoring and alerting logic.
| Parameter | Value |
|---|---|
| Metric | ob_cluster_index_fail_count |
| Source | SQL: select /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(100000000)*/ count(*) from gv$table where table_type in (5) and index_status in (5, 6); |
| Collected metric | index_fail_count |
| Metric expression | sum(index_error_count{metric_group="index_error",@LABELS}) by (@GBLABELS) |
| Collection cycle | 60 seconds |
The value of the metric ob_cluster_index_fail_count indicates the number of tables with an indexing failure in the OceanBase cluster. When the value exceeds the threshold, this alert is triggered. The default threshold is 0.
Alert rule
| Metric | Default threshold | Duration | Detection cycle | Time before clearance |
|---|---|---|---|---|
| ob_cluster_index_fail_count | 0 | 0 seconds | 60 seconds | 5 minutes |
Alert information
| Trigger method | Alert level | Scope |
|---|---|---|
| Metric expression | Critical | Cluster |
Alert templates
Overview: ${alarm_target} ${alarm_name}
Details: ${alarm_target} ${alarm_name}. The number of tables with an indexing failure is ${value}.
Overview example: ob_cluster=C1-1000. Tables with an indexing failure exist in the OceanBase cluster.
Details example: ob_cluster=C1-1000. The number of tables with an indexing failure in the OceanBase cluster is 1.0.
Impact on the system
The failure of index creation makes it impossible to optimize SQL execution based on the index.
Possible causes
This problem is commonly found when an error occurred while creating an index. It occurs in the following scenarios:
Failure to create a normal index. This error is caused by an incorrect cluster status.
Failure to create a unique index. OceanBase Database creates indexes asynchronously. If data in the table is not unique, an error is returned with a delay. In this case, the DBA must collaborate with the customer to verify whether the data is unique and meets the requirement of the index.
Suggested solutions
Find the data with an indexing failure.
Run the following command to find the index that failed to be created:
-- Applies to OceanBase Database 1.4.x SELECT tenant_id, database_id, table_id, `table_name` AS `index_name`, data_table_id, index_status from __all_table where table_type=5 and index_status not in (1, 2, 3) LIMIT 10; -- Applies to OceanBase Database 2.x SELECT tenant_id, database_id, table_id, `table_name` AS `index_name`, data_table_id, index_status from __all_virtual_table where table_type=5 and index_status not in (1, 2, 3) LIMIT 10;Find the index_name and data_table_id.
Find the name of the table with the indexing failure based on data_table_id.
-- Use __all_database for OceanBase Database 1.4.x and __all_virtual_database for OceanBase Database 2.x. > SELECT t2.database_name, t1.table_name from __all_table t1 join __all_database t2 on t1.database_id=t2.database_id where t2.tenant_id=1014 and t1.table_id=1114904790614901; +---------------+---------------+ | database_name | table_name | +---------------+---------------+ | obsink | question_dest | +---------------+---------------+ 1 row in set (0.05 sec)Run the following commands to find the index that failed to be created.
# MySQL mode # obclient> SHOW INDEX FROM table_name; # Oracle mode obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='table_name';
Check whether the cluster status is normal.
Delete and recreate the index.
For more information about how to delete an index, see Drop an index in the OceanBase Database documentation of the corresponding version.
For more information about how to create an index, see Create an index in the OceanBase Database documentation of the corresponding version.