Help Center > > Developer Guide> Resource Load Management> Resource Monitoring> Real-Time TopSQL

Real-Time TopSQL

Updated at: Jul 15, 2020 GMT+08:00

When a database user performs a query job, the system provides real-time resource monitoring views at the query- and operator-levels to query SQL queries in the Active state for the real-time TopSQLs whose execution cost is greater than resource_track_cost. The views record the resource usage (including memory, disk, CPU time, and I/O) and performance alarms during job execution. Based on the recorded information, you can evaluate whether the query has performance bottlenecks and whether it affects the cluster performance.

By default, query-level resource monitoring is enabled. To enable resource monitoring at both the query and operator levels, set resource_track_level to operator. The following jobs support real-time resource monitoring:

  • Jobs whose execution cost estimated by the optimizer is greater than or equal to resource_track_cost. You can run the EXPLAIN statement to query for the execution cost of a statement.
  • Jobs of the SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE AS, EXPLAIN ANALYZE, and EXPLAIN PERFORMANCE statements. A query-level view also supports the FETCH statement. In a query-level view, jobs of the FETCH statement are also monitored.

The real-time resource monitoring view is described in the following table. You can query such a view in the same way as querying database tables. Common users can only query views with the prefix gs.

Table 1 Real-time resource monitoring view

Monitoring Level

Monitored Node

Query View

View Description

Query

Current CN

GS_WLM_SESSION_STATISTICS

Queries for the real-time resources on the current CN.

All CNs

PGXC_WLM_SESSION_STATISTICS

Queries for the real-time resources on all CNs.

Operator

Current CN

GS_WLM_OPERATOR_STATISTICS

Queries for the real-time resources of job operators on the current CN.

All CNs

PGXC_WLM_OPERATOR_STATISTICS

Queries for the real-time resources of job operators on all CNs.

  • Prefixes gs and pgxc indicate views showing single CN information and those showing cluster information, respectively. Common users can log in to a CN in the cluster to query only views with the gs prefix.
  • When you query this type of views, there will be network latency, because the views obtain resource usage in real time.
  • If instance fault occurs, some SQL statement information may fail to be recorded in real-time resource monitoring views.
  • SQL statements are recorded in real-time resource monitoring views as follows:
    • DDL statements are not recorded, such as CREATE, ALTER, DROP, GRANT, REVOKE, and VACUUM.
    • DML statements are recorded, such as SELECT, INSERT, UPDATE, and DELETE.
    • Statements in functions and stored procedures and statements used for calling functions and stored procedures are recorded. Statements in loop bodies (if any) of functions and stored procedures are not recorded.
    • Statements in anonymous blocks are not recorded.
    • Statements in transaction blocks are recorded. Statements in loop bodies (if any) of transaction blocks are not recorded.
    • Cursor statements are recorded.

Typical Queries for Real-Time Top SQLs

The query for statements executed on the current CN is used as an example to describe some typical SQL statements used for querying for Top SQLs in terms of real-time resource usage and performance. To query for the SQL statements executed on all CNs, replace the prefix gs of the view name in the query statement with pgxc.
  • Query level. By default, the first column in each query statement provided in the example is query (that is, the SQL statement to be queried).
    • Query for the start time, end time, execution time, and execution status of the SQL statement. The time unit is ms.
      1
      SELECT query,start_time,finish_time,duration,status FROM gs_wlm_session_history ORDER BY start_time DESC;
      
    • Query for the maximum memory peak of the SQL statement among all DNs, average memory usage during statement execution, and the memory usage skew ratio across DNs. The memory unit is MB.
      1
      SELECT query,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
      
    • Query for the spill of the SQL statement to each DN, maximum data spill volume among all DNs, average data spill volume among all DNs, and spill skew ratio across DNs. The data volume unit is MB.
      1
      SELECT query,spill_info,max_spill_size,average_spill_size,spill_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
      
    • Query for the maximum execution time of the SQL statements among all DNs and the execution time skew ratio across DNs. The time unit is ms.
      1
      SELECT query,max_dn_time,dntime_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
      
    • Query for the maximum CPU time of the SQL statement among all DNs, the total CPU time on all DNs, and the CPU time skew across DNs. The time unit is ms.
      1
      SELECT query,max_cpu_time,total_cpu_time,cpu_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
      
    • Query for the maximum IOPS peak of the SQL statement among all DNs, the average IOPS peak among all DNs, and the I/O skew ratio across DNs. The I/Os are counted by ones for column storage and by 10 thousands for row storage.
      1
      SELECT query,max_peak_iops,average_peak_iops,iops_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
      
  • Operator level. By default, the first column in each query statement provided in the example is plan_node_name (that is, the operator name matching plan_node_id).
    • Query for the start time for the operator to process data, total data processing duration (unit: ms) of the operator, and operator execution status.
      1
      SELECT plan_node_name,start_time,duration,status FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
      
    • Query for the maximum memory peak of the operator among all DNs, average memory peak among all DNs, and memory usage skew ratio across DNs. The memory unit is MB.
      1
      SELECT plan_node_name,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
      
    • Query for the maximum data spill volume of the operator among all DNs, average data spill volume among all DNs, and spill skew ratio across DNs. The data volume unit is MB.
      1
      SELECT plan_node_name,max_spill_size,average_spill_size,spill_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
      
    • Query for the maximum execution time of the operator among all DNs, total execution time on all DNs, and execution time skew ratio across DNs. The time unit is ms.
      1
      SELECT plan_node_name,max_cpu_time,total_cpu_time,cpu_skew_percent FROM gs_wlm_operator_statistics ORDER BY start_time DESC;
      

Precautions

  • Jobs in a redistribution process are not monitored.
  • Prefixes gs and pgxc indicate real-time views showing single CN information and those showing cluster information, respectively. Common users can query only the views with the prefix gs.
  • When you query real-time views, there will be network latency, because the views obtain resource usage in real time.
  • When you query views in Table 1, network latency may be caused by the views to obtain real-time resource usage.
  • If you only need to query for the real-time CPU information, use the GS_SESSION_CPU_STATISTICS view; if you only need to query for the real-time memory information, use the GS_SESSION_MEMORY_STATISTICS view. The GS_WLM_SESSION_STATISTICS view returns a query result containing both GS_SESSION_CPU_STATISTICS and GS_SESSION_MEMORY_STATISTICS.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel