Hologres:Query and analyze slow query logs

Limits

Query the hologres.hg_query_log table

Grant query permissions to users

Only authorized users can query slow query logs. When you grant query permissions to users, take note of the following items:

• A user can be granted permissions to query the slow query logs of all databases in a Hologres instance.

  • Grant the superuser permissions to a user.

    After the superuser permissions on a Hologres instance are granted to a user, the user can query the slow query logs of all databases in the Hologres instance.

    ALTER USER "Account ID" SUPERUSER;-- Replace Account ID with the Alibaba Cloud account ID of the user. If the user is a RAM user, prefix the account ID with p4_.

  • Add the user to the pg_read_all_stats user group.

    In addition to superusers, users in the pg_read_all_stats user group have the permissions to query the slow query logs of all databases in the relevant Hologres instance. If you are a superuser, you can add regular users to this user group. To add a user to this user group, execute one of the following statements:

    GRANT pg_read_all_stats TO "Account ID";-- Use the standard PostgreSQL authorization model to grant related permissions to the user.
    CALL spm_grant('pg_read_all_stats', 'Account ID');  -- Use the simple permission model (SPM) to grant related permissions to the user.
    CALL slpm_grant('pg_read_all_stats', 'Account ID'); -- Use the schema-level permission model (SLPM) to grant related permissions to the user.

• A user can be granted permissions to query the slow query logs of a specific database in a Hologres instance.

  Users in the db_admin user group have the permissions to query the slow query logs of the relevant database. If you are a superuser, you can add regular users to this user group by using the SPM or SLPM.

  CALL spm_grant('<db_name>_admin', 'Account ID');  -- Use the SPM to grant related permissions to the user.
  CALL slpm_grant('<db_name>.admin', 'Account ID'); -- Use the SLPM to grant related permissions to the user.

• A regular user has the permissions to query the logs of slow queries that were initiated by the regular user in the database within the relevant Alibaba Cloud account.

Query slow query logs in the HoloWeb console

1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb.

2. In the top navigation bar, click Diagnostics and Optimization.

3. In the left-side navigation pane, click Historical Slow Query.

4. In the upper part of the Historical Slow Query page, set the query conditions.

   For more information about the parameters on the Historical Slow Query page, see Query and analyze slow query logs.

5. Click Query and view the query results in the Query Trend Analysis and Queries sections.

   • Query Trend Analysis

     The Query Trend Analysis section displays the trends of slow queries or failed queries in the specified time range in a visualized manner. This allows you to find the periods during which slow queries and failed queries frequently occurred and troubleshoot issues in a centralized manner.

   • Queries

     The Queries section displays the details of slow queries and failed queries. For more information about the parameters in the Queries section, see Query and analyze slow query logs. You can also click Customize Columns to select the fields that you want to display in the Queries section.

Analyze slow query logs

Export slow query logs

Hologres allows you to execute the INSERT statement to export slow query logs from the hologres.hg_query_log table to a custom internal table or a foreign table such as a MaxCompute foreign table or an Object Storage Service (OSS) foreign table.

• Usage notes

  To correctly and efficiently export slow query logs, take note of the following items:

  • The account that is used to execute the INSERT INTO ... SELECT ... FROM hologres.hg_query_log; statement must have access permissions on the hologres.hg_query_log table. For more information, see Grant query permissions to users. To export all slow query logs of an instance, the account that is used to execute the INSERT statement must have the superuser permissions or the pg_read_all_stats permission. Otherwise, only part of the data in the hologres.hg_query_log table can be queried and exported.

  • An index is configured for the query_start field in the hologres.hg_query_log table. If you want to export data generated within a specific time range, use the index to query the data. This ensures better performance and consumes fewer resources.

  • If you use the index to query data, do not use nested expressions for the query_start field. Otherwise, the index may not work. For example, we recommend that you replace where to_char(query_start, 'yyyymmdd') = '20220101'; with where query_start >= to_char('20220101', 'yyyy-mm-dd') and query_start < to_char('20220102', 'yyyy-mm-dd') ;.

