Query管理

本文将为您介绍如何对实例中的Query进行诊断和管理。

Query管理概述

Hologres兼容PostgreSQL,可以通过查询hg_stat_activity(pg_stat_activity)视图信息来查看实例Query的运行信息,以达到分析和诊断运行SQL的目的。具体涉及的操作内容如下所示:

查询hg_stat_activity(pg_stat_activity)视图信息

从Hologres V2.0版本开始,pg_stat_activity升级为hg_stat_activity视图。hg_stat_activity是pg_stat_activity视图的扩展,可以通过hg_stat_activity查询到活跃SQL更详细的运行信息,包括query idquery engine以及资源消耗等信息,从而帮助更进一步诊断活跃Query。您可以执行如下命令查看Query的运行信息。其他Postgres兼容管理工具,依旧可以查询pg_stat_activity获取SQL运行态信息。

--2.0及以上推荐语法
SELECT *  FROM hg_stat_activity;

--1.3及以下版本语法
SELECT *  FROM pg_stat_activity;

hg_stat_activity视图的字段说明如下所示:

字段

描述

hg_stat_activity视图支持情况

pg_stat_activity视图支持情况

datid

Hologres后端连接到的数据库的OID。

支持

支持

datname

Hologres后端连接到的数据库的名称。

支持

支持

pid

Hologres后端的进程ID。

支持

支持

query_id

当前执行SQL的ID,一个SQL对应一个query_id。

支持

不支持

transaction_id

当前SQL所属的事务ID,一个事务中可能有多个SQL。

支持

不支持

usesysid

当前活跃会话用户的系统标识符。

支持

支持

usename

当前连接的用户名。

支持

支持

application_name

客户端的应用类型。

其中常见的应用类型如下:

  • 阿里云Flink(VVR版):{client_version}_ververica-connector-hologres

  • 开源Flink:{client_version}_hologres-connector-flink

  • DataWorks数据集成离线同步读取Hologres:datax_{jobId}

  • DataWorks数据集成离线同步写入Hologres:{client_version}_datax_{jobId}

  • DataWorks数据集成实时同步:{client_version}_streamx_{jobId}

  • HoloWeb:holoweb

  • 在MaxCompute中通过外部表的方式访问Hologres:MaxCompute

  • Holo Client发起的读Hologres Binlog进程:holo_client_replication。该类型的任务不会显示Query内容。

其余应用建议业务上在连接Hologres时连接串上显式指定application_name

支持

支持

running_info

包含SQL执行过程各种状态信息,格式为JSON格式。其子字段包括:

  • current_resource

    Serverless:仅使用Serverless资源执行的Query有该字段。

  • current_stage

    • stage_name<PARSE|OPTIMIZE|QUEUE|START|EXECUTE|FINISH>:

      • PARSE:SQL解析。

        说明

        如果SQL是PARSE阶段,则无法显示执行引擎engine_type。

      • OPTIMIZE:生成执行计划。

      • QUEUE:等待资源。

      • START:Query开始阶段。

      • EXECUTE:Query执行阶段。

      • FINISH:Query完成。

    • stage_duration_ms: 当前Stage到现在消耗的时间(单位毫秒)。

    • queue_time_ms:排队等待时长(单位:毫秒),仅Serverless Query有该字段。

    • serverless_allocated_cores:申请的Serverless资源量(单位:CU),仅Serverless Query有该字段。

    • serverless_allocated_workers:申请的Serverless资源Worker数(单位:个),仅Serverless Query有该字段。

  • engine_type:当前Query的执行引擎。

    • {HQE}:Hologres引擎。

    • {HQE,PQE}:走PQE的SQL。

    • {PG}:系统SQL,无需过多关注。

    • {SDK}:Fixed Plan SQL。

    • {HQE,SQE}:未使用直读的MaxCompute外部表SQL。

    • {HQE,HiveQE}: 未使用直读的OSS外部表SQL。

    • {HQE,SQE,HiveQE}:未直读的外部表SQL。

  • fe_id:FE节点对应的ID。

  • warehouse_id:如果实例是warehouse模式,代表warehouse的ID。

支持

不支持

extend_info

