MCP Tools for ClickHouse — ReplicatedMergeTree health, system tables, replication queue lag, and merge backlog

ClickHouse HTTP interface for MCP tool authors

ClickHouse exposes two interfaces: HTTP (port 8123, used by MCP tools and monitoring) and native binary protocol (port 9000, used by the native ClickHouse client). The HTTP interface accepts GET and POST requests. POST with the query in the request body is preferred for queries containing special characters. The FORMAT clause at the end of the SQL determines the output format — FORMAT JSON returns a JSON object with a data array, FORMAT JSONEachRow returns one JSON object per line (NDJSON, more memory-efficient for large result sets).

ClickHouse Keeper (since 22.4) is ClickHouse's built-in ZooKeeper replacement. The system.zookeeper table allows querying the ZooKeeper namespace directly. If system.zookeeper returns an error, the ClickHouse node is disconnected from ZooKeeper/Keeper — all ReplicatedMergeTree tables on this node are degraded.

async function createClickHouseClient(host, port = 8123, user = 'default', password = '') {
  const baseUrl = `http://${host}:${port}`;

  async function query(sql, format = 'JSON') {
    const url = new URL(baseUrl);
    url.searchParams.set('user', user);
    url.searchParams.set('password', password);
    url.searchParams.set('database', 'system');

    const body = `${sql} FORMAT ${format}`;

    const res = await fetch(url.toString(), {
      method: 'POST',
      headers: {
        'Content-Type': 'text/plain',
        'X-ClickHouse-User': user,
        'X-ClickHouse-Key': password,
      },
      body: sql.includes('FORMAT') ? sql : `${sql} FORMAT ${format}`,
      signal: AbortSignal.timeout(30_000),
    });

    if (!res.ok) {
      const errBody = await res.text();
      throw new Error(`ClickHouse HTTP ${res.status}: ${errBody.slice(0, 500)}`);
    }

    if (format === 'JSON') {
      const result = await res.json();
      return result.data;
    }
    return res.text();
  }

  // Simple liveness check
  async function ping() {
    const res = await fetch(`${baseUrl}/ping`, { signal: AbortSignal.timeout(5_000) });
    return res.ok && (await res.text()).trim() === 'Ok.';
  }

  return { query, ping };
}

ReplicatedMergeTree health via system.replicas

The system.replicas table is the primary health source for ClickHouse replication. Each row represents one replicated table on this ClickHouse node. The most important columns are is_readonly (1 if the replica is in read-only mode — no writes accepted, typically a ZooKeeper connectivity issue), is_session_expired (1 if the ZooKeeper session for this table has expired), future_parts (parts in the replication queue not yet fetched — high values mean replication lag), parts_to_check (parts that need data integrity verification — high values indicate corruption or network issues during replication), and active_replicas vs total_replicas.

The last_queue_update column shows when the replication queue was last refreshed. If this timestamp is stale (e.g., not updated in the last 60 seconds despite active inserts), the replication queue is stuck — the replica cannot receive tasks from ZooKeeper. queue_oldest_time shows the oldest pending task timestamp; if this is hours old, there are stuck tasks that the replica cannot complete.

