ALM-27013 Response Time of 95% SQL Queries in the Database Exceeds the Threshold

Alarm Description

The system checks the response time of 95% SQL queries in the DBServer database every 30 seconds. This alarm is generated when the response time of 95% SQL queries in the database exceeds the threshold for N consecutive times. N indicates the value of Trigger Count, which is 3 by default.

When Trigger Count is set to 1, this alarm is cleared if the response time of 95% SQL queries in the DBServer database is less than or equal to the threshold. When Trigger Count is set to a value greater than 1, this alarm is cleared if the response time of 95% SQL queries in the DBServer database is less than 90% of the threshold for N consecutive times.

This section applies to MRS 3.6.0-LTS.1 and later versions.

Alarm Attributes

Alarm Parameters

Impact on the System

• Long-running SQL queries consume excessive system resources, affecting other tasks to obtain resources.
• The service task runs slowly, affecting user experience.

Possible Causes

• The alarm threshold is improperly configured.
• The response time of 95% SQL queries in the database exceeds the threshold.
  • The system resource usage is too high, causing slow task running.
  • The SQL service logic is complex or the data volume is too large, causing slow task execution.

Handling Procedure

Check whether the threshold is set properly.

1. On FusionInsight Manager, choose O&M > Alarm > Thresholds > Name of the desired cluster > DBService > Database > Response Time of 95% SQL Queries and check whether the alarm threshold is proper.
   • If yes, go to Step 3.
   • If no, go to Step 2.

2. Click Modify in the Operation column and change the alarm threshold as required.
3. Choose Cluster > Services > DBService. On the Dashboard page, check whether the SQL response time of the database is lower than the threshold in the DBService SQL Response Time chart.
   • If yes, go to Step 4.
   • If no, go to Step 5.

4. Check whether the alarm is cleared 2 minutes later.
   • If yes, no further action is required.
   • If no, go to Step 5.

Check the response time of 95% SQL queries in the database.

5. Log in to the active DBService management node as user omm and log in to the DBService database.

   gsql -p 20015 -U omm -W ${Database password}

6. Check whether the response time of 95% SQL queries in the database exceeds the threshold.
   1. Calculate the total SQL execution time in the current database and the number of 95% SQL queries.

      select count(1) from pg_stat_activity where STATE = 'active' and pid <> pg_backend_pid()

      Number of 95% SQL queries = Total number of SQL queries × 0.95

   2. Calculate the total execution time of 95% SQL queries in the current database.

      SELECT SUM(EXTRACT(EPOCH FROM (NOW() - QUERY_START::TIMESTAMPTZ))) AS TOTALSECONDS FROM (SELECT QUERY_START FROM PG_STAT_ACTIVITY WHERE PID <> PG_BACKEND_PID() AND STATE = 'active' ORDER BY NOW() - QUERY_START LIMIT ${Number of 95% SQL queries});

   3. Calculate the average response time of 95% SQL queries using the following formula and check whether it exceeds the threshold:

      Average response time of 95% SQL queries = Total SQL execution time/Number of 95% SQL queries

   • If yes, go to Step 7.
   • If no, go to Step 8.

7. Handle the time-consuming SQL tasks or change the alarm threshold based on the site requirements. Wait for 2 minutes and check whether the alarm is cleared.
   • If yes, no further action is required.
   • If no, go to Step 8.

Collect fault information.

8. On FusionInsight Manager, choose O&M. In the navigation pane on the left, choose Log > Download.
9. Expand the Service drop-down list, and select DBService and OMS for the target cluster.
10. Specify Hosts for collecting logs, which is optional. By default, all hosts are selected.
11. Click the edit icon in the upper right corner, and set Start Date and End Date for log collection to 10 minutes ahead of and after the alarm generation time, respectively. Then, click Download.
12. Send the collected fault logs to O&M engineers for help.

Alarm Clearance

This alarm is automatically cleared after the fault is rectified.

Related Information

None