包含SQL执行过程资源消耗等的扩展信息,为JSON格式。其子字段包括:

  • total_cpu_max_time_ms:SQL累积CPU消耗(单位毫秒)。

  • total_mem_max_bytes:Query中各算子内存最大值之和(单位byte)。

  • scanned_rows:Query已读取数据的总行数。

  • affected_rows:DML语句影响的行数。

  • be_lock_waiters:当前SQL拿锁情况,表示当前SQL拿着锁,谁(Query_id)在等这个SQL释放锁。

  • be_lock_converting_waiters:当前SQL被锁的情况,表示当前SQL被谁(Query_id)锁住,需要等其释放锁。

说明

be_lock_waiters和be_lock_converting_waiters采集的都是backend的锁,而不是frontend的锁,排查锁请参见锁以及排查锁

支持

不支持

state

表示连接的状态。常见的状态如下:

  • active:活跃。

  • idle:空闲。

  • idle in transaction:长事务中的空闲状态。

  • idle in transaction(Aborted):已失败事务中的空闲状态。

  • \N: 状态为空,表示非用户连接的进程,一般属于系统后台的维护进程,可以忽略。

支持

支持

query_start

Query开始执行的时间,如果active不是active,取值为上一个查询开始的时间。

支持

支持

client_addr

客户端的IP地址。

显示的IP地址可能是被解析过的,不保证一定是源端IP。

支持

支持

client_hostname

客户端的主机名。

支持

支持

client_port

客户端的端口。

支持

支持

backend_start

后台进程开始的时间。

在Hologres中无需过多关注。

支持

支持

xact_start

该进程的当前事务被启动的时间。

  • 如果没有活动事务,则为空。

  • 如果当前查询是该进程的第一个事务,这一列等于query_start

在Hologres中无需过多关注。

支持

支持

state_change

连接的状态(state)上一次被改变的时间。

在Hologres中无需过多关注。

支持

支持

wait_event_type

后端正在等待的事件类型,如果不存在则为NULL。可能的取值有:

  • LWLock:后端正在等待一个轻量级锁。

  • Lock:后端正在等待一个重量级锁。wait_event将标识等待的锁的类型。

  • BufferPin:服务器进程正在等待访问一个数据缓冲区,而此时没有其他进程正在检查该缓冲区。

  • Activity:服务器进程处于闲置状态。被用于在其主处理循环中等待活动的系统进程。

  • Extension:服务器进程正在一个扩展模块中等待活动。

  • Client:服务器进程正在等待来自用户应用的某种查询,并且该服务器预期某种与其内部处理无关的事情发生。

  • PC:服务器进程正在等待来自服务器中另一个进程的某种活动。

  • Timeout:服务器进程正在等待一次超时发生。

  • IO:服务器进程正在等待一次IO完成。

支持

支持

wait_event

如果后端当前正在等待,则是等待事件的名称,否则为 NULL。

支持

支持

backend_xid

Hologres后端的顶层事务标识符。

支持

支持

backend_xmin

当前后端的xmin范围。

支持

支持

query

后端最近执行的查询。如果stateactive,将会显示当前正在执行的查询。在其他状态下,显示上一个被执行的查询。

支持

支持

backend_type

当前后端的类型。可能的类型为autovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker、background writer、client backend、checkpointer、startup、walreceiver、walsender以及 walwriter。除此之外还包括后端的执行组件,例如PQE等。

说明

需要关注client backend类型,代表应用的连接。

支持

支持

使用SQL查看活跃Query

