Platform Engineering

Appearance

some sql to use on doris for troubleshooting ​

compare number of queries going into each workload group replace the time.

sql
SELECT 
  workload_group, count(*) 
FROM __internal_schema.audit_log 
WHERE 
  query_time > 1000 
  AND time > '2025-05-28 13:40:00'
  AND time < '2025-05-28 13:50:00'
GROUP BY workload_group;

find specific audit logs

sql
select query_id, time, error_code, error_message, 
query_time, scan_rows, return_rows, workload_group, stmt  
from __internal_schema.audit_log 
where query_time > 1000 
and time > "2025-05-19 14:00:00" 
and workload_group="group_cold"
order by time asc\G

whether the logs have REGEXP

sql
SELECT
  (stmt LIKE '%REGEXP%') AS has_REGEXP, 
  COUNT(*) AS cnt
FROM __internal_schema.audit_log
WHERE
  query_time > 1000
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
  AND stmt LIKE '%limit 100001%'
  AND workload_group = 'group_cold'
GROUP BY has_REGEXP;

based on for queries, where are they going

sql
SELECT
  workload_group,
  COUNT(*) AS cnt
FROM __internal_schema.audit_log
WHERE
  query_time > 1000
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
GROUP BY workload_group;

inside group_cold for the slowest 50 queries, find out the slowness origin, are they limit 100001 or limit 50

sql
SELECT
  SUM(limit_100001),
  SUM(limit_50),
  workload_group
FROM (
    SELECT
      workload_group,
      (stmt LIKE '%limit 100001%') AS limit_100001, 
      (stmt LIKE '%limit 50%') AS limit_50,
      query_time
    FROM __internal_schema.audit_log
    WHERE
      query_time > 1000
      AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
      AND workload_group = 'group_cold'
    ORDER BY 
      query_time 
      DESC
) AS t
GROUP BY
  workload_group;

inside group_cold for the slowest 50 queries, what are the queries that is like limit 100001

sql
SELECT
  time,
  stmt,
  query_time
FROM __internal_schema.audit_log
WHERE
  query_time > 1000
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
  AND stmt LIKE '%limit 100001%'
  AND workload_group = 'group_cold'
  AND stmt like '%REGEXP%' 
ORDER BY 
  query_time 
  DESC
LIMIT 15\G

find out the patterns for %limit 100001%

sql
SELECT
  TRIM(
    SUBSTRING_INDEX(
      SUBSTRING(stmt, LOCATE('*/', stmt) + 2),
      ' where',
      1
    )
  ) AS extracted,
  MAX(query_time) AS avg_query_time,
  COUNT(*)       AS cnt
FROM __internal_schema.audit_log
WHERE
  query_time  > 1000
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
  AND stmt   LIKE '%limit 100001%'
  AND workload_group = 'group_cold'
  AND stmt like '%REGEXP%' 
GROUP BY extracted
ORDER BY avg_query_time DESC;

find out exactly what stmt is slow

sql
SELECT
  query_time,
  scan_rows,
  stmt, 
  FROM_UNIXTIME(
    CAST(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(stmt, 'from_microsecond(', -2),
            ')',
            1
        ) AS BIGINT
      ) / 1000000
  ) AS start_time_unix, 
  FROM_UNIXTIME(
    CAST(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(stmt, 'from_microsecond(', -1),
            ')',
            1
        ) AS BIGINT
    ) / 1000000
  ) AS end_time_unix
FROM __internal_schema.audit_log
WHERE
  stmt like '%REGEXP%' 
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
  AND stmt LIKE '%limit 100001%'
ORDER BY 
  query_time 
  DESC
LIMIT 15\G

checking whether there are any hot queries inside group_cold workload_group

sql
SELECT
    end_time_micro,
    stmt,
    workload_group,
    scan_rows,
    return_rows
FROM (
    SELECT
        stmt,
        workload_group,
        scan_rows,
        return_rows,
        CAST(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(stmt, 'from_microsecond(', -2),
                ')',
                1
            ) AS BIGINT
        ) AS end_time_micro
    FROM
        __internal_schema.audit_log
    WHERE
        query_time    > 1000
        AND stmt      NOT LIKE '%REGEXP%'
        AND workload_group = 'group_cold'
  AND time BETWEEN '2025/06/09 13:50:00' AND '2025/06/09 16:00:00'
        AND stmt       LIKE '%from_microsecond(%'
) AS t
WHERE
    end_time_micro < 1747965000000000;
sql
select 
query_id, time, error_code, error_message, 
query_time, scan_rows, return_rows, workload_group, stmt 
from __internal_schema.audit_log 
where query_time > 1000 and time > "2025-05-19 17:15:00" 
and workload_group="group_cold"
and stmt like '%limit 50%' and stmt like '1747645450291' 
order by time asc\G

filter out cold queries

sql
SELECT
  time_bucket,
  COUNT(*) AS cnt
FROM (
  SELECT
    CASE
      WHEN CAST(
             substring_index(
               substring_index(stmt, 'from_microsecond(', -1),
               ')',
               1
             ) AS BIGINT
           ) < 1747353848000000
        THEN 'older_than_3_days'
      ELSE 'within_3_days'
    END AS time_bucket
  FROM __internal_schema.audit_log
  WHERE query_time > 1000
    AND time       > '2025-05-19 14:00:00'
    AND stmt LIKE '%from_microsecond(%'
) t
GROUP BY time_bucket;

checking hot queries without REGEX going into cold

sql
SELECT
    end_time_micro,
    stmt,
    workload_group,
    scan_rows,
    return_rows
FROM (
    SELECT
        stmt,
        workload_group,
        scan_rows,
        return_rows,
        CAST(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(stmt, 'from_microsecond(', -2),
                ')',
                1
            ) AS BIGINT
        ) AS end_time_micro
    FROM __internal_schema.audit_log
    WHERE query_time > 1000
      AND stmt NOT LIKE '%REGEXP%'
      AND workload_group = 'group_cold'
      AND time > '2025-05-24 14:00:00'
      AND stmt LIKE '%from_microsecond(%'
) AS t
WHERE end_time_micro > 1748066400000000