zabbix_export:
  version: '6.0'
  date: '2021-11-21T21:55:49Z'
  groups:
    -
      uuid: 7df96b18c230490a9a0a9e2307226338
      name: Templates
  templates:
    -
      uuid: a7129a0136d04d1594b66ea720267d4d
      template: Clickhouse
      name: Clickhouse
      description: |
        ## Overview
        
        Template to monitor main Clickhouse parameters *without using additional scripts*
        
        
        It uses HTTP interface to read metrics from *system* database using Zabbix Agent's HTTP client
        
        
        Gathered metrics:
        
        
        * MaxPartCountForPartition
        * ReplicasMaxQueueSize
        * Uptime
        * InsertedBytes
        * InsertedRows
        * Query
        * InsertQuery
        * SelectQuery
        * Merge
        * Inactive parts count
        * HTTPConnection
        * TCPConnection
        * InterserverConnection
        * LeaderReplica
        * ReadonlyReplica
        * Used memory (this is roughly calculated from metrics and is not accurate)
        * Ping (`/ping` HTTP endpoint)
        
        
        <p style="box-sizing: border-box; margin-top: 0px; margin-bottom: 16px; color: #24292e; font-family: -apple-system, system-ui, 'Segoe UI', Helvetica, Arial, sans-serif, 'Apple Color Emoji',
        
        ## Author
        
        Igor Novgorodov
        
        
      groups:
        -
          name: Templates
      items:
        -
          uuid: 7e8b4c2249134b7484e77197e5756b82
          name: 'Clickhouse Events - InsertedBytes'
          type: DEPENDENT
          key: events.InsertedBytes
          delay: '0'
          history: 31d
          units: B
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.InsertedBytes
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 1f801f89cde642dabe7e7ef4f09b8580
          name: 'Clickhouse Events - InsertedRows'
          type: DEPENDENT
          key: events.InsertedRows
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.InsertedRows
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 22bf1bf6f62f44aa82c7cc6ba3b41c79
          name: 'Clickhouse Events - InsertQuery'
          type: DEPENDENT
          key: events.InsertQuery
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.InsertQuery
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 6e7162667f564286825281466663a916
          name: 'Clickhouse Events - Merge'
          type: DEPENDENT
          key: events.Merge
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.Merge
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 5c6de918ff3948dd827b6c77da9a95be
          name: 'Clickhouse Events - Query'
          type: DEPENDENT
          key: events.Query
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.Query
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 6f1de3fe593b4bd8a01f08c0e23a4fb1
          name: 'Clickhouse Events - SelectQuery'
          type: DEPENDENT
          key: events.SelectQuery
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.SelectQuery
            -
              type: CHANGE_PER_SECOND
              parameters:
                - ''
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 7d194e8ff29c44069680ac8d6a71f1b9
          name: 'Clickhouse AsyncMetrics - MaxPartCountForPartition'
          type: DEPENDENT
          key: metrics.async.MaxPartCountForPartition
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.MaxPartCountForPartition
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.asynchronous_metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: 32649020dc6d4f608b8928f2cccbbd11
              expression: 'min(/Clickhouse/metrics.async.MaxPartCountForPartition,600s)>={$CH_MAX_PARTS}'
              name: 'Clickhouse: Too many parts in a partition (>{$MAX_REPLICA_QUEUE})'
              priority: AVERAGE
              description: 'Some partition has too many unmerged parts'
        -
          uuid: a87ffcef4781420da653f875a6a6d83e
          name: 'Clickhouse AsyncMetrics - Memory'
          type: DEPENDENT
          key: metrics.async.Memory
          delay: '0'
          history: 31d
          units: B
          preprocessing:
            -
              type: JAVASCRIPT
              parameters:
                - |
                  var d = JSON.parse(value)
                  return d['jemalloc.retained'] + d['jemalloc.mapped'] + d['UncompressedCacheBytes'] + d['MarkCacheBytes']
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.asynchronous_metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: aade611de70a463d8687c0efbf244d91
          name: 'Clickhouse AsyncMetrics - ReplicasMaxQueueSize'
          type: DEPENDENT
          key: metrics.async.ReplicasMaxQueueSize
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.ReplicasMaxQueueSize
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.asynchronous_metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: 0725be34166546b5a5c56918815ff4fb
              expression: 'min(/Clickhouse/metrics.async.ReplicasMaxQueueSize,600s)>={$CH_MAX_REPLICA_QUEUE}'
              name: 'Clickhouse: Too large replcation queue (>{$MAX_REPLICA_QUEUE})'
              priority: AVERAGE
              description: 'Replication queue of some tables is too large'
        -
          uuid: 92f33ad63b5d46989505e2188a6f5ad7
          name: 'Clickhouse AsyncMetrics - Uptime'
          type: DEPENDENT
          key: metrics.async.Uptime
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.Uptime
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.asynchronous_metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: c2bc2d14d0d64f6292f49c5e5d59eab7
              expression: last(/Clickhouse/metrics.async.Uptime)<600
              name: 'Clickhouse: Service was restarted'
              priority: INFO
        -
          uuid: 214f36ae7a8843c2ad7f49315bae1ab7
          name: 'Clickhouse Metrics - HTTPConnection'
          type: DEPENDENT
          key: metrics.HTTPConnection
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.HTTPConnection
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 0b4bbbebcb32471296a6f042285ec7ed
          name: 'Clickhouse Metrics - InterserverConnection'
          type: DEPENDENT
          key: metrics.InterserverConnection
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.InterserverConnection
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 65b1bd3adcc5462aa965789324c307bc
          name: 'Clickhouse Metrics - LeaderReplica'
          type: DEPENDENT
          key: metrics.LeaderReplica
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.LeaderReplica
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 644e9ce3805a4a769da1749ccc7e4785
          name: 'Clickhouse Metrics - Merge'
          type: DEPENDENT
          key: metrics.Merge
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.Merge
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: f1a9e7a4e9b54ee298e74f592a6429e0
          name: 'Clickhouse Metrics - Query'
          type: DEPENDENT
          key: metrics.Query
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.Query
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: b3a04955291c45098cd0b6c1aebd6378
          name: 'Clickhouse Metrics - ReadonlyReplica'
          type: DEPENDENT
          key: metrics.ReadonlyReplica
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.ReadonlyReplica
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: 70490c81439f47b28ca6b98fa7448814
              expression: 'min(/Clickhouse/metrics.ReadonlyReplica,600s)>0'
              name: 'Clickhouse: Read only replicas detected'
              priority: AVERAGE
        -
          uuid: b91a6885ee2e42fa8b911f0ceeada8ed
          name: 'Clickhouse Metrics - TCPConnection'
          type: DEPENDENT
          key: metrics.TCPConnection
          delay: '0'
          history: 31d
          preprocessing:
            -
              type: JSONPATH
              parameters:
                - $.TCPConnection
          master_item:
            key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 88445d3fb9624b31b0379010b722390d
          name: 'Clickhouse Inactive Parts'
          key: 'web.page.get[{$CH_URL}/?query=SELECT%20COUNT%28%2A%29%20AS%20c%20FROM%20system.parts%20WHERE%20active%20%3D%200%20FORMAT%20JSON]'
          history: 31d
          description: |
            Number of inactive parts
            
            SELECT COUNT(*) AS c FROM system.parts WHERE active = 0 FORMAT JSON
          preprocessing:
            -
              type: JAVASCRIPT
              parameters:
                - 'return JSON.parse(value.substring(value.indexOf("{\n")).trim()).data[0].c;'
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: fd76dd9b78cf40a8913e7a99039e55c0
              expression: 'min(/Clickhouse/web.page.get[{$CH_URL}/?query=SELECT%20COUNT%28%2A%29%20AS%20c%20FROM%20system.parts%20WHERE%20active%20%3D%200%20FORMAT%20JSON],600s)>={$CH_MAX_INACTIVE_PARTS}'
              name: 'Clickhouse: Too many inactive parts (>{$MAX_INACTIVE_PARTS})'
              priority: HIGH
              description: 'Too many inactive parts which take up disk space'
        -
          uuid: b96e9519d16e44b0af0dd9d5d0ba97ea
          name: 'Clickhouse Events'
          key: 'web.page.get[{$CH_URL}/?query=SELECT%20event%2C%20value%20FROM%20system.events%20FORMAT%20JSON]'
          history: '0'
          trends: '0'
          value_type: TEXT
          description: 'SELECT event, value FROM system.events FORMAT JSON'
          preprocessing:
            -
              type: JAVASCRIPT
              parameters:
                - |
                  var d = JSON.parse(value.substring(value.indexOf("{\n")).trim()).data;
                  var r = {};
                  
                  d.forEach(function(v) {
                   r[v.event] = parseInt(v.value)
                  })
                  
                  return JSON.stringify(r);
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: 519cfaa3a1a041fdba5bc451a110450a
          name: 'Clickhouse AsyncMetrics'
          key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.asynchronous_metrics%20FORMAT%20JSON]'
          history: '0'
          trends: '0'
          value_type: TEXT
          description: 'SELECT metric, value FROM system.asynchronous_metrics FORMAT JSON'
          preprocessing:
            -
              type: JAVASCRIPT
              parameters:
                - |
                  var d = JSON.parse(value.substring(value.indexOf("{\n")).trim()).data;
                  var r = {};
                  
                  d.forEach(function(v) {
                   r[v.metric] = parseInt(v.value)
                  })
                  
                  return JSON.stringify(r);
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: a79e55cceb8043feaeb064a2aa5218e2
          name: 'Clickhouse Metrics'
          key: 'web.page.get[{$CH_URL}/?query=SELECT%20metric%2C%20value%20FROM%20system.metrics%20FORMAT%20JSON]'
          history: '0'
          trends: '0'
          value_type: TEXT
          description: 'SELECT metric, value FROM system.metrics FORMAT JSON'
          preprocessing:
            -
              type: JAVASCRIPT
              parameters:
                - |
                  var d = JSON.parse(value.substring(value.indexOf("{\n")).trim()).data;
                  var r = {};
                  
                  d.forEach(function(v) {
                   r[v.metric] = parseInt(v.value)
                  })
                  
                  return JSON.stringify(r);
                  
          tags:
            -
              tag: Application
              value: Clickhouse
        -
          uuid: a037d50194a446598574ca5b1fed80e7
          name: 'Clickhouse Ping'
          key: 'web.page.regexp[{$CH_URL}/ping,,,HTTP/1.1 (\d+),,\1]'
          history: 1d
          trends: 7d
          tags:
            -
              tag: Application
              value: Clickhouse
          triggers:
            -
              uuid: b995018a5a554e8c97a844615b10d2f2
              expression: 'nodata(/Clickhouse/web.page.regexp[{$CH_URL}/ping,,,HTTP/1.1 (\d+),,\1],600s)=1 or last(/Clickhouse/web.page.regexp[{$CH_URL}/ping,,,HTTP/1.1 (\d+),,\1])<>200'
              name: 'Clickhouse: Ping failed'
              priority: HIGH
      macros:
        -
          macro: '{$CH_MAX_INACTIVE_PARTS}'
          value: '2000'
        -
          macro: '{$CH_MAX_PARTS}'
          value: '50'
        -
          macro: '{$CH_MAX_REPLICA_QUEUE}'
          value: '5'
        -
          macro: '{$CH_URL}'
          value: 'http://127.0.0.1:8123'