> ## Documentation Index
> Fetch the complete documentation index at: https://launchdarkly.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Database monitoring

**Developer**

**Federal docs**

> [!CAUTION]
>
> **This integration is not available in federal environments**
>
> To learn more, read [LaunchDarkly in federal environments](https://launchdarkly.com/docs/home/infrastructure/federal).

This topic explains how to use LaunchDarkly observability features to monitor a ClickHouse database, and optionally a PostgreSQL database. You run an OpenTelemetry (OTel) Collector that queries your database's system tables once a minute and sends the results to LaunchDarkly as observability metrics. You can then graph the metrics, alert on them, and use them when you investigate slow queries alongside your traces, logs, and errors.

Database monitoring does not require an agent inside your database. The Collector connects as a read-only user and runs a fixed set of queries against system tables. Your application data never leaves your network.

## Prerequisites

To use database monitoring, you need:

* A LaunchDarkly project key for your target environment.
* A ClickHouse server with the `system.query_log` and `system.part_log` tables enabled. Older ClickHouse versions lack some of the server metrics listed below. The Collector omits any metric your version does not report.
* (Optional) A PostgreSQL server, if you also want lock monitoring.
* A host that can run the [OpenTelemetry Collector Contrib](https://github.com/open-telemetry/opentelemetry-collector-contrib) container image and reach both your database and the LaunchDarkly OpenTelemetry endpoint.

To learn how to find and copy your LaunchDarkly project key, read [Project keys](https://launchdarkly.com/docs/home/account/project#project-keys).

## How database monitoring works

The OpenTelemetry Collector Contrib distribution includes the [SQL query receiver](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver). The receiver connects to a database, runs the SQL you give it on a schedule, and turns each result row into OpenTelemetry metric data points. LaunchDarkly provides a Collector configuration that uses this receiver to collect metrics from ClickHouse system tables.

Every 60 seconds, the Collector:

1. Reads `system.query_log` for the queries that finished in the previous minute, and groups them by the table they selected from or inserted into.
2. Reads `system.part_log` for the merges that completed in the previous minute, grouped by table.
3. Reads `system.parts` for the active part count and primary key memory of your largest tables.
4. Reads `system.metrics`, `system.asynchronous_metrics`, and `system.events` for server-wide memory, cache, and throughput counters.
5. Stamps every metric with your LaunchDarkly project key and sends it to the LaunchDarkly OpenTelemetry endpoint.

The query log window ends 15 seconds before the scrape runs. ClickHouse flushes `system.query_log` a few seconds after each query finishes. The delay keeps the Collector from missing queries that are still waiting to be flushed.

## Metrics collected

Database monitoring sends the following metrics to LaunchDarkly. All ClickHouse metrics are prefixed with `clickhouse.`. Table-level metrics carry a `database` attribute and one of `select_table`, `insert_table`, `merge_table`, or `part_table`.

### Select query metrics

These metrics describe the `SELECT` queries that finished in each 60-second window, grouped by the table they read. A query that reads several tables is counted on each table's series. The attribute is `select_table`, and the value includes the database prefix, for example `default.events`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.select.queries`</td>
      <td>count</td>
      <td>Selects that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_rows`, `clickhouse.select.selected_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes read to answer the queries.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_marks`, `clickhouse.select.selected_parts`</td>
      <td>count</td>
      <td>Index marks and data parts the queries had to scan. A high mark count relative to rows returned suggests the primary key is not filtering the query.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.primary_key_time`</td>
      <td>microseconds</td>
      <td>Time spent filtering marks with the primary key.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.loaded_marks_bytes`, `clickhouse.select.mark_cache_hits`, `clickhouse.select.mark_cache_misses`</td>
      <td>bytes, count</td>
      <td>Mark cache activity. A rising miss count means queries are loading index marks from disk.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.disk_read_time`, `clickhouse.select.read_compressed_bytes`</td>
      <td>microseconds, bytes</td>
      <td>Local disk reads.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.s3_read_time`, `clickhouse.select.s3_read_bytes`, `clickhouse.select.s3_read_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage reads, for tables stored on S3-compatible disks.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.filesystem_cache_bytes`, `clickhouse.select.filesystem_cache_miss_bytes`</td>
      <td>bytes</td>
      <td>Bytes served from the filesystem cache and bytes that missed it.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.real_time`, `clickhouse.select.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all selects in the window.</td>
    </tr>
  </tbody>
</table>

### Insert query metrics

These metrics describe the `INSERT` queries that finished in each 60-second window. The attribute is `insert_table`, which is the `INSERT INTO` target without a database prefix.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.insert.queries`</td>
      <td>count</td>
      <td>Inserts that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.inserted_rows`, `clickhouse.insert.inserted_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes written.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.disk_write_time`</td>
      <td>microseconds</td>
      <td>Local disk write time.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.s3_write_time`, `clickhouse.insert.s3_write_bytes`, `clickhouse.insert.s3_write_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage writes.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.delayed_inserts`, `clickhouse.insert.rejected_inserts`</td>
      <td>count</td>
      <td>Inserts that ClickHouse throttled or rejected because a table had too many parts. Any non-zero value means merges are falling behind.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.real_time`, `clickhouse.insert.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all inserts in the window.</td>
    </tr>
  </tbody>
</table>

### Merge and part metrics

These metrics describe background merges and the parts that make up each table. The attributes are `merge_table` and `part_table`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.merges.completed`</td>
      <td>count</td>
      <td>Merges that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.duration`</td>
      <td>milliseconds</td>
      <td>Total merge time in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.rows`, `clickhouse.merges.rows_read`</td>
      <td>count</td>
      <td>Rows written by and read into merges.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.peak_memory_bytes`</td>
      <td>bytes</td>
      <td>Largest peak memory of any merge in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.active`</td>
      <td>count</td>
      <td>Active parts per table, for the 25 tables with the most primary key memory.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.primary_key_bytes_allocated`</td>
      <td>bytes</td>
      <td>Memory allocated to each table's primary key index.</td>
    </tr>
  </tbody>
</table>

### Server metrics

These metrics describe the whole server and have no table attribute. The metric name ends with the ClickHouse metric name. You can look up each one in the [ClickHouse system tables documentation](https://clickhouse.com/docs/operations/system-tables).

<table>
  <thead>
    <tr>
      <th>Metric prefix</th>
      <th>Source</th>
      <th>Metrics</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.server.metrics.`</td>
      <td>`system.metrics`</td>
      <td>`MemoryTracking`, `MergesMutationsMemoryTracking`, `MarkCacheBytes`, `UncompressedCacheBytes`, `QueryCacheBytes`, `QueryConditionCacheBytes`, `FilesystemCacheSize`, `PageCacheBytes`, `PartsActive`, `Query`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.async_metrics.`</td>
      <td>`system.asynchronous_metrics`</td>
      <td>`MemoryResident`, `OSMemoryAvailable`, `jemalloc.allocated`, `jemalloc.resident`, `TotalPrimaryKeyBytesInMemory`, `TotalPrimaryKeyBytesInMemoryAllocated`, `QueriesMemoryUsage`, `FilesystemCacheBytes`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.events.`</td>
      <td>`system.events`</td>
      <td>`SelectedRows`, `SelectedBytes`, `MarkCacheHits`, `MarkCacheMisses`, `DiskReadElapsedMicroseconds`. These are cumulative counters since the server started.</td>
    </tr>
  </tbody>
</table>

### PostgreSQL lock metrics

If you enable the optional PostgreSQL receiver, the Collector also sends `postgresql.locks`, a gauge of lock requests in `pg_locks` that are still waiting to be granted. Its attributes are `lock_mode`, `lock_type`, `schema`, `db`, `table`, `granted`, and `fastpath`. When nothing is waiting, the Collector sends a single point with the value 0. This lets you tell a quiet database from a Collector that has stopped reporting.

## Create a read-only database user

The Collector needs a database user that can read system tables and nothing else. We recommend a dedicated user so you can audit and revoke its access independently.

### ClickHouse

To create a monitoring user, run the following statements as an administrator:

```sql title="ClickHouse SQL" lines wrap theme={null}
CREATE USER otel_monitoring IDENTIFIED BY 'replace-with-a-strong-password';

GRANT SELECT ON system.query_log TO otel_monitoring;
GRANT SELECT ON system.part_log TO otel_monitoring;
GRANT SELECT ON system.parts TO otel_monitoring;
GRANT SELECT ON system.metrics TO otel_monitoring;
GRANT SELECT ON system.asynchronous_metrics TO otel_monitoring;
GRANT SELECT ON system.events TO otel_monitoring;
```

Grant access to each system table separately rather than running `GRANT SELECT ON system.*`. On ClickHouse Cloud, the wildcard grant fails because the default administrator does not have grant rights on every system table.

The Collector also sets the `readonly` setting to `1` on its own session, so it cannot modify data even if the user is later given broader grants.

### PostgreSQL

If you also want lock monitoring, create a PostgreSQL user and add it to the built-in `pg_monitor` role:

```sql title="PostgreSQL SQL" lines wrap theme={null}
CREATE ROLE otel_monitoring LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT pg_monitor TO otel_monitoring;
GRANT CONNECT ON DATABASE your_database TO otel_monitoring;
```

## Configure the Collector

Create a file named `database-monitoring.yaml` with the following configuration. It defines one SQL query receiver for ClickHouse, a resource processor that stamps your project key on every metric, and an exporter that sends the metrics to LaunchDarkly:

```yaml title="database-monitoring.yaml" expandable lines wrap theme={null}
receivers:
  sqlquery/clickhouse:
    driver: clickhouse
    host: ${env:CLICKHOUSE_HOST}
    port: ${env:CLICKHOUSE_PORT:-9440}
    database: system
    username: ${env:CLICKHOUSE_USERNAME:-otel_monitoring}
    password: ${env:CLICKHOUSE_PASSWORD}
    additional_params:
      dial_timeout: 5s
      read_timeout: 30s
      max_execution_time: 30
      readonly: "1"
      secure: ${env:CLICKHOUSE_SECURE:-true}
      # Lets the query log filter exclude the Collector's own queries.
      log_comment: launchdarkly-database-monitoring
    collection_interval: 60s
    timeout: 30s
    max_open_conn: 2
    queries:
      # Selects that finished in the previous minute, by table.
      - sql: >
          SELECT
              arrayJoin(tables) AS select_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['SelectedRows'])) AS selected_rows,
              toFloat64(sum(ProfileEvents['SelectedBytes'])) AS selected_bytes,
              toFloat64(sum(ProfileEvents['SelectedMarks'])) AS selected_marks,
              toFloat64(sum(ProfileEvents['SelectedParts'])) AS selected_parts,
              toFloat64(sum(ProfileEvents['FilteringMarksWithPrimaryKeyMicroseconds'])) AS primary_key_us,
              toFloat64(sum(ProfileEvents['LoadedMarksMemoryBytes'])) AS loaded_marks_bytes,
              toFloat64(sum(ProfileEvents['DiskReadElapsedMicroseconds'])) AS disk_read_us,
              toFloat64(sum(ProfileEvents['ReadCompressedBytes'])) AS read_compressed_bytes,
              toFloat64(sum(ProfileEvents['MarkCacheHits'])) AS mark_cache_hits,
              toFloat64(sum(ProfileEvents['MarkCacheMisses'])) AS mark_cache_misses,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Microseconds'])) AS s3_read_us,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Bytes'])) AS s3_read_bytes,
              toFloat64(sum(ProfileEvents['S3ReadRequestsCount'])) AS s3_read_requests,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromCacheBytes'])) AS filesystem_cache_bytes,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromSourceBytes'])) AS filesystem_cache_miss_bytes,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind = 'Select'
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY select_table
        metrics:
          - {metric_name: clickhouse.select.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_rows, value_column: selected_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_bytes, value_column: selected_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_marks, value_column: selected_marks, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_parts, value_column: selected_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.primary_key_time, value_column: primary_key_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.loaded_marks_bytes, value_column: loaded_marks_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.disk_read_time, value_column: disk_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.read_compressed_bytes, value_column: read_compressed_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_hits, value_column: mark_cache_hits, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_misses, value_column: mark_cache_misses, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_time, value_column: s3_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_bytes, value_column: s3_read_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_requests, value_column: s3_read_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_bytes, value_column: filesystem_cache_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_miss_bytes, value_column: filesystem_cache_miss_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Inserts that finished in the previous minute, by target table.
      - sql: >
          SELECT
              extract(query, '(?i)INSERT\\s+INTO\\s+(?:TABLE\\s+)?(?:`?\\w+`?\\.)?`?(\\w+)') AS insert_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['InsertedRows'])) AS inserted_rows,
              toFloat64(sum(ProfileEvents['InsertedBytes'])) AS inserted_bytes,
              toFloat64(sum(ProfileEvents['DiskWriteElapsedMicroseconds'])) AS disk_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Microseconds'])) AS s3_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Bytes'])) AS s3_write_bytes,
              toFloat64(sum(ProfileEvents['S3WriteRequestsCount'])) AS s3_write_requests,
              toFloat64(sum(ProfileEvents['DelayedInserts'])) AS delayed_inserts,
              toFloat64(sum(ProfileEvents['RejectedInserts'])) AS rejected_inserts,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind IN ('Insert', 'AsyncInsert')
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY insert_table
        metrics:
          - {metric_name: clickhouse.insert.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_rows, value_column: inserted_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_bytes, value_column: inserted_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.disk_write_time, value_column: disk_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_time, value_column: s3_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_bytes, value_column: s3_write_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_requests, value_column: s3_write_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.delayed_inserts, value_column: delayed_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.rejected_inserts, value_column: rejected_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Merges that completed in the previous minute, by table.
      - sql: >
          SELECT
              table AS merge_table,
              toFloat64(count()) AS completed,
              toFloat64(sum(duration_ms)) AS duration_ms,
              toFloat64(sum(rows)) AS rows,
              toFloat64(sum(read_rows)) AS rows_read,
              toFloat64(max(peak_memory_usage)) AS peak_memory_bytes
          FROM system.part_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND event_type = 'MergeParts'
            AND error = 0
          GROUP BY merge_table
        metrics:
          - {metric_name: clickhouse.merges.completed, value_column: completed, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.duration, value_column: duration_ms, value_type: double, data_type: gauge, unit: ms, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows, value_column: rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows_read, value_column: rows_read, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.peak_memory_bytes, value_column: peak_memory_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Active parts and primary key memory for the 25 largest tables.
      - sql: >
          SELECT
              table AS part_table,
              toFloat64(sum(primary_key_bytes_in_memory_allocated)) AS primary_key_bytes_allocated,
              toFloat64(count()) AS active_parts
          FROM system.parts
          WHERE active
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
          GROUP BY table
          ORDER BY primary_key_bytes_allocated DESC
          LIMIT 25
        metrics:
          - {metric_name: clickhouse.parts.primary_key_bytes_allocated, value_column: primary_key_bytes_allocated, value_type: double, data_type: gauge, unit: By, attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.parts.active, value_column: active_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Server-wide memory, cache, and concurrency gauges.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.metrics
          WHERE name IN (
              'MemoryTracking', 'MergesMutationsMemoryTracking',
              'MarkCacheBytes', 'UncompressedCacheBytes', 'QueryCacheBytes', 'QueryConditionCacheBytes',
              'FilesystemCacheSize', 'PageCacheBytes',
              'PartsActive', 'Query'
          )
        metrics:
          - {metric_name: clickhouse.server.metrics.MemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MergesMutationsMemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MergesMutationsMemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MarkCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MarkCacheBytes}}
          - {metric_name: clickhouse.server.metrics.UncompressedCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: UncompressedCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryConditionCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryConditionCacheBytes}}
          - {metric_name: clickhouse.server.metrics.FilesystemCacheSize, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheSize}}
          - {metric_name: clickhouse.server.metrics.PageCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: PageCacheBytes}}
          - {metric_name: clickhouse.server.metrics.PartsActive, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: PartsActive}}
          - {metric_name: clickhouse.server.metrics.Query, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: Query}}

      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.asynchronous_metrics
          WHERE name IN (
              'MemoryResident', 'OSMemoryAvailable',
              'jemalloc.allocated', 'jemalloc.resident',
              'TotalPrimaryKeyBytesInMemory', 'TotalPrimaryKeyBytesInMemoryAllocated',
              'QueriesMemoryUsage', 'FilesystemCacheBytes'
          )
        metrics:
          - {metric_name: clickhouse.server.async_metrics.MemoryResident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryResident}}
          - {metric_name: clickhouse.server.async_metrics.OSMemoryAvailable, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: OSMemoryAvailable}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.allocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.allocated}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.resident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.resident}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemory, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemory}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemoryAllocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemoryAllocated}}
          - {metric_name: clickhouse.server.async_metrics.QueriesMemoryUsage, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueriesMemoryUsage}}
          - {metric_name: clickhouse.server.async_metrics.FilesystemCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheBytes}}

      # Cumulative counters since the server started.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.events
          WHERE name IN (
              'SelectedRows', 'SelectedBytes',
              'MarkCacheHits', 'MarkCacheMisses',
              'DiskReadElapsedMicroseconds'
          )
        metrics:
          - {metric_name: clickhouse.server.events.SelectedRows, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: SelectedRows}}
          - {metric_name: clickhouse.server.events.SelectedBytes, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: By, row_condition: {column: name, value: SelectedBytes}}
          - {metric_name: clickhouse.server.events.MarkCacheHits, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheHits}}
          - {metric_name: clickhouse.server.events.MarkCacheMisses, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheMisses}}
          - {metric_name: clickhouse.server.events.DiskReadElapsedMicroseconds, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: us, row_condition: {column: name, value: DiskReadElapsedMicroseconds}}

processors:
  # Route every metric to your LaunchDarkly project.
  resource:
    attributes:
      - key: launchdarkly.project_id
        value: ${env:LAUNCHDARKLY_PROJECT_KEY}
        action: upsert
      - key: deployment.environment.name
        value: ${env:ENVIRONMENT:-production}
        action: upsert
      - key: service.name
        value: ${env:SERVICE_NAME:-clickhouse}
        action: upsert

exporters:
  otlphttp/launchdarkly:
    endpoint: https://otel.observability.app.launchdarkly.com
    compression: gzip
    timeout: 30s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s

service:
  pipelines:
    metrics:
      receivers: [sqlquery/clickhouse]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

The `database` attribute and the `current_database` filter scope the table-level queries to one ClickHouse database. If your workload spans several databases, run one Collector per database, or remove the `current_database` and `database` filters to see every database at once.

### Configuration options

The configuration reads its connection details from environment variables. You can keep the same file in every environment. The following table describes each variable:

<table>
  <thead>
    <tr>
      <th>Variable</th>
      <th>Description</th>
      <th>Required</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`LAUNCHDARKLY_PROJECT_KEY`</td>
      <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_HOST`</td>
      <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read [Monitor separate read and write clusters](#monitor-separate-read-and-write-clusters).</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_PORT`</td>
      <td>Native protocol port. Defaults to `9440`, the TLS port. Use `9000` with `CLICKHOUSE_SECURE=false` for a server without TLS.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_SECURE`</td>
      <td>Whether to connect with TLS. Defaults to `true`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_USERNAME`, `CLICKHOUSE_PASSWORD`</td>
      <td>Credentials for the monitoring user you created. The username defaults to `otel_monitoring`.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_DATABASE`</td>
      <td>The ClickHouse database whose tables you want to monitor. Defaults to `default`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`ENVIRONMENT`</td>
      <td>Value for the `deployment.environment.name` attribute. Use it to filter metrics by environment. Defaults to `production`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`SERVICE_NAME`</td>
      <td>Value for the `service.name` attribute. Defaults to `clickhouse`. Use the same name on your application's database spans to correlate them with these metrics.</td>
      <td>No</td>
    </tr>
  </tbody>
</table>

### Add PostgreSQL lock monitoring

To also monitor waiting lock requests in a PostgreSQL database, add a second receiver and pipeline to the same file:

```yaml title="database-monitoring.yaml (PostgreSQL additions)" expandable lines wrap theme={null}
receivers:
  sqlquery/postgres-locks:
    driver: postgres
    host: ${env:POSTGRES_HOST}
    port: ${env:POSTGRES_PORT:-5432}
    database: ${env:POSTGRES_DATABASE:-postgres}
    username: ${env:POSTGRES_USERNAME:-otel_monitoring}
    password: ${env:POSTGRES_PASSWORD}
    additional_params:
      sslmode: ${env:POSTGRES_SSLMODE:-require}
      connect_timeout: 5
    collection_interval: 60s
    timeout: 10s
    max_open_conn: 1
    queries:
      - sql: >
          WITH blocked AS (
              SELECT
                  l.mode::text AS lock_mode,
                  l.locktype::text AS lock_type,
                  COALESCE(pn.nspname, '')::text AS schema_name,
                  COALESCE(pd.datname, '')::text AS db_name,
                  COALESCE(pc.relname, '')::text AS rel_name,
                  l.granted::text AS granted,
                  l.fastpath::text AS fastpath
              FROM pg_locks l
              LEFT JOIN pg_database pd ON (l.database = pd.oid)
              LEFT JOIN pg_class pc ON (l.relation = pc.oid)
              LEFT JOIN pg_namespace pn ON (pn.oid = pc.relnamespace)
              WHERE (pc.oid IS NULL OR (pc.relname NOT LIKE 'pg^_%' ESCAPE '^'))
                AND l.mode IS NOT NULL
                AND l.granted = false
                AND (l.database IS NULL OR pd.datname = current_database())
          )
          SELECT
              lock_mode,
              lock_type,
              schema_name AS schema,
              db_name AS db,
              rel_name AS table,
              granted,
              fastpath,
              count(*)::float8 AS locks
          FROM blocked
          GROUP BY lock_mode, lock_type, schema_name, db_name, rel_name, granted, fastpath
          UNION ALL
          SELECT '', '', '', current_database()::text, '', 'false', 'false', 0::float8
          WHERE NOT EXISTS (SELECT 1 FROM blocked)
        metrics:
          - metric_name: postgresql.locks
            value_column: locks
            value_type: double
            data_type: gauge
            unit: "1"
            description: Ungranted pg_locks rows
            attribute_columns: [lock_mode, lock_type, schema, db, table, granted, fastpath]

service:
  pipelines:
    metrics/postgres:
      receivers: [sqlquery/postgres-locks]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

Set `POSTGRES_HOST`, `POSTGRES_DATABASE`, `POSTGRES_USERNAME`, and `POSTGRES_PASSWORD` when you run the Collector. `POSTGRES_SSLMODE` defaults to `require`. Set it to `disable` only for a server without TLS.

### Monitor separate read and write clusters

Many ClickHouse deployments send queries to a read replica and send inserts to a writer. In that case, the query log on each host only records the queries that host ran. To monitor both, duplicate the `sqlquery/clickhouse` receiver as `sqlquery/clickhouse-read` and `sqlquery/clickhouse-write`, point each at the appropriate host, and list both receivers in the `metrics` pipeline. Keep the select, parts, and server memory queries on the read receiver, and the insert and merge queries on the write receiver. That way you do not count the same work twice.

## Run the Collector

Use the OpenTelemetry Collector Contrib image, which includes the SQL query receiver. We recommend pinning the image to the version you tested with rather than using `latest`.

### Docker

To run the Collector with Docker:

```bash title="Shell" lines wrap theme={null}
docker run -d \
  --name database-monitoring \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_USERNAME=otel_monitoring \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -e CLICKHOUSE_DATABASE=default \
  -e ENVIRONMENT=production \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0
```

Replace `YOUR_PROJECT_KEY` with your LaunchDarkly project key and `YOUR_PASSWORD` with the monitoring user's password.

### Docker Compose

To run the Collector alongside your database with Docker Compose:

```yaml title="compose.yaml" lines wrap theme={null}
services:
  database-monitoring:
    image: otel/opentelemetry-collector-contrib:0.159.0
    restart: unless-stopped
    environment:
      LAUNCHDARKLY_PROJECT_KEY: YOUR_PROJECT_KEY
      CLICKHOUSE_HOST: clickhouse
      CLICKHOUSE_PORT: "9000"
      CLICKHOUSE_SECURE: "false"
      CLICKHOUSE_USERNAME: otel_monitoring
      CLICKHOUSE_PASSWORD: YOUR_PASSWORD
      CLICKHOUSE_DATABASE: default
      ENVIRONMENT: staging
    volumes:
      - ./database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro
```

### Kubernetes

Run the Collector as a single-replica Deployment so each scrape runs once. Store the configuration in a ConfigMap and the database password in a Secret. The [OpenTelemetry Operator for Kubernetes](https://opentelemetry.io/docs/kubernetes/operator/) can manage the Deployment for you. Set `mode: deployment` and `replicas: 1` on the `OpenTelemetryCollector` resource.

### Validate the configuration

Before you deploy, check that the Collector accepts the configuration and that every environment variable resolves:

```bash title="Shell" lines wrap theme={null}
docker run --rm \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0 validate --config /etc/otelcol-contrib/config.yaml
```

## Correlate database metrics with traces

Database monitoring tells you what each table is doing. Your application's traces tell you which requests are waiting on it. To connect the two, instrument database calls in your application with the OpenTelemetry [database semantic conventions](https://opentelemetry.io/docs/specs/semconv/database/database-spans/), and use the same `service.name` on the Collector and in your application's database client spans.

The most useful span attributes are:

* `db.system.name`: Set to `clickhouse` or `postgresql`.
* `db.namespace`: The database name, matching the `database` attribute on your metrics.
* `db.collection.name`: The table name, matching `select_table` or `insert_table` on your metrics.
* `db.query.summary`: A low-cardinality summary of the statement, such as `SELECT events`.

Most OpenTelemetry database instrumentations set these attributes for you. With them in place, you can open a slow span in [Traces](https://launchdarkly.com/docs/home/observability/traces.md), read the table it touched, and compare it against that table's select metrics for the same minute.

## View database metrics in LaunchDarkly

After you start the Collector, metrics begin arriving in LaunchDarkly within two minutes.

To review your database metrics:

1. Open the **Telemetry** section and navigate to the **Observability metrics** list.
2. Search for `clickhouse.` to list the database metrics.
3. Click a metric name to open its details, then group by `select_table`, `insert_table`, or `merge_table` to compare tables.

To learn more about graphing metrics, read [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](https://launchdarkly.com/docs/home/observability/custom-dashboards.md).

### Alert on database metrics

You can create alerts on any database metric. Useful starting points include:

* `clickhouse.insert.rejected_inserts` or `clickhouse.insert.delayed_inserts` above 0, which means a table has too many parts and merges are falling behind.
* `clickhouse.parts.active` rising steadily for one table, which usually precedes rejected inserts.
* `clickhouse.server.metrics.MemoryTracking` approaching your server's memory limit.
* `clickhouse.select.mark_cache_misses` rising for a table while `clickhouse.select.selected_marks` stays flat, which means the mark cache is too small for your working set.
* `postgresql.locks` above 0 for more than a few minutes, which means a transaction is blocking others.

To learn how to create alerts, read [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md).

**Developer**

### Investigate with Vega

Vega, the LaunchDarkly AI-powered agent, can read your database metrics during an investigation. When an alert fires on a slow endpoint or an error spike, Vega can check whether the affected service's database tables saw a merge backlog, a mark cache miss spike, rejected inserts, or lock contention in the same window, and include that in its summary. If your traces carry the database semantic convention attributes described above, Vega can also name the table and query shape behind a slow span.

To learn more, read [Vega for auto-remediation](https://launchdarkly.com/docs/home/observability/vega.md).

## Troubleshooting

If database metrics are not appearing in LaunchDarkly:

1. **Check the Collector logs**: Run `docker logs database-monitoring` or the equivalent for your platform. A connection error names the host and port the receiver tried to reach. An authentication error means the monitoring user's credentials or grants are wrong.

2. **Check the project key**: Verify that `LAUNCHDARKLY_PROJECT_KEY` matches the project key in your LaunchDarkly project settings, not an environment key or SDK key.

3. **Verify that ClickHouse writes the query log**: Run `SELECT count() FROM system.query_log WHERE event_time > now() - INTERVAL 5 MINUTE` as the monitoring user. If the table does not exist or is empty while queries are running, enable `query_log` and `part_log` in your ClickHouse server configuration.

4. **Verify network connectivity**: Confirm the Collector host can reach `otel.observability.app.launchdarkly.com` on port `443`:

```bash title="Shell" lines wrap theme={null}
curl -sS -o /dev/null -w "%{http_code}\n" https://otel.observability.app.launchdarkly.com/v1/metrics
```

5. **Check for empty windows**: Table-level metrics only have data points for tables that ran queries in the previous minute. A table that received no selects or inserts has no points for that minute, which is expected. Server metrics are reported every minute regardless.

6. **Check TLS settings**: If the Collector reports a handshake error, confirm that `CLICKHOUSE_SECURE` and `CLICKHOUSE_PORT` match your server. The TLS native port is usually `9440` and the plain port is `9000`.

## Related content

* [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics)
* [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md)
* [Traces](https://launchdarkly.com/docs/home/observability/traces.md)
* [Self-hosting an OpenTelemetry Collector](https://launchdarkly.com/docs/sdk/features/observability-otel-collector.md)
* [SQL query receiver documentation](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver)
* [ClickHouse system tables](https://clickhouse.com/docs/operations/system-tables)

**Federal docs**

**Federal docs**

> [!CAUTION]
>
> **This integration is not available in federal environments**
>
> To learn more, read [LaunchDarkly in federal environments](https://launchdarkly.com/docs/home/infrastructure/federal).

This topic explains how to use LaunchDarkly observability features to monitor a ClickHouse database, and optionally a PostgreSQL database. You run an OpenTelemetry (OTel) Collector that queries your database's system tables once a minute and sends the results to LaunchDarkly as observability metrics. You can then graph the metrics, alert on them, and use them when you investigate slow queries alongside your traces, logs, and errors.

Database monitoring does not require an agent inside your database. The Collector connects as a read-only user and runs a fixed set of queries against system tables. Your application data never leaves your network.

## Prerequisites

To use database monitoring, you need:

* A LaunchDarkly project key for your target environment.
* A ClickHouse server with the `system.query_log` and `system.part_log` tables enabled. Older ClickHouse versions lack some of the server metrics listed below. The Collector omits any metric your version does not report.
* (Optional) A PostgreSQL server, if you also want lock monitoring.
* A host that can run the [OpenTelemetry Collector Contrib](https://github.com/open-telemetry/opentelemetry-collector-contrib) container image and reach both your database and the LaunchDarkly OpenTelemetry endpoint.

To learn how to find and copy your LaunchDarkly project key, read [Project keys](https://launchdarkly.com/docs/home/account/project#project-keys).

## How database monitoring works

The OpenTelemetry Collector Contrib distribution includes the [SQL query receiver](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver). The receiver connects to a database, runs the SQL you give it on a schedule, and turns each result row into OpenTelemetry metric data points. LaunchDarkly provides a Collector configuration that uses this receiver to collect metrics from ClickHouse system tables.

Every 60 seconds, the Collector:

1. Reads `system.query_log` for the queries that finished in the previous minute, and groups them by the table they selected from or inserted into.
2. Reads `system.part_log` for the merges that completed in the previous minute, grouped by table.
3. Reads `system.parts` for the active part count and primary key memory of your largest tables.
4. Reads `system.metrics`, `system.asynchronous_metrics`, and `system.events` for server-wide memory, cache, and throughput counters.
5. Stamps every metric with your LaunchDarkly project key and sends it to the LaunchDarkly OpenTelemetry endpoint.

The query log window ends 15 seconds before the scrape runs. ClickHouse flushes `system.query_log` a few seconds after each query finishes. The delay keeps the Collector from missing queries that are still waiting to be flushed.

## Metrics collected

Database monitoring sends the following metrics to LaunchDarkly. All ClickHouse metrics are prefixed with `clickhouse.`. Table-level metrics carry a `database` attribute and one of `select_table`, `insert_table`, `merge_table`, or `part_table`.

### Select query metrics

These metrics describe the `SELECT` queries that finished in each 60-second window, grouped by the table they read. A query that reads several tables is counted on each table's series. The attribute is `select_table`, and the value includes the database prefix, for example `default.events`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.select.queries`</td>
      <td>count</td>
      <td>Selects that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_rows`, `clickhouse.select.selected_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes read to answer the queries.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_marks`, `clickhouse.select.selected_parts`</td>
      <td>count</td>
      <td>Index marks and data parts the queries had to scan. A high mark count relative to rows returned suggests the primary key is not filtering the query.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.primary_key_time`</td>
      <td>microseconds</td>
      <td>Time spent filtering marks with the primary key.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.loaded_marks_bytes`, `clickhouse.select.mark_cache_hits`, `clickhouse.select.mark_cache_misses`</td>
      <td>bytes, count</td>
      <td>Mark cache activity. A rising miss count means queries are loading index marks from disk.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.disk_read_time`, `clickhouse.select.read_compressed_bytes`</td>
      <td>microseconds, bytes</td>
      <td>Local disk reads.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.s3_read_time`, `clickhouse.select.s3_read_bytes`, `clickhouse.select.s3_read_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage reads, for tables stored on S3-compatible disks.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.filesystem_cache_bytes`, `clickhouse.select.filesystem_cache_miss_bytes`</td>
      <td>bytes</td>
      <td>Bytes served from the filesystem cache and bytes that missed it.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.real_time`, `clickhouse.select.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all selects in the window.</td>
    </tr>
  </tbody>
</table>

### Insert query metrics

These metrics describe the `INSERT` queries that finished in each 60-second window. The attribute is `insert_table`, which is the `INSERT INTO` target without a database prefix.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.insert.queries`</td>
      <td>count</td>
      <td>Inserts that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.inserted_rows`, `clickhouse.insert.inserted_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes written.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.disk_write_time`</td>
      <td>microseconds</td>
      <td>Local disk write time.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.s3_write_time`, `clickhouse.insert.s3_write_bytes`, `clickhouse.insert.s3_write_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage writes.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.delayed_inserts`, `clickhouse.insert.rejected_inserts`</td>
      <td>count</td>
      <td>Inserts that ClickHouse throttled or rejected because a table had too many parts. Any non-zero value means merges are falling behind.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.real_time`, `clickhouse.insert.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all inserts in the window.</td>
    </tr>
  </tbody>
</table>

### Merge and part metrics

These metrics describe background merges and the parts that make up each table. The attributes are `merge_table` and `part_table`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.merges.completed`</td>
      <td>count</td>
      <td>Merges that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.duration`</td>
      <td>milliseconds</td>
      <td>Total merge time in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.rows`, `clickhouse.merges.rows_read`</td>
      <td>count</td>
      <td>Rows written by and read into merges.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.peak_memory_bytes`</td>
      <td>bytes</td>
      <td>Largest peak memory of any merge in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.active`</td>
      <td>count</td>
      <td>Active parts per table, for the 25 tables with the most primary key memory.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.primary_key_bytes_allocated`</td>
      <td>bytes</td>
      <td>Memory allocated to each table's primary key index.</td>
    </tr>
  </tbody>
</table>

### Server metrics

These metrics describe the whole server and have no table attribute. The metric name ends with the ClickHouse metric name. You can look up each one in the [ClickHouse system tables documentation](https://clickhouse.com/docs/operations/system-tables).

<table>
  <thead>
    <tr>
      <th>Metric prefix</th>
      <th>Source</th>
      <th>Metrics</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.server.metrics.`</td>
      <td>`system.metrics`</td>
      <td>`MemoryTracking`, `MergesMutationsMemoryTracking`, `MarkCacheBytes`, `UncompressedCacheBytes`, `QueryCacheBytes`, `QueryConditionCacheBytes`, `FilesystemCacheSize`, `PageCacheBytes`, `PartsActive`, `Query`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.async_metrics.`</td>
      <td>`system.asynchronous_metrics`</td>
      <td>`MemoryResident`, `OSMemoryAvailable`, `jemalloc.allocated`, `jemalloc.resident`, `TotalPrimaryKeyBytesInMemory`, `TotalPrimaryKeyBytesInMemoryAllocated`, `QueriesMemoryUsage`, `FilesystemCacheBytes`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.events.`</td>
      <td>`system.events`</td>
      <td>`SelectedRows`, `SelectedBytes`, `MarkCacheHits`, `MarkCacheMisses`, `DiskReadElapsedMicroseconds`. These are cumulative counters since the server started.</td>
    </tr>
  </tbody>
</table>

### PostgreSQL lock metrics

If you enable the optional PostgreSQL receiver, the Collector also sends `postgresql.locks`, a gauge of lock requests in `pg_locks` that are still waiting to be granted. Its attributes are `lock_mode`, `lock_type`, `schema`, `db`, `table`, `granted`, and `fastpath`. When nothing is waiting, the Collector sends a single point with the value 0. This lets you tell a quiet database from a Collector that has stopped reporting.

## Create a read-only database user

The Collector needs a database user that can read system tables and nothing else. We recommend a dedicated user so you can audit and revoke its access independently.

### ClickHouse

To create a monitoring user, run the following statements as an administrator:

```sql title="ClickHouse SQL" lines wrap theme={null}
CREATE USER otel_monitoring IDENTIFIED BY 'replace-with-a-strong-password';

GRANT SELECT ON system.query_log TO otel_monitoring;
GRANT SELECT ON system.part_log TO otel_monitoring;
GRANT SELECT ON system.parts TO otel_monitoring;
GRANT SELECT ON system.metrics TO otel_monitoring;
GRANT SELECT ON system.asynchronous_metrics TO otel_monitoring;
GRANT SELECT ON system.events TO otel_monitoring;
```

Grant access to each system table separately rather than running `GRANT SELECT ON system.*`. On ClickHouse Cloud, the wildcard grant fails because the default administrator does not have grant rights on every system table.

The Collector also sets the `readonly` setting to `1` on its own session, so it cannot modify data even if the user is later given broader grants.

### PostgreSQL

If you also want lock monitoring, create a PostgreSQL user and add it to the built-in `pg_monitor` role:

```sql title="PostgreSQL SQL" lines wrap theme={null}
CREATE ROLE otel_monitoring LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT pg_monitor TO otel_monitoring;
GRANT CONNECT ON DATABASE your_database TO otel_monitoring;
```

## Configure the Collector

Create a file named `database-monitoring.yaml` with the following configuration. It defines one SQL query receiver for ClickHouse, a resource processor that stamps your project key on every metric, and an exporter that sends the metrics to LaunchDarkly:

```yaml title="database-monitoring.yaml" expandable lines wrap theme={null}
receivers:
  sqlquery/clickhouse:
    driver: clickhouse
    host: ${env:CLICKHOUSE_HOST}
    port: ${env:CLICKHOUSE_PORT:-9440}
    database: system
    username: ${env:CLICKHOUSE_USERNAME:-otel_monitoring}
    password: ${env:CLICKHOUSE_PASSWORD}
    additional_params:
      dial_timeout: 5s
      read_timeout: 30s
      max_execution_time: 30
      readonly: "1"
      secure: ${env:CLICKHOUSE_SECURE:-true}
      # Lets the query log filter exclude the Collector's own queries.
      log_comment: launchdarkly-database-monitoring
    collection_interval: 60s
    timeout: 30s
    max_open_conn: 2
    queries:
      # Selects that finished in the previous minute, by table.
      - sql: >
          SELECT
              arrayJoin(tables) AS select_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['SelectedRows'])) AS selected_rows,
              toFloat64(sum(ProfileEvents['SelectedBytes'])) AS selected_bytes,
              toFloat64(sum(ProfileEvents['SelectedMarks'])) AS selected_marks,
              toFloat64(sum(ProfileEvents['SelectedParts'])) AS selected_parts,
              toFloat64(sum(ProfileEvents['FilteringMarksWithPrimaryKeyMicroseconds'])) AS primary_key_us,
              toFloat64(sum(ProfileEvents['LoadedMarksMemoryBytes'])) AS loaded_marks_bytes,
              toFloat64(sum(ProfileEvents['DiskReadElapsedMicroseconds'])) AS disk_read_us,
              toFloat64(sum(ProfileEvents['ReadCompressedBytes'])) AS read_compressed_bytes,
              toFloat64(sum(ProfileEvents['MarkCacheHits'])) AS mark_cache_hits,
              toFloat64(sum(ProfileEvents['MarkCacheMisses'])) AS mark_cache_misses,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Microseconds'])) AS s3_read_us,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Bytes'])) AS s3_read_bytes,
              toFloat64(sum(ProfileEvents['S3ReadRequestsCount'])) AS s3_read_requests,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromCacheBytes'])) AS filesystem_cache_bytes,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromSourceBytes'])) AS filesystem_cache_miss_bytes,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind = 'Select'
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY select_table
        metrics:
          - {metric_name: clickhouse.select.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_rows, value_column: selected_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_bytes, value_column: selected_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_marks, value_column: selected_marks, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_parts, value_column: selected_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.primary_key_time, value_column: primary_key_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.loaded_marks_bytes, value_column: loaded_marks_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.disk_read_time, value_column: disk_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.read_compressed_bytes, value_column: read_compressed_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_hits, value_column: mark_cache_hits, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_misses, value_column: mark_cache_misses, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_time, value_column: s3_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_bytes, value_column: s3_read_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_requests, value_column: s3_read_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_bytes, value_column: filesystem_cache_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_miss_bytes, value_column: filesystem_cache_miss_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Inserts that finished in the previous minute, by target table.
      - sql: >
          SELECT
              extract(query, '(?i)INSERT\\s+INTO\\s+(?:TABLE\\s+)?(?:`?\\w+`?\\.)?`?(\\w+)') AS insert_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['InsertedRows'])) AS inserted_rows,
              toFloat64(sum(ProfileEvents['InsertedBytes'])) AS inserted_bytes,
              toFloat64(sum(ProfileEvents['DiskWriteElapsedMicroseconds'])) AS disk_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Microseconds'])) AS s3_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Bytes'])) AS s3_write_bytes,
              toFloat64(sum(ProfileEvents['S3WriteRequestsCount'])) AS s3_write_requests,
              toFloat64(sum(ProfileEvents['DelayedInserts'])) AS delayed_inserts,
              toFloat64(sum(ProfileEvents['RejectedInserts'])) AS rejected_inserts,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind IN ('Insert', 'AsyncInsert')
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY insert_table
        metrics:
          - {metric_name: clickhouse.insert.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_rows, value_column: inserted_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_bytes, value_column: inserted_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.disk_write_time, value_column: disk_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_time, value_column: s3_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_bytes, value_column: s3_write_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_requests, value_column: s3_write_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.delayed_inserts, value_column: delayed_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.rejected_inserts, value_column: rejected_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Merges that completed in the previous minute, by table.
      - sql: >
          SELECT
              table AS merge_table,
              toFloat64(count()) AS completed,
              toFloat64(sum(duration_ms)) AS duration_ms,
              toFloat64(sum(rows)) AS rows,
              toFloat64(sum(read_rows)) AS rows_read,
              toFloat64(max(peak_memory_usage)) AS peak_memory_bytes
          FROM system.part_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND event_type = 'MergeParts'
            AND error = 0
          GROUP BY merge_table
        metrics:
          - {metric_name: clickhouse.merges.completed, value_column: completed, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.duration, value_column: duration_ms, value_type: double, data_type: gauge, unit: ms, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows, value_column: rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows_read, value_column: rows_read, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.peak_memory_bytes, value_column: peak_memory_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Active parts and primary key memory for the 25 largest tables.
      - sql: >
          SELECT
              table AS part_table,
              toFloat64(sum(primary_key_bytes_in_memory_allocated)) AS primary_key_bytes_allocated,
              toFloat64(count()) AS active_parts
          FROM system.parts
          WHERE active
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
          GROUP BY table
          ORDER BY primary_key_bytes_allocated DESC
          LIMIT 25
        metrics:
          - {metric_name: clickhouse.parts.primary_key_bytes_allocated, value_column: primary_key_bytes_allocated, value_type: double, data_type: gauge, unit: By, attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.parts.active, value_column: active_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Server-wide memory, cache, and concurrency gauges.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.metrics
          WHERE name IN (
              'MemoryTracking', 'MergesMutationsMemoryTracking',
              'MarkCacheBytes', 'UncompressedCacheBytes', 'QueryCacheBytes', 'QueryConditionCacheBytes',
              'FilesystemCacheSize', 'PageCacheBytes',
              'PartsActive', 'Query'
          )
        metrics:
          - {metric_name: clickhouse.server.metrics.MemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MergesMutationsMemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MergesMutationsMemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MarkCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MarkCacheBytes}}
          - {metric_name: clickhouse.server.metrics.UncompressedCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: UncompressedCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryConditionCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryConditionCacheBytes}}
          - {metric_name: clickhouse.server.metrics.FilesystemCacheSize, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheSize}}
          - {metric_name: clickhouse.server.metrics.PageCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: PageCacheBytes}}
          - {metric_name: clickhouse.server.metrics.PartsActive, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: PartsActive}}
          - {metric_name: clickhouse.server.metrics.Query, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: Query}}

      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.asynchronous_metrics
          WHERE name IN (
              'MemoryResident', 'OSMemoryAvailable',
              'jemalloc.allocated', 'jemalloc.resident',
              'TotalPrimaryKeyBytesInMemory', 'TotalPrimaryKeyBytesInMemoryAllocated',
              'QueriesMemoryUsage', 'FilesystemCacheBytes'
          )
        metrics:
          - {metric_name: clickhouse.server.async_metrics.MemoryResident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryResident}}
          - {metric_name: clickhouse.server.async_metrics.OSMemoryAvailable, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: OSMemoryAvailable}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.allocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.allocated}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.resident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.resident}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemory, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemory}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemoryAllocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemoryAllocated}}
          - {metric_name: clickhouse.server.async_metrics.QueriesMemoryUsage, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueriesMemoryUsage}}
          - {metric_name: clickhouse.server.async_metrics.FilesystemCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheBytes}}

      # Cumulative counters since the server started.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.events
          WHERE name IN (
              'SelectedRows', 'SelectedBytes',
              'MarkCacheHits', 'MarkCacheMisses',
              'DiskReadElapsedMicroseconds'
          )
        metrics:
          - {metric_name: clickhouse.server.events.SelectedRows, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: SelectedRows}}
          - {metric_name: clickhouse.server.events.SelectedBytes, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: By, row_condition: {column: name, value: SelectedBytes}}
          - {metric_name: clickhouse.server.events.MarkCacheHits, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheHits}}
          - {metric_name: clickhouse.server.events.MarkCacheMisses, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheMisses}}
          - {metric_name: clickhouse.server.events.DiskReadElapsedMicroseconds, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: us, row_condition: {column: name, value: DiskReadElapsedMicroseconds}}

