ClickHouse Metrics endpoint (on port 8888) returns duplicates

[janeklb]

We have been using our own system.custom_metrics table:

CREATE TABLE IF NOT EXISTS system.custom_metrics
(
    `name` String,
    `value` Float64,
    `help` String,
    `labels` Map(String, String),
    `type` String
)
ENGINE = Memory;

After upgrading operator from 0.25.3 to 0.25.6 (with ClickHouse 25.8.10.7) the localhost:8888/metrics endpoint responds with an HTTP 500 status

$ curl -s localhost:8888/metrics 2>&1 1>/dev/null | sort | uniq
* collected metric "chi_clickhouse_metric_" { label:{name:"chi"  value:"clickhouse-installation-1"}  label:{name:"hostname"  value:"chi-clickhouse-installation-1-ch-1-0-0.some-app-name.svc.cluster.local"}  label:{name:"kustomize_toolkit_fluxcd_io_name"  value:"some-app-name"}  label:{name:"kustomize_toolkit_fluxcd_io_namespace"  value:"flux-system"}  label:{name:"namespace"  value:"some-app-name"}  gauge:{value:0}} was collected before with the same name and label values
* collected metric "chi_clickhouse_metric_" { label:{name:"chi"  value:"clickhouse-installation-1"}  label:{name:"hostname"  value:"chi-clickhouse-installation-1-ch-1-0-0.some-app-name.svc.cluster.local"}  label:{name:"kustomize_toolkit_fluxcd_io_name"  value:"some-app-name"}  label:{name:"kustomize_toolkit_fluxcd_io_namespace"  value:"flux-system"}  label:{name:"namespace"  value:"some-app-name"}  gauge:{value:1}} was collected before with the same name and label values
* collected metric "chi_clickhouse_metric_" { label:{name:"chi"  value:"clickhouse-installation-1"}  label:{name:"hostname"  value:"chi-clickhouse-installation-1-ch-1-0-1.some-app-name.svc.cluster.local"}  label:{name:"kustomize_toolkit_fluxcd_io_name"  value:"some-app-name"}  label:{name:"kustomize_toolkit_fluxcd_io_namespace"  value:"flux-system"}  label:{name:"namespace"  value:"some-app-name"}  gauge:{value:0}} was collected before with the same name and label values
81 error(s) occurred:
An error has occurred while serving metrics:

I suppose this error it is related to 12de7da

As far as I understand custom_metrics is not a standard out-of-the-box ClickHouse table.. but maybe that has changed? The only reference I found to custom_metrics is https://clickhouse.com/docs/knowledgebase/mapping-of-system-metrics-to-prometheus-metrics but that doesn't seem to imply any sort of table schema...

There is also https://clickhouse.com/blog/clickhouse-cloud-now-supports-prometheus-monitoring#implementation - which is where we got our schema from -- but that seems to be set up for ClickHouse Cloud only.

Is the workaround / fix to change our custom_metrics table schema? And if so, what schema should be used / is expected by clickhouse-operator?

[janeklb]

We use our own system.custom_metrics table like this to set up a /custom_metrics endpoint for prometheus to scrape

kind: ClickHouseInstallation
spec:
  configuration:
    files:
      conf.d/startup_scripts.yaml: |
        startup_scripts:
          script:
            - query: >-
                DROP TABLE IF EXISTS system.custom_metrics;
            - query: >-
                CREATE TABLE IF NOT EXISTS system.custom_metrics
                (
                    `name` String,
                    `value` Float64,
                    `help` String,
                    `labels` Map(String, String),
                    `type` String
                )
                ENGINE = Memory;
            - query: >-
                DROP TABLE IF EXISTS system.custom_metrics_refresher;
            - query: >-
                CREATE MATERIALIZED VIEW IF NOT EXISTS system.custom_metrics_refresher
                REFRESH EVERY 1 MINUTE TO system.custom_metrics
                (
                    `name` String,
                    `value` Float64,
                    `help` String,
                    `labels` Map(String, String),
                    `type` String
                )
                DEFINER = default SQL SECURITY DEFINER
                AS SELECT
                    'view_refresh_exceptions' AS name,
                    if(exception != '', 1, 0) AS value,
                    'Gauge indicating whether an exception was thrown while refreshing a view' AS help,
                    map('hostname', hostName(), 'view_name', view, 'database', database) AS labels,
                    'gauge' AS type
                FROM system.view_refreshes
                UNION ALL
                SELECT
                    'dictionary_exceptions' AS name,
                    if(last_exception != '', 1, 0) AS value,
                    'Gauge indicating whether an exception was thrown while loading a dictionary' AS help,
                    map('hostname', hostName(), 'table', d.name, 'database', database) AS labels,
                    'gauge' AS type
                FROM system.dictionaries AS d;
      conf.d/custom_metrics_endpoints.xml: |
        <clickhouse>
          <http_handlers>
            <rule>
              <url>/custom_metrics</url>
              <methods>POST,GET</methods>
              <handler>
                <type>predefined_query_handler</type>
                <query>SELECT * FROM system.custom_metrics FORMAT prometheus</query>
              </handler>
            </rule>
            <defaults/>
          </http_handlers>
        </clickhouse>

[janeklb]

I suppose the solution for my problem would just be to use a different table name for our custom metrics (and update the /custom_metrics endpoint accordingly).

Ideally, in my setup above, we could get rid of this endpoint and just rely on the metrics-exporter; however, at the moment there is no way to add additional labels there.