Help Center> Data Warehouse Service> Troubleshooting> Database Performance and Resources> How Do I Locate the SQL Statements with High CPU Usage?
View PDF

How Do I Locate the SQL Statements with High CPU Usage?

Symptom

The CPU usage remains high.

Possible Causes

During SQL statement execution, a thread occupies excessive CPU resources.

Handling Procedure

  1. Find a GaussDB process and confirm the process ID and the corresponding DN port number, for example, 32089.
  2. Run the following command to check the CPU usage of each thread in the process: 
    1
2
3
4
    		 
    ps H -eo pid,tid,pcpu | sort -n -k 3 | grep 32089,
32089    48369    81.9
32089    41335    82.3
32089    10596    85.6
  3. Run the preceding command for multiple times to query the thread that keeps occupying excessive CPU resources and connect to the DN. Then run the following command: 
    1
    		 
    select query_id from pg_thread_wait_status where lwtid = 10596;

    The value of query_id is 95174458.

  4. Run the following command to query the corresponding SQL statement: 
    1
    		 
    select * from pg_stat_activity where query_id = 95174458;