processors:
  # Route every metric to your LaunchDarkly project.
  resource:
    attributes:
      - key: launchdarkly.project_id
        value: ${env:LAUNCHDARKLY_PROJECT_KEY}
        action: upsert
      - key: deployment.environment.name
        value: ${env:ENVIRONMENT:-production}
        action: upsert
      - key: service.name
        value: ${env:SERVICE_NAME:-clickhouse}
        action: upsert

exporters:
  otlphttp/launchdarkly:
    endpoint: https://otel.observability.app.launchdarkly.com
    compression: gzip
    timeout: 30s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s

service:
  pipelines:
    metrics:
      receivers: [sqlquery/clickhouse]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

The `database` attribute and the `current_database` filter scope the table-level queries to one ClickHouse database. If your workload spans several databases, run one Collector per database, or remove the `current_database` and `database` filters to see every database at once.

### Configuration options

The configuration reads its connection details from environment variables. You can keep the same file in every environment. The following table describes each variable:

<table>
  <thead>
    <tr>
      <th>Variable</th>
      <th>Description</th>
      <th>Required</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`LAUNCHDARKLY_PROJECT_KEY`</td>
      <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_HOST`</td>
      <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read [Monitor separate read and write clusters](#monitor-separate-read-and-write-clusters).</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_PORT`</td>
      <td>Native protocol port. Defaults to `9440`, the TLS port. Use `9000` with `CLICKHOUSE_SECURE=false` for a server without TLS.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_SECURE`</td>
      <td>Whether to connect with TLS. Defaults to `true`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_USERNAME`, `CLICKHOUSE_PASSWORD`</td>
      <td>Credentials for the monitoring user you created. The username defaults to `otel_monitoring`.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_DATABASE`</td>
      <td>The ClickHouse database whose tables you want to monitor. Defaults to `default`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`ENVIRONMENT`</td>
      <td>Value for the `deployment.environment.name` attribute. Use it to filter metrics by environment. Defaults to `production`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`SERVICE_NAME`</td>
      <td>Value for the `service.name` attribute. Defaults to `clickhouse`. Use the same name on your application's database spans to correlate them with these metrics.</td>
      <td>No</td>
    </tr>
  </tbody>
</table>

### Add PostgreSQL lock monitoring

To also monitor waiting lock requests in a PostgreSQL database, add a second receiver and pipeline to the same file:

```yaml title="database-monitoring.yaml (PostgreSQL additions)" expandable lines wrap theme={null}
receivers:
  sqlquery/postgres-locks:
    driver: postgres
    host: ${env:POSTGRES_HOST}
    port: ${env:POSTGRES_PORT:-5432}
    database: ${env:POSTGRES_DATABASE:-postgres}
    username: ${env:POSTGRES_USERNAME:-otel_monitoring}
    password: ${env:POSTGRES_PASSWORD}
    additional_params:
      sslmode: ${env:POSTGRES_SSLMODE:-require}
      connect_timeout: 5
    collection_interval: 60s
    timeout: 10s
    max_open_conn: 1
    queries:
      - sql: >
          WITH blocked AS (
              SELECT
                  l.mode::text AS lock_mode,
                  l.locktype::text AS lock_type,
                  COALESCE(pn.nspname, '')::text AS schema_name,
                  COALESCE(pd.datname, '')::text AS db_name,
                  COALESCE(pc.relname, '')::text AS rel_name,
                  l.granted::text AS granted,
                  l.fastpath::text AS fastpath
              FROM pg_locks l
              LEFT JOIN pg_database pd ON (l.database = pd.oid)
              LEFT JOIN pg_class pc ON (l.relation = pc.oid)
              LEFT JOIN pg_namespace pn ON (pn.oid = pc.relnamespace)
              WHERE (pc.oid IS NULL OR (pc.relname NOT LIKE 'pg^_%' ESCAPE '^'))
                AND l.mode IS NOT NULL
                AND l.granted = false
                AND (l.database IS NULL OR pd.datname = current_database())
          )
          SELECT
              lock_mode,
              lock_type,
              schema_name AS schema,
              db_name AS db,
              rel_name AS table,
              granted,
              fastpath,
              count(*)::float8 AS locks
          FROM blocked
          GROUP BY lock_mode, lock_type, schema_name, db_name, rel_name, granted, fastpath
          UNION ALL
          SELECT '', '', '', current_database()::text, '', 'false', 'false', 0::float8
          WHERE NOT EXISTS (SELECT 1 FROM blocked)
        metrics:
          - metric_name: postgresql.locks
            value_column: locks
            value_type: double
            data_type: gauge
            unit: "1"
            description: Ungranted pg_locks rows
            attribute_columns: [lock_mode, lock_type, schema, db, table, granted, fastpath]

service:
  pipelines:
    metrics/postgres:
      receivers: [sqlquery/postgres-locks]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

Set `POSTGRES_HOST`, `POSTGRES_DATABASE`, `POSTGRES_USERNAME`, and `POSTGRES_PASSWORD` when you run the Collector. `POSTGRES_SSLMODE` defaults to `require`. Set it to `disable` only for a server without TLS.

### Monitor separate read and write clusters

Many ClickHouse deployments send queries to a read replica and send inserts to a writer. In that case, the query log on each host only records the queries that host ran. To monitor both, duplicate the `sqlquery/clickhouse` receiver as `sqlquery/clickhouse-read` and `sqlquery/clickhouse-write`, point each at the appropriate host, and list both receivers in the `metrics` pipeline. Keep the select, parts, and server memory queries on the read receiver, and the insert and merge queries on the write receiver. That way you do not count the same work twice.

## Run the Collector

Use the OpenTelemetry Collector Contrib image, which includes the SQL query receiver. We recommend pinning the image to the version you tested with rather than using `latest`.

### Docker

To run the Collector with Docker:

```bash title="Shell" lines wrap theme={null}
docker run -d \
  --name database-monitoring \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_USERNAME=otel_monitoring \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -e CLICKHOUSE_DATABASE=default \
  -e ENVIRONMENT=production \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0
```

Replace `YOUR_PROJECT_KEY` with your LaunchDarkly project key and `YOUR_PASSWORD` with the monitoring user's password.

### Docker Compose

To run the Collector alongside your database with Docker Compose:

```yaml title="compose.yaml" lines wrap theme={null}
services:
  database-monitoring:
    image: otel/opentelemetry-collector-contrib:0.159.0
    restart: unless-stopped
    environment:
      LAUNCHDARKLY_PROJECT_KEY: YOUR_PROJECT_KEY
      CLICKHOUSE_HOST: clickhouse
      CLICKHOUSE_PORT: "9000"
      CLICKHOUSE_SECURE: "false"
      CLICKHOUSE_USERNAME: otel_monitoring
      CLICKHOUSE_PASSWORD: YOUR_PASSWORD
      CLICKHOUSE_DATABASE: default
      ENVIRONMENT: staging
    volumes:
      - ./database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro
```

### Kubernetes

