Querying and Analyzing Top SQL

Overview

The complex O&M environment of DWS distributed databases is prone to emergencies such as execution plan jumps, unexpected interruptions, and long-running transactions. Traditional O&M approaches cannot reproduce exceptions, trace execution paths, or retain runtime metrics. This makes fault location a resource-intensive and often cyclical process.

To address these challenges, DWS introduces Top SQL monitoring. It tracks both real-time and historical SQL statements to:

• Quickly locate the query statements that affect database performance and consume the most resources.
• Monitor the performance changes of these query statements over time.
• Analyze the query execution plan to determine potential optimization methods.

As a core support tool for database O&M, Top SQL monitoring has been deeply integrated into various scenarios in the production environment to help diagnose system performance, trace SQL deterioration, and trace security audit. The monitoring matrix covers multiple core metrics, such as memory usage, execution time, I/O throughput, network latency, and storage space.

Top SQL Principles

After a job is delivered and executed, the database kernel records job resource information, such as memory, CPU, spilling, I/O, and network information, by using stubs. These information is stored in the lockless queue and then dumped to the dbms_om.gs_wlm_session_info system catalog by the resource management background auxiliary thread. Later, the data is aged periodically by using topsql_retention_time.

Figure 1 Top SQL Principles

Related GUC Parameters

After a cluster is created, top SQL monitoring is enabled by default. This function is controlled by the use_workload_manager, enable_resource_track and enable_resource_record parameters (They default to on). For details about the parameters, see Table 1.

1

SELECT * FROM pgxc_total_memory_detail WHERE nodename LIKE 'cn_%' AND memorytype LIKE '%topsql%' ORDER BY 1,2;

How to Configure the GUC Parameters

Set the parameters on the DWS console.

1. Log in to the DWS console. In the navigation pane, choose Dedicated Clusters > Clusters.
2. In the cluster list, find the target cluster and click the cluster name. The Cluster Information page is displayed.
3. Click the Parameters tab and modify the parameter values. Then click Save.

Common SQL Statements for Querying the Settings of the GUC Parameters

SELECT name,setting FROM pg_settings WHERE name LIKE '%resource%';

Views and Analysis Methods

The Top SQL monitoring level is determined by the resource_track_level parameter. The value can be query (default), perf, operator_realtime, or operator.

• query: The plan information (similar to the output of EXPLAIN) of SQL statements is recorded in top SQL statements.
• perf: The plan information (similar to the output of EXPLAIN ANALYZE) that contains the actual execution time and number of executed rows is recorded in top SQL statements.
• operator_realtime: The information about the real-time job operators is recorded in top SQL statements, but not persisted in historical top SQL statements.
• operator: The information to be recorded includes the actual execution time, the number of executed rows, and the operator execution.

Top SQL statements are carried by views in Table 2. You can analyze top SQL information based on the fields in the views. For details about the common analysis fields in the views, see Table 3.

Conclusion:

1. The job is processing a much larger amount of data than before. You can analyze A2, B1, D1, and G1 to check whether there is a substantial increase in the data volume of the queried tables.
2. Currently, the historical Top SQL views contains a large number of fields. You can view the PGXC_QUERY_INFO view to query the classic fields, instead of manually filtering out irrelevant fields.
3. Competition for resources from other jobs is a frequent cause of slowdowns. For job queuing, you can analyze A1, B1, and D1 to check whether a large number of concurrent jobs are executed during the job execution.
4. For CPU contention, you can analyze A2/D1/E1 to check whether a large number of concurrent jobs are being executed.
5. For I/O contention, you can analyze A2/F1 to check whether a large number of concurrent jobs are being executed.
6. A certain type of statements uses heavy resources. You can analyze H1 to prohibit their execution.

CPU contention, I/O contention, and job queuing may concurrently occur during resource contention. You can analyze and solve the issues step by step. For example:

