ClickHouse census
Read where a ClickHouse cluster's CPU goes from its own system tables, over the last days (default 7), with no 10x install. The report gives W, the share of node CPU spent on INSERTs into the logs table and on its merges, with TTL merges reported apart; Q, the share spent on SELECTs, with the ten query shapes that cost the most CPU, each with its user and count; the peak hour over the mean and W in that hour; rows and bytes written per table, bytes stored and the compression ratio per table; and the tables in the database that no SELECT read in the window.
Insert and SELECT CPU come from system.query_log, merge CPU from system.part_log, and node CPU from system.metric_log, else system.asynchronous_metric_log, else the sum the logs attribute; the report says which. On ClickHouse Cloud the system logs are read across replicas with clusterAllReplicas. A system table the user cannot read is named with the GRANT it needs, and the rest of the census still runs. Every SQL statement sent is in the result.
The census reports CPU as shares of node CPU, which is what the system tables hold; the autoscaler's response and the bill sit outside them. log10x_estimate_savings takes the measured W as compute_write_share and scales its compute saving by it. The ClickHouse FAQ works the saving on compute from W.
Example
You
where does our ClickHouse CPU go
Log10x
W 41% and Q 33% of node CPU on default.otel_logs over 7 days; peak hour 2.3 times the mean, W 55% in it.
More to ask
- "census the
logs.eventstable over 14 days" - "which tables in
defaultdid no query read this week" - "the ten SELECT shapes that cost the most CPU"
- "read one node, without clusterAllReplicas"
Prerequisites
The MCP server's environment needs CLICKHOUSE_URL, with CLICKHOUSE_API_KEY (the default user's password) or with CLICKHOUSE_USER and CLICKHOUSE_PASSWORD. The census user needs SELECT on system.query_log, system.part_log, system.metric_log, system.asynchronous_metric_log, system.parts, system.tables and system.columns, SHOW TABLES on the database, and REMOTE ON *.* on a cluster; a missing grant is reported with its GRANT statement. The census sends only SELECT and SHOW TABLES statements.
Schema and samples
Input example
Representative call (synthetic, not captured from a live cluster).
Input schema
Agent-facing JSON Schema (the canonical shape the MCP server publishes via tools/list):
{
"type": "object",
"properties": {
"days": {
"type": "integer",
"minimum": 1,
"maximum": 30,
"default": 7,
"description": "Days back from now to read. Default 7. System-log retention bounds what the server still holds; the report states the hours it found."
},
"database": {
"type": "string",
"pattern": "^[A-Za-z0-9_]+$",
"default": "default",
"description": "Database of the logs table, and the database checked for tables no SELECT read. Default `default`."
},
"table": {
"type": "string",
"pattern": "^[A-Za-z0-9_]+$",
"default": "otel_logs",
"description": "The logs table whose write share is W. Default `otel_logs`, the ClickStack logs table."
},
"cluster": {
"type": "string",
"description": "Cluster name for clusterAllReplicas over the system logs. Default `default` on a ClickHouse Cloud host and none elsewhere; pass \"\" to read one node."
},
"view": {
"type": "string",
"enum": ["markdown", "summary"],
"default": "markdown",
"description": "`markdown` (default): the census page. `summary`: the same data without the page."
}
},
"additionalProperties": false,
"$schema": "http://json-schema.org/draft-07/schema#"
}
Source: src/tools/clickhouse-census.ts.
Output example
Representative envelope (synthetic, not captured from a live cluster). view: "markdown" adds the rendered census page as must_render_verbatim; summary returns the same data without it. Lists are trimmed to one entry each; the real call returns them in full.
Headline (the 1-line agent-facing answer):
W 41% and Q 33% of node CPU on default.otel_logs over 7 days; peak hour 2.3 times the mean, W 55% in it.
{
"schema_version": "1.0",
"schema_epoch": "2026-05-25",
"tool": "log10x_clickhouse_census",
"generated_at": "2026-11-08T10:12:04.118Z",
"view": "summary",
"status": "success",
"summary": {
"headline": "W 41% and Q 33% of node CPU on default.otel_logs over 7 days; peak hour 2.3 times the mean, W 55% in it."
},
"data": {
"status": "success",
"decisions": { "threshold_used": null, "threshold_basis": "default" },
"source_disclosure": {
"bytes_source": "siem_direct",
"rate_source": "none",
"siem_vendor": "clickhouse",
"source_label": "ClickHouse abc123.us-east-1.aws.clickhouse.cloud:8443, system tables"
},
"scope": { "window": "2026-11-01T10:00:00Z/2026-11-08T10:00:00Z", "window_basis": "auto_default" },
"payload": {
"target_table": "default.otel_logs",
"database": "default",
"cluster": "default",
"window": { "from": "2026-11-01T10:00:00Z", "to": "2026-11-08T10:00:00Z", "days": 7 },
"window_found": { "first_hour": "2026-11-01T10:00:00Z", "last_hour": "2026-11-08T09:00:00Z", "hours": 168 },
"denominator": "metric_log",
"merge_cpu_method": "profile_events",
"ttl_split": true,
"cpu_seconds": {
"node_total": 1814400,
"insert_table": 445000,
"merge_table": 280000,
"ttl_merge_table": 18904,
"insert_all": 490000,
"merge_all": 305000,
"ttl_merge_all": 20000,
"select": 598752,
"other_queries": 40000
},
"selects": 48210,
"w_share": 0.41,
"w_parts": { "insert": 0.245, "merge": 0.154, "ttl_merge": 0.01 },
"w_share_all_tables": 0.449,
"q_share": 0.33,
"residual_share": 0.199,
"peak": { "hour": "2026-11-05T14:00:00Z", "cores": 6.9, "mean_cores": 3, "peak_to_mean": 2.3, "w_at_peak": 0.55, "q_at_peak": 0.28 },
"hours": [
{ "hour": "2026-11-01T10:00:00Z", "node_cpu_seconds": 10800, "cores": 3, "write_cpu_seconds": 4428, "read_cpu_seconds": 3564 }
],
"top_shapes": [
{
"user": "hyperdx",
"shape": "SELECT ... FROM default.otel_logs WHERE ServiceName = ? AND Timestamp >= ? AND Timestamp < ? ORDER BY Timestamp DESC LIMIT ?",
"selects": 18211,
"cpu_seconds": 161000,
"share_of_node": 0.089,
"read_rows": 91000000000,
"read_bytes": 4400000000000
}
],
"tables_written": [
{ "table": "default.otel_logs", "new_parts": 60480, "rows_written": 3912000000, "bytes_written": 1300000000000, "rows_merged": 7824000000, "write_amplification": 2 }
],
"tables_stored": [
{ "table": "default.otel_logs", "rows": 3912000000, "bytes_on_disk": 98000000000, "compressed_bytes": 97000000000, "uncompressed_bytes": 1300000000000, "compression_ratio": 13.4 }
],
"tables_read": [
{ "table": "default.otel_logs", "selects": 47900, "last_read": "2026-11-08T09:58:11Z" }
],
"tables_listed": 9,
"never_read": [
{ "table": "default.otel_logs_legacy", "engine": "MergeTree", "rows": 610000000, "bytes_on_disk": 21000000000 }
],
"unreadable": [],
"missing_logs": [],
"notes": [],
"queries": [ "SELECT /* log10x_census */ ... FROM clusterAllReplicas(default, system.query_log) WHERE ..." ]
}
}
}
Output schema
The payload block inside the envelope, as the server types it.
interface CensusReport {
target_table: string;
database: string;
cluster: string | null;
window: { from: string; to: string; days: number };
window_found: { first_hour: string | null; last_hour: string | null; hours: number };
denominator: 'metric_log' | 'asynchronous_metric_log' | 'attributed';
merge_cpu_method: 'profile_events' | 'duration' | 'none';
ttl_split: boolean;
cpu_seconds: {
node_total: number;
insert_table: number;
merge_table: number;
ttl_merge_table: number;
insert_all: number;
merge_all: number;
ttl_merge_all: number;
select: number;
other_queries: number;
};
selects: number;
/** W on the target table: its inserts and merges (ordinary and TTL) over node CPU. */
w_share: number | null;
w_parts: { insert: number; merge: number; ttl_merge: number } | null;
/** The same over every table on the node. */
w_share_all_tables: number | null;
q_share: number | null;
/** Node CPU no log attributes (replication, moves, log flushes); printed, never folded into W or Q. */
residual_share: number | null;
peak: {
hour: string;
cores: number;
mean_cores: number;
peak_to_mean: number;
w_at_peak: number | null;
q_at_peak: number | null;
} | null;
hours: { hour: string; node_cpu_seconds: number; cores: number; write_cpu_seconds: number; read_cpu_seconds: number }[];
top_shapes: { user: string; shape: string; selects: number; cpu_seconds: number; share_of_node: number | null; read_rows: number; read_bytes: number }[];
tables_written: { table: string; new_parts: number; rows_written: number; bytes_written: number; rows_merged: number; write_amplification: number | null }[];
tables_stored: { table: string; rows: number; bytes_on_disk: number; compressed_bytes: number; uncompressed_bytes: number; compression_ratio: number | null }[];
tables_read: { table: string; selects: number; last_read: string }[];
/** Tables system.tables listed in the database; never_read is only meaningful above zero. */
tables_listed: number;
never_read: { table: string; engine: string; rows: number; bytes_on_disk: number }[];
unreadable: { table: string; reason: 'grant' | 'error'; message: string; grant?: string }[];
missing_logs: string[];
notes: string[];
queries: string[];
}
Envelope-level fields the agent should also read: summary.headline (the 1-line answer), status (success, partial when a system table was unreadable or a log table missing, no_signal when the window holds no CPU), scope.window and scope.candidates_failed (the tables the census could not read), and must_render_verbatim (the census page, with view: "markdown").