若您更倾向于使用SQL方式查询活跃Query,可以使用以下SQL进行操作:

  1. 查看当前活跃Query以及对应的执行阶段和资源消耗:

    说明

    Superuser可以查看所有用户的SQL运行信息,RAM用户只能查看自己的SQL运行信息。

    --2.0及以上版本语法
    SELECT query,state,query_id,transaction_id,running_info, extend_info 
    FROM hg_stat_activity 
    WHERE state = 'active' 
    AND     backend_type = 'client backend'
    AND     application_name != 'hologres'
    
    --1.3及以下版本语法
    SELECT query,state,pid
    FROM pg_stat_activity 
    WHERE state = 'active' 
    AND     backend_type = 'client backend'
    AND     application_name != 'hologres'

    示例返回结果:

    -------------------------------------------------------------------------------
    query          | insert into test_hg_stat_activity select i, (i % 7) :: text, (i % 1007) from generate_series(1, 10000000)i;
    state          | active
    query_id       | 100713xxxx
    transaction_id | 100713xxxx
    running_info   | {"current_stage" : {"stage_duration_ms" :5994,
                                          "stage_name" :"EXECUTE" },
                       "engine_type" :"{HQE,PQE}",
                       "fe_id" :1,
                       "warehouse_id" :0 }
    extend_info    | {"affected_rows" :9510912,
                      "scanned_rows" :9527296 }
  2. 当前运行的Query按照CPU消耗排序:

    --2.0及以上语法
    SELECT query,((extend_info::json)->'total_cpu_max_time_ms')::text::bigint AS cpu_cost,state,query_id,transaction_id 
    FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;

    示例返回结果:

    ---------------------------------------------------------------------------------
    query          | select xxxxx
    cpu_cost       | 523461
    state          | active
    query_id       | 10053xxxx
    transaction_id | 10053xxxx
    ---------------------------------------------------------------------------------
    query          | insert xxxx
    cpu_cost       | 4817
    state          | active
    query_id       | 1008305xxx
    transaction_id | 1008305xxx
  3. 当前运行的Query按照内存消耗排序:

    --2.0及以上语法
    SELECT query,((extend_info::json)->'total_mem_max_bytes')::text::bigint AS mem_max_cost,state,query_id,transaction_id 
    FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;

    示例返回结果:

    ---------------------------------------------------------------------------------
    query          | update xxxx;
    mem_max_cost   | 5727634542
    state          | active
    query_id       | 10053302784827629
    transaction_id | 10053302784827629
    ---------------------------------------------------------------------------------
    query          | select xxxx;
    mem_max_cost   | 19535640
    state          | active
    query_id       | 10083259096119559
    transaction_id | 10083259096119559
  4. 查看当前实例正在运行且耗时较长的Query:

    --2.0及以上语法
    SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, query_id
        FROM hg_stat_activity
        WHERE state != 'idle'
        AND backend_type = 'client backend'
        AND application_name != 'hologres'
        ORDER BY 1 DESC;
    
    --1.3及以下语法
    SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, pid
        FROM pg_stat_activity
        WHERE state != 'idle'
        AND backend_type = 'client backend'
        AND application_name != 'hologres'
        ORDER BY 1 DESC;

    示例返回结果:

    runtime          |  datname  | usename  | query_id    |      current_query
    -----------------+----------------+----------+------------------------------------
     00:00:24.258388 | holotest  | 123xxx   | 1267xx | UPDATE xxx;
     00:00:1.186394  | testdb    | 156xx    | 1783xx | select xxxx;

    可以看到update耗时较久,已经运行了24s还没有结束。

HoloWeb可视化活跃Query管理

您可以通过HoloWeb可视化查看活跃Query,并进行管理。

  1. 登录HoloWeb控制台,详情请参见连接HoloWeb并执行查询

  2. 单击顶部导航栏的诊断与优化

  3. 在左侧导航栏选择活跃信息管理 > 活跃Query

  4. 进入活跃Query页面,单击查询,查看当前实例的活跃Query及对活跃Query进行管理。

    查询结果列表包含如下信息:

    参数

    说明

    Query Start

    查询开始时间。

    Runtime

    查询运行时间。

    PID

    查询服务进程ID。

    Query

    查询执行的SQL语句。

    State

    当前连接的状态。常见的状态如下:

    • active:活跃。

    • idle:空闲。

    • idle in transaction:长事务中的空闲状态。

    • idle in transaction(Aborted):已失败事务中的空闲状态。

    • \N: 状态为空,表示非用户连接的进程,一般属于系统后台的维护进程,可以忽略。

    User Name

    当前连接的用户名。

    Application

    查询应用类型。

    Client Address

    查询客户端的IP地址。

    如果Query长时间运行不结束,您可以单击对应Query操作列的取消,终结Query。也可以多选Query,执行批量取消操作。

  5. (可选)单击目标查询操作列的详情,查看当前查询的详细信息。

    您可在详情页面,执行如下操作:

    • 复制:复制当前查询执行的SQL语句。

    • 格式化:格式化当前查询执行的SQL语句。

排查锁

可以通过活跃Query排查当前SQL是否有锁或者被锁,详情请参见锁以及排查锁

终止Query

不符合预期的Query需要终止,可以通过以下命令语句操作。

  • 终止单个Query:

    SELECT pg_cancel_backend(<pid>);
  • 批量终止Query:

    SELECT pg_cancel_backend(pid)
            ,query
            ,datname
            ,usename
            ,application_name
            ,client_addr
            ,client_port
            ,backend_start
            ,state
    FROM    pg_stat_activity
    WHERE   length(query) > 0
    AND     pid != pg_backend_pid()
    AND     backend_type = 'client backend'
    AND     application_name != 'hologres'