• Example 1: Export slow query logs to a Hologres internal table

  Execute the following statement in Hologres to export slow query logs to the query_log_download internal table:

  --Hologres SQL
  
  CREATE TABLE query_log_download (
      usename text,
      status text,
      query_id text,
      datname text,
      command_tag text,
      duration integer,
      message text,
      query_start timestamp with time zone,
      query_date text,
      query text,
      result_rows bigint,
      result_bytes bigint,
      read_rows bigint,
      read_bytes bigint,
      affected_rows bigint,
      affected_bytes bigint,
      memory_bytes bigint,
      shuffle_bytes bigint,
      cpu_time_ms bigint,
      physical_reads bigint,
      pid integer,
      application_name text,
      engine_type text[],
      client_addr text,
      table_write text,
      table_read text[],
      session_id text,
      session_start timestamp with time zone,
      trans_id text,
      command_id text,
      optimization_cost integer,
      start_query_cost integer,
      get_next_cost integer,
      extended_cost text,
      plan text,
      statistics text,
      visualization_info text,
      query_detail text,
      query_extinfo text[]
  );
  
  INSERT INTO query_log_download SELECT
    usename,
    status,
    query_id,
    datname,
    command_tag,
    duration,
    message,
    query_start,
    query_date,
    query,
    result_rows,
    result_bytes,
    read_rows,
    read_bytes,
    affected_rows,
    affected_bytes,
    memory_bytes,
    shuffle_bytes,
    cpu_time_ms,
    physical_reads,
    pid,
    application_name,
    engine_type,
    client_addr,
    table_write,
    table_read,
    session_id,
    session_start,
    trans_id,
    command_id,
    optimization_cost,
    start_query_cost,
    get_next_cost,
    extended_cost,
    plan,
    statistics,
    visualization_info,
    query_detail,
    query_extinfo
  FROM hologres.hg_query_log
  where query_start >= '2022-08-03'
    and query_start < '2022-08-04';

• Example 2: Export slow query logs to a MaxCompute foreign table

  1. Execute the following statement in MaxCompute to create a MaxCompute table to which you want to export slow query logs:

     CREATE TABLE if NOT EXISTS mc_holo_query_log (
         username STRING COMMENT 'The name of the user who initiated the query.'
         ,status STRING COMMENT 'The status of the query. The value indicates whether the query is successful.'
         ,query_id STRING COMMENT 'The ID of the query.'
         ,datname STRING COMMENT 'The name of the queried database.'
         ,command_tag STRING COMMENT 'The type of the statement executed in the query.'
         ,duration BIGINT COMMENT 'The amount of time consumed by the query, in milliseconds.'
         ,message STRING COMMENT 'The returned error message.'
         ,query STRING COMMENT 'The statement executed in the query.'
         ,read_rows BIGINT COMMENT 'The number of rows that were read by the query.'
         ,read_bytes BIGINT COMMENT 'The number of bytes that were read by the query.'
         ,memory_bytes BIGINT COMMENT 'The peak memory usage of a single node. The value is not an accurate value.'
         ,shuffle_bytes BIGINT COMMENT 'The number of bytes that were shuffled. The value is not an accurate value.'
         ,cpu_time_ms BIGINT COMMENT 'The total CPU time, in milliseconds. The value is not an accurate value.'
         ,physical_reads BIGINT COMMENT 'The number of physical reads.'
         ,application_name STRING COMMENT 'The type of the application that initiated the query.'
         ,engine_type ARRAY<STRING> COMMENT 'The engine used in the query.'
         ,table_write STRING COMMENT 'The table to which data was written.'
         ,table_read ARRAY<STRING> COMMENT 'The table from which data was read.'
         ,plan STRING COMMENT 'The execution plan of the query.'
         ,optimization_cost BIGINT COMMENT 'The amount of time consumed to generate an execution plan for the query.'
         ,start_query_cost BIGINT COMMENT 'The amount of time consumed to start the query.'
         ,get_next_cost BIGINT COMMENT 'The amount of time consumed to execute the query.'
         ,extended_cost STRING COMMENT 'The amount of time consumed by other operations of the query.'
         ,query_detail STRING COMMENT 'The extended information about the query in the JSON format.'
         ,query_extinfo ARRAY<STRING> COMMENT 'The extended information about the query in the form of an array.'
         ,query_start STRING COMMENT 'The point in time when the query was initiated.'
         ,query_date STRING COMMENT 'The date when the query was initiated.'
     ) COMMENT 'hologres instance query log daily'
     PARTITIONED BY (
         ds STRING COMMENT 'stat date'
     ) LIFECYCLE 365;
     
     ALTER TABLE mc_holo_query_log ADD PARTITION (ds=20220803);

  2. Execute the following statement in Hologres to export slow query logs to the MaxCompute foreign table:

     IMPORT FOREIGN SCHEMA project_name LIMIT TO (mc_holo_query_log)
     FROM SERVER odps_server INTO public;
     
     INSERT INTO mc_holo_query_log
     SELECT usename as username,
             status,
             query_id,
             datname,
             command_tag,
             duration,
             message,
             query,
             read_rows,
             read_bytes,
             memory_bytes,
             shuffle_bytes,
             cpu_time_ms,
             physical_reads,
             application_name,
             engine_type,
             table_write,
             table_read,
             plan,
             optimization_cost,
             start_query_cost,
             get_next_cost,
             extended_cost,
             query_detail,
             query_extinfo,
             query_start,
             query_date
             ,'20220803'
     from hologres.hg_query_log
     where query_start >= '2022-08-03'
       and query_start < '2022-08-04';