Run the Collector as a single-replica Deployment so each scrape runs once. Store the configuration in a ConfigMap and the database password in a Secret. The [OpenTelemetry Operator for Kubernetes](https://opentelemetry.io/docs/kubernetes/operator/) can manage the Deployment for you. Set `mode: deployment` and `replicas: 1` on the `OpenTelemetryCollector` resource.

### Validate the configuration

Before you deploy, check that the Collector accepts the configuration and that every environment variable resolves:

```bash title="Shell" lines wrap theme={null}
docker run --rm \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0 validate --config /etc/otelcol-contrib/config.yaml
```

## Correlate database metrics with traces

Database monitoring tells you what each table is doing. Your application's traces tell you which requests are waiting on it. To connect the two, instrument database calls in your application with the OpenTelemetry [database semantic conventions](https://opentelemetry.io/docs/specs/semconv/database/database-spans/), and use the same `service.name` on the Collector and in your application's database client spans.

The most useful span attributes are:

* `db.system.name`: Set to `clickhouse` or `postgresql`.
* `db.namespace`: The database name, matching the `database` attribute on your metrics.
* `db.collection.name`: The table name, matching `select_table` or `insert_table` on your metrics.
* `db.query.summary`: A low-cardinality summary of the statement, such as `SELECT events`.

Most OpenTelemetry database instrumentations set these attributes for you. With them in place, you can open a slow span in [Traces](https://launchdarkly.com/docs/home/observability/traces.md), read the table it touched, and compare it against that table's select metrics for the same minute.

## View database metrics in LaunchDarkly

After you start the Collector, metrics begin arriving in LaunchDarkly within two minutes.

To review your database metrics:

1. Open the **Telemetry** section and navigate to the **Observability metrics** list.
2. Search for `clickhouse.` to list the database metrics.
3. Click a metric name to open its details, then group by `select_table`, `insert_table`, or `merge_table` to compare tables.

To learn more about graphing metrics, read [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](https://launchdarkly.com/docs/home/observability/custom-dashboards.md).

### Alert on database metrics

You can create alerts on any database metric. Useful starting points include:

* `clickhouse.insert.rejected_inserts` or `clickhouse.insert.delayed_inserts` above 0, which means a table has too many parts and merges are falling behind.
* `clickhouse.parts.active` rising steadily for one table, which usually precedes rejected inserts.
* `clickhouse.server.metrics.MemoryTracking` approaching your server's memory limit.
* `clickhouse.select.mark_cache_misses` rising for a table while `clickhouse.select.selected_marks` stays flat, which means the mark cache is too small for your working set.
* `postgresql.locks` above 0 for more than a few minutes, which means a transaction is blocking others.

To learn how to create alerts, read [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md).

**Developer**

### Investigate with Vega

Vega, the LaunchDarkly AI-powered agent, can read your database metrics during an investigation. When an alert fires on a slow endpoint or an error spike, Vega can check whether the affected service's database tables saw a merge backlog, a mark cache miss spike, rejected inserts, or lock contention in the same window, and include that in its summary. If your traces carry the database semantic convention attributes described above, Vega can also name the table and query shape behind a slow span.

To learn more, read [Vega for auto-remediation](https://launchdarkly.com/docs/home/observability/vega.md).

## Troubleshooting

If database metrics are not appearing in LaunchDarkly:

1. **Check the Collector logs**: Run `docker logs database-monitoring` or the equivalent for your platform. A connection error names the host and port the receiver tried to reach. An authentication error means the monitoring user's credentials or grants are wrong.

2. **Check the project key**: Verify that `LAUNCHDARKLY_PROJECT_KEY` matches the project key in your LaunchDarkly project settings, not an environment key or SDK key.

3. **Verify that ClickHouse writes the query log**: Run `SELECT count() FROM system.query_log WHERE event_time > now() - INTERVAL 5 MINUTE` as the monitoring user. If the table does not exist or is empty while queries are running, enable `query_log` and `part_log` in your ClickHouse server configuration.

4. **Verify network connectivity**: Confirm the Collector host can reach `otel.observability.app.launchdarkly.com` on port `443`:

```bash title="Shell" lines wrap theme={null}
curl -sS -o /dev/null -w "%{http_code}\n" https://otel.observability.app.launchdarkly.com/v1/metrics
```

5. **Check for empty windows**: Table-level metrics only have data points for tables that ran queries in the previous minute. A table that received no selects or inserts has no points for that minute, which is expected. Server metrics are reported every minute regardless.

6. **Check TLS settings**: If the Collector reports a handshake error, confirm that `CLICKHOUSE_SECURE` and `CLICKHOUSE_PORT` match your server. The TLS native port is usually `9440` and the plain port is `9000`.

## Related content

* [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics)
* [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md)
* [Traces](https://launchdarkly.com/docs/home/observability/traces.md)
* [Self-hosting an OpenTelemetry Collector](https://launchdarkly.com/docs/sdk/features/observability-otel-collector.md)
* [SQL query receiver documentation](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver)
* [ClickHouse system tables](https://clickhouse.com/docs/operations/system-tables)

**EU docs**

**Federal docs**

> [!CAUTION]
>
> **This integration is not available in federal environments**
>
> To learn more, read [LaunchDarkly in federal environments](https://launchdarkly.com/docs/home/infrastructure/federal).

This topic explains how to use LaunchDarkly observability features to monitor a ClickHouse database, and optionally a PostgreSQL database. You run an OpenTelemetry (OTel) Collector that queries your database's system tables once a minute and sends the results to LaunchDarkly as observability metrics. You can then graph the metrics, alert on them, and use them when you investigate slow queries alongside your traces, logs, and errors.

Database monitoring does not require an agent inside your database. The Collector connects as a read-only user and runs a fixed set of queries against system tables. Your application data never leaves your network.

## Prerequisites

To use database monitoring, you need:

* A LaunchDarkly project key for your target environment.
* A ClickHouse server with the `system.query_log` and `system.part_log` tables enabled. Older ClickHouse versions lack some of the server metrics listed below. The Collector omits any metric your version does not report.
* (Optional) A PostgreSQL server, if you also want lock monitoring.
* A host that can run the [OpenTelemetry Collector Contrib](https://github.com/open-telemetry/opentelemetry-collector-contrib) container image and reach both your database and the LaunchDarkly OpenTelemetry endpoint.

To learn how to find and copy your LaunchDarkly project key, read [Project keys](https://launchdarkly.com/docs/home/account/project#project-keys).

## How database monitoring works

The OpenTelemetry Collector Contrib distribution includes the [SQL query receiver](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver). The receiver connects to a database, runs the SQL you give it on a schedule, and turns each result row into OpenTelemetry metric data points. LaunchDarkly provides a Collector configuration that uses this receiver to collect metrics from ClickHouse system tables.

Every 60 seconds, the Collector:

1. Reads `system.query_log` for the queries that finished in the previous minute, and groups them by the table they selected from or inserted into.
2. Reads `system.part_log` for the merges that completed in the previous minute, grouped by table.
3. Reads `system.parts` for the active part count and primary key memory of your largest tables.
4. Reads `system.metrics`, `system.asynchronous_metrics`, and `system.events` for server-wide memory, cache, and throughput counters.
5. Stamps every metric with your LaunchDarkly project key and sends it to the LaunchDarkly OpenTelemetry endpoint.

The query log window ends 15 seconds before the scrape runs. ClickHouse flushes `system.query_log` a few seconds after each query finishes. The delay keeps the Collector from missing queries that are still waiting to be flushed.

## Metrics collected

Database monitoring sends the following metrics to LaunchDarkly. All ClickHouse metrics are prefixed with `clickhouse.`. Table-level metrics carry a `database` attribute and one of `select_table`, `insert_table`, `merge_table`, or `part_table`.

### Select query metrics

These metrics describe the `SELECT` queries that finished in each 60-second window, grouped by the table they read. A query that reads several tables is counted on each table's series. The attribute is `select_table`, and the value includes the database prefix, for example `default.events`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.select.queries`</td>
      <td>count</td>
      <td>Selects that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_rows`, `clickhouse.select.selected_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes read to answer the queries.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.selected_marks`, `clickhouse.select.selected_parts`</td>
      <td>count</td>
      <td>Index marks and data parts the queries had to scan. A high mark count relative to rows returned suggests the primary key is not filtering the query.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.primary_key_time`</td>
      <td>microseconds</td>
      <td>Time spent filtering marks with the primary key.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.loaded_marks_bytes`, `clickhouse.select.mark_cache_hits`, `clickhouse.select.mark_cache_misses`</td>
      <td>bytes, count</td>
      <td>Mark cache activity. A rising miss count means queries are loading index marks from disk.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.disk_read_time`, `clickhouse.select.read_compressed_bytes`</td>
      <td>microseconds, bytes</td>
      <td>Local disk reads.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.s3_read_time`, `clickhouse.select.s3_read_bytes`, `clickhouse.select.s3_read_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage reads, for tables stored on S3-compatible disks.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.filesystem_cache_bytes`, `clickhouse.select.filesystem_cache_miss_bytes`</td>
      <td>bytes</td>
      <td>Bytes served from the filesystem cache and bytes that missed it.</td>
    </tr>

    <tr>
      <td>`clickhouse.select.real_time`, `clickhouse.select.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all selects in the window.</td>
    </tr>
  </tbody>
</table>

### Insert query metrics

These metrics describe the `INSERT` queries that finished in each 60-second window. The attribute is `insert_table`, which is the `INSERT INTO` target without a database prefix.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.insert.queries`</td>
      <td>count</td>
      <td>Inserts that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.inserted_rows`, `clickhouse.insert.inserted_bytes`</td>
      <td>count, bytes</td>
      <td>Rows and bytes written.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.disk_write_time`</td>
      <td>microseconds</td>
      <td>Local disk write time.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.s3_write_time`, `clickhouse.insert.s3_write_bytes`, `clickhouse.insert.s3_write_requests`</td>
      <td>microseconds, bytes, count</td>
      <td>Object storage writes.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.delayed_inserts`, `clickhouse.insert.rejected_inserts`</td>
      <td>count</td>
      <td>Inserts that ClickHouse throttled or rejected because a table had too many parts. Any non-zero value means merges are falling behind.</td>
    </tr>

    <tr>
      <td>`clickhouse.insert.real_time`, `clickhouse.insert.cpu_time`</td>
      <td>microseconds</td>
      <td>Wall clock and CPU time across all inserts in the window.</td>
    </tr>
  </tbody>
</table>

### Merge and part metrics

These metrics describe background merges and the parts that make up each table. The attributes are `merge_table` and `part_table`.

<table>
  <thead>
    <tr>
      <th>Metric</th>
      <th>Unit</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.merges.completed`</td>
      <td>count</td>
      <td>Merges that finished in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.duration`</td>
      <td>milliseconds</td>
      <td>Total merge time in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.rows`, `clickhouse.merges.rows_read`</td>
      <td>count</td>
      <td>Rows written by and read into merges.</td>
    </tr>

    <tr>
      <td>`clickhouse.merges.peak_memory_bytes`</td>
      <td>bytes</td>
      <td>Largest peak memory of any merge in the window.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.active`</td>
      <td>count</td>
      <td>Active parts per table, for the 25 tables with the most primary key memory.</td>
    </tr>

    <tr>
      <td>`clickhouse.parts.primary_key_bytes_allocated`</td>
      <td>bytes</td>
      <td>Memory allocated to each table's primary key index.</td>
    </tr>
  </tbody>
</table>

### Server metrics

These metrics describe the whole server and have no table attribute. The metric name ends with the ClickHouse metric name. You can look up each one in the [ClickHouse system tables documentation](https://clickhouse.com/docs/operations/system-tables).

<table>
  <thead>
    <tr>
      <th>Metric prefix</th>
      <th>Source</th>
      <th>Metrics</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`clickhouse.server.metrics.`</td>
      <td>`system.metrics`</td>
      <td>`MemoryTracking`, `MergesMutationsMemoryTracking`, `MarkCacheBytes`, `UncompressedCacheBytes`, `QueryCacheBytes`, `QueryConditionCacheBytes`, `FilesystemCacheSize`, `PageCacheBytes`, `PartsActive`, `Query`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.async_metrics.`</td>
      <td>`system.asynchronous_metrics`</td>
      <td>`MemoryResident`, `OSMemoryAvailable`, `jemalloc.allocated`, `jemalloc.resident`, `TotalPrimaryKeyBytesInMemory`, `TotalPrimaryKeyBytesInMemoryAllocated`, `QueriesMemoryUsage`, `FilesystemCacheBytes`</td>
    </tr>

    <tr>
      <td>`clickhouse.server.events.`</td>
      <td>`system.events`</td>
      <td>`SelectedRows`, `SelectedBytes`, `MarkCacheHits`, `MarkCacheMisses`, `DiskReadElapsedMicroseconds`. These are cumulative counters since the server started.</td>
    </tr>
  </tbody>
</table>

### PostgreSQL lock metrics

If you enable the optional PostgreSQL receiver, the Collector also sends `postgresql.locks`, a gauge of lock requests in `pg_locks` that are still waiting to be granted. Its attributes are `lock_mode`, `lock_type`, `schema`, `db`, `table`, `granted`, and `fastpath`. When nothing is waiting, the Collector sends a single point with the value 0. This lets you tell a quiet database from a Collector that has stopped reporting.

## Create a read-only database user

The Collector needs a database user that can read system tables and nothing else. We recommend a dedicated user so you can audit and revoke its access independently.

### ClickHouse

To create a monitoring user, run the following statements as an administrator:

```sql title="ClickHouse SQL" lines wrap theme={null}
CREATE USER otel_monitoring IDENTIFIED BY 'replace-with-a-strong-password';

GRANT SELECT ON system.query_log TO otel_monitoring;
GRANT SELECT ON system.part_log TO otel_monitoring;
GRANT SELECT ON system.parts TO otel_monitoring;
GRANT SELECT ON system.metrics TO otel_monitoring;
GRANT SELECT ON system.asynchronous_metrics TO otel_monitoring;
GRANT SELECT ON system.events TO otel_monitoring;
```

Grant access to each system table separately rather than running `GRANT SELECT ON system.*`. On ClickHouse Cloud, the wildcard grant fails because the default administrator does not have grant rights on every system table.

The Collector also sets the `readonly` setting to `1` on its own session, so it cannot modify data even if the user is later given broader grants.

### PostgreSQL

If you also want lock monitoring, create a PostgreSQL user and add it to the built-in `pg_monitor` role:

```sql title="PostgreSQL SQL" lines wrap theme={null}
CREATE ROLE otel_monitoring LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT pg_monitor TO otel_monitoring;
GRANT CONNECT ON DATABASE your_database TO otel_monitoring;
```

## Configure the Collector

Create a file named `database-monitoring.yaml` with the following configuration. It defines one SQL query receiver for ClickHouse, a resource processor that stamps your project key on every metric, and an exporter that sends the metrics to LaunchDarkly:

```yaml title="database-monitoring.yaml" expandable lines wrap theme={null}
receivers:
  sqlquery/clickhouse:
    driver: clickhouse
    host: ${env:CLICKHOUSE_HOST}
    port: ${env:CLICKHOUSE_PORT:-9440}
    database: system
    username: ${env:CLICKHOUSE_USERNAME:-otel_monitoring}
    password: ${env:CLICKHOUSE_PASSWORD}
    additional_params:
      dial_timeout: 5s
      read_timeout: 30s
      max_execution_time: 30
      readonly: "1"
      secure: ${env:CLICKHOUSE_SECURE:-true}
      # Lets the query log filter exclude the Collector's own queries.
      log_comment: launchdarkly-database-monitoring
    collection_interval: 60s
    timeout: 30s
    max_open_conn: 2
    queries:
      # Selects that finished in the previous minute, by table.
      - sql: >
          SELECT
              arrayJoin(tables) AS select_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['SelectedRows'])) AS selected_rows,
              toFloat64(sum(ProfileEvents['SelectedBytes'])) AS selected_bytes,
              toFloat64(sum(ProfileEvents['SelectedMarks'])) AS selected_marks,
              toFloat64(sum(ProfileEvents['SelectedParts'])) AS selected_parts,
              toFloat64(sum(ProfileEvents['FilteringMarksWithPrimaryKeyMicroseconds'])) AS primary_key_us,
              toFloat64(sum(ProfileEvents['LoadedMarksMemoryBytes'])) AS loaded_marks_bytes,
              toFloat64(sum(ProfileEvents['DiskReadElapsedMicroseconds'])) AS disk_read_us,
              toFloat64(sum(ProfileEvents['ReadCompressedBytes'])) AS read_compressed_bytes,
              toFloat64(sum(ProfileEvents['MarkCacheHits'])) AS mark_cache_hits,
              toFloat64(sum(ProfileEvents['MarkCacheMisses'])) AS mark_cache_misses,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Microseconds'])) AS s3_read_us,
              toFloat64(sum(ProfileEvents['ReadBufferFromS3Bytes'])) AS s3_read_bytes,
              toFloat64(sum(ProfileEvents['S3ReadRequestsCount'])) AS s3_read_requests,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromCacheBytes'])) AS filesystem_cache_bytes,
              toFloat64(sum(ProfileEvents['CachedReadBufferReadFromSourceBytes'])) AS filesystem_cache_miss_bytes,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind = 'Select'
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY select_table
        metrics:
          - {metric_name: clickhouse.select.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_rows, value_column: selected_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_bytes, value_column: selected_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_marks, value_column: selected_marks, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.selected_parts, value_column: selected_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.primary_key_time, value_column: primary_key_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.loaded_marks_bytes, value_column: loaded_marks_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.disk_read_time, value_column: disk_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.read_compressed_bytes, value_column: read_compressed_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_hits, value_column: mark_cache_hits, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.mark_cache_misses, value_column: mark_cache_misses, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_time, value_column: s3_read_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_bytes, value_column: s3_read_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.s3_read_requests, value_column: s3_read_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_bytes, value_column: filesystem_cache_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.filesystem_cache_miss_bytes, value_column: filesystem_cache_miss_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.select.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [select_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Inserts that finished in the previous minute, by target table.
      - sql: >
          SELECT
              extract(query, '(?i)INSERT\\s+INTO\\s+(?:TABLE\\s+)?(?:`?\\w+`?\\.)?`?(\\w+)') AS insert_table,
              toFloat64(count()) AS queries,
              toFloat64(sum(ProfileEvents['InsertedRows'])) AS inserted_rows,
              toFloat64(sum(ProfileEvents['InsertedBytes'])) AS inserted_bytes,
              toFloat64(sum(ProfileEvents['DiskWriteElapsedMicroseconds'])) AS disk_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Microseconds'])) AS s3_write_us,
              toFloat64(sum(ProfileEvents['WriteBufferFromS3Bytes'])) AS s3_write_bytes,
              toFloat64(sum(ProfileEvents['S3WriteRequestsCount'])) AS s3_write_requests,
              toFloat64(sum(ProfileEvents['DelayedInserts'])) AS delayed_inserts,
              toFloat64(sum(ProfileEvents['RejectedInserts'])) AS rejected_inserts,
              toFloat64(sum(ProfileEvents['RealTimeMicroseconds'])) AS real_us,
              toFloat64(sum(ProfileEvents['OSCPUVirtualTimeMicroseconds'])) AS cpu_us
          FROM system.query_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
            AND is_initial_query = 1
            AND query_kind IN ('Insert', 'AsyncInsert')
            AND current_database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND log_comment != 'launchdarkly-database-monitoring'
          GROUP BY insert_table
        metrics:
          - {metric_name: clickhouse.insert.queries, value_column: queries, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_rows, value_column: inserted_rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.inserted_bytes, value_column: inserted_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.disk_write_time, value_column: disk_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_time, value_column: s3_write_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_bytes, value_column: s3_write_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.s3_write_requests, value_column: s3_write_requests, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.delayed_inserts, value_column: delayed_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.rejected_inserts, value_column: rejected_inserts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.real_time, value_column: real_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.insert.cpu_time, value_column: cpu_us, value_type: double, data_type: gauge, unit: us, attribute_columns: [insert_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Merges that completed in the previous minute, by table.
      - sql: >
          SELECT
              table AS merge_table,
              toFloat64(count()) AS completed,
              toFloat64(sum(duration_ms)) AS duration_ms,
              toFloat64(sum(rows)) AS rows,
              toFloat64(sum(read_rows)) AS rows_read,
              toFloat64(max(peak_memory_usage)) AS peak_memory_bytes
          FROM system.part_log
          WHERE event_date >= today() - 1
            AND event_time >= now() - INTERVAL 75 SECOND
            AND event_time < now() - INTERVAL 15 SECOND
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
            AND event_type = 'MergeParts'
            AND error = 0
          GROUP BY merge_table
        metrics:
          - {metric_name: clickhouse.merges.completed, value_column: completed, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.duration, value_column: duration_ms, value_type: double, data_type: gauge, unit: ms, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows, value_column: rows, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.rows_read, value_column: rows_read, value_type: double, data_type: gauge, unit: "1", attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.merges.peak_memory_bytes, value_column: peak_memory_bytes, value_type: double, data_type: gauge, unit: By, attribute_columns: [merge_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Active parts and primary key memory for the 25 largest tables.
      - sql: >
          SELECT
              table AS part_table,
              toFloat64(sum(primary_key_bytes_in_memory_allocated)) AS primary_key_bytes_allocated,
              toFloat64(count()) AS active_parts
          FROM system.parts
          WHERE active
            AND database = '${env:CLICKHOUSE_DATABASE:-default}'
          GROUP BY table
          ORDER BY primary_key_bytes_allocated DESC
          LIMIT 25
        metrics:
          - {metric_name: clickhouse.parts.primary_key_bytes_allocated, value_column: primary_key_bytes_allocated, value_type: double, data_type: gauge, unit: By, attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}
          - {metric_name: clickhouse.parts.active, value_column: active_parts, value_type: double, data_type: gauge, unit: "1", attribute_columns: [part_table], static_attributes: {database: "${env:CLICKHOUSE_DATABASE:-default}"}}

      # Server-wide memory, cache, and concurrency gauges.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.metrics
          WHERE name IN (
              'MemoryTracking', 'MergesMutationsMemoryTracking',
              'MarkCacheBytes', 'UncompressedCacheBytes', 'QueryCacheBytes', 'QueryConditionCacheBytes',
              'FilesystemCacheSize', 'PageCacheBytes',
              'PartsActive', 'Query'
          )
        metrics:
          - {metric_name: clickhouse.server.metrics.MemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MergesMutationsMemoryTracking, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MergesMutationsMemoryTracking}}
          - {metric_name: clickhouse.server.metrics.MarkCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MarkCacheBytes}}
          - {metric_name: clickhouse.server.metrics.UncompressedCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: UncompressedCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryCacheBytes}}
          - {metric_name: clickhouse.server.metrics.QueryConditionCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueryConditionCacheBytes}}
          - {metric_name: clickhouse.server.metrics.FilesystemCacheSize, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheSize}}
          - {metric_name: clickhouse.server.metrics.PageCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: PageCacheBytes}}
          - {metric_name: clickhouse.server.metrics.PartsActive, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: PartsActive}}
          - {metric_name: clickhouse.server.metrics.Query, value_column: value, value_type: double, data_type: gauge, unit: "1", row_condition: {column: name, value: Query}}

      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.asynchronous_metrics
          WHERE name IN (
              'MemoryResident', 'OSMemoryAvailable',
              'jemalloc.allocated', 'jemalloc.resident',
              'TotalPrimaryKeyBytesInMemory', 'TotalPrimaryKeyBytesInMemoryAllocated',
              'QueriesMemoryUsage', 'FilesystemCacheBytes'
          )
        metrics:
          - {metric_name: clickhouse.server.async_metrics.MemoryResident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: MemoryResident}}
          - {metric_name: clickhouse.server.async_metrics.OSMemoryAvailable, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: OSMemoryAvailable}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.allocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.allocated}}
          - {metric_name: clickhouse.server.async_metrics.jemalloc.resident, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: jemalloc.resident}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemory, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemory}}
          - {metric_name: clickhouse.server.async_metrics.TotalPrimaryKeyBytesInMemoryAllocated, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: TotalPrimaryKeyBytesInMemoryAllocated}}
          - {metric_name: clickhouse.server.async_metrics.QueriesMemoryUsage, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: QueriesMemoryUsage}}
          - {metric_name: clickhouse.server.async_metrics.FilesystemCacheBytes, value_column: value, value_type: double, data_type: gauge, unit: By, row_condition: {column: name, value: FilesystemCacheBytes}}

      # Cumulative counters since the server started.
      - sql: >
          SELECT name, toFloat64(value) AS value
          FROM system.events
          WHERE name IN (
              'SelectedRows', 'SelectedBytes',
              'MarkCacheHits', 'MarkCacheMisses',
              'DiskReadElapsedMicroseconds'
          )
        metrics:
          - {metric_name: clickhouse.server.events.SelectedRows, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: SelectedRows}}
          - {metric_name: clickhouse.server.events.SelectedBytes, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: By, row_condition: {column: name, value: SelectedBytes}}
          - {metric_name: clickhouse.server.events.MarkCacheHits, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheHits}}
          - {metric_name: clickhouse.server.events.MarkCacheMisses, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: "1", row_condition: {column: name, value: MarkCacheMisses}}
          - {metric_name: clickhouse.server.events.DiskReadElapsedMicroseconds, value_column: value, value_type: double, data_type: sum, monotonic: true, aggregation: cumulative, unit: us, row_condition: {column: name, value: DiskReadElapsedMicroseconds}}