async function checkReplicaHealth(ch) {
  const replicas = await ch.query(`
    SELECT
      database,
      table,
      engine,
      is_leader,
      can_become_leader,
      is_readonly,
      is_session_expired,
      future_parts,
      parts_to_check,
      zookeeper_path,
      active_replicas,
      total_replicas,
      queue_size,
      queue_oldest_time,
      inserts_in_queue,
      merges_in_queue,
      log_max_index,
      log_pointer,
      last_queue_update,
      absolute_delay  -- seconds behind the leader replica
    FROM system.replicas
    ORDER BY database, table
  `);

  const problems = [];

  for (const r of replicas) {
    const tableId = `${r.database}.${r.table}`;

    if (r.is_readonly) {
      problems.push({
        table: tableId, severity: 'critical',
        type: 'readonly', detail: 'Replica in read-only mode — ZooKeeper session issue or disk full',
      });
    }

    if (r.is_session_expired) {
      problems.push({
        table: tableId, severity: 'critical',
        type: 'session_expired', detail: 'ZooKeeper session expired — replica cannot coordinate',
      });
    }

    // future_parts > 50: significant replication lag
    if (r.future_parts > 50) {
      problems.push({
        table: tableId, severity: r.future_parts > 200 ? 'critical' : 'warning',
        type: 'replication_lag', detail: `${r.future_parts} parts in replication queue`,
      });
    }

    // parts_to_check > 0: data integrity concerns
    if (r.parts_to_check > 0) {
      problems.push({
        table: tableId, severity: 'warning',
        type: 'parts_to_check', detail: `${r.parts_to_check} parts need integrity verification`,
      });
    }

    // absolute_delay > 300 seconds: replica is significantly behind leader
    const delay = parseInt(r.absolute_delay ?? '0', 10);
    if (delay > 300) {
      problems.push({
        table: tableId, severity: delay > 3600 ? 'critical' : 'warning',
        type: 'replica_delay', detail: `${delay}s behind leader replica`,
      });
    }

    // Not enough active replicas
    if (r.active_replicas < r.total_replicas) {
      problems.push({
        table: tableId, severity: 'warning',
        type: 'dead_replicas',
        detail: `${r.active_replicas}/${r.total_replicas} replicas active`,
      });
    }
  }

  return {
    totalTables: replicas.length,
    problems,
    healthy: problems.filter(p => p.severity === 'critical').length === 0,
    tables: replicas.map(r => ({
      id: `${r.database}.${r.table}`,
      isReadonly: !!r.is_readonly,
      futureParts: parseInt(r.future_parts ?? '0', 10),
      queueSize: parseInt(r.queue_size ?? '0', 10),
      absoluteDelaySecs: parseInt(r.absolute_delay ?? '0', 10),
      activeReplicas: parseInt(r.active_replicas ?? '0', 10),
      totalReplicas: parseInt(r.total_replicas ?? '0', 10),
    })),
  };
}

Merge backlog and part count explosion

ClickHouse's MergeTree family stores data in parts — immutable files written on each INSERT. Background merges combine small parts into larger ones. If inserts arrive faster than merges complete, the part count for a table grows without bound. Beyond ~300 active parts per partition, ClickHouse starts throttling or blocking inserts with the error Too many parts (N). Merges are processing significantly slower than inserts.

The system.merges table shows active background merges. The system.replication_queue table shows pending replication tasks including merge requests that haven't been picked up. An empty system.merges while system.replication_queue has hundreds of merge tasks indicates a background merge worker is stuck or limited by background_pool_size.

