ClickHouse 分区变更后计费任务卡死：一次隐藏锁竞争的排查与修复

-- 查看当前所有正在执行的查询及其等待时间
SELECT
    query_id,
    query,
    query_start_time,
    now() - query_start_time AS elapsed_seconds,
    thread_ids,
    peak_memory_usage
FROM system.processes
ORDER BY elapsed_seconds DESC
LIMIT 20;

-- 如果开启了 trace_log，查看规划阶段耗时
SELECT
    trace_type,
    count() AS cnt,
    avg(duration_ns) / 1e6 AS avg_ms
FROM system.trace_log
WHERE query_id IN ('<卡死查询的query_id>')
GROUP BY trace_type
ORDER BY avg_ms DESC;

<!-- /etc/clickhouse-server/config.xml -->
<query_trace_log>
    <database>system</database>
    <table>trace_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_trace_log>

-- 统计最近1小时内查询规划阶段耗时占比
SELECT
    query_id,
    sum(duration_ns) / 1e9 AS total_trace_sec,
    sumIf(duration_ns, trace_type = 'QueryPlan') / 1e9 AS plan_sec,
    plan_sec / total_trace_sec AS plan_ratio
FROM system.trace_log
WHERE event_date = today()
  AND event_time >= now() - INTERVAL 1 HOUR
GROUP BY query_id
ORDER BY plan_ratio DESC
LIMIT 10;

-- 实时查看处于规划阶段的查询数量（通过极低内存+长时间运行特征推断）
SELECT count() AS stuck_in_planning
FROM system.processes
WHERE peak_memory_usage < 1000  -- 几乎未分配内存
  AND now() - query_start_time > 5;  -- 运行超过5秒但内存极低

# 示例：将原本10并发改为3并发执行计费聚合
# 假设原始脚本用 xargs -P10 并发，改为 -P3
cat billing_queries.sql | xargs -I{} -P3 clickhouse-client --query "{}"

-- 检查当前表的分区数量
SELECT count() AS partition_count
FROM system.parts
WHERE database = 'billing_db' AND table = 'billing_events'
  AND active = 1;

-- 重新定义分区键（示例：从按天改为按月）
ALTER TABLE billing_events
  MODIFY PARTITION BY toYYYYMM(event_time);

#!/bin/bash
# check_planning_lock.sh — 每小时检测规划器锁竞争迹象
CH_CLIENT="clickhouse-client --host=ch-node1 --user=monitor --password=xxx"

STUCK_COUNT=$($CH_CLIENT --query="
    SELECT count() FROM system.processes
    WHERE peak_memory_usage < 1000 AND now() - query_start_time > 10
")

PARTITION_COUNT=$($CH_CLIENT --query="
    SELECT count() FROM system.parts
    WHERE database = 'billing_db' AND table = 'billing_events' AND active = 1
")

echo "$(date): stuck_in_planning=$STUCK_COUNT, partition_count=$PARTITION_COUNT"

# 告警阈值：超过5个查询疑似卡在规划阶段 或 分区数超过3000
if [ "$STUCK_COUNT" -gt 5 ] || [ "$PARTITION_COUNT" -gt 3000 ]; then
    echo "ALERT: potential planner lock contention" >&2
    # 可接入 PagerDuty / 钉钉 / Slack 告警
fi