Hydrolix - Table Health
Monitoring partition count and table health for Hydrolix
Monitoring Hydrolix table health
The following dashboards are used for establishing the health and size of the Tables being generated. A key component to this is the number of Partitions being generated and being used by the query engine to satisfy queries.

The following reports pull partition information from the Catalog (a DB that is used to manage partitions within the Hydrolix infrastructure) and Prometheus.
{
  "__inputs": [
    {
      "name": "DS_HDX-MONITORING-QUERY",
      "label": "hdx-monitoring-query",
      "description": "Hydrolix SQL Engine",
      "type": "datasource",
      "pluginId": "vertamedia-clickhouse-datasource",
      "pluginName": "ClickHouse"
    },
    {
      "name": "DS_HDX-MONITORING-PROMETHEUS",
      "label": "hdx-monitoring-prometheus",
      "description": "Hydrolix Prometheus exporter",
      "type": "datasource",
      "pluginId": "prometheus",
      "pluginName": "Prometheus"
    }
  ],
  "__requires": [
    {
      "type": "grafana",
      "id": "grafana",
      "name": "Grafana",
      "version": "7.5.6"
    },
    {
      "type": "panel",
      "id": "graph",
      "name": "Graph",
      "version": ""
    },
    {
      "type": "datasource",
      "id": "prometheus",
      "name": "Prometheus",
      "version": "1.0.0"
    },
    {
      "type": "panel",
      "id": "table",
      "name": "Table",
      "version": ""
    },
    {
      "type": "panel",
      "id": "text",
      "name": "Text",
      "version": ""
    },
    {
      "type": "datasource",
      "id": "vertamedia-clickhouse-datasource",
      "name": "ClickHouse",
      "version": "2.2.3"
    }
  ],
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": 14442,
  "graphTooltip": 0,
  "id": null,
  "iteration": 1621237671870,
  "links": [],
  "panels": [
    {
      "datasource": null,
      "description": "",
      "fieldConfig": {
        "defaults": {},
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "id": 57,
      "options": {
        "content": "# Table \n\nThe following dashboards are used for establishing the health \nand size of the Tables being generated. A key component to this is\nthe number of Partitions being generated and being used by the query engine\nto satisfy queries.\n\nThe following reports pull partition information from the Catalog (a DB that is used to \nmanage partitions within the Hydrolix infrastructure) and Prometheus.\n\nNote Table Overview and Detail should be used with 7 Day time windows to see how \npartition counts are distributed.",
        "mode": "markdown"
      },
      "pluginVersion": "7.5.6",
      "timeFrom": null,
      "timeShift": null,
      "title": "Description",
      "type": "text"
    },
    {
      "collapsed": true,
      "datasource": "${DS_HDX-MONITORING-QUERY}",
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 8
      },
      "id": 55,
      "panels": [
        {
          "aliasColors": {},
          "bars": true,
          "dashLength": 10,
          "dashes": false,
          "datasource": "${DS_HDX-MONITORING-QUERY}",
          "description": "The partition count that is being used by Query to retrieve data. This does not include partitions that are scheduled for deletion due to the ageing or merge services.",
          "fieldConfig": {
            "defaults": {
              "unit": "short"
            },
            "overrides": []
          },
          "fill": 1,
          "fillGradient": 0,
          "gridPos": {
            "h": 14,
            "w": 12,
            "x": 0,
            "y": 9
          },
          "hiddenSeries": false,
          "id": 49,
          "interval": null,
          "legend": {
            "alignAsTable": false,
            "avg": false,
            "current": false,
            "hideEmpty": false,
            "hideZero": false,
            "max": false,
            "min": false,
            "rightSide": false,
            "show": false,
            "total": false,
            "values": false
          },
          "lines": false,
          "linewidth": 2,
          "maxDataPoints": null,
          "nullPointMode": "null",
          "options": {
            "alertThreshold": false
          },
          "percentage": false,
          "pluginVersion": "7.5.5",
          "pointradius": 2,
          "points": false,
          "renderer": "flot",
          "seriesOverrides": [],
          "spaceLength": 10,
          "stack": false,
          "steppedLine": false,
          "targets": [
            {
              "database": "autoingest",
              "dateColDataType": "",
              "dateLoading": false,
              "dateTimeColDataType": "min_timestamp",
              "dateTimeType": "DATETIME64",
              "datetimeLoading": false,
              "extrapolate": true,
              "format": "time_series",
              "formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
              "hide": false,
              "interval": "",
              "intervalFactor": 1,
              "query": "SELECT\n    toDate(min_timestamp) as Time,\n    count() as Partition_Count\nFROM $projectname.`$tablename#.catalog`\n\nWHERE min_timestamp >=subtractDays(now(),7) \nGROUP BY Time\n\nORDER BY Time\n",
              "rawQuery": "SELECT\n    toDate(min_timestamp) as Time,\n    count() as Partition_Count\nFROM stream.`stream#.catalog`\n\nWHERE min_timestamp >=subtractDays(now(),7) \nGROUP BY Time\n\nORDER BY Time",
              "refId": "A",
              "round": "0s",
              "skip_comments": false,
              "table": "autoingest#.catalog",
              "tableLoading": false
            }
          ],
          "thresholds": [],
          "timeFrom": null,
          "timeRegions": [],
          "timeShift": null,
          "title": "Active Partition Count For $projectname.$tablename",
          "tooltip": {
            "shared": true,
            "sort": 0,
            "value_type": "individual"
          },
          "type": "graph",
          "xaxis": {
            "buckets": null,
            "mode": "time",
            "name": null,
            "show": true,
            "values": []
          },
          "yaxes": [
            {
              "$$hashKey": "object:230",
              "format": "short",
              "label": "",
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            },
            {
              "$$hashKey": "object:231",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            }
          ],
          "yaxis": {
            "align": false,
            "alignLevel": null
          }
        }
      ],
      "title": "Table Overview",
      "type": "row"
    },
    {
      "collapsed": true,
      "datasource": "${DS_HDX-MONITORING-QUERY}",
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 9
      },
      "id": 53,
      "panels": [
        {
          "datasource": "${DS_HDX-MONITORING-QUERY}",
          "description": "Provides detailed analysis of the size and count of the partitions for the time period chosen. \n\nNote -  The minimum time window is 1 Hour due to the query used.\n- This table may take some time to fill due to the interpretation required. \n- It is strongly suggested this is used sparingly due to the load on the catalog server.",
          "fieldConfig": {
            "defaults": {
              "color": {
                "mode": "thresholds"
              },
              "custom": {
                "align": null,
                "filterable": false
              },
              "mappings": [],
              "thresholds": {
                "mode": "absolute",
                "steps": [
                  {
                    "color": "green",
                    "value": null
                  },
                  {
                    "color": "red",
                    "value": 80
                  }
                ]
              }
            },
            "overrides": [
              {
                "matcher": {
                  "id": "byName",
                  "options": "t"
                },
                "properties": [
                  {
                    "id": "unit",
                    "value": "none"
                  }
                ]
              },
              {
                "matcher": {
                  "id": "byName",
                  "options": "Catalog Avg Size"
                },
                "properties": [
                  {
                    "id": "unit",
                    "value": "decbytes"
                  }
                ]
              },
              {
                "matcher": {
                  "id": "byName",
                  "options": "time"
                },
                "properties": [
                  {
                    "id": "custom.width",
                    "value": null
                  }
                ]
              }
            ]
          },
          "gridPos": {
            "h": 9,
            "w": 14,
            "x": 0,
            "y": 10
          },
          "id": 13,
          "options": {
            "showHeader": true,
            "sortBy": []
          },
          "pluginVersion": "7.5.5",
          "targets": [
            {
              "database": "autoingest",
              "dateColDataType": "",
              "dateLoading": false,
              "dateTimeColDataType": "min_timestamp",
              "dateTimeType": "DATETIME64",
              "datetimeLoading": false,
              "extrapolate": false,
              "format": "table",
              "formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
              "hide": false,
              "intervalFactor": 1,
              "query": "SELECT\n    toDate(min_timestamp) as time,\n    count() as count,\n    round(avg((manifest_size + data_size + index_size) / 1024 ), 2) as \"Avg (KB)\",\n    round(min((manifest_size + data_size + index_size) / 1024 ), 2) as \"Min (KB)\",\n    quantileExact(.25)((manifest_size + data_size + index_size) / 1024) as \"25% (KB)\",\n    round(medianExact((manifest_size + data_size + index_size) / 1024 ), 2) as \"Median Size (KB)\",\n    quantileExact(.75)((manifest_size + data_size + index_size) / 1024 ) as \"75% (KB)\",\n    round(max((manifest_size + data_size + index_size) / 1024 ), 2) as \"Max (KB)\"\nFROM $projectname.\"$tablename#.catalog\"\n\nWHERE $timeFilter\n\nGROUP BY time\n\nORDER BY time\n",
              "rawQuery": "SELECT\n    toDate(min_timestamp) as time,\n    count() as count,\n    round(avg((manifest_size + data_size + index_size) / 1024 ), 2) as \"Avg (KB)\",\n    round(min((manifest_size + data_size + index_size) / 1024 ), 2) as \"Min (KB)\",\n    quantileExact(.25)((manifest_size + data_size + index_size) / 1024) as \"25% (KB)\",\n    round(medianExact((manifest_size + data_size + index_size) / 1024 ), 2) as \"Median Size (KB)\",\n    quantileExact(.75)((manifest_size + data_size + index_size) / 1024 ) as \"75% (KB)\",\n    round(max((manifest_size + data_size + index_size) / 1024 ), 2) as \"Max (KB)\"\nFROM stream.\"stream#.catalog\"\n\nWHERE toDateTime64(min_timestamp, 3) >= toDateTime64(1620231946, 3)\n\nGROUP BY time\n\nORDER BY time",
              "refId": "A",
              "round": "0s",
              "skip_comments": true,
              "table": "autoingest#.catalog",
              "tableLoading": false
            }
          ],
          "timeFrom": null,
          "timeShift": null,
          "title": "Detailed Partition Analysis for $projectname.$tablename",
          "type": "table"
        }
      ],
      "title": "Table Detailed Analysis",
      "type": "row"
    },
    {
      "collapsed": true,
      "datasource": null,
      "gridPos": {
        "h": 1,
        "w": 24,
        "x": 0,
        "y": 10
      },
      "id": 51,
      "panels": [
        {
          "aliasColors": {},
          "bars": false,
          "dashLength": 10,
          "dashes": false,
          "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
          "description": "Total number of partitions created by the Hydrolix deployment by time",
          "fieldConfig": {
            "defaults": {},
            "overrides": []
          },
          "fill": 1,
          "fillGradient": 0,
          "gridPos": {
            "h": 10,
            "w": 20,
            "x": 0,
            "y": 11
          },
          "hiddenSeries": false,
          "id": 23,
          "interval": "30s",
          "legend": {
            "avg": false,
            "current": false,
            "max": false,
            "min": false,
            "show": true,
            "total": false,
            "values": false
          },
          "lines": true,
          "linewidth": 1,
          "nullPointMode": "null",
          "options": {
            "alertThreshold": true
          },
          "percentage": false,
          "pluginVersion": "7.5.5",
          "pointradius": 2,
          "points": false,
          "renderer": "flot",
          "seriesOverrides": [],
          "spaceLength": 10,
          "stack": false,
          "steppedLine": false,
          "targets": [
            {
              "exemplar": true,
              "expr": "avg by (service, instance, table_id, project_id) (increase(partitions_created{service=~\"^$Service\",project_id=~\"^$projectuuid\",table_id=~\"^$tableuuid\"}[$__rate_interval]))",
              "interval": "",
              "legendFormat": "{{service}}",
              "refId": "A"
            }
          ],
          "thresholds": [],
          "timeFrom": null,
          "timeRegions": [],
          "timeShift": null,
          "title": "Partitions Created by Service",
          "tooltip": {
            "shared": true,
            "sort": 0,
            "value_type": "individual"
          },
          "type": "graph",
          "xaxis": {
            "buckets": null,
            "mode": "time",
            "name": null,
            "show": true,
            "values": []
          },
          "yaxes": [
            {
              "$$hashKey": "object:172",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            },
            {
              "$$hashKey": "object:173",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            }
          ],
          "yaxis": {
            "align": false,
            "alignLevel": null
          }
        },
        {
          "aliasColors": {},
          "bars": false,
          "dashLength": 10,
          "dashes": false,
          "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
          "description": "Total Catalog queries by service to access the Catalog",
          "fieldConfig": {
            "defaults": {},
            "overrides": []
          },
          "fill": 1,
          "fillGradient": 0,
          "gridPos": {
            "h": 7,
            "w": 10,
            "x": 0,
            "y": 21
          },
          "hiddenSeries": false,
          "id": 59,
          "interval": "30s",
          "legend": {
            "avg": false,
            "current": false,
            "max": false,
            "min": false,
            "show": true,
            "total": false,
            "values": false
          },
          "lines": true,
          "linewidth": 1,
          "nullPointMode": "null",
          "options": {
            "alertThreshold": true
          },
          "percentage": false,
          "pluginVersion": "7.5.5",
          "pointradius": 2,
          "points": false,
          "renderer": "flot",
          "seriesOverrides": [],
          "spaceLength": 10,
          "stack": false,
          "steppedLine": false,
          "targets": [
            {
              "exemplar": true,
              "expr": "sum (increase(query_count{db=\"catalog\", service=~\"^$Service\"}[$__rate_interval])) by (service)",
              "interval": "",
              "legendFormat": "{{service}}",
              "refId": "A"
            }
          ],
          "thresholds": [],
          "timeFrom": null,
          "timeRegions": [],
          "timeShift": null,
          "title": "Catalog  Queries/s by Service",
          "tooltip": {
            "shared": true,
            "sort": 0,
            "value_type": "individual"
          },
          "type": "graph",
          "xaxis": {
            "buckets": null,
            "mode": "time",
            "name": null,
            "show": true,
            "values": []
          },
          "yaxes": [
            {
              "$$hashKey": "object:172",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            },
            {
              "$$hashKey": "object:173",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            }
          ],
          "yaxis": {
            "align": false,
            "alignLevel": null
          }
        },
        {
          "aliasColors": {},
          "bars": false,
          "dashLength": 10,
          "dashes": false,
          "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
          "description": "Average number of failures by a service to access the Catalog",
          "fieldConfig": {
            "defaults": {},
            "overrides": []
          },
          "fill": 1,
          "fillGradient": 0,
          "gridPos": {
            "h": 7,
            "w": 10,
            "x": 10,
            "y": 21
          },
          "hiddenSeries": false,
          "id": 58,
          "interval": "30s",
          "legend": {
            "avg": false,
            "current": false,
            "hideEmpty": true,
            "hideZero": true,
            "max": false,
            "min": false,
            "show": true,
            "total": false,
            "values": false
          },
          "lines": true,
          "linewidth": 1,
          "nullPointMode": "null",
          "options": {
            "alertThreshold": true
          },
          "percentage": false,
          "pluginVersion": "7.5.5",
          "pointradius": 2,
          "points": false,
          "renderer": "flot",
          "seriesOverrides": [],
          "spaceLength": 10,
          "stack": false,
          "steppedLine": false,
          "targets": [
            {
              "exemplar": true,
              "expr": "avg by (service, instance)(increase(query_failure{db=\"catalog\",service=~\"^$Service\"}[$__rate_interval]))",
              "interval": "",
              "legendFormat": "{{service}} {{instance}}",
              "refId": "A"
            }
          ],
          "thresholds": [],
          "timeFrom": null,
          "timeRegions": [],
          "timeShift": null,
          "title": "Catalog Failure rate by Service",
          "tooltip": {
            "shared": true,
            "sort": 0,
            "value_type": "individual"
          },
          "type": "graph",
          "xaxis": {
            "buckets": null,
            "mode": "time",
            "name": null,
            "show": true,
            "values": []
          },
          "yaxes": [
            {
              "$$hashKey": "object:172",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            },
            {
              "$$hashKey": "object:173",
              "format": "short",
              "label": null,
              "logBase": 1,
              "max": null,
              "min": null,
              "show": true
            }
          ],
          "yaxis": {
            "align": false,
            "alignLevel": null
          }
        }
      ],
      "title": "System",
      "type": "row"
    }
  ],
  "refresh": false,
  "schemaVersion": 27,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": [
      {
        "allValue": "",
        "current": {},
        "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
        "definition": "label_values(up{service=~\"stream-peer|kafka-peer|batch-peer|merge-peer\"}, service)",
        "description": "Service",
        "error": null,
        "hide": 0,
        "includeAll": true,
        "label": "Service",
        "multi": true,
        "name": "Service",
        "options": [],
        "query": {
          "query": "label_values(up{service=~\"stream-peer|kafka-peer|batch-peer|merge-peer\"}, service)",
          "refId": "StandardVariableQuery"
        },
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {},
        "datasource": "${DS_HDX-MONITORING-QUERY}",
        "definition": "select distinct(database) from system.tables where database NOT IN ('system', 'hdx')",
        "description": null,
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": "Project",
        "multi": false,
        "name": "projectname",
        "options": [],
        "query": "select distinct(database) from system.tables where database NOT IN ('system', 'hdx')",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {},
        "datasource": "${DS_HDX-MONITORING-QUERY}",
        "definition": "select name from system.tables where name NOT Like '%#%' AND database IN '$projectname'",
        "description": null,
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": "Table",
        "multi": false,
        "name": "tablename",
        "options": [],
        "query": "select name from system.tables where name NOT Like '%#%' AND database IN '$projectname'",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {},
        "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
        "definition": "label_values(partitions_created{service=~\"$Service\"}, project_id)",
        "description": "Project UUID",
        "error": null,
        "hide": 0,
        "includeAll": true,
        "label": "Project UUID",
        "multi": true,
        "name": "projectuuid",
        "options": [],
        "query": {
          "query": "label_values(partitions_created{service=~\"$Service\"}, project_id)",
          "refId": "StandardVariableQuery"
        },
        "refresh": 2,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {},
        "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
        "definition": "label_values(partitions_created{service=~\"$Service\",project_id=~\"$projectuuid\"}, table_id)",
        "description": "Table UUID of Table",
        "error": null,
        "hide": 0,
        "includeAll": true,
        "label": "Table UUID",
        "multi": true,
        "name": "tableuuid",
        "options": [],
        "query": {
          "query": "label_values(partitions_created{service=~\"$Service\",project_id=~\"$projectuuid\"}, table_id)",
          "refId": "StandardVariableQuery"
        },
        "refresh": 2,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      },
      {
        "allValue": null,
        "current": {},
        "datasource": "${DS_HDX-MONITORING-PROMETHEUS}",
        "definition": "label_values(up{service=~\"$Service\"}, instance)",
        "description": "Instance",
        "error": null,
        "hide": 0,
        "includeAll": true,
        "label": "Instance",
        "multi": true,
        "name": "Instance",
        "options": [],
        "query": {
          "query": "label_values(up{service=~\"$Service\"}, instance)",
          "refId": "StandardVariableQuery"
        },
        "refresh": 2,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "tagValuesQuery": "",
        "tags": [],
        "tagsQuery": "",
        "type": "query",
        "useTags": false
      }
    ]
  },
  "time": {
    "from": "now-30m",
    "to": "now"
  },
  "timepicker": {
    "refresh_intervals": [
      "5s",
      "10s",
      "30s",
      "1m",
      "5m",
      "15m",
      "30m",
      "1h",
      "2h",
      "1d"
    ]
  },
  "timezone": "",
  "title": "Hydrolix - Table Health",
  "uid": "-0U0qWjGz",
  "version": 1,
  "description": "Monitoring partition count and table health for Hydrolix"
}