[SWIP-5][DISCUSSION] Support ClickHouse monitoring

[CzyerChen]

Motivation

ClickHouse is a high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP). It is available as both an open-source software and a cloud offering. Now I want to add ClickHouse monitoring via the OpenTelemetry Collector, which fetches metrics from it's own HTTP endpoint to expose metrics data for Prometheus(since ClickHouse v20.1.2.4). Clickhouse Exporter used only for old ClickHouse versions, modern versions have embedded prometheus endpoint.

Architecture Graph

There is no significant architecture-level change.

Proposed Changes

ClickHouse expose own metrics via HTTP endpoint to opentelemetry collector,using skyWalking openTelemetry receiver to receive these metrics.
Expose metrics from the system.metrics table / the system.events table / the system.asynchronous_metrics table.

ClickHouse Instance Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
CpuUsage	count	meter_clickhouse_instance_cpu_usage	CPU time spent seen by OS per second(according to ClickHouse.system.dashboard.CPU Usage (cores)).	ClickHouse
MemoryUsage	percentage	meter_clickhouse_instance_memory_usage	Total amount of memory (bytes) allocated by the server/ total amount of OS memory.	ClickHouse
MemoryAvailable	percentage	meter_clickhouse_instance_memory_available	Total amount of memory (bytes) available for program / total amount of OS memory.	ClickHouse
Uptime	sec	meter_clickhouse_instance_uptime	The server uptime in seconds. It includes the time spent for server initialization before accepting connections.	ClickHouse
Version	string	meter_clickhouse_instance_version	Version of the server in a single integer number in base-1000.	ClickHouse
FileOpen	count	meter_clickhouse_instance_file_open	Number of files opened.	ClickHouse

ClickHouse Network Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
TcpConnections	count	meter_clickhouse_instance_tcp_connections meter_clickhouse_tcp_connections	Number of connections to TCP server.	ClickHouse
MysqlConnections	count	meter_clickhouse_instance_mysql_connections meter_clickhouse_mysql_connections	Number of client connections using MySQL protocol.	ClickHouse
HttpConnections	count	meter_clickhouse_instance_http_connections meter_clickhouse_mysql_connections	Number of connections to HTTP server.	ClickHouse
InterserverConnections	count	meter_clickhouse_instance_interserver_connections meter_clickhouse_interserver_connections	Number of connections from other replicas to fetch parts.	ClickHouse
PostgresqlConnections	count	meter_clickhouse_instance_postgresql_connections meter_clickhouse_postgresql_connections	Number of client connections using PostgreSQL protocol.	ClickHouse
ReceiveBytes	bytes	meter_clickhouse_instance_network_receive_bytes meter_clickhouse_network_receive_bytes	Total number of bytes received from network.	ClickHouse
SendBytes	bytes	meter_clickhouse_instance_network_send_bytes meter_clickhouse_network_send_bytes	Total number of bytes send to network.	ClickHouse

ClickHouse Query Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
QueryCount	count	meter_clickhouse_instance_query meter_clickhouse_query	Number of executing queries.	ClickHouse
SelectQueryCount	count	meter_clickhouse_instance_query_select meter_clickhouse_query_select	Number of executing queries, but only for SELECT queries.	ClickHouse
InsertQueryCount	count	meter_clickhouse_instance_query_insert meter_clickhouse_query_insert	Number of executing queries, but only for INSERT queries.	ClickHouse
SelectQueryRate	count/sec	meter_clickhouse_instance_query_select_rate meter_clickhouse_query_select_rate	Number of SELECT queries per second.	ClickHouse
InsertQueryRate	count/sec	meter_clickhouse_instance_query_insert_rate meter_clickhouse_query_insert_rate	Number of INSERT queries per second.	ClickHouse
Querytime	microsec	meter_clickhouse_instance_querytime_microseconds meter_clickhouse_querytime_microseconds	Total time of all queries.	ClickHouse
SelectQuerytime	microsec	meter_clickhouse_instance_querytime_select_microseconds meter_clickhouse_querytime_select_microseconds	Total time of SELECT queries.	ClickHouse
InsertQuerytime	microsec	meter_clickhouse_instance_querytime_insert_microseconds meter_clickhouse_querytime_insert_microseconds	Total time of INSERT queries.	ClickHouse
OtherQuerytime	microsec	meter_clickhouse_instance_querytime_other_microseconds meter_clickhouse_querytime_other_microseconds	Total time of queries that are not SELECT or INSERT.	ClickHouse
QuerySlowCount	count	meter_clickhouse_instance_query_slow meter_clickhouse_query_slow	Number of reads from a file that were slow.	ClickHouse