1. Adjust the job execution sequence, reduce the number of concurrent jobs, and reduce the blocking time.
2. Identify and shift compute and storage-intensive jobs to off-peak periods to minimize contention with priority jobs.
3. If no other jobs intervene, perform further analysis.

Notes and Constraints

• Top SQL does not record whitelisted and internal statements, but records statements delivered by superusers and scheduled tasks.
• The key difference between query and perf the query_plan field. query_plan provides operator-level details in query, but in perf, the field augments this with runtime statistics like actual memory usage, automatic memory expansion information, and CU/Buffers data.
• For query and perf levels, the start_time field represents the job delivery time in real-time top SQL, but the time that the job starts to run in historical top SQL.
• When querying the historical top SQL statements of the query, perf, and operator levels, you can only connect to the postgres database.
• Constraints on real-time top SQL statements:
  1. Special DDL statements, such as SET, RESET, SHOW, ALTER SESSION SET, and SET CONSTRAINTS, are not recorded.
  2. DDL statements, such as CREATE, ALTER, DROP, GRANT, REVOKE, and VACUUM, are recorded.
  3. DML statements are recorded, including:
     1. SELECT, INSERT, UPDATE, and DELETE
     2. EXPLAIN ANALYZE and EXPLAIN PERFORMANCE
     3. Views at the query or perf level
  4. The entry statements for invoking functions and stored procedures are recorded. When the GUC parameter enable_track_record_subsql is enabled, some internal statements (except the DECLARE definition statement) of a stored procedure can be recorded. Only the internal statements delivered to DNs for execution are recorded.
  5. The anonymous block statement is recorded. When the GUC parameter enable_track_record_subsql is enabled, some internal statements of an anonymous block can be recorded. Only the internal statements delivered to DNs for execution are recorded.
  6. The cursor statements are recorded. The system only records cursor statements that are executed on DNs. The statement and execution plan are enhanced. If a cursor's data is served from the cache, the statement is not recorded. Furthermore, a known architectural limitation prevents the recording of monitoring data for cursors inside functions or anonymous blocks that read large amounts of data from a DN but do not fully consume the result set. The With Hold cursor syntax has a special execution logic. It executes queries when a transaction is committed. If a statement execution error is reported, the aborted status of the job cannot be recorded in the Top SQL history table.
  7. Statistics are not collected for jobs in the redistribution process.
  8. For a statement with placeholders executed by JDBC, the parameter content is usually supplemented. However, if the total length of the parameter and the original statement exceeds 64 KB, the parameter is not recorded. Lightweight statements are directly delivered to the DNs for execution and their parameter are not recorded. If a common statement exceeds 64 KB, it will be truncated. For details, see the query field.
  9. In clusters of version 8.1.3 or later, the top SQL monitoring at the query and perf levels does not affect the query performance. The default value of resource_track_cost is 0. When you query the real-time monitoring views, all statements that are being executed are displayed by default.
  10. In clusters of version 8.1.3 or later, if enable_track_record_subsql is enabled, regardless of whether sub-statement monitoring is enabled in the service statements, you can view the sub-statement execution information in the real-time monitoring views.
  11. In clusters of version 8.1.3, statements cannot be filtered by time. Once enable_track_record_subsql is enabled, too many statements may be recorded and the archived monitoring table occupies a large amount of disk space. You are advised to enable only the parameters in specified sessions when querying real-time monitoring information or locating and analyzing some stored procedures. In clusters of version 8.2.1, you can use resource_track_subsql_duration to filter sub-statements to be archived based on the execution time. Its default value is 180 seconds and you can change the value as required.
  12. For a main statement that is not spilled to disks, its record in the top SQL history table is displayed only when the next job is delivered.
  13. In clusters of version 8.2.1.200 or later, you can enable operator_realtime-level monitoring to query the execution plan and detailed execution information of a statement. When you query the operator-realtime monitoring views of top SQL statements, all statements that are being executed are displayed by default. However, in stored procedure and cursor scenarios, operator-realtime monitoring information cannot be displayed.
  14. operator_realtime monitoring is not supported for lightweight CN statements and stored procedures. Operators are executed at a high speed, so there is a delay in displaying operator information.
  15. The spill_size field at the query level (job monitoring) and operator level (operator monitoring) varies due to the statistical dimension. The field indicates the statement files spilled to disks at the query level, while at the query level, it indicates the read and write I/O volume of a specific operator at the logical layer.
  16. When enable_stream_operator is set to off, the displayed operator execution information may be inaccurate.
