> ## 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

export const host_2 = "otel.observability.app.eu.launchdarkly.com"

export const host_1 = "otel.observability.app.launchdarkly.com"

export const host_0 = "otel.observability.app.launchdarkly.com"

<View title="Developer">
  <View title="Federal docs">
    <Danger>
      **This integration is not available in federal environments**

      To learn more, read <a href="/docs/home/infrastructure/federal">LaunchDarkly in federal environments</a>.
    </Danger>
  </View>

  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](/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><code>clickhouse.select.queries</code></td>
        <td>count</td>
        <td>Selects that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_rows</code>, <code>clickhouse.select.selected\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes read to answer the queries.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_marks</code>, <code>clickhouse.select.selected\_parts</code></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><code>clickhouse.select.primary\_key\_time</code></td>
        <td>microseconds</td>
        <td>Time spent filtering marks with the primary key.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.loaded\_marks\_bytes</code>, <code>clickhouse.select.mark\_cache\_hits</code>, <code>clickhouse.select.mark\_cache\_misses</code></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><code>clickhouse.select.disk\_read\_time</code>, <code>clickhouse.select.read\_compressed\_bytes</code></td>
        <td>microseconds, bytes</td>
        <td>Local disk reads.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.s3\_read\_time</code>, <code>clickhouse.select.s3\_read\_bytes</code>, <code>clickhouse.select.s3\_read\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage reads, for tables stored on S3-compatible disks.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.filesystem\_cache\_bytes</code>, <code>clickhouse.select.filesystem\_cache\_miss\_bytes</code></td>
        <td>bytes</td>
        <td>Bytes served from the filesystem cache and bytes that missed it.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.real\_time</code>, <code>clickhouse.select.cpu\_time</code></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><code>clickhouse.insert.queries</code></td>
        <td>count</td>
        <td>Inserts that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.inserted\_rows</code>, <code>clickhouse.insert.inserted\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes written.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.disk\_write\_time</code></td>
        <td>microseconds</td>
        <td>Local disk write time.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.s3\_write\_time</code>, <code>clickhouse.insert.s3\_write\_bytes</code>, <code>clickhouse.insert.s3\_write\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage writes.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.delayed\_inserts</code>, <code>clickhouse.insert.rejected\_inserts</code></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><code>clickhouse.insert.real\_time</code>, <code>clickhouse.insert.cpu\_time</code></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><code>clickhouse.merges.completed</code></td>
        <td>count</td>
        <td>Merges that finished in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.merges.rows</code>, <code>clickhouse.merges.rows\_read</code></td>
        <td>count</td>
        <td>Rows written by and read into merges.</td>
      </tr>

      <tr>
        <td><code>clickhouse.merges.peak\_memory\_bytes</code></td>
        <td>bytes</td>
        <td>Largest peak memory of any merge in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.parts.primary\_key\_bytes\_allocated</code></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><code>clickhouse.server.metrics.</code></td>
        <td><code>system.metrics</code></td>
        <td><code>MemoryTracking</code>, <code>MergesMutationsMemoryTracking</code>, <code>MarkCacheBytes</code>, <code>UncompressedCacheBytes</code>, <code>QueryCacheBytes</code>, <code>QueryConditionCacheBytes</code>, <code>FilesystemCacheSize</code>, <code>PageCacheBytes</code>, <code>PartsActive</code>, <code>Query</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.async\_metrics.</code></td>
        <td><code>system.asynchronous\_metrics</code></td>
        <td><code>MemoryResident</code>, <code>OSMemoryAvailable</code>, <code>jemalloc.allocated</code>, <code>jemalloc.resident</code>, <code>TotalPrimaryKeyBytesInMemory</code>, <code>TotalPrimaryKeyBytesInMemoryAllocated</code>, <code>QueriesMemoryUsage</code>, <code>FilesystemCacheBytes</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.events.</code></td>
        <td><code>system.events</code></td>
        <td><code>SelectedRows</code>, <code>SelectedBytes</code>, <code>MarkCacheHits</code>, <code>MarkCacheMisses</code>, <code>DiskReadElapsedMicroseconds</code>. 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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  ## 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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><code>LAUNCHDARKLY\_PROJECT\_KEY</code></td>
        <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_HOST</code></td>
        <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read <a href="#monitor-separate-read-and-write-clusters">Monitor separate read and write clusters</a>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_PORT</code></td>
        <td>Native protocol port. Defaults to <code>9440</code>, the TLS port. Use <code>9000</code> with <code>CLICKHOUSE\_SECURE=false</code> for a server without TLS.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_SECURE</code></td>
        <td>Whether to connect with TLS. Defaults to <code>true</code>.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_USERNAME</code>, <code>CLICKHOUSE\_PASSWORD</code></td>
        <td>Credentials for the monitoring user you created. The username defaults to <code>otel\_monitoring</code>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_DATABASE</code></td>
        <td>The ClickHouse database whose tables you want to monitor. Defaults to <code>default</code>.</td>
        <td>No</td>
      </tr>

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

      <tr>
        <td><code>SERVICE\_NAME</code></td>
        <td>Value for the <code>service.name</code> attribute. Defaults to <code>clickhouse</code>. 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ### 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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ## 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](/docs/home/observability/traces), 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](/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](/docs/home/observability/custom-dashboards).

  ### 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](/docs/home/observability/alerts).

  <View title="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](/docs/home/observability/vega).
  </View>

  ## 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 <code>{host_0}</code> on port `443`:

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

  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](/docs/home/observability/observability-metrics)
  * [Alerts](/docs/home/observability/alerts)
  * [Traces](/docs/home/observability/traces)
  * [Self-hosting an OpenTelemetry Collector](/docs/sdk/features/observability-otel-collector)
  * [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)