ClickHouse Insertion Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
InsertQueryCount	count	meter_clickhouse_instance_query_insert meter_clickhouse_query_insert	Number of executing queries, but only for INSERT queries.	ClickHouse
InsertedRowCount	count	meter_clickhouse_instance_inserted_rows meter_clickhouse_inserted_rows	Number of rows INSERTed to all tables.	ClickHouse
InsertedBytes	bytes	meter_clickhouse_instance_inserted_bytes meter_clickhouse_inserted_bytes	Number of bytes INSERTed to all tables.	ClickHouse
DelayedInsertCount	count	meter_clickhouse_instance_delayed_insert meter_clickhouse_delayed_insert	Number of times the INSERT of a block to a MergeTree table was throttled due to high number of active data parts for partition.	ClickHouse

ClickHouse Replicas Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
ReplicatedChecks	count	meter_clickhouse_instance_replicated_checks meter_clickhouse_replicated_checks	Number of data parts checking for consistency.	ClickHouse
ReplicatedFetch	count	meter_clickhouse_instance_replicated_fetch meter_clickhouse_replicated_fetch	Number of data parts being fetched from replica.	ClickHouse
ReplicatedSend	count	meter_clickhouse_instance_replicated_send meter_clickhouse_replicated_send	Number of data parts being sent to replicas.	ClickHouse

ClickHouse MergeTree Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
BackgroundMergeCount	count	meter_clickhouse_instance_background_merge meter_clickhouse_background_merge	Number of executing background merges.	ClickHouse
MergeRows	count	meter_clickhouse_instance_merge_rows meter_clickhouse_merge_rows	Rows read for background merges. This is the number of rows before merge.	ClickHouse
MergeUncompressedBytes	bytes	meter_clickhouse_instance_merge_uncompressed_bytes meter_clickhouse_merge_uncompressed_bytes	Uncompressed bytes (for columns as they stored in memory) that was read for background merges. This is the number before merge.	ClickHouse
MoveCount	count	meter_clickhouse_instance_move meter_clickhouse_move	Number of currently executing moves.	ClickHouse
PartsActive	Count	meter_clickhouse_instance_parts_active meter_clickhouse_parts_active	Active data part, used by current and upcoming SELECTs.	ClickHouse
MutationsCount	count	meter_clickhouse_instance_mutations meter_clickhouse_mutations	Number of mutations (ALTER DELETE/UPDATE).	ClickHouse

ClickHouse Kafka Table Engine Supported Metrics

When table engine works
with Apache Kafka.

Kafka lets you:

• Publish or subscribe to data flows.
• Organize fault-tolerant storage.
• Process streams as they become available.

Monitoring Panel	Unit	Metric Name	Description	Data Source
KafkaMessagesRead	count	meter_clickhouse_instance_kafka_messages_read meter_clickhouse_kafka_messages_read	Number of Kafka messages already processed by ClickHouse.	ClickHouse
KafkaWrites	count	meter_clickhouse_instance_kafka_writes meter_clickhouse_kafka_writes	Number of writes (inserts) to Kafka tables.	ClickHouse
KafkaConsumers	count	meter_clickhouse_instance_kafka_consumers meter_clickhouse_kafka_consumers	Number of active Kafka consumers.	ClickHouse
KafkProducers	count	meter_clickhouse_instance_kafka_producers meter_clickhouse_kafka_producers	Number of active Kafka producer created.	ClickHouse

ClickHouse ZooKeeper Supported Metrics

ClickHouse uses ZooKeeper for storing metadata of replicas when using replicated tables. If replicated tables are not
used, this section of parameters can be omitted.

Monitoring Panel	Unit	Metric Name	Description	Data Source
ZookeeperSession	count	meter_clickhouse_instance_zookeeper_session meter_clickhouse_zookeeper_session	Number of sessions (connections) to ZooKeeper.	ClickHouse
ZookeeperWatch	count	meter_clickhouse_instance_zookeeper_watch meter_clickhouse_zookeeper_watch	Number of watches (event subscriptions) in ZooKeeper.	ClickHouse
ZookeeperBytesSent	bytes	meter_clickhouse_instance_zookeeper_bytes_sent meter_clickhouse_zookeeper_bytes_sent	Number of bytes send over network while communicating with ZooKeeper.	ClickHouse
ZookeeperBytesReceive	bytes	meter_clickhouse_instance_zookeeper_bytes_received meter_clickhouse_zookeeper_bytes_received	Number of bytes send over network while communicating with ZooKeeper.	ClickHouse

ClickHouse Keeper Supported Metrics

Monitoring Panel	Unit	Metric Name	Description	Data Source
KeeperAliveConnections	count	meter_clickhouse_instance_keeper_connections_alive meter_clickhouse_keeper_connections_alive	Number of alive connections for embedded ClickHouse Keeper.	ClickHouse
KeeperOutstandingRequets	count	meter_clickhouse_instance_keeper_outstanding_requests meter_clickhouse_keeper_outstanding_requests	Number of outstanding requests for embedded ClickHouse Keeper.	ClickHouse

Imported Dependencies libs and their licenses.

No new dependency.

Compatibility

no breaking changes.