• For clusters of version 8.1.3, except for the initial user, if enable_gtm_free is enabled and the joined queue is not controlled, user jobs are not managed in resource management. The system does not record the jobs delivered by the user in real-time or in historical top SQL statements.

Identifying Resource-Intensive SQL Statements Using Top SQL Monitoring

• Identify statements with a large number of streams.

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') AS stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100; 

• Identify statements with high memory usage.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_peak_memory desc limit 100; 

• Identify statements to be optimized.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning is not null ORDER BY duration desc limit 100; 

• Identify statements that take a long time to execute.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY duration desc; 

• Identify statements that cannot be pushed down.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning like '%can not be shipped%' ORDER BY max_peak_memory desc;

• Identify statements with high CPU usage.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• Identify statements with a large amount of data to be written to disks.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• Identify the statements that are not analyzed.

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning ilike '%statistics%';

Monitoring PERT Job-Level Top SQL Statements

On the live network, an SQL statement that initially executes quickly may later experience slowdowns. To diagnose this, use the query_plan and resource data in top SQL statements. A detailed query_plan acts like an EXPLAIN PERFORMANCE and simplifies fault localization. The key difference between query-level and perf-level top SQL statements is the detail in the query_plan. Perf-level query_plan provides execution details for each operator, including actual time, memory usage, rows processed, and buffer activity, while query-level query_plan does not.

Monitoring OPERATOR Operator-Level Top SQL Statements

To monitor operator-level progress for long-running SQL statements, set resource_track_level to OPERATOR. This allows users to identify slow operators by their execution time and processed rows, and determine whether to kill the SQL statements.

Operator monitoring visualizes SQL execution data, providing an intuitive overview of operator status and performance. Its benefits include:

• Enhanced user experience: You gain an immediate, intuitive understanding of operator behavior and health.
• Streamlined performance tuning: You can quickly identify performance bottlenecks and inefficiencies within operators to optimize execution speed.
• Accelerated troubleshooting: You can detect runtime exceptions and problems in real-time, enabling faster remediation and improving overall SQL maintainability.
• Informed scalability planning: You can uncover and eliminate bottlenecks, increasing operator scalability for future business growth.

Operator monitoring is similar to query/perf statement monitoring. They both include real-time and historical information or static and dynamic information.

• Static statement information is generated by the optimizer before a statement is executed. The information includes node name of the execution plan, query ID, and estimated rows. You can use the information to analyze whether the generated execution plan is appropriate.
• Dynamic statement information refers to the resource information occupied during statement execution in the executor. The information includes the operator progress, peak memory, spill size, network size, disk I/O (read bytes and write bytes), and CPU time on each DN. You can use the information to analyze the progress and resource consumption during statement execution.

Example: Query the real-time operator view pgxc_wlm_operator_statistics in another session. The command output is as follows.

Interaction Between Top SQL Views and Other views

Different views collect statistics on the database status from different dimensions. As these views share common fields, they can be joined for further analysis. The following figure lists other views that are frequently work with historical top SQL views.

Figure 2 Interaction between top SQL views and other views

Example: The historical pgxc_wlm_session_info view shows that a statement is completed quickly, but the pgxc_stat_activity view shows that the statement has been executed for a long time. In this case, an exception may occur.

Perform the following steps to locate the fault:

