Description
This alert is triggered when slow SQL queries are found in an OBProxy log. In OBProxy, an SQL query with an execution time exceeding 50 ms is considered a slow SQL query.
Principle
Parameter |
Value |
|---|---|
| Metric | slow_sql_query_count |
| Source | This metric is collected from the OBProxy log obproxy_slow.log. The metric shows the number of slow SQL queries. |
| Collected metric | None |
| Metric expression | None |
| Collection cycle | 1 minute |
Alert rule
Metric expression |
Metric description |
Default threshold |
Detection cycle |
Elimination cycle |
|---|---|---|---|---|
| slow_sql_query_count > 0 | The number of slow SQL queries. | 0 | 60 seconds | 5 minutes |
Alert information
Trigger method |
Alert level |
Scope |
|---|---|---|
| Based on the expression of the metric | Critical | OBProxy |
Alert templates
Alert overview
- Template: ${alarm_target} ${alarm_name}
- Example: alarm_template_id=0:odp_instance_id=odp-tx9ympkg37m4:host=xxx.xxx.xxx.xxx:odp_schema=obcluster:ocp_meta:test odp_sql_query_slow
Alert details
- Template: OBProxy instance: ${obproxy_cluster}, deployment unit: ${host}, database: ${odp_schema}, alert: The number of slow SQL queries is ${value}, which exceeds the threshold of ${alarm_threshold}.
- Example: OBProxy instance: odp-tx9ympkg37m4, deployment unit: xxx.xxx.xxx.xxx, database: obcluster:ocp_meta:test, alert: The number of slow SQL queries is 1, which exceeds the threshold of 0.
Alert recovery
- Template: Alert: ${alarm_name}, Slow SQL query count: ${value}
- Example: Alert: Slow SQL query exceeded, Slow SQL query count: 0
Impact on the system
The execution of SQL statements consumes system resources. When many SQL statements are executed slowly, system resource usage increases, affecting the system throughput. If the execution of SQL statements is too slow, services will be affected.
Possible causes
- The SQL statement needs to be optimized. For example, the statement has join queries on large tables or lacks an index.
- The OBServer does not have sufficient resources and therefore cannot handle concurrent requests, thus slowing down the SQL statement execution.
Solutions
- Use the SQL diagnostics feature in the tenant management module in OCP to identify the cause. If the problem is caused by a business SQL statement, bind an index or an execution plan to the statement.
- Check whether the resource usage, such as the CPU usage, IOPS or I/O time, and network throughput, of the OBServer has exceeded the limits.