ClickHouse数据库的监控与运维：监控指标、监控工具、运维策略、故障处理

# prometheus.yml
scrape_configs:
  - job_name: 'clickhouse'
    static_configs:
      - targets: ['clickhouse1:9363', 'clickhouse2:9363', 'clickhouse3:9363']
  - job_name: 'zookeeper'
    static_configs:
      - targets: ['zookeeper1:9141', 'zookeeper2:9141', 'zookeeper3:9141']

-- 查看查询状态
select * from system.processes;

-- 查看查询历史
select * from system.query_log order by event_time desc limit 100;

-- 查看复制状态
select * from system.replication_queue;

-- 查看表大小
select table, sum(bytes) as size from system.parts group by table;

<logger>
    <level>information</level>
    <log>/var/log/clickhouse-server/clickhouse-server.log</log>
    <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
    <size>100m</size>
    <count>10</count>
</logger>

#!/bin/bash

# 备份表结构
clickhouse-client --query="show create table database.table" > /backup/table_structure_$(date +%y%m%d).sql

# 备份数据
clickhouse-client --query="backup table database.table to disk('backup', 'table_backup_$(date +%y%m%d)')"

-- 合并分区
optimize table events final;

-- 重建索引
alter table events drop index idx_event_type;
alter table events add index idx_event_type event_type type minmax granularity 1;

#!/bin/bash

# 检查 clickhouse 状态
systemctl status clickhouse-server

# 检查查询性能
clickhouse-client --query="select query, time, read_rows, written_rows from system.query_log where event_time > now() - interval 1 hour order by time desc limit 10"

# 检查复制状态
clickhouse-client --query="select * from system.replication_queue"

<clickhouse>
    <!-- 内存配置 -->
    <max_memory_usage>32gb</max_memory_usage>
    <max_bytes_before_external_group_by>20gb</max_bytes_before_external_group_by>
    <max_bytes_before_external_sort>20gb</max_bytes_before_external_sort>
    
    <!-- 并发配置 -->
    <max_concurrent_queries>100</max_concurrent_queries>
    <background_pool_size>16</background_pool_size>
    
    <!-- 日志配置 -->
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>100m</size>
        <count>10</count>
    </logger>
</clickhouse>

<users>
    <default>
        <password>default_password</password>
        <networks>
            <ip>127.0.0.1</ip>
        </networks>
        <profile>default</profile>
        <quota>default</quota>
    </default>
    <admin>
        <password_sha256_hex>admin_password_hash</password_sha256_hex>
        <networks>
            <ip>192.168.1.0/24</ip>
        </networks>
        <profile>admin</profile>
        <quota>admin</quota>
    </admin>
</users>

<openssl>
    <server>
        <certificatefile>/etc/clickhouse-server/server.crt</certificatefile>
        <privatekeyfile>/etc/clickhouse-server/server.key</privatekeyfile>
        <dhparamsfile>/etc/clickhouse-server/dhparams.pem</dhparamsfile>
        <verificationmode>none</verificationmode>
        <loaddefaultcafile>true</loaddefaultcafile>
        <cachesessions>true</cachesessions>
        <disableprotocols>sslv2,sslv3</disableprotocols>
    </server>
</openssl>

groups:
- name: clickhouse_alerts
  rules:
  - alert: clickhousedown
    expr: up{job="clickhouse"} == 0
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "clickhouse 节点宕机"
      description: "{{ $labels.instance }} 节点已宕机超过 5 分钟"

  - alert: highcpuusage
    expr: (100 - (avg by(instance) (irate(node_cpu_seconds_total{mode="idle"}[5m])) * 100)) > 80
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "cpu 使用率过高"
      description: "{{ $labels.instance }} cpu 使用率超过 80% 已持续 10 分钟"

  - alert: highmemoryusage
    expr: (node_memory_memtotal_bytes - node_memory_memavailable_bytes) / node_memory_memtotal_bytes * 100 > 90
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "内存使用率过高"
      description: "{{ $labels.instance }} 内存使用率超过 90% 已持续 10 分钟"

  - alert: diskspacelow
    expr: (node_filesystem_size_bytes{mountpoint="/"} - node_filesystem_free_bytes{mountpoint="/"}) / node_filesystem_size_bytes{mountpoint="/"} * 100 > 85
    for: 10m
    labels:
      severity: warning
    annotations:
      summary: "磁盘空间不足"
      description: "{{ $labels.instance }} 磁盘使用率超过 85% 已持续 10 分钟"

  - alert: replicationdelay
    expr: clickhouse_replication_delay > 300
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "复制延迟过高"
      description: "{{ $labels.instance }} 复制延迟超过 300 秒已持续 5 分钟"