1. Query the pgxc_stat_activity view to find the abnormal jobs that have been running for a long time.

   SELECT coorname, usename, client_addr, now()-query_start as dur, state, enqueue, waiting, pid, query_id, substr(query,1,150) FROM pgxc_stat_activity WHERE usename not in ('omm','Ruby') AND state = 'active' ORDER BY dur DESC limit 100; 

2. Query the historical view using the information obtained in 1, and compare the statement's execution time before and after the fault. A marked increase in duration suggests an abnormal statement. The view is partitioned by day. You can use start_time to query the historical view.

   SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2024-08-22 00:00' AND start_time < '2024-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

3. Query the pgxc_thread_wait_status view based on the query_id obtained in 1, obtain the job thread number lwtid, and print the job stack for analysis.

   SELECT * FROM pgxc_thread_wait_status WHERE query_id = xxx; 
   SELECT * FROM gs_stack('nodename',tid); 

Analyzing and Handling Top SQL Cases

When there is a larger amount of information about top SQL statements, you can use start_time to target specific time ranges and LIMIT to constrain the result set. This prevents full-table scans and avoids client-side issues from excessive data.

• Case 1: The system-level performance of a customer's cluster is poor, the CPU usage keeps increasing, and services are affected.

  The top SQL historical view shows that more than 10 QL statements have more than 100 streams. The CPU usage is high.

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') as stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100;

  

  Solution: Bring the SQL statement offline and optimize it.

• Case 2: A statement runs fast at first and then slows down.

  Top SQL monitoring records statement execution and resource consumption to help you diagnose performance issues. For example, if a periodic SQL statement slows down, you can compare execution and block times to determine if it was queued or is genuinely slow. Analyze the execution plan to check for outdated statistics or missing ANALYZE operations. Check whether the slow performance is caused by a large amount of data spilled to disks.

  1. Check the historical SQL information in the pgxc_wlm_session_info view. (Note: The historical view is partitioned by day. When querying the view, add start_time.)

     SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2023-08-22 00:00' AND start_time < '2023-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

  2. Determine the historical execution status of jobs based on sql_hash, and analyze the job performance deterioration cause based on the resource information and query_plan field of historical top SQL statements.

     SELECT start_time, block_time, duration, sql_hash, warning, max_peak_memory, max_spill_size, query_plan FROM pgxc_wlm_session_info were start_time > 'xxxx-xx-xx xx:xx' and sql_hash = 'xxx' ORDER BY start_time desc limit 10; 

     Compare the execution plans (query_plan) of the fast and slow statements. It is found that the execution plans change greatly.

     

  3. Perform ANALYZE on the corresponding table and choose a proper plan. After that, the statement performance is restored.

     Outdated statistics often cause execution plan changes and slow queries. To prevent this issue, run ANALYZE, which doesn't affect read and write operations.

     ANALYZE dwrdim_dw1.dwr_dim_region_rc_d;

• Case 3: A job runs for a long time and does not end.

  If a job is running without queuing or deadlock but is taking excessively long, you can use operator_realtime monitoring to pinpoint slow operators. Based on an operator's duration and processed rows, you can decide whether to kill the statement.

  1. Enable operator_realtime monitoring.

     SET resource_track_level = 'operator_realtime';

     

  2. Kill the job as required.

     SELECT * FROM pg_terminate_backend (xxx); #The input parameter is pid.

• Case 4: The overall memory usage of a job is high. You need to analyze the memory usage of operators.

  When a job's overall memory usage is high, query-level monitoring cannot capture operator-level resource details. To resolve this, set the monitoring level to perf. This level records more granular execution data with a performance overhead of less than 5%. The query_plan field of perf-level monitoring can display operator time (actual time, memory, rows, and buffer).

  1. Enable perf-level monitoring.

     SET resource_track_level = 'perf';

  2. After a job is executed, query the pgxc_wlm_session_info view to check the memory usage of each operator.