Reducing Memory Usage

If the memory load of the current cluster is high or the "memory is temporary unavailable" error is reported, locate the node where the memory is abnormal based on the log information, log in to the node and query the PV_TOTAL_MEMORY_DETAIL view to check whether the memory is insufficient. Compare the values of process_used_memory and max_process_memory. If the latter is much bigger, it means that all memory-intensive statements have been executed or terminated, and the system is back to normal. However, if process_used_memory is equal to or close to max_process_memory, it indicates that the current memory usage has reached or is about to exceed the threshold. An excessively high value of dynamic_used_memory means that too much dynamically allocated memory is being used. This issue could be attributed to the SQL statements being executed. To address this issue, follow the steps below to optimize memory usage:

1. Check whether the memory usage of the current cluster service is too high.
   1. Log in to the GaussDB(DWS) console.
   2. Choose Monitoring > Alarm, select the cluster for which the alarm is generated in the cluster selection drop-down list in the upper right corner, view the alarm information of the cluster in the last seven days, and locate the name of the node for which the alarm is generated based on the location information.
   3. Choose Dedicated Clusters > Clusters, locate the row that contains the cluster for which the alarm is generated, and click Monitoring Panel in the Operation column.
   4. Choose Monitoring > Node Monitoring > Overview to view the CPU usage of each node in the current cluster. Click on the right to view the CPU performance metrics in the last 1, 3, 12, or 24 hours and see whether there is a sharp increase in the CPU usage.

2. Set exception rules to promptly terminate statements with high memory usage.

   In extreme scenarios, certain statements may consume excessive memory and affect the execution of other statements. You can create exception rules for better memory usage. For details, see Exception Rules. To maintain the stability of the cluster, promptly terminate SQL statements that surpass the threshold defined by the exception rule.

3. Perform the ANALYZE statement and optimize slow statements.
   • Run the ANALYZE statement on the relevant tables in the query to fix memory estimation errors caused by allocating too much memory
   • Check whether a complete pushdown is performed. For details, see Case: Pushing Down Sort Operations to DNs.
   • Check if broadcast is being used on tables with a large amount of data.
   • Check if the join sequence is appropriate. For instance, if JOIN is used for multiple tables, ensure that the intermediate result set of the two tables associated is not too large, as it can lead to high execution costs.

4. Reduce the number of concurrent jobs based on service scenarios.