processors:
  # Route every metric to your LaunchDarkly project.
  resource:
    attributes:
      - key: launchdarkly.project_id
        value: ${env:LAUNCHDARKLY_PROJECT_KEY}
        action: upsert
      - key: deployment.environment.name
        value: ${env:ENVIRONMENT:-production}
        action: upsert
      - key: service.name
        value: ${env:SERVICE_NAME:-clickhouse}
        action: upsert

exporters:
  otlphttp/launchdarkly:
    endpoint: https://otel.observability.app.eu.launchdarkly.com
    compression: gzip
    timeout: 30s
    retry_on_failure:
      enabled: true
      initial_interval: 5s
      max_interval: 30s
      max_elapsed_time: 300s

service:
  pipelines:
    metrics:
      receivers: [sqlquery/clickhouse]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

The `database` attribute and the `current_database` filter scope the table-level queries to one ClickHouse database. If your workload spans several databases, run one Collector per database, or remove the `current_database` and `database` filters to see every database at once.

### Configuration options

The configuration reads its connection details from environment variables. You can keep the same file in every environment. The following table describes each variable:

<table>
  <thead>
    <tr>
      <th>Variable</th>
      <th>Description</th>
      <th>Required</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`LAUNCHDARKLY_PROJECT_KEY`</td>
      <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_HOST`</td>
      <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read [Monitor separate read and write clusters](#monitor-separate-read-and-write-clusters).</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_PORT`</td>
      <td>Native protocol port. Defaults to `9440`, the TLS port. Use `9000` with `CLICKHOUSE_SECURE=false` for a server without TLS.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_SECURE`</td>
      <td>Whether to connect with TLS. Defaults to `true`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_USERNAME`, `CLICKHOUSE_PASSWORD`</td>
      <td>Credentials for the monitoring user you created. The username defaults to `otel_monitoring`.</td>
      <td>Yes</td>
    </tr>

    <tr>
      <td>`CLICKHOUSE_DATABASE`</td>
      <td>The ClickHouse database whose tables you want to monitor. Defaults to `default`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`ENVIRONMENT`</td>
      <td>Value for the `deployment.environment.name` attribute. Use it to filter metrics by environment. Defaults to `production`.</td>
      <td>No</td>
    </tr>

    <tr>
      <td>`SERVICE_NAME`</td>
      <td>Value for the `service.name` attribute. Defaults to `clickhouse`. Use the same name on your application's database spans to correlate them with these metrics.</td>
      <td>No</td>
    </tr>
  </tbody>