General usage docs

[wu-sheng]

Do you consider the service hierarchy between this and virtual DB? I checked the plugin supported list, at least, clickhouse-JDBC has been supported in Java agent.

[wu-sheng]

Could you update the SWIP about how would you catalog these metrics into the standard service/instance/endpoint concept? The metrics you listed are from the original system, but you haven't indicated how these would look like through MAL and eventually visualized in SkyWalking concepts.

[wu-sheng]

@CzyerChen I think you missed this in this update. We should write these in the SWIP.

[CzyerChen]

Data flow

1. Configure ClickHouse to expose metrics for Prometheus Receiver.
2. OpenTelemetry Collector fetches metrics from Prometheus Receiver and pushes metrics to SkyWalking OAP Server via OpenTelemetry gRPC exporter.
3. The SkyWalking OAP Server parses the expression with MAL to filter/calculate/aggregate and store the results.

Set up

1. Set up built-in prometheus endpoint.

    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>

2. Set up OpenTelemetry Collector . For details on Prometheus Receiver in OpenTelemetry Collector

version: '3.8'
services: 
  otel-collector:
    image: otel/opentelemetry-collector:latest
    container_name: otel-collector
    network_mode: host
    command: [ "--config=/etc/otel-collector-config.yaml" ]
    volumes:
      - ./otel-collector-config.yaml:/etc/otel-collector-config.yaml
    expose:
      - 5567

3. Config SkyWalking OpenTelemetry receiver.

receivers:
  prometheus:
    config:
     scrape_configs:
       - job_name: 'clickhouse-monitoring'
         scrape_interval: 15s
         static_configs:
           - targets: ['ip1:9363','1ip2:9363','ip3:9363']
             labels:
               host_name: prometheus-clickhouse
processors:
  batch:
 
exporters:
  otlp:
    endpoint: ip:11800
    tls:
      insecure: true
service:
  pipelines:
    metrics:
      receivers:
      - prometheus
      processors:
      - batch
      exporters:
      - otlp

[wu-sheng]

how would you catalog these metrics into the standard service/instance/endpoint concept?

Could you add definition of these mapping in the SWIP?

[wu-sheng]

Any update here? I noticed this SWIP has no update for a week.

[CzyerChen]

I will update it today with some changes of meter and add some images.

[CzyerChen]

1. Add CPU/Memory Usage into Instance.
   CPU Usage according to the calculation of official ClickHouse system.dashboard
2. Simple view of the dashboard about the ClickHouse cluster with 1 ZooKeeper Node and 2 ClickHouse DB Node.

I have to check the metrics value again.

[CzyerChen]

I also will test service hierarchy concept later.

[CzyerChen]

I have encountered two problems during working on the dashboard that can not solved.

1. ClickHouse version string give in base-1000 format like version 11.22.33 is translated to 11022033, is it possible to translate 11022033 into 11.22.33 when collecting metrics?
2. When using table format in dashboard with two meter metrics and refreshing data with icon at top right, the table data sometimes will not be updated. Can it be optimized when data reloading?

so do the demo in mongo monitoring.

[wu-sheng]

ClickHouse version string give in base-1000 format like version 11.22.33 is translated to 11022033, is it possible to translate 11022033 into 11.22.33 when collecting metrics?

Where do you get that? Which type does OTLP provide for this?

When using table format in dashboard with two meter metrics and refreshing data with icon at top right, the table data sometimes will not be updated. Can it be optimized when data reloading?

Do you have a temp env to make @Fine0830 test and verify?

[wu-sheng]

@Fine0830 Could you check whether this widget has similar issues with apache/skywalking-booster-ui#373?

[CzyerChen]

Just from system.metrics in ClickHouse

iZ2ze72qvclbhzjoaz5c2jZ :) select * from system.metrics where metric ='VersionInteger' limit 1;

SELECT *
FROM system.metrics
WHERE metric = 'VersionInteger'
LIMIT 1

Query id: b0189d45-9054-4a01-9506-586d216661ed

┌─metric─────────┬────value─┬─description─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ VersionInteger │ 23012002 │ Version of the server in a single integer number in base-1000. For example, version 11.22.33 is translated to 11022033. │
└────────────────┴──────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.007 sec. 

If possible, we can show the version data.

# HELP ClickHouseMetrics_VersionInteger Version of the server in a single integer number in base-1000. For example, version 11.22.33 is translated to 11022033.
# TYPE ClickHouseMetrics_VersionInteger gauge
ClickHouseMetrics_VersionInteger 23012002

[wu-sheng]

Show 23012002 is fine. But it may be hard to show as 23.12.2. @wankai123 Any suggestion?

[wankai123]

I think show 23012002 and adding some tips is enough.

[wu-sheng]

PR continues on #11966. This is accepted.

[CzyerChen]

Final version could be found https://skywalking.apache.org/docs/main/next/en/swip/swip-5/