Configuration items

Hologres provides the following configuration items that allow you to configure the collection of slow query logs:

log_min_duration_statement

• Description

This configuration item is used to specify a threshold of the amount of time consumed by slow queries. By default, Hologres collects the logs of slow queries that consume more than 100 ms, and only queries that consume 1 second or longer are displayed in the query result. You can configure this configuration item to modify the duration. Take note of the following items:

• Modification of this configuration item takes effect only for new queries. For queries that have been collected, the original value takes effect.

• Only superusers can modify this configuration item.

• If you set this configuration item to -1, no slow query logs are collected. If you set this configuration item to a positive value, the minimum value is 100 ms.

• Examples

  Enable the system to collect the logs of slow queries that consume 250 ms or longer:

  -- Execute the following statement as a superuser for a database:
  ALTER DATABASE dbname SET log_min_duration_statement = '250ms';
  
  -- Execute the following statement as a regular user for a session:
  SET log_min_duration_statement = '250ms';

log_min_duration_query_stats

• Description

This configuration item is used to specify a threshold of the amount of time consumed by queries for the collection of execution statistics. By default, Hologres collects execution statistics about the slow queries that consume more than 10 seconds. Take note of the following items:

• Modification of this configuration item takes effect only for new queries. For queries that have been collected, the original value takes effect.

• If you set this configuration item to -1, no execution statistics about slow queries are collected.

• Such statistics require large storage space. If Hologres collects a large amount of execution statistics, the query and analysis of slow query logs may slow down. Therefore, we recommend that you specify a threshold of at least 10 seconds in general scenarios. Specify a threshold of less than 10 seconds only when you troubleshoot specific issues.

• Examples

  Enable the system to collect execution statistics about the slow queries that consume more than 20 seconds:

  -- Execute the following statement as a superuser for a database:
  ALTER DATABASE dbname SET log_min_duration_query_stats = '20s';
  
  -- Execute the following statement as a regular user for a session:
  SET log_min_duration_query_stats = '20s';

log_min_duration_query_plan

• Description

This configuration item is used to specify a threshold of the amount of time consumed by queries for the collection of execution plans. By default, Hologres displays the execution plans of slow queries that consume 10 or more seconds. Take note of the following items:

• Modification of this configuration item takes effect only for new queries. For queries that have been collected, the original value takes effect.

• If the amount of time consumed by a slow query is greater than or equal to the specified value, the execution plan of the slow query is collected.

• By default, you can execute the EXPLAIN statement to query execution plans in real time, and you do not need to collect such data.

• If you set this configuration item to -1, no execution plans of slow queries are collected.

• Examples

  Enable the system to collect the execution plans of slow queries that consume more than 10 seconds:

  -- Execute the following statement as a superuser for a database:
  ALTER DATABASE dbname SET log_min_duration_query_plan = '10s';
  
  -- Execute the following statement as a regular user for a session:
  SET log_min_duration_query_plan = '10s';

FAQ

• Problem description:

  In Hologres V1.1, detailed statistics such as the number of queried rows and the number of returned rows cannot be returned in queried slow query logs.

• Cause:

  The collected slow query logs are incomplete.

• Solution:

  In Hologres V1.1.36 to Hologres V1.1.49, you can execute the following statement to configure a GUC parameter, which enables the system to return complete slow query logs. Complete slow query logs are directly returned for Hologres V1.1.49 and later.

  Note

  If the version of your Hologres instance is earlier than V1.1.36, manually upgrade your Hologres instance or join the Hologres DingTalk group for technical support. For more information, see Instance upgrades and Obtain online support for Hologres.

  -- (Recommended) Configure the GUC parameter at the database level. You need to execute the following statement only once for each database: 
  alter databse <db_name> set hg_experimental_force_sync_collect_execution_statistics = on;
  
  -- Configure the GUC parameter at the session level.
  set hg_experimental_force_sync_collect_execution_statistics = on;

  The db_name parameter specifies the name of the database.

References

For more information about how to diagnose and manage queries in an instance, see Manage queries.