HomeMogDBMogDB StackUqbar
v3.1

Documentation:v3.1

Supported Versions:

Other Versions:

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.

Copyright © 2011-2024 www.enmotech.com All rights reserved.