第99集ClickHouse数据库运维监控与优化实战

1. ClickHouse数据库运维监控概述

ClickHouse作为高性能列式数据库，在生产环境中需要专业的运维监控和管理。本文将详细介绍ClickHouse集群监控、查询优化、存储管理、性能调优的完整解决方案，帮助运维人员有效管理ClickHouse集群。

1.1 核心挑战

集群监控: 实时监控ClickHouse集群和节点状态
查询优化: 优化查询性能和资源使用
存储管理: 管理数据存储和压缩
性能调优: 优化ClickHouse性能和吞吐量
故障诊断: 快速定位ClickHouse相关问题

1.2 技术架构

ClickHouse监控 → 数据采集 → 性能分析 → 告警通知 → 自动优化
   ↓        ↓         ↓         ↓         ↓
集群指标 → 监控代理 → 数据存储 → 告警引擎 → 调优脚本
   ↓        ↓         ↓         ↓         ↓
查询管理 → 存储优化 → 性能调优 → 自动修复 → 运维记录

2. ClickHouse监控系统

2.1 Maven依赖配置

<!-- pom.xml -->
<dependencies>
    <!-- Spring Boot Web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- Spring Boot Data Redis -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-redis</artifactId>
    </dependency>
    
    <!-- ClickHouse JDBC Driver -->
    <dependency>
        <groupId>com.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.4.6</version>
    </dependency>
    
    <!-- Micrometer监控 -->
    <dependency>
        <groupId>io.micrometer</groupId>
        <artifactId>micrometer-registry-prometheus</artifactId>
    </dependency>
    
    <!-- MyBatis Plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.2</version>
    </dependency>
</dependencies>

2.2 应用配置

# application.yml
server:
  port: 8080

spring:
  redis:
    host: localhost
    port: 6379
    database: 0

# ClickHouse监控配置
clickhouse-monitor:
  jdbc-url: "jdbc:clickhouse://localhost:8123/default"  # ClickHouse连接地址
  cluster-name: "production-cluster"                      # 集群名称
  database: "monitoring"                                 # 监控数据库
  collection-interval: 10000                            # 采集间隔(毫秒)
  query-timeout: 30000                                  # 查询超时时间(毫秒)
  memory-threshold: 80                                  # 内存使用率告警阈值(%)
  disk-threshold: 85                                    # 磁盘使用率告警阈值(%)

3. ClickHouse监控服务

3.1 ClickHouse监控实体类

/**
 * ClickHouse集群监控数据实体类
 */
@Data
@TableName("clickhouse_cluster_monitor")
public class ClickHouseClusterMonitor {
    
    @TableId(type = IdType.AUTO)
    private Long id;                    // 主键ID
    
    private String clusterName;          // 集群名称
    
    private String hostname;             // 主机名
    
    private String ip;                   // IP地址
    
    private Integer nodeCount;           // 节点数量
    
    private Integer databaseCount;       // 数据库数量
    
    private Integer tableCount;          // 表数量
    
    private Long totalRows;              // 总行数
    
    private Long totalSize;              // 总大小(字节)
    
    private Double compressionRatio;      // 压缩比
    
    private Integer activeQueries;       // 活跃查询数
    
    private Integer queuedQueries;       // 排队查询数
    
    private Double avgQueryTime;         // 平均查询时间(毫秒)
    
    private Double maxQueryTime;         // 最大查询时间(毫秒)
    
    private Long memoryUsed;             // 内存使用量(字节)
    
    private Long memoryTotal;            // 总内存(字节)
    
    private Double memoryUsage;          // 内存使用率
    
    private Long diskUsed;               // 磁盘使用量(字节)
    
    private Long diskTotal;              // 总磁盘空间(字节)
    
    private Double diskUsage;            // 磁盘使用率
    
    private String clusterStatus;        // 集群状态
    
    private Date collectTime;            // 采集时间
    
    private Date createTime;             // 创建时间
}

/**
 * ClickHouse查询监控数据实体类
 */
@Data
@TableName("clickhouse_query_monitor")
public class ClickHouseQueryMonitor {
    
    @TableId(type = IdType.AUTO)
    private Long id;                    // 主键ID
    
    private String clusterName;          // 集群名称
    
