Description
This alert is triggered when a suspicious SQL statement with degraded performance is detected.
Principle
| Parameter | Value |
|---|---|
| Metric | None |
| Source | None |
| Collected metric | None |
| Metric expression | None |
| Collection cycle | None |
Alert rule
The CPU time of the SQL statement is longer than the average CPU time of the SQL statement in the last 1 hour or 24 hours. The alerting criteria are the same as the diagnostic criteria for the diagnostic result Deteriorated performance on the
Alert information
| Trigger method | Alert level | Scope |
|---|---|---|
| Based on monitored conditions | Warning | Tenant |
Alert templates
Details
Template:
The execution performance of the SQL statement ${sql_id} in the ${db_name} database of the ${tenant_name} tenant of the ${ob_cluster_name} cluster has degraded during the period from ${start_time} to ${end_time}. The average CPU time is ${sql_cpu_time_ms} ms. The average response time is ${sql_elapsed_time_ms} ms. The baseline period is from ${baseline_start_time} to ${baseline_end_time}. During the baseline period, the average CPU time is ${sql_baseline_cpu_time_ms} ms, and the average response time is ${sql_baseline_elapsed_time_ms} ms. Recommended action: [Refresh Plan Cache].
Example:
Overview: alarm_template_id=0:ob_cluster=admin-4:tenant_name=s**:db_name=oceanbase:sql_id=C38F5CFF605DC65C6FFD9ED72026****:recommend_operations=FLUSHING_PLAN_CACHE. It is detected during the SQL inspection that the execution performance of the SQL statement has degraded. Period: 2023-09-08T14:24:17 to 2023-09-08T14:28:47 Associated SQL statement: SQL ID: C38F5CFF605DC65C6FFD9ED72026****. Database: oceanbase. Tenant: s**. Hit rule: The execution performance degrades. Basic information of the SQL statement: The average CPU time is 1.78 ms. The average response time is 1.78 ms. Baseline period: 2023-09-08T14:04:17 to 2023-09-08T14:23:47 Information about the baseline SQL statements: The average CPU time is 1.39 ms. The average response time is 1.39 ms. Recommended action: [Refresh Plan Cache].
Impact on the system
The response time of the SQL statement and the consumption of system resources such as CPU resources are affected.
Possible causes
- The SQL statement is poorly or frequently executed.
- The volume of business data increases or the leader role is switched.
- The SQL execution plan has changed.
Troubleshooting method
Refresh the plan cache. To do so, you can execute the alert clearance plan. For more information, see Execute the alert clearance plan.
Optimize the SQL statement.
If you want to ignore this alert, you can disable the alert push configuration or reduce the alert push frequency.
Disable the alert push configuration: Specify
"alarm":falsein the OCP system parameterocp.perf.sql-diag.performance-degradation-config. If the parameter does not contain thealarmattribute, you can manually add it. Make sure that the modified parameter is in the JSON format.Reduce the alert push frequency: Modify the
cpuTimeLimitUsorexecPsLimitattribute in the OCP system parameterocp.perf.sql-diag.performance-degradation-config. If the parameter does not contain the attribute, you can manually add it. Make sure that the modified parameter is in the JSON format.
For more information about the
ocp.perf.sql-diag.performance-degradation-configparameter, see Parameters related to SQL performance diagnostics.