</View>

<View title="Federal docs">
  <View title="Federal docs">
    <Danger>
      **This integration is not available in federal environments**

      To learn more, read <a href="/docs/home/infrastructure/federal">LaunchDarkly in federal environments</a>.
    </Danger>
  </View>

  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](/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><code>clickhouse.select.queries</code></td>
        <td>count</td>
        <td>Selects that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_rows</code>, <code>clickhouse.select.selected\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes read to answer the queries.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_marks</code>, <code>clickhouse.select.selected\_parts</code></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><code>clickhouse.select.primary\_key\_time</code></td>
        <td>microseconds</td>
        <td>Time spent filtering marks with the primary key.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.loaded\_marks\_bytes</code>, <code>clickhouse.select.mark\_cache\_hits</code>, <code>clickhouse.select.mark\_cache\_misses</code></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><code>clickhouse.select.disk\_read\_time</code>, <code>clickhouse.select.read\_compressed\_bytes</code></td>
        <td>microseconds, bytes</td>
        <td>Local disk reads.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.s3\_read\_time</code>, <code>clickhouse.select.s3\_read\_bytes</code>, <code>clickhouse.select.s3\_read\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage reads, for tables stored on S3-compatible disks.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.filesystem\_cache\_bytes</code>, <code>clickhouse.select.filesystem\_cache\_miss\_bytes</code></td>
        <td>bytes</td>
        <td>Bytes served from the filesystem cache and bytes that missed it.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.real\_time</code>, <code>clickhouse.select.cpu\_time</code></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><code>clickhouse.insert.queries</code></td>
        <td>count</td>
        <td>Inserts that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.inserted\_rows</code>, <code>clickhouse.insert.inserted\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes written.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.disk\_write\_time</code></td>
        <td>microseconds</td>
        <td>Local disk write time.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.s3\_write\_time</code>, <code>clickhouse.insert.s3\_write\_bytes</code>, <code>clickhouse.insert.s3\_write\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage writes.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.delayed\_inserts</code>, <code>clickhouse.insert.rejected\_inserts</code></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><code>clickhouse.insert.real\_time</code>, <code>clickhouse.insert.cpu\_time</code></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><code>clickhouse.merges.completed</code></td>
        <td>count</td>
        <td>Merges that finished in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.merges.rows</code>, <code>clickhouse.merges.rows\_read</code></td>
        <td>count</td>
        <td>Rows written by and read into merges.</td>
      </tr>

      <tr>
        <td><code>clickhouse.merges.peak\_memory\_bytes</code></td>
        <td>bytes</td>
        <td>Largest peak memory of any merge in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.parts.primary\_key\_bytes\_allocated</code></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><code>clickhouse.server.metrics.</code></td>
        <td><code>system.metrics</code></td>
        <td><code>MemoryTracking</code>, <code>MergesMutationsMemoryTracking</code>, <code>MarkCacheBytes</code>, <code>UncompressedCacheBytes</code>, <code>QueryCacheBytes</code>, <code>QueryConditionCacheBytes</code>, <code>FilesystemCacheSize</code>, <code>PageCacheBytes</code>, <code>PartsActive</code>, <code>Query</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.async\_metrics.</code></td>
        <td><code>system.asynchronous\_metrics</code></td>
        <td><code>MemoryResident</code>, <code>OSMemoryAvailable</code>, <code>jemalloc.allocated</code>, <code>jemalloc.resident</code>, <code>TotalPrimaryKeyBytesInMemory</code>, <code>TotalPrimaryKeyBytesInMemoryAllocated</code>, <code>QueriesMemoryUsage</code>, <code>FilesystemCacheBytes</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.events.</code></td>
        <td><code>system.events</code></td>
        <td><code>SelectedRows</code>, <code>SelectedBytes</code>, <code>MarkCacheHits</code>, <code>MarkCacheMisses</code>, <code>DiskReadElapsedMicroseconds</code>. 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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  ## 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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><code>LAUNCHDARKLY\_PROJECT\_KEY</code></td>
        <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_HOST</code></td>
        <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read <a href="#monitor-separate-read-and-write-clusters">Monitor separate read and write clusters</a>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_PORT</code></td>
        <td>Native protocol port. Defaults to <code>9440</code>, the TLS port. Use <code>9000</code> with <code>CLICKHOUSE\_SECURE=false</code> for a server without TLS.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_SECURE</code></td>
        <td>Whether to connect with TLS. Defaults to <code>true</code>.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_USERNAME</code>, <code>CLICKHOUSE\_PASSWORD</code></td>
        <td>Credentials for the monitoring user you created. The username defaults to <code>otel\_monitoring</code>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_DATABASE</code></td>
        <td>The ClickHouse database whose tables you want to monitor. Defaults to <code>default</code>.</td>
        <td>No</td>
      </tr>

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

      <tr>
        <td><code>SERVICE\_NAME</code></td>
        <td>Value for the <code>service.name</code> attribute. Defaults to <code>clickhouse</code>. 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ### 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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ## 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](/docs/home/observability/traces), 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](/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](/docs/home/observability/custom-dashboards).

  ### 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](/docs/home/observability/alerts).

  <View title="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](/docs/home/observability/vega).
  </View>

  ## 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 <code>{host_1}</code> on port `443`:

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

  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](/docs/home/observability/observability-metrics)
  * [Alerts](/docs/home/observability/alerts)
  * [Traces](/docs/home/observability/traces)
  * [Self-hosting an OpenTelemetry Collector](/docs/sdk/features/observability-otel-collector)
  * [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)