    private String queryId;              // 查询ID
    
    private String queryText;            // 查询文本
    
    private String user;                 // 用户
    
    private String clientHost;           // 客户端主机
    
    private Long queryDuration;          // 查询持续时间(毫秒)
    
    private Long readRows;               // 读取行数
    
    private Long readBytes;             // 读取字节数
    
    private Long writtenRows;           // 写入行数
    
    private Long writtenBytes;          // 写入字节数
    
    private Long memoryUsage;           // 内存使用量(字节)
    
    private String queryStatus;         // 查询状态
    
    private String errorMessage;        // 错误消息
    
    private Date startTime;             // 开始时间
    
    private Date endTime;               // 结束时间
    
    private Date collectTime;           // 采集时间
    
    private Date createTime;            // 创建时间
}

3.2 ClickHouse监控服务

/**
 * ClickHouse监控服务
 * 负责ClickHouse集群和查询数据的采集、存储和分析
 */
@Service
public class ClickHouseMonitorService {

    @Autowired
    private ClickHouseClusterMonitorMapper clickHouseClusterMonitorMapper;
    
    @Autowired
    private ClickHouseQueryMonitorMapper clickHouseQueryMonitorMapper;
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private AlertService alertService;
    
    private ClickHouseDataSource dataSource;
    private ClickHouseConnection connection;