async function checkMergeHealth(ch) {
  const [activeMerges, replicationQueue, partCount] = await Promise.all([
    // Active background merges
    ch.query(`
      SELECT
        database, table,
        elapsed,
        progress,
        num_parts,
        source_part_names,
        result_part_name,
        total_size_bytes_compressed,
        bytes_read_uncompressed,
        rows_read
      FROM system.merges
    `),

    // Pending replication tasks (including merge tasks not yet started)
    ch.query(`
      SELECT
        database, table, type, num_tries, last_exception,
        create_time, postpone_reason
      FROM system.replication_queue
      WHERE num_tries > 3  -- Tasks that have retried multiple times
      ORDER BY num_tries DESC
      LIMIT 50
    `),

    // Tables with high part count (per partition)
    ch.query(`
      SELECT
        database,
        table,
        partition,
        count() AS part_count,
        sum(bytes_on_disk) AS total_bytes
      FROM system.parts
      WHERE active = 1
      GROUP BY database, table, partition
      HAVING part_count > 100  -- Alert threshold
      ORDER BY part_count DESC
      LIMIT 20
    `),
  ]);

  // Find long-running merges (> 10 minutes — may indicate a stuck merge)
  const longRunningMerges = activeMerges.filter(m => m.elapsed > 600);

  // Find stuck replication tasks (num_tries > 10 = repeatedly failing)
  const stuckTasks = replicationQueue.filter(r => parseInt(r.num_tries, 10) > 10);

  // High part count partitions — approaching insert throttle
  const criticalPartCount = partCount.filter(p => parseInt(p.part_count, 10) > 300);
  const warningPartCount = partCount.filter(p => {
    const count = parseInt(p.part_count, 10);
    return count > 100 && count <= 300;
  });

  const alerts = [
    ...longRunningMerges.map(m => ({
      severity: 'warning',
      type: 'long_merge',
      detail: `Merge on ${m.database}.${m.table} running for ${Math.round(m.elapsed / 60)}m (${(m.progress * 100).toFixed(1)}% complete)`,
    })),
    ...stuckTasks.map(t => ({
      severity: 'critical',
      type: 'stuck_replication_task',
      detail: `Replication task on ${t.database}.${t.table} failed ${t.num_tries} times: ${t.last_exception?.slice(0, 200)}`,
    })),
    ...criticalPartCount.map(p => ({
      severity: 'critical',
      type: 'part_count_explosion',
      detail: `${p.database}.${p.table} partition ${p.partition}: ${p.part_count} parts — inserts will be throttled`,
    })),
    ...warningPartCount.map(p => ({
      severity: 'warning',
      type: 'high_part_count',
      detail: `${p.database}.${p.table} partition ${p.partition}: ${p.part_count} parts — merges falling behind`,
    })),
  ];

  return {
    activeMerges: activeMerges.length,
    longRunningMerges: longRunningMerges.length,
    stuckReplicationTasks: stuckTasks.length,
    highPartCountPartitions: partCount.length,
    alerts,
    healthy: alerts.filter(a => a.severity === 'critical').length === 0,
  };
}

ZooKeeper connectivity and read-only mode detection

All ReplicatedMergeTree tables depend on ZooKeeper (or ClickHouse Keeper) for coordination. When the ClickHouse node loses its ZooKeeper session, every ReplicatedMergeTree table on that node enters read-only mode simultaneously. This is one of the most dangerous ClickHouse failure modes: the HTTP endpoint continues responding with 200 OK for SELECT queries, but all INSERT and DDL statements fail silently or with confusing errors. Applications that don't check INSERT results may continue thinking writes succeed while all data is being lost.

Check ZooKeeper connectivity by querying system.zookeeper — an error or timeout from this query indicates the ZooKeeper connection is down. Alternatively, check SELECT count() FROM system.replicas WHERE is_readonly = 1 — if any replicated tables are in read-only mode, the ZooKeeper session is likely expired. The system.zookeeper_connection table (ClickHouse 22.6+) shows the current ZooKeeper connection state directly.

async function checkZooKeeperHealth(ch) {
  const results = await Promise.allSettled([
    // Try to read from ZooKeeper namespace (will fail if ZK disconnected)
    ch.query(`SELECT count() AS zk_accessible FROM system.zookeeper WHERE path = '/'`),
    // Check for read-only replicas (symptom of ZK session loss)
    ch.query(`SELECT count() AS readonly_count FROM system.replicas WHERE is_readonly = 1`),
    // Check ZooKeeper connection status (ClickHouse 22.6+)
    ch.query(`SELECT * FROM system.zookeeper_connection`),
  ]);

  const zkAccessible = results[0];
  const readonlyCount = results[1];
  const zkConnection = results[2];

  const zkDown = zkAccessible.status === 'rejected';
  const readonlyReplicas = readonlyCount.status === 'fulfilled'
    ? parseInt(readonlyCount.value[0]?.readonly_count ?? '0', 10)
    : null;

  const connectionDetails = zkConnection.status === 'fulfilled' ? zkConnection.value : null;

  const alerts = [];

  if (zkDown) {
    alerts.push({
      severity: 'critical',
      type: 'zookeeper_unreachable',
      detail: `ZooKeeper query failed: ${zkAccessible.reason?.message} — all ReplicatedMergeTree tables may be read-only`,
    });
  }

  if (readonlyReplicas && readonlyReplicas > 0) {
    alerts.push({
      severity: 'critical',
      type: 'replicas_readonly',
      detail: `${readonlyReplicas} ReplicatedMergeTree table(s) in read-only mode — writes will fail`,
    });
  }

  if (connectionDetails) {
    const disconnected = connectionDetails.filter(c => c.is_connected === '0' || c.is_connected === 0);
    if (disconnected.length > 0) {
      alerts.push({
        severity: 'critical',
        type: 'zookeeper_connection_lost',
        detail: `${disconnected.length} ZooKeeper connection(s) not connected`,
      });
    }
  }

  return {
    zkAccessible: !zkDown,
    readonlyReplicas,
    connectionDetails,
    alerts,
    healthy: alerts.length === 0,
  };
}