修改活跃Query超时时间

Hologres支持您通过如下方式修改活跃Query运行超时时间。

  • 语法示例

    SET statement_timeout = <time>;
  • 参数说明

    time:超时时间取值范围为0~2147483647ms,单位默认为ms(当time后加单位时需要使用单引号,否则会报错)。当前默认超时时间为8小时,该设置针对session级别生效。

    说明

    SET statement_timeout = <time> 和要修改超时时间的SQL语句一起执行方可生效。

  • 使用示例

    • 设置超时时间为5000min,其中具体时间带单位,5000min需要整体添加单引号。

      SET statement_timeout = '5000min' ; 
      SELECT * FROM tablename;
    • 设置超时时间为5000ms。

      SET statement_timeout = 5000 ; 
      SELECT * FROM tablename;

修改空闲Query超时时间

参数idle_in_transaction_session_timeout描述了事务进入idle状态后的超时行为,如果不设置参数值,默认不会做事务超时的释放,容易发生事务不释放,导致查询被锁死的情况。Hologres支持您通过如下方式修改空闲Query运行超时时间。

  • 应用场景

    当Query执行产生死锁时,需要设置超时时间。例如如下代码,未执行commit,开启了一个事务,但是没有提交,会造成事务泄漏,进而引发数据库级别的死锁,影响服务正常使用。

    BEGIN; 
    SELECT * FROM t;

    当出现这种死锁场景时,可以通过设置idle_in_transaction_session_timeout超时时间来解决。当一个带事务的空闲连接超过idle_in_transaction_session_timeout设置的时间还未提交或者回滚事务,系统将自动根据超时时间回滚事务,并关闭连接。

  • 语法示例

    --session修改空闲事务超时时间
    SET idle_in_transaction_session_timeout=<time>;
    
    --DB级别修改空闲事务超时时间
    ALTER database db_name SET idle_in_transaction_session_timeout=<time>;
  • 参数说明

    time:超时时间取值范围为0~2147483647ms,单位默认为ms(当time后加单位时需要使用单引号,否则会报错)。在Hologres V0.10及以下版本,默认值为0,即不会自动清理;在Hologres V1.1版本,默认值为10分钟,超过10分钟后将会回滚事务。

    说明

    不建议超时时间设置过短,如果过短容易错误回滚正在使用中的事务。

  • 使用示例

    设置超时时间为300000ms。

    --session修改空闲事务超时时间
    SET idle_in_transaction_session_timeout=300000;
    
    --DB级别修改空闲事务超时时间
    ALTER database db_name SET idle_in_transaction_session_timeout=300000;

查询慢Query日志

从Hologres V0.10版本开始,支持进行慢query日志查询,详情请参见慢Query日志查看与分析

常见问题

  • 问题现象

    执行SQL后出现报错:ERROR: canceling statement due to statement timeout

  • 问题原因及解决方法

    • 原因1:客户端或Hologres实例设置了超时时间,常见的超时时间如下。

      • 通过数据服务生成API,数据服务的超时时间为10s,不可以修改,建议优化SQL以降低执行时间。

      • HoloWeb或DataWorks的Hologres SQL模块执行的Query,超时时间为1h,不可以修改,建议优化SQL以降低执行时间。

      • 为Hologres实例设置的超时时间,可以通过以下SQL查看设置的实例超时时间,如果是实例超时时间导致,可以根据业务情况重新设置合理的超时时间。

        SHOW statement_timeout;
      • 客户端或应用设置的超时时间,需要业务排查客户端的设置。如果是客户端设置的超时时间导致,可以根据业务情况重新设置合理的超时时间。

    • 原因2:执行DML SQL时对表执行了DROP或者Truncate操作导致超时。

      Truncate的原理是drop+create即先删除表再创建表。当执行DML SQL时,会获取行锁或表锁,锁相关内容请参见锁以及排查锁。此时再对这个表同时执行DROP或者Truncate,DROP或者Truncate操作会抢DML的锁,然后系统会将DML SQL取消,即出现statement timeout的报错。

      解决方法:通过慢Query日志排查是否同一时间对该表执行droptruncate操作,示例如下,需避免此类操作。

      --示例查询过去一天某张表执行的drop/truncate记录
      SELECT * FROM hologres.hg_query_log 
      WHERE command_tag IN ('DROP TABLE','TRUNCATE TABLE') 
      AND query LIKE '%xxx%' AND query_start >= now() - interval '1 day';