DWS_2000000017 Number of Queuing Query Statements Exceeds the Threshold

Description

When real-time query monitoring is enabled, DWS checks the queuing status of jobs on CNs through the GS_WLM_SESSION_STATISTICS view every 60 seconds by default.

This alarm is generated when there are over 10 queuing SQL statements (configurable) in the cluster within 10 minutes (configurable), and is cleared once the number of queuing statements falls below 10.

If there continues to be queuing query statements more than the alarm threshold, the alarm is generated again 24 hours later (configurable).

Alarm Attributes

Alarm Parameters

Alarm Metrics

Call the Querying Collected Data of a Specified Metric - ListMetricsData API to obtain the alarm metric. The metric name in the API is QueryMonitor.

• Example request:

  GET https://{Endpoint}/v1/{project_id}/clusters/{cluster_id}/dms/metrics/QueryMonitor?offset=0&limit=1&from=1786782138946&to=1786784159004&order_by=ctime&sort_by=desc

• Example response:

  The alarm metric is the number of data records whose enqueue is not None and block_time is greater than 5,000 in the response.

  {
   "code": 0,
   "msg": "OK",
   "data": [
    {
     "query_id": "80220368369538752",
     "backend_start": 1786783088,
     "pid": "140504051222312",
     "block_time": 0,
     "application_name": "OM",
     "estimate_total_time": 0,
     "memory_skew_percent": 0,
     "max_peak_memory": 0,
     "iops_skew_percent": 0,
     "min_peak_memory": 0,
     "ctime": 1786783088000,
     "warning": "",
     "min_cpu_time": 0,
     "total_cpu_time": 0,
     "dntime_skew_percent": 0,
     "average_spill_size": 0,
     "query": "/* autopilot */ SELECT pgsac.datname as db_name, pgws.nodename as inst_name, pgsac.pid as pid, pgsac.query_id as query_id, pgws.query_band, null as job_name, null as job_inst, pgsac.usename as user_name, pgsac.application_name, pgsac.client_addr as client_address, pgsac.client_hostname, pgsac.client_port, pgsac.waiting, extract('epoch' from pgws.start_time at time zone 'UTC')::bigint as start_time, pgswlm.block_time, pgws.duration, pgws.estimate_total_time, pgws.estimate_left_time, pgswlm.enqueue, pgswlm.priority, pgsac.resource_pool, pgws.control_group, pgws.min_peak_memory, pgws.max_peak_memory, pgws.average_peak_memory, pgws.memory_skew_percent, pgws.estimate_memory, pgws.spill_info, pgws.min_spill_size, pgws.max_spill_size, pgws.average_spill_size, pgws.spill_skew_percent, pgws.min_dn_time, pgws.max_dn_time, pgws.average_dn_time, pgws.dntime_skew_percent, pgws.min_cpu_time, pgws.max_cpu_time, pgws.total_cpu_time, pgws.cpu_skew_percent, pgws.warning, pgws.average_peak_iops, pgws.iops_skew_percent, pgws.ma",
     "priority": 2,
     "start_time": 0,
     "max_spill_size": 0,
     "db_name": "postgres",
     "average_peak_memory": 0,
     "query_plan": "",
     "query_band": "",
     "job_inst": "",
     "wlm_attrib": "Internal",
     "spill_info": "",
     "virtual_cluster_id": 81882,
     "waiting": false,
     "average_dn_time": 0,
     "user_name": "Ruby",
     "min_spill_size": 0,
     "query_status": "active",
     "resource_pool": "root",
     "duration": 0,
     "client_port": "-1",
     "estimate_memory": 0,
     "elapsed_time": 0,
     "curr_xact_start": 1786783088,
     "client_address": "",
     "lane": "None",
     "query_elapsed_time": 0,
     "control_group": "",
     "average_peak_iops": 0,
     "query_start": 1786783088,
     "max_dn_time": 0,
     "cpu_skew_percent": 0,
     "min_peak_iops": 0,
     "enqueue": "None",
     "job_name": "",
     "client_hostname": "",
     "spill_skew_percent": 0,
     "system_query": true,
     "estimate_left_time": 0,
     "min_dn_time": 0,
     "wlm_status": "running",
     "max_peak_iops": 0,
     "inst_name": "cn_5001",
     "max_cpu_time": 0,
     "state_change": 1786783088
    }
   ],
   "count": 166
  }

Impact on the System

SQL queries are blocked. As a result, the execution time is too long.

Possible Causes

The number of queuing query statements during service execution exceeds the alarm threshold.

Handling Procedure

Check whether the current queuing jobs in the cluster are normal.

1. Log in to the DWS console.
2. Choose Monitoring > Alarm in the navigation pane on the left, select the current cluster from the cluster selection drop-down list in the upper right corner and view the alarm information of the cluster in the last seven days. Locate the name of the cluster that triggers the alarm based on the location information.
3. Choose Cluster > Cluster List, locate the row that contains the cluster for which the alarm is generated, and click Monitoring Panel in the Operation column.
4. Choose Monitoring > Queries to view the real-time sessions and queries of the current cluster. Select the Queries tab to view the status of jobs being executed in the current cluster. Click and select Blocking Time (ms) and Waiting status. Click to sort the values of Blocking Time (ms). You can view the information about the waiting SQL statements with the longest blocking time. If a query job is in the waiting state and the blocking time is abnormal, you can terminate the query.

   Current queuing status of the statements, including:

   • Global: global queuing.
   • Respool: resource pool queuing.
   • CentralQueue: queuing on the CCN
   • Transaction: being in a transaction block
   • StoredProc: being in a stored procedure
   • None: not in a queue
   • Forced None: being forcibly executed (transaction block statement or stored procedure statement are) because the statement waiting time exceeds the specified value

Alarm Clearance

This alarm is automatically cleared when the number of queuing statements drops below the threshold.