Slow Response to a Query Statement

Symptom

After a query statement has been executed, no response is returned for a long time.

Cause Analysis

• The query statement is complex and requires a long time for execution.
• The query statement is blocked.

Procedure

1. Log in to the host as the OS user omm.

2. Run the following command to connect to the database:

   gsql -d mogdb -p 8000

   mogdb is the name of the database, and 8000 is the port number.

3. Check for the query statements that are executed for a long time in the system.

   SELECT EXTRACT(DAY FROM (current_timestamp - query_start)) * 24 * 60 + EXTRACT(HOUR FROM (current_timestamp - query_start)) * 60 + EXTRACT(MINUTE FROM (current_timestamp - query_start)) AS runtime, datname, usename, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY 1 desc;
       
   -- In B-compatible mode, run the following statement:
   SELECT timestampdiff(minutes, query_start, current_timestamp) AS runtime, datname, usename, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY 1 desc;

   Query statements are returned, sorted by execution time length in descending order. The first record is the query statement that takes the long time for execution.

   Alternatively, you can use the TIMESTAMPDIFF function to set current_timestamp and query_start to be greater than a threshold to identify query statements that are executed for a duration longer than this threshold. The first parameter of timestampdiff is the time difference unit. For example, execute the following statement to query the statements whose execution lasts more than 2 minutes:

   SELECT query FROM pg_stat_activity WHERE (EXTRACT(DAY FROM (current_timestamp - query_start)) * 24 * 60 + EXTRACT(HOUR FROM (current_timestamp - query_start)) * 60 + EXTRACT(MINUTE FROM (current_timestamp - query_start))) > 2;
       
   -- In B-compatible mode, run the following statement:
   SELECT query FROM pg_stat_activity WHERE timestampdiff(minutes, query_start, current_timestamp) > 2;

4. Analyze the status of the query statements that were run for a long time.

   • If the query statement is normal, wait until the execution of the query statement is complete.
   • If the query statement is blocked, rectify the fault by referring to Analyzing Whether a Query Statement Is Blocked.