AliveMCP integration for ClickHouse monitoring

Register four AliveMCP probes for a ClickHouse cluster. A liveness probe (60s interval, 5s timeout, GET /ping) catches complete node failures. A replica health probe (60s interval, 15s timeout, queries system.replicas) catches ZooKeeper session issues and replication lag. A merge backlog probe (5-minute interval, 20s timeout, queries system.merges and system.replication_queue) catches merge stalls before they cause insert throttling. A query performance probe (60s interval, 10s timeout, queries system.processes for long-running queries) catches runaway queries consuming resources.

async function clickHouseClusterHealthProbe(host, port, user, password) {
  const ch = createClickHouseClient(host, port, user, password);

  // Layer 1: HTTP liveness
  const alive = await ch.ping().catch(() => false);
  if (!alive) {
    return { healthy: false, layer: 'http_unavailable', host };
  }

  // Layer 2: Replica and ZooKeeper health
  const [replicaHealth, mergeHealth, zkHealth] = await Promise.allSettled([
    checkReplicaHealth(ch),
    checkMergeHealth(ch),
    checkZooKeeperHealth(ch),
  ]);

  const allAlerts = [
    ...(replicaHealth.status === 'fulfilled' ? replicaHealth.value.problems : [{ severity: 'warning', detail: 'system.replicas query failed' }]),
    ...(mergeHealth.status === 'fulfilled' ? mergeHealth.value.alerts : [{ severity: 'warning', detail: 'system.merges query failed' }]),
    ...(zkHealth.status === 'fulfilled' ? zkHealth.value.alerts : [{ severity: 'warning', detail: 'ZooKeeper check failed' }]),
  ];

  // Layer 3: Long-running queries (> 60 seconds)
  let longQueries = [];
  try {
    longQueries = await ch.query(`
      SELECT user, query, elapsed, memory_usage, read_rows
      FROM system.processes
      WHERE elapsed > 60
      ORDER BY elapsed DESC
      LIMIT 10
    `);
  } catch (_) {
    // Non-critical
  }

  return {
    healthy: allAlerts.filter(a => a.severity === 'critical').length === 0,
    host,
    replicaHealth: replicaHealth.status === 'fulfilled' ? replicaHealth.value : null,
    mergeHealth: mergeHealth.status === 'fulfilled' ? mergeHealth.value : null,
    zkHealth: zkHealth.status === 'fulfilled' ? zkHealth.value : null,
    longRunningQueries: longQueries.length,
    allAlerts,
  };
}

Related guides

• MCP tools for Trino — coordinator health, worker state, query execution stages, and memory pool management
• MCP tools for Apache Flink — streaming job health, checkpoint failures, and backpressure detection
• MCP tools for Apache Spark — SparkUI REST API, stage health, executor state, and shuffle spill
• MCP tools for Apache Kafka — consumer group lag, partition health, and broker availability
• MCP server health check patterns — composite endpoints and failure classification