CCN Queuing Under Dynamic Load Management

Symptom

Services are running slowly. Only a few statements are being executed, and other service statements are waiting in the CCN queue.

Possible Causes

In dynamic load management, statements are sorted based on the estimated memory. For example, if the maximum available dynamic memory is 10 GB (per instance) and the estimated memory used by a statement is 5 GB, a maximum of two statements can be executed at the same time, and other statements have to wait in the CCN queue.

Solution

• Scenario 1: The estimated statement memory is too large. Statements are queuing.
  • Query the pg_session_wlmstat view to check whether there are only a few statements in the running state, and whether the value of statement_mem is large. (The unit is MB. Generally, statements whose estimated memory usage is greater than 1/3 of max_dynamic_memory are large-memory statements.) If all these conditions are met, the slow execution is caused by the statements that occupy too much memory.

    1	SELECT usename,substr(query,0,20),threadid,status,statement_mem FROM pg_session_wlmstat where usename not in ('omm','Ruby') order by statement_mem,status desc;

    

    As shown in the preceding figure, only the last statement is in the running state, and other statements are in the pending state. The statement_mem column shows that the running statement occupies 2576 MB memory. In this case, run the following statement to kill the thread based on the thread ID of the statement. After the thread is killed, resources will be released for other statements to run.

    1	SELECT pg_terminate_backend(threadid);

• Scenario 2: All the statements are in the pending state. No statements are running. This is because the management and control mechanism is abnormal. You can kill all the threads to rectify the fault.

  1	SELECT pg_terminate_backend(pid) FROM pg_stat_activity where usename not in ('omm','Ruby');