</View>

<View title="EU docs">
  <View title="Federal docs">
    <Danger>
      **This integration is not available in federal environments**

      To learn more, read <a href="/docs/home/infrastructure/federal">LaunchDarkly in federal environments</a>.
    </Danger>
  </View>

  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](/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><code>clickhouse.select.queries</code></td>
        <td>count</td>
        <td>Selects that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_rows</code>, <code>clickhouse.select.selected\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes read to answer the queries.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.selected\_marks</code>, <code>clickhouse.select.selected\_parts</code></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><code>clickhouse.select.primary\_key\_time</code></td>
        <td>microseconds</td>
        <td>Time spent filtering marks with the primary key.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.loaded\_marks\_bytes</code>, <code>clickhouse.select.mark\_cache\_hits</code>, <code>clickhouse.select.mark\_cache\_misses</code></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><code>clickhouse.select.disk\_read\_time</code>, <code>clickhouse.select.read\_compressed\_bytes</code></td>
        <td>microseconds, bytes</td>
        <td>Local disk reads.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.s3\_read\_time</code>, <code>clickhouse.select.s3\_read\_bytes</code>, <code>clickhouse.select.s3\_read\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage reads, for tables stored on S3-compatible disks.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.filesystem\_cache\_bytes</code>, <code>clickhouse.select.filesystem\_cache\_miss\_bytes</code></td>
        <td>bytes</td>
        <td>Bytes served from the filesystem cache and bytes that missed it.</td>
      </tr>

      <tr>
        <td><code>clickhouse.select.real\_time</code>, <code>clickhouse.select.cpu\_time</code></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><code>clickhouse.insert.queries</code></td>
        <td>count</td>
        <td>Inserts that finished in the window.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.inserted\_rows</code>, <code>clickhouse.insert.inserted\_bytes</code></td>
        <td>count, bytes</td>
        <td>Rows and bytes written.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.disk\_write\_time</code></td>
        <td>microseconds</td>
        <td>Local disk write time.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.s3\_write\_time</code>, <code>clickhouse.insert.s3\_write\_bytes</code>, <code>clickhouse.insert.s3\_write\_requests</code></td>
        <td>microseconds, bytes, count</td>
        <td>Object storage writes.</td>
      </tr>

      <tr>
        <td><code>clickhouse.insert.delayed\_inserts</code>, <code>clickhouse.insert.rejected\_inserts</code></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><code>clickhouse.insert.real\_time</code>, <code>clickhouse.insert.cpu\_time</code></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><code>clickhouse.merges.completed</code></td>
        <td>count</td>
        <td>Merges that finished in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.merges.rows</code>, <code>clickhouse.merges.rows\_read</code></td>
        <td>count</td>
        <td>Rows written by and read into merges.</td>
      </tr>

      <tr>
        <td><code>clickhouse.merges.peak\_memory\_bytes</code></td>
        <td>bytes</td>
        <td>Largest peak memory of any merge in the window.</td>
      </tr>

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

      <tr>
        <td><code>clickhouse.parts.primary\_key\_bytes\_allocated</code></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><code>clickhouse.server.metrics.</code></td>
        <td><code>system.metrics</code></td>
        <td><code>MemoryTracking</code>, <code>MergesMutationsMemoryTracking</code>, <code>MarkCacheBytes</code>, <code>UncompressedCacheBytes</code>, <code>QueryCacheBytes</code>, <code>QueryConditionCacheBytes</code>, <code>FilesystemCacheSize</code>, <code>PageCacheBytes</code>, <code>PartsActive</code>, <code>Query</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.async\_metrics.</code></td>
        <td><code>system.asynchronous\_metrics</code></td>
        <td><code>MemoryResident</code>, <code>OSMemoryAvailable</code>, <code>jemalloc.allocated</code>, <code>jemalloc.resident</code>, <code>TotalPrimaryKeyBytesInMemory</code>, <code>TotalPrimaryKeyBytesInMemoryAllocated</code>, <code>QueriesMemoryUsage</code>, <code>FilesystemCacheBytes</code></td>
      </tr>

      <tr>
        <td><code>clickhouse.server.events.</code></td>
        <td><code>system.events</code></td>
        <td><code>SelectedRows</code>, <code>SelectedBytes</code>, <code>MarkCacheHits</code>, <code>MarkCacheMisses</code>, <code>DiskReadElapsedMicroseconds</code>. 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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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;
    ```
  </CodeGroup>

  ## 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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><code>LAUNCHDARKLY\_PROJECT\_KEY</code></td>
        <td>Your LaunchDarkly project key. LaunchDarkly uses it to route metrics to your project.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_HOST</code></td>
        <td>Hostname of your ClickHouse server. If you run separate read and write replicas, read <a href="#monitor-separate-read-and-write-clusters">Monitor separate read and write clusters</a>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_PORT</code></td>
        <td>Native protocol port. Defaults to <code>9440</code>, the TLS port. Use <code>9000</code> with <code>CLICKHOUSE\_SECURE=false</code> for a server without TLS.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_SECURE</code></td>
        <td>Whether to connect with TLS. Defaults to <code>true</code>.</td>
        <td>No</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_USERNAME</code>, <code>CLICKHOUSE\_PASSWORD</code></td>
        <td>Credentials for the monitoring user you created. The username defaults to <code>otel\_monitoring</code>.</td>
        <td>Yes</td>
      </tr>

      <tr>
        <td><code>CLICKHOUSE\_DATABASE</code></td>
        <td>The ClickHouse database whose tables you want to monitor. Defaults to <code>default</code>.</td>
        <td>No</td>
      </tr>

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

      <tr>
        <td><code>SERVICE\_NAME</code></td>
        <td>Value for the <code>service.name</code> attribute. Defaults to <code>clickhouse</code>. 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:

  <CodeGroup>
    ```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]
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ### 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:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  ## 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](/docs/home/observability/traces), 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](/docs/home/observability/observability-metrics). To add database graphs to a custom dashboard, read [Creating custom dashboards](/docs/home/observability/custom-dashboards).

  ### 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](/docs/home/observability/alerts).

  <View title="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](/docs/home/observability/vega).
  </View>

  ## 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 <code>{host_2}</code> on port `443`:

  <CodeGroup>
    ```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
    ```
  </CodeGroup>

  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](/docs/home/observability/observability-metrics)
  * [Alerts](/docs/home/observability/alerts)
  * [Traces](/docs/home/observability/traces)
  * [Self-hosting an OpenTelemetry Collector](/docs/sdk/features/observability-otel-collector)
  * [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)
</View>