</table>

### Add PostgreSQL lock monitoring

To also monitor waiting lock requests in a PostgreSQL database, add a second receiver and pipeline to the same file:

```yaml title="database-monitoring.yaml (PostgreSQL additions)" expandable lines wrap theme={null}
receivers:
  sqlquery/postgres-locks:
    driver: postgres
    host: ${env:POSTGRES_HOST}
    port: ${env:POSTGRES_PORT:-5432}
    database: ${env:POSTGRES_DATABASE:-postgres}
    username: ${env:POSTGRES_USERNAME:-otel_monitoring}
    password: ${env:POSTGRES_PASSWORD}
    additional_params:
      sslmode: ${env:POSTGRES_SSLMODE:-require}
      connect_timeout: 5
    collection_interval: 60s
    timeout: 10s
    max_open_conn: 1
    queries:
      - sql: >
          WITH blocked AS (
              SELECT
                  l.mode::text AS lock_mode,
                  l.locktype::text AS lock_type,
                  COALESCE(pn.nspname, '')::text AS schema_name,
                  COALESCE(pd.datname, '')::text AS db_name,
                  COALESCE(pc.relname, '')::text AS rel_name,
                  l.granted::text AS granted,
                  l.fastpath::text AS fastpath
              FROM pg_locks l
              LEFT JOIN pg_database pd ON (l.database = pd.oid)
              LEFT JOIN pg_class pc ON (l.relation = pc.oid)
              LEFT JOIN pg_namespace pn ON (pn.oid = pc.relnamespace)
              WHERE (pc.oid IS NULL OR (pc.relname NOT LIKE 'pg^_%' ESCAPE '^'))
                AND l.mode IS NOT NULL
                AND l.granted = false
                AND (l.database IS NULL OR pd.datname = current_database())
          )
          SELECT
              lock_mode,
              lock_type,
              schema_name AS schema,
              db_name AS db,
              rel_name AS table,
              granted,
              fastpath,
              count(*)::float8 AS locks
          FROM blocked
          GROUP BY lock_mode, lock_type, schema_name, db_name, rel_name, granted, fastpath
          UNION ALL
          SELECT '', '', '', current_database()::text, '', 'false', 'false', 0::float8
          WHERE NOT EXISTS (SELECT 1 FROM blocked)
        metrics:
          - metric_name: postgresql.locks
            value_column: locks
            value_type: double
            data_type: gauge
            unit: "1"
            description: Ungranted pg_locks rows
            attribute_columns: [lock_mode, lock_type, schema, db, table, granted, fastpath]

service:
  pipelines:
    metrics/postgres:
      receivers: [sqlquery/postgres-locks]
      processors: [resource]
      exporters: [otlphttp/launchdarkly]
```

Set `POSTGRES_HOST`, `POSTGRES_DATABASE`, `POSTGRES_USERNAME`, and `POSTGRES_PASSWORD` when you run the Collector. `POSTGRES_SSLMODE` defaults to `require`. Set it to `disable` only for a server without TLS.

### Monitor separate read and write clusters

Many ClickHouse deployments send queries to a read replica and send inserts to a writer. In that case, the query log on each host only records the queries that host ran. To monitor both, duplicate the `sqlquery/clickhouse` receiver as `sqlquery/clickhouse-read` and `sqlquery/clickhouse-write`, point each at the appropriate host, and list both receivers in the `metrics` pipeline. Keep the select, parts, and server memory queries on the read receiver, and the insert and merge queries on the write receiver. That way you do not count the same work twice.

## Run the Collector

Use the OpenTelemetry Collector Contrib image, which includes the SQL query receiver. We recommend pinning the image to the version you tested with rather than using `latest`.

### Docker

To run the Collector with Docker:

```bash title="Shell" lines wrap theme={null}
docker run -d \
  --name database-monitoring \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_USERNAME=otel_monitoring \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -e CLICKHOUSE_DATABASE=default \
  -e ENVIRONMENT=production \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0
```

Replace `YOUR_PROJECT_KEY` with your LaunchDarkly project key and `YOUR_PASSWORD` with the monitoring user's password.

### Docker Compose

To run the Collector alongside your database with Docker Compose:

```yaml title="compose.yaml" lines wrap theme={null}
services:
  database-monitoring:
    image: otel/opentelemetry-collector-contrib:0.159.0
    restart: unless-stopped
    environment:
      LAUNCHDARKLY_PROJECT_KEY: YOUR_PROJECT_KEY
      CLICKHOUSE_HOST: clickhouse
      CLICKHOUSE_PORT: "9000"
      CLICKHOUSE_SECURE: "false"
      CLICKHOUSE_USERNAME: otel_monitoring
      CLICKHOUSE_PASSWORD: YOUR_PASSWORD
      CLICKHOUSE_DATABASE: default
      ENVIRONMENT: staging
    volumes:
      - ./database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro
```

### Kubernetes

Run the Collector as a single-replica Deployment so each scrape runs once. Store the configuration in a ConfigMap and the database password in a Secret. The [OpenTelemetry Operator for Kubernetes](https://opentelemetry.io/docs/kubernetes/operator/) can manage the Deployment for you. Set `mode: deployment` and `replicas: 1` on the `OpenTelemetryCollector` resource.

### Validate the configuration

Before you deploy, check that the Collector accepts the configuration and that every environment variable resolves:

```bash title="Shell" lines wrap theme={null}
docker run --rm \
  -e LAUNCHDARKLY_PROJECT_KEY=YOUR_PROJECT_KEY \
  -e CLICKHOUSE_HOST=clickhouse.internal.example.com \
  -e CLICKHOUSE_PASSWORD=YOUR_PASSWORD \
  -v $(pwd)/database-monitoring.yaml:/etc/otelcol-contrib/config.yaml:ro \
  otel/opentelemetry-collector-contrib:0.159.0 validate --config /etc/otelcol-contrib/config.yaml
```

## Correlate database metrics with traces

Database monitoring tells you what each table is doing. Your application's traces tell you which requests are waiting on it. To connect the two, instrument database calls in your application with the OpenTelemetry [database semantic conventions](https://opentelemetry.io/docs/specs/semconv/database/database-spans/), and use the same `service.name` on the Collector and in your application's database client spans.

The most useful span attributes are:

* `db.system.name`: Set to `clickhouse` or `postgresql`.
* `db.namespace`: The database name, matching the `database` attribute on your metrics.
* `db.collection.name`: The table name, matching `select_table` or `insert_table` on your metrics.
* `db.query.summary`: A low-cardinality summary of the statement, such as `SELECT events`.

Most OpenTelemetry database instrumentations set these attributes for you. With them in place, you can open a slow span in [Traces](https://launchdarkly.com/docs/home/observability/traces.md), read the table it touched, and compare it against that table's select metrics for the same minute.

## View database metrics in LaunchDarkly

After you start the Collector, metrics begin arriving in LaunchDarkly within two minutes.

To review your database metrics:

1. Open the **Telemetry** section and navigate to the **Observability metrics** list.
2. Search for `clickhouse.` to list the database metrics.
3. Click a metric name to open its details, then group by `select_table`, `insert_table`, or `merge_table` to compare tables.

To learn more about graphing metrics, read [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](https://launchdarkly.com/docs/home/observability/custom-dashboards.md).

### Alert on database metrics

You can create alerts on any database metric. Useful starting points include:

* `clickhouse.insert.rejected_inserts` or `clickhouse.insert.delayed_inserts` above 0, which means a table has too many parts and merges are falling behind.
* `clickhouse.parts.active` rising steadily for one table, which usually precedes rejected inserts.
* `clickhouse.server.metrics.MemoryTracking` approaching your server's memory limit.
* `clickhouse.select.mark_cache_misses` rising for a table while `clickhouse.select.selected_marks` stays flat, which means the mark cache is too small for your working set.
* `postgresql.locks` above 0 for more than a few minutes, which means a transaction is blocking others.

To learn how to create alerts, read [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md).

**Developer**

### Investigate with Vega

Vega, the LaunchDarkly AI-powered agent, can read your database metrics during an investigation. When an alert fires on a slow endpoint or an error spike, Vega can check whether the affected service's database tables saw a merge backlog, a mark cache miss spike, rejected inserts, or lock contention in the same window, and include that in its summary. If your traces carry the database semantic convention attributes described above, Vega can also name the table and query shape behind a slow span.

To learn more, read [Vega for auto-remediation](https://launchdarkly.com/docs/home/observability/vega.md).

## Troubleshooting

If database metrics are not appearing in LaunchDarkly:

1. **Check the Collector logs**: Run `docker logs database-monitoring` or the equivalent for your platform. A connection error names the host and port the receiver tried to reach. An authentication error means the monitoring user's credentials or grants are wrong.

2. **Check the project key**: Verify that `LAUNCHDARKLY_PROJECT_KEY` matches the project key in your LaunchDarkly project settings, not an environment key or SDK key.

3. **Verify that ClickHouse writes the query log**: Run `SELECT count() FROM system.query_log WHERE event_time > now() - INTERVAL 5 MINUTE` as the monitoring user. If the table does not exist or is empty while queries are running, enable `query_log` and `part_log` in your ClickHouse server configuration.

4. **Verify network connectivity**: Confirm the Collector host can reach `otel.observability.app.eu.launchdarkly.com` on port `443`:

```bash title="Shell" lines wrap theme={null}
curl -sS -o /dev/null -w "%{http_code}\n" https://otel.observability.app.eu.launchdarkly.com/v1/metrics
```

5. **Check for empty windows**: Table-level metrics only have data points for tables that ran queries in the previous minute. A table that received no selects or inserts has no points for that minute, which is expected. Server metrics are reported every minute regardless.

6. **Check TLS settings**: If the Collector reports a handshake error, confirm that `CLICKHOUSE_SECURE` and `CLICKHOUSE_PORT` match your server. The TLS native port is usually `9440` and the plain port is `9000`.

## Related content

* [Observability metrics](https://launchdarkly.com/docs/home/observability/observability-metrics)
* [Alerts](https://launchdarkly.com/docs/home/observability/alerts.md)
* [Traces](https://launchdarkly.com/docs/home/observability/traces.md)
* [Self-hosting an OpenTelemetry Collector](https://launchdarkly.com/docs/sdk/features/observability-otel-collector.md)
* [SQL query receiver documentation](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver)
* [ClickHouse system tables](https://clickhouse.com/docs/operations/system-tables)