    /**
     * 初始化ClickHouse客户端
     */
    @PostConstruct
    public void initClickHouseClient() {
        try {
            // 创建数据源
            dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/default");
            
            // 创建连接
            connection = dataSource.getConnection();
            
            log.info("ClickHouse客户端初始化成功");
            
        } catch (Exception e) {
            log.error("初始化ClickHouse客户端失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 采集ClickHouse集群数据
     * 定期采集ClickHouse集群和查询信息
     */
    @Scheduled(fixedRate = 10000) // 每10秒执行一次
    public void collectClickHouseData() {
        try {
            // 1. 采集集群信息
            collectClusterInfo();
            
            // 2. 采集查询信息
            collectQueryInfo();
            
        } catch (Exception e) {
            log.error("采集ClickHouse数据失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 采集集群信息
     */
    private void collectClusterInfo() {
        try {
            // 1. 获取集群信息
            ClickHouseClusterInfo clusterInfo = getClusterInfo();
            
            // 2. 创建集群监控数据
            ClickHouseClusterMonitor monitorData = createClusterMonitorData(clusterInfo);
            
            // 3. 保存到数据库
            clickHouseClusterMonitorMapper.insert(monitorData);
            
            // 4. 更新缓存
            updateClusterCache(monitorData);
            
            // 5. 检查集群告警
            checkClusterAlert(monitorData);
            
            log.debug("采集集群信息: nodeCount={}, databaseCount={}", 
                    monitorData.getNodeCount(), monitorData.getDatabaseCount());
                    
        } catch (Exception e) {
            log.error("采集集群信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 获取集群信息
     */
    private ClickHouseClusterInfo getClusterInfo() {
        ClickHouseClusterInfo clusterInfo = new ClickHouseClusterInfo();
        
        try {
            // 获取集群统计信息
            setClusterStatistics(clusterInfo);
            
            // 获取数据库和表信息
            setDatabaseAndTableInfo(clusterInfo);
            
            // 获取查询统计信息
            setQueryStatistics(clusterInfo);
            
            // 获取资源使用信息
            setResourceUsageInfo(clusterInfo);
            
            // 设置集群状态
            clusterInfo.setClusterStatus(determineClusterStatus(clusterInfo));
            
        } catch (Exception e) {
            log.error("获取集群信息失败: {}", e.getMessage(), e);
        }
        
        return clusterInfo;
    }

    /**
     * 设置集群统计信息
     */
    private void setClusterStatistics(ClickHouseClusterInfo clusterInfo) {
        try {
            // 获取集群信息
            String sql = "SELECT * FROM system.clusters";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            int nodeCount = 0;
            while (resultSet.next()) {
                nodeCount++;
            }
            clusterInfo.setNodeCount(nodeCount);
            
            resultSet.close();
            statement.close();
            
        } catch (Exception e) {
            log.error("设置集群统计信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 设置数据库和表信息
     */
    private void setDatabaseAndTableInfo(ClickHouseClusterInfo clusterInfo) {
        try {
            // 获取数据库数量
            String sql = "SELECT COUNT(*) as db_count FROM system.databases";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setDatabaseCount(resultSet.getInt("db_count"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取表数量
            sql = "SELECT COUNT(*) as table_count FROM system.tables";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setTableCount(resultSet.getInt("table_count"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取总行数和总大小
            sql = "SELECT SUM(rows) as total_rows, SUM(data_compressed_bytes) as total_size FROM system.tables";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setTotalRows(resultSet.getLong("total_rows"));
                clusterInfo.setTotalSize(resultSet.getLong("total_size"));
            }
            
            resultSet.close();
            statement.close();
            
        } catch (Exception e) {
            log.error("设置数据库和表信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 设置查询统计信息
     */
    private void setQueryStatistics(ClickHouseClusterInfo clusterInfo) {
        try {
            // 获取活跃查询数
            String sql = "SELECT COUNT(*) as active_queries FROM system.processes";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setActiveQueries(resultSet.getInt("active_queries"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取排队查询数
            sql = "SELECT COUNT(*) as queued_queries FROM system.processes WHERE query_id != ''";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setQueuedQueries(resultSet.getInt("queued_queries"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取平均查询时间
            sql = "SELECT AVG(query_duration_ms) as avg_query_time FROM system.query_log WHERE event_date = today()";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setAvgQueryTime(resultSet.getDouble("avg_query_time"));
            }
            
            resultSet.close();
            statement.close();
            
        } catch (Exception e) {
            log.error("设置查询统计信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 设置资源使用信息
     */
    private void setResourceUsageInfo(ClickHouseClusterInfo clusterInfo) {
        try {
            // 获取内存使用信息
            String sql = "SELECT * FROM system.metrics WHERE metric = 'MemoryTracking'";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            if (resultSet.next()) {
                clusterInfo.setMemoryUsed(resultSet.getLong("value"));
            }
            
            resultSet.close();
            statement.close();
            
            // 获取磁盘使用信息
            sql = "SELECT * FROM system.disks";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            
            long totalDiskSpace = 0;
            long usedDiskSpace = 0;
            
            while (resultSet.next()) {
                totalDiskSpace += resultSet.getLong("total_space");
                usedDiskSpace += resultSet.getLong("used_space");
            }
            
            clusterInfo.setDiskTotal(totalDiskSpace);
            clusterInfo.setDiskUsed(usedDiskSpace);
            clusterInfo.setDiskUsage(totalDiskSpace > 0 ? (double) usedDiskSpace / totalDiskSpace * 100 : 0);
            
            resultSet.close();
            statement.close();
            
        } catch (Exception e) {
            log.error("设置资源使用信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 确定集群状态
     */
    private String determineClusterStatus(ClickHouseClusterInfo clusterInfo) {
        String status = "Healthy";
        
        if (clusterInfo.getMemoryUsage() > 90) {
            status = "HighMemoryUsage";
        } else if (clusterInfo.getDiskUsage() > 90) {
            status = "HighDiskUsage";
        } else if (clusterInfo.getActiveQueries() > 100) {
            status = "HighQueryLoad";
        } else if (clusterInfo.getAvgQueryTime() > 10000) {
            status = "SlowQueries";
        }
        
        return status;
    }

    /**
     * 创建集群监控数据
     */
    private ClickHouseClusterMonitor createClusterMonitorData(ClickHouseClusterInfo clusterInfo) {
        ClickHouseClusterMonitor monitorData = new ClickHouseClusterMonitor();
        
        // 设置基本信息
        monitorData.setClusterName(clusterInfo.getClusterName());
        monitorData.setHostname(getHostname());
        monitorData.setIp(getLocalIpAddress());
        monitorData.setCollectTime(new Date());
        monitorData.setCreateTime(new Date());
        
        // 设置集群信息
        monitorData.setNodeCount(clusterInfo.getNodeCount());
        monitorData.setDatabaseCount(clusterInfo.getDatabaseCount());
        monitorData.setTableCount(clusterInfo.getTableCount());
        
        // 设置数据信息
        monitorData.setTotalRows(clusterInfo.getTotalRows());
        monitorData.setTotalSize(clusterInfo.getTotalSize());
        monitorData.setCompressionRatio(clusterInfo.getCompressionRatio());
        
        // 设置查询信息
        monitorData.setActiveQueries(clusterInfo.getActiveQueries());
        monitorData.setQueuedQueries(clusterInfo.getQueuedQueries());
        monitorData.setAvgQueryTime(clusterInfo.getAvgQueryTime());
        monitorData.setMaxQueryTime(clusterInfo.getMaxQueryTime());
        
        // 设置资源信息
        monitorData.setMemoryUsed(clusterInfo.getMemoryUsed());
        monitorData.setMemoryTotal(clusterInfo.getMemoryTotal());
        monitorData.setMemoryUsage(clusterInfo.getMemoryUsage());
        monitorData.setDiskUsed(clusterInfo.getDiskUsed());
        monitorData.setDiskTotal(clusterInfo.getDiskTotal());
        monitorData.setDiskUsage(clusterInfo.getDiskUsage());
        
        // 设置状态信息
        monitorData.setClusterStatus(clusterInfo.getClusterStatus());
        
        return monitorData;
    }

    /**
     * 采集查询信息
     */
    private void collectQueryInfo() {
        try {
            // 获取当前查询信息
            String sql = "SELECT * FROM system.processes WHERE query_id != ''";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {
                try {
                    // 创建查询监控数据
                    ClickHouseQueryMonitor queryMonitor = createQueryMonitorData(resultSet);
                    
                    // 保存到数据库
                    clickHouseQueryMonitorMapper.insert(queryMonitor);
                    
                    // 更新缓存
                    updateQueryCache(queryMonitor);
                    
                    // 检查查询告警
                    checkQueryAlert(queryMonitor);
                    
                } catch (Exception e) {
                    log.error("处理查询信息失败: queryId={}, error={}", 
                            resultSet.getString("query_id"), e.getMessage());
                }
            }
            
            resultSet.close();
            statement.close();
            
        } catch (Exception e) {
            log.error("采集查询信息失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 创建查询监控数据
     */
    private ClickHouseQueryMonitor createQueryMonitorData(ResultSet resultSet) throws SQLException {
        ClickHouseQueryMonitor queryMonitor = new ClickHouseQueryMonitor();
        
        // 设置基本信息
        queryMonitor.setClusterName("production-cluster");
        queryMonitor.setQueryId(resultSet.getString("query_id"));
        queryMonitor.setQueryText(resultSet.getString("query"));
        queryMonitor.setUser(resultSet.getString("user"));
        queryMonitor.setClientHost(resultSet.getString("client_host"));
        queryMonitor.setCollectTime(new Date());
        queryMonitor.setCreateTime(new Date());
        
        // 设置查询统计信息
        queryMonitor.setQueryDuration(resultSet.getLong("elapsed"));
        queryMonitor.setReadRows(resultSet.getLong("read_rows"));
        queryMonitor.setReadBytes(resultSet.getLong("read_bytes"));
        queryMonitor.setWrittenRows(resultSet.getLong("written_rows"));
        queryMonitor.setWrittenBytes(resultSet.getLong("written_bytes"));
        queryMonitor.setMemoryUsage(resultSet.getLong("memory_usage"));
        
        // 设置状态信息
        queryMonitor.setQueryStatus(resultSet.getString("query_status"));
        queryMonitor.setErrorMessage(resultSet.getString("exception"));
        
        // 设置时间信息
        queryMonitor.setStartTime(resultSet.getTimestamp("start_time"));
        queryMonitor.setEndTime(resultSet.getTimestamp("end_time"));
        
        return queryMonitor;
    }

    /**
     * 更新集群缓存
     */
    private void updateClusterCache(ClickHouseClusterMonitor monitorData) {
        try {
            String cacheKey = "clickhouse:cluster:" + monitorData.getClusterName();
            redisTemplate.opsForValue().set(cacheKey, monitorData, Duration.ofMinutes(5));
            
        } catch (Exception e) {
            log.warn("更新集群缓存失败: {}", e.getMessage());
        }
    }

    /**
     * 更新查询缓存
     */
    private void updateQueryCache(ClickHouseQueryMonitor queryMonitor) {
        try {
            String cacheKey = "clickhouse:query:" + queryMonitor.getQueryId();
            redisTemplate.opsForValue().set(cacheKey, queryMonitor, Duration.ofMinutes(5));
            
        } catch (Exception e) {
            log.warn("更新查询缓存失败: {}", e.getMessage());
        }
    }

    /**
     * 检查集群告警
     */
    private void checkClusterAlert(ClickHouseClusterMonitor monitorData) {
        try {
            String alertType = null;
            String alertLevel = null;
            String alertMessage = null;
            
            // 检查内存使用率告警
            if (monitorData.getMemoryUsage() > 90) {
                alertType = "CLICKHOUSE_MEMORY_HIGH";
                alertLevel = "CRITICAL";
                alertMessage = String.format("ClickHouse内存使用率过高: %.2f%%", monitorData.getMemoryUsage());
            } else if (monitorData.getMemoryUsage() > 80) {
                alertType = "CLICKHOUSE_MEMORY_WARNING";
                alertLevel = "WARNING";
                alertMessage = String.format("ClickHouse内存使用率较高: %.2f%%", monitorData.getMemoryUsage());
            }
            
            // 检查磁盘使用率告警
            if (monitorData.getDiskUsage() > 90) {
                alertType = "CLICKHOUSE_DISK_HIGH";
                alertLevel = "CRITICAL";
                alertMessage = String.format("ClickHouse磁盘使用率过高: %.2f%%", monitorData.getDiskUsage());
            } else if (monitorData.getDiskUsage() > 80) {
                alertType = "CLICKHOUSE_DISK_WARNING";
                alertLevel = "WARNING";
                alertMessage = String.format("ClickHouse磁盘使用率较高: %.2f%%", monitorData.getDiskUsage());
            }
            
            // 检查查询负载告警
            if (monitorData.getActiveQueries() > 100) {
                alertType = "CLICKHOUSE_QUERY_LOAD_HIGH";
                alertLevel = "WARNING";
                alertMessage = String.format("ClickHouse查询负载过高: %d", monitorData.getActiveQueries());
            }
            
            // 发送告警
            if (alertType != null) {
                sendClusterAlert(monitorData, alertType, alertLevel, alertMessage);
            }
            
        } catch (Exception e) {
            log.error("检查集群告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 检查查询告警
     */
    private void checkQueryAlert(ClickHouseQueryMonitor queryMonitor) {
        try {
            String alertType = null;
            String alertLevel = null;
            String alertMessage = null;
            
            // 检查查询时间告警
            if (queryMonitor.getQueryDuration() > 30000) {
                alertType = "CLICKHOUSE_QUERY_SLOW";
                alertLevel = "WARNING";
                alertMessage = String.format("ClickHouse查询执行时间过长: %dms, queryId: %s", 
                        queryMonitor.getQueryDuration(), queryMonitor.getQueryId());
            }
            
            // 检查查询错误告警
            if (queryMonitor.getErrorMessage() != null && !queryMonitor.getErrorMessage().isEmpty()) {
                alertType = "CLICKHOUSE_QUERY_ERROR";
                alertLevel = "WARNING";
                alertMessage = String.format("ClickHouse查询执行错误: %s, queryId: %s", 
                        queryMonitor.getErrorMessage(), queryMonitor.getQueryId());
            }
            
            // 发送告警
            if (alertType != null) {
                sendQueryAlert(queryMonitor, alertType, alertLevel, alertMessage);
            }
            
        } catch (Exception e) {
            log.error("检查查询告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 发送集群告警
     */
    private void sendClusterAlert(ClickHouseClusterMonitor monitorData, String alertType, String alertLevel, String alertMessage) {
        try {
            String alertKey = "clickhouse:cluster:alert:" + monitorData.getClusterName() + ":" + alertType;
            Boolean hasAlert = redisTemplate.hasKey(alertKey);
            
            if (hasAlert == null || !hasAlert) {
                AlertMessage alert = new AlertMessage();
                alert.setType(alertType);
                alert.setLevel(alertLevel);
                alert.setMessage(alertMessage);
                alert.setTimestamp(new Date());
                alert.setHostname(monitorData.getHostname());
                
                alertService.sendAlert(alert);
                
                redisTemplate.opsForValue().set(alertKey, "1", Duration.ofMinutes(5));
                
                log.warn("发送集群告警: clusterName={}, type={}, level={}", 
                        monitorData.getClusterName(), alertType, alertLevel);
            }
            
        } catch (Exception e) {
            log.error("发送集群告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 发送查询告警
     */
    private void sendQueryAlert(ClickHouseQueryMonitor queryMonitor, String alertType, String alertLevel, String alertMessage) {
        try {
            String alertKey = "clickhouse:query:alert:" + queryMonitor.getQueryId() + ":" + alertType;
            Boolean hasAlert = redisTemplate.hasKey(alertKey);
            
            if (hasAlert == null || !hasAlert) {
                AlertMessage alert = new AlertMessage();
                alert.setType(alertType);
                alert.setLevel(alertLevel);
                alert.setMessage(alertMessage);
                alert.setTimestamp(new Date());
                alert.setHostname(queryMonitor.getClientHost());
                
                alertService.sendAlert(alert);
                
                redisTemplate.opsForValue().set(alertKey, "1", Duration.ofMinutes(5));
                
                log.warn("发送查询告警: queryId={}, type={}, level={}", 
                        queryMonitor.getQueryId(), alertType, alertLevel);
            }
            
        } catch (Exception e) {
            log.error("发送查询告警失败: {}", e.getMessage(), e);
        }
    }

    /**
     * 获取实时集群数据
     */
    public ClickHouseClusterMonitor getRealTimeClusterData(String clusterName) {
        String cacheKey = "clickhouse:cluster:" + clusterName;
        return (ClickHouseClusterMonitor) redisTemplate.opsForValue().get(cacheKey);
    }

    /**
     * 获取实时查询数据
     */
    public ClickHouseQueryMonitor getRealTimeQueryData(String queryId) {
        String cacheKey = "clickhouse:query:" + queryId;
        return (ClickHouseQueryMonitor) redisTemplate.opsForValue().get(cacheKey);
    }

    /**
     * 获取主机名
     */
    private String getHostname() {
        try {
            return InetAddress.getLocalHost().getHostName();
        } catch (UnknownHostException e) {
            return "unknown";
        }
    }

    /**
     * 获取本地IP地址
     */
    private String getLocalIpAddress() {
        try {
            return InetAddress.getLocalHost().getHostAddress();
        } catch (UnknownHostException e) {
            return "127.0.0.1";
        }
    }

    /**
     * 关闭ClickHouse客户端
     */
    @PreDestroy
    public void shutdownClickHouseClient() {
        try {
            if (connection != null) {
                connection.close();
            }
            
            log.info("ClickHouse客户端关闭成功");
            
        } catch (Exception e) {
            log.error("关闭ClickHouse客户端失败: {}", e.getMessage(), e);
        }
    }
}

/**
 * ClickHouse集群信息实体类
 */
@Data
public class ClickHouseClusterInfo {
    private String clusterName;          // 集群名称
    private Integer nodeCount;           // 节点数量
    private Integer databaseCount;       // 数据库数量
    private Integer tableCount;          // 表数量
    private Long totalRows;              // 总行数
    private Long totalSize;              // 总大小
    private Double compressionRatio;     // 压缩比
    private Integer activeQueries;       // 活跃查询数
    private Integer queuedQueries;       // 排队查询数
    private Double avgQueryTime;         // 平均查询时间
    private Double maxQueryTime;         // 最大查询时间
    private Long memoryUsed;             // 内存使用量
    private Long memoryTotal;            // 总内存
    private Double memoryUsage;          // 内存使用率
    private Long diskUsed;               // 磁盘使用量
    private Long diskTotal;              // 总磁盘空间
    private Double diskUsage;            // 磁盘使用率
    private String clusterStatus;        // 集群状态
}

4. ClickHouse管理服务

4.1 ClickHouse管理服务

/**
 * ClickHouse管理服务
 * 提供ClickHouse集群管理功能
 */
@Service
public class ClickHouseManagementService {

    @Autowired
    private ClickHouseDataSource dataSource;
    
    @Autowired
    private AlertService alertService;

    /**
     * 创建数据库
     */
    public void createDatabase(String databaseName) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format("CREATE DATABASE IF NOT EXISTS %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("创建数据库成功: databaseName={}", databaseName);
            
        } catch (Exception e) {
            log.error("创建数据库失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
        }
    }

    /**
     * 删除数据库
     */
    public void dropDatabase(String databaseName) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format("DROP DATABASE IF EXISTS %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("删除数据库成功: databaseName={}", databaseName);
            
        } catch (Exception e) {
            log.error("删除数据库失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
        }
    }

    /**
     * 创建表
     */
    public void createTable(String databaseName, String tableName, String columns) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format(
                "CREATE TABLE IF NOT EXISTS %s.%s (%s) ENGINE = MergeTree() ORDER BY tuple()",
                databaseName, tableName, columns
            );
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("创建表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("创建表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 删除表
     */
    public void dropTable(String databaseName, String tableName) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format("DROP TABLE IF EXISTS %s.%s", databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("删除表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("删除表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 优化表
     */
    public void optimizeTable(String databaseName, String tableName) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format("OPTIMIZE TABLE %s.%s", databaseName, tableName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.execute();
            
            statement.close();
            connection.close();
            
            log.info("优化表成功: databaseName={}, tableName={}", databaseName, tableName);
            
        } catch (Exception e) {
            log.error("优化表失败: databaseName={}, tableName={}, error={}", 
                    databaseName, tableName, e.getMessage(), e);
        }
    }

    /**
     * 获取数据库列表
     */
    public List<String> getDatabases() {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = "SHOW DATABASES";
            
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            List<String> databases = new ArrayList<>();
            while (resultSet.next()) {
                databases.add(resultSet.getString("name"));
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return databases;
            
        } catch (Exception e) {
            log.error("获取数据库列表失败: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 获取表列表
     */
    public List<String> getTables(String databaseName) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String sql = String.format("SHOW TABLES FROM %s", databaseName);
            
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            
            List<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                tables.add(resultSet.getString("name"));
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return tables;
            
        } catch (Exception e) {
            log.error("获取表列表失败: databaseName={}, error={}", databaseName, e.getMessage(), e);
            return new ArrayList<>();
        }
    }

    /**
     * 执行查询
     */
    public ResultSet executeQuery(String sql) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            return statement.executeQuery();
            
        } catch (Exception e) {
            log.error("执行查询失败: sql={}, error={}", sql, e.getMessage(), e);
            return null;
        }
    }

    /**
     * 获取查询执行计划
     */
    public String getQueryPlan(String sql) {
        try {
            ClickHouseConnection connection = dataSource.getConnection();
            String explainSql = "EXPLAIN " + sql;
            
            PreparedStatement statement = connection.prepareStatement(explainSql);
            ResultSet resultSet = statement.executeQuery();
            
            StringBuilder plan = new StringBuilder();
            while (resultSet.next()) {
                plan.append(resultSet.getString(1)).append("\n");
            }
            
            resultSet.close();
            statement.close();
            connection.close();
            
            return plan.toString();
            
        } catch (Exception e) {
            log.error("获取查询执行计划失败: sql={}, error={}", sql, e.getMessage(), e);
            return "";
        }
    }
}

5. 总结

本文详细介绍了ClickHouse数据库运维监控与管理的完整解决方案，包括：

5.1 核心技术点

集群监控: 实时监控ClickHouse集群和节点状态
查询监控: 监控查询性能和执行状态
资源监控: 监控内存、磁盘使用情况
数据管理: 管理数据库、表、查询优化
告警通知: 多级告警、智能通知

5.2 架构优势

实时监控: 10秒间隔的实时ClickHouse数据采集
智能告警: 基于阈值的智能告警机制
自动化管理: 自动化的数据库和表管理
多维度分析: 集群、查询、资源等多维度分析

5.3 最佳实践

监控策略: 设置合理的ClickHouse监控阈值
管理策略: 根据业务需求执行数据管理
性能优化: 合理配置ClickHouse参数
预防措施: 提前预防ClickHouse集群问题

通过以上架构设计，可以构建完善的ClickHouse运维监控系统，实现ClickHouse集群的有效管理和优化。