Alert description
This alert is triggered when the OceanBase cluster has tables with an indexing failure.
Alert principle
The following table lists the key parameters involved in the monitoring logic of this alert.
| Parameter | Value |
|---|---|
| Metric | ob_cluster_index_fail_count |
| Source |
|
| Collected metric | index_fail_count |
| Metric expression | sum(ob_index_error_num{@LABELS}) by (@GBLABELS) |
| Collection cycle | 60 seconds |
The metric ob_cluster_index_fail_count indicates the number of tables with an indexing failure in the OceanBase cluster. An alert is triggered when the value exceeds the threshold (which is 0 by default).
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 template
Alert overview
- Template: ${alarm_target} ${alarm_name}
- Example: ob_cluster=obcluster-1 The OceanBase cluster has tables with an indexing failure.
Alert details
- Template: Cluster: ${ob_cluster_name}, Alert: ${alarm_name}. The number of tables with an indexing failure is ${value}.
- Example: Cluster: obcluster-1, Alert: The OceanBase cluster has tables with an indexing failure. The number of tables with an indexing failure is 1.0.
Alert recovery
- Template: Alert: ${alarm_name}. The number of tables with abnormal indexes in the OceanBase cluster: ${value}
- Example: Alert: The OceanBase cluster has tables with an indexing failure. The number of tables with abnormal indexes in the OceanBase cluster: 0
Alert details
Impact on the system
Failed index creation prevents SQL execution from being optimized based on the index, thereby failing to demonstrate the execution efficiency.
Possible causes
This issue commonly occurs during the execution of index creation tasks when the index cannot be established. Common scenarios include:
Failure to create a normal index. This error is caused by incorrect cluster status.
Failed creation of a unique index: OceanBase creates indexes asynchronously. If the data does not meet the uniqueness constraints, errors are reported later. In this case, DBAs and business teams need to collaborate to verify data uniqueness and index requirements.
Suggested solutions
Find the failed index information by using the following method:
Run the following command to find the failed index.
-- For OceanBase Database versions earlier than V4.0: select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(100000000) */ count(*) as cnt from gv$table where table_type in (5) and index_status in (5, 6) -- For OceanBase Database V4.0 and later: select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000) */ count(*) as cnt from CDB_INDEXES where status in ('ERROR','UNUSABLE')Take the values of index_name (index name) and data_table_id (ID of the table to which the index belongs).
Use the data_table_id (ID of the table to which the index belongs) to find the name of the table to which the index belongs.
-- Note that OceanBase Database V2.x uses __all_virtual_database. > 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';
Confirm that the cluster status is normal.
Drop and recreate the index.
To drop an index, see Drop an index in the documentation of the corresponding OceanBase Database version.
To recreate an index, see Create an index in the documentation of the corresponding OceanBase Database version.