利用 DataWorks 数据推送定期推播 Hologres Query 诊断信息

前言

DataWorks 近期上线了数据推送功能，能够将数据库查询的数据组织后推送到各渠道 (如钉钉、飞书、企业微信及 Teams)，除了能将业务数据组织后推送，也能将数据库自身提供的监控数据组织后推送，这边我们就以 Hologres 为例，定期推播 Hologres 的慢 Query、数据访问量变化等信息，帮助用户掌握 Hologres 状态。

效果图

以下为一段时间内慢 Query 的信息整理，并推送到钉群、飞书、企业微信或 Teams

以下就列出几个 Hologres 诊断 Query 并推送的例子供参考：

实践：查看制造最多慢 Query 的使用者

查询制造最多慢 Query 的使用者，也可以更改 SQL 查询前十名最多慢 Query 数的用户。 以下为 SQL 内容

-- 查出慢 query 最多的 user
SELECT  usename as topuser,
        count(1) as topuser_counts
FROM    hologres.hg_query_log
WHERE   query_start >= '${date_start}'
AND     query_start < '${date_end}'
AND     duration > 1000
group by usename
order by topuser_counts desc
LIMIT   1
;

-- 统计前 50 笔慢 query
SELECT  usename
        ,datname
        ,duration
        ,application_name
FROM    hologres.hg_query_log
WHERE   query_start >= '${date_start}'
AND     query_start < '${date_end}'
AND     duration > 1000
order by duration desc
LIMIT   50
;

以下为推送内容设定

修改耗时的颜色条件判断

推送设置

推送结果

实践：查询近 12 小时消耗比较高的 Query

查询最近某个时间段（如近12小时内）消耗比较高的 Query，也可以根据业务需求修改具体时间，查询目标时间段消耗比较高的Query。(Query ID 可以用来定位，并在 HoloWeb 上查询)

以下为 SQL 内容

SELECT status AS "状态",
       duration AS "耗时(ms)",
       query_start AS "开始时间",
       (read_bytes/1048576)::text || ' MB' AS "读取量",
       (memory_bytes/1048576)::text || ' MB' AS "内存",
       (shuffle_bytes/1048576)::text || ' MB' AS "Shuffle",
       (cpu_time_ms/1000)::text || ' s' AS "CPU时间",
       physical_reads as "读盘",
       query_id as "QueryID",
       query::char(30)
 FROM hologres.hg_query_log
 WHERE query_start >= now() - interval '12 hour'
 and duration > 1000
 ORDER BY duration DESC,
          read_bytes DESC,
          shuffle_bytes DESC,
          memory_bytes DESC,
          cpu_time_ms DESC,
          physical_reads DESC
LIMIT 100;

以下为推送内容设定

推送结果

实践：查看最近 3 小时内每小时的 Query 访问量和数据读取总量

查看最近 3 小时内每小时的 Query 访问量和数据读取总量，用于对比判断每个小时是否有数据量变化。 以下为 SQL 内容

SELECT
    date_trunc('hour', query_start) AS query_start,
    count(1) AS query_count,
    sum(read_bytes/1048576)::text || ' MB' AS read_MB,
    sum(cpu_time_ms/1000)::text || ' s' AS cpu_time_s
FROM
    hologres.hg_query_log
WHERE
    query_start >= now() - interval '3 h'
GROUP BY 1;

以下为推送内容设定

推送结果

实践：查看昨日新增的 Query 数量

查看昨日新增的 Query 数量，时间区段可以更改 SQL，如改为上周、上个月等。 以下为 SQL 内容

SELECT
    COUNT(1) as "increse_queries"
FROM ( SELECT DISTINCT
        t1.digest
    FROM
        hologres.hg_query_log t1
    WHERE
        t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
        AND t1.query_start < CURRENT_DATE
        AND NOT EXISTS (
            SELECT
                1
            FROM
                hologres.hg_query_log t2
            WHERE
                t2.digest = t1.digest
                AND t2.query_start < CURRENT_DATE - INTERVAL '1 day')
        AND digest IS NOT NULL
 ) AS a;

以下为推送内容设定

实践：查看与昨天同一时间对比过去 3 小时的数据访问量情况

查看与昨天同一时间对比过去3小时的数据访问量情况，用于判断相较于昨日是否有数据量访问变化。 以下为 SQL 内容

SELECT
    query_date,
    count(1) AS query_count,
    sum(read_bytes) AS read_bytes,
    sum(cpu_time_ms) AS cpu_time_ms
FROM
    hologres.hg_query_log
WHERE
    query_start >= now() - interval '180min'
GROUP BY
    query_date
UNION ALL
SELECT
    query_date,
    count(1) AS query_count,
    sum(read_bytes) AS read_bytes,
    sum(cpu_time_ms) AS cpu_time_ms
FROM
    hologres.hg_query_log
WHERE
    query_start >= now() - interval '1d 180min'
    AND query_start <= now() - interval '1d'
GROUP BY
    query_date;

以下为推送内容设定

推送结果

实践：查询最近 30 分钟 Query 各阶段耗时比较高的 Query

查询最近时间段（如最近 30 分钟）Query 各阶段耗时比较高的 Query。您也可以根据业务需求修改具体时间，查询目标时间段 Query 各阶段耗时比较高的 Query。 以下为 SQL 内容

SELECT
    status AS "状态",
    duration AS "耗时(ms)",
    optimization_cost AS "优化耗时(ms)",
    start_query_cost AS "启动耗时(ms)",
    get_next_cost AS "执行耗时(ms)",
    duration - optimization_cost - start_query_cost - get_next_cost AS "其他耗时(ms)",
    query_id AS "QueryID"
FROM
    hologres.hg_query_log
WHERE
    query_start >= now() - interval '30 min'
ORDER BY
    duration DESC,
    start_query_cost DESC,
    optimization_cost,
    get_next_cost DESC,
    duration - optimization_cost - start_query_cost - get_next_cost DESC
LIMIT 100;

以下为推送内容设定

推送结果

实践：查询最先失败的 Query

查询一段时间内最先失败的 Query，可设定在系统初始化前后，帮助定位问题。 以下为 SQL 内容

SELECT
    status AS "状态",
    regexp_replace(message, '\n', ' ')::char(150) AS "报错信息",
    duration AS "耗时(ms)",
    query_start AS "开始时间",
    query_id AS "QueryID",
    query::char(100) AS "Query"
FROM
    hologres.hg_query_log
WHERE
    query_start BETWEEN '2024-07-10 17:00:00'::timestamptz AND '2024-07-10 17:42:00'::timestamptz + interval '2 min'
    AND status = 'FAILED'
ORDER BY
    query_start ASC
LIMIT 100;

以下为推送内容设定

推送结果

小结

Hologres 提供 Query Logs 查询运行的状态，能利用此数据加工后，透过 DataWorks 数据推送推至渠道。相同地，许多数据库也提供了 Logs 信息供查询，如 MySQL、PostgreSQL 等，皆能利用引擎储存的 Logs 再透过 DataWorks 数据推送推至渠道，提升引擎运行态的质量。

相关文章

数据推送功能详细介绍 help.aliyun.com/zh/datawork…

数据开发工作流 + 数据推送介绍 help.aliyun.com/zh/datawork…

Hologres 查看 Query Logs help.aliyun.com/zh/hologres…