最佳实践：数据推送定时推播Hologres Query诊断信息

-- 查出慢 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}'
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}'
ORDER BY duration DESC
LIMIT 50;

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;

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;

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;

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;

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;

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;