Memory Overflow Occurs in a Cluster

Symptom

The error log is as follows:

[ERROR] Mpp task queryDataAnalyseById or updateDataAnalyseHistoryEndTimesAndResult fail, dataAnalyseId:17615 org.postgresql.util.PSQLException: ERROR: memory is temporarily unavailable
sql: vacuum full dws_customer_360.t_user_resource;

Possible Causes

Some SQL statements have exhausted memory. When other statements are executed, no memory can be allocated, and a message is displayed indicating that the memory is insufficient.

Handling Procedure

1. Adjust the service execution time window to ensure that the service execution time is different from the time when a large number of concurrent services are executed.
2. Query the memory usage of the current cluster, find the statements with high memory usage, and terminate them to release the cluster memory. Here is the procedure:

• For a cluster of 8.1.1 or an earlier version, perform the following steps:

1. Run the following statement to query the memory usage of the current cluster and check whether the value of dynamic_used_memory of an instance is greater than or close to the value of max_dynamic_memory. If the preceding error is reported, the value of dynamic_used_memory reaches the upper limit.

   1	SELECT * FROM pgxc_total_memory_detail;

2. When the Top SQL feature is enabled, run the real-time TOP SQL command to query the query statements that use most of the memory. You can find the statements that consume a large amount of memory based on the values of max_peak_memory and memory_skew_percent in the command output.

   1	SELECT nodename,pid,dbname,username,application_name,min_peak_memory,max_peak_memory,average_peak_memory,memory_skew_percent,substr(query,0,50) as query FROM pgxc_wlm_session_statistics;

3. Based on the session information obtained in Step 2, execute the pg_terminate_backend function to end the corresponding session to restore the memory. After the restoration, you can optimize the SQL statements that consume large memory.

   SELECT pg_terminate_backend(pid);

• For clusters of 8.1.2 or later, you can log in to the GaussDB(DWS) management console and perform the following steps on the real-time query monitoring page:

• Real-time query is supported only in clusters of version 8.1.2 and later.
• To enable the real-time query function, choose Monitoring > Monitoring Collection and enable Real-Time Query Monitoring.

1. Log in to the GaussDB(DWS) management console. On the Clusters page, locate the target cluster and click Monitoring Panel in the Operation column. The database monitoring page is displayed.
2. In the navigation pane, choose Monitoring > Queries.
3. Choose a time period and view queries executed in the cluster.
4. Click a session query ID to view the monitoring details. The detail information includes the Username, Database Name, Execution Time, Query Statement, Query Status, Workload Queue, Min. Peak DN Memory, Max. Peak DN Memory, Max. Peak IOPS on DN, Min. Peak IOPS on DN, and Average Memory Usage.

   A larger value of Max. Peak DN Memory or Average Memory Usage indicates a larger memory usage.

   

5. If you have confirmed that a statement with high memory usage needs to be terminated, select the query ID and click Terminate Query to terminate the query.

   The fine-grained permission control function is added. Only users with the operate permission are able to terminate queries. For users with the read-only permission, the Terminate Query button is grayed out.