Skip to content

Instantly share code, notes, and snippets.

@kolobaev

kolobaev/clickhouse dashboard: log_queries

Created April 13, 2020 14:30
Show Gist options
  • Save kolobaev/a50e9e4a163d8e20e39fac76d4030c35 to your computer and use it in GitHub Desktop.
Save kolobaev/a50e9e4a163d8e20e39fac76d4030c35 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
clickhouse dashboard: log_queries
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": "-- Grafana --",
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"editable": true,
"gnetId": null,
"graphTooltip": 0,
"id": 5709,
"iteration": 1586787849349,
"links": [],
"panels": [
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "$ch_server",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 5,
"w": 12,
"x": 0,
"y": 0
},
"hiddenSeries": false,
"id": 2,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": false,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"database": "system",
"dateColDataType": "event_date",
"dateLoading": false,
"dateTimeColDataType": "event_time",
"dateTimeType": "DATETIME",
"datetimeLoading": false,
"format": "time_series",
"formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
"intervalFactor": 1,
"query": "SELECT\n $timeSeries as t,\n count()\nFROM $table\nPREWHERE $timeFilter\nGROUP BY t\nORDER BY t",
"rawQuery": true,
"refId": "A",
"round": "0s",
"table": "query_log",
"tableLoading": false
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "Total queries on $ch_server",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"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": "$ch_server",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 5,
"w": 12,
"x": 12,
"y": 0
},
"hiddenSeries": false,
"id": 5,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": false,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"database": "system",
"dateColDataType": "event_date",
"dateLoading": false,
"dateTimeColDataType": "event_time",
"dateTimeType": "DATETIME",
"datetimeLoading": false,
"format": "time_series",
"formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
"intervalFactor": 1,
"query": "SELECT\n $timeSeries as t,\n quantile(0.99)(query_duration_ms)\nFROM $table\nPREWHERE $timeFilter\nWHERE query LIKE '%$DB%'\nGROUP BY t\nORDER BY t",
"rawQuery": true,
"refId": "A",
"round": "0s",
"table": "query_log",
"tableLoading": false
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "query_duration [%%99]",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"decimals": 1,
"format": "ms",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"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": "$ch_server",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 10,
"w": 24,
"x": 0,
"y": 5
},
"hiddenSeries": false,
"id": 3,
"legend": {
"alignAsTable": true,
"avg": false,
"current": true,
"max": true,
"min": false,
"rightSide": true,
"show": true,
"sideWidth": 800,
"sort": "max",
"sortDesc": true,
"total": false,
"values": true
},
"lines": true,
"linewidth": 1,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"database": "system",
"dateColDataType": "event_date",
"dateLoading": false,
"dateTimeColDataType": "event_time",
"dateTimeType": "DATETIME",
"datetimeLoading": false,
"format": "time_series",
"formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
"intervalFactor": 1,
"query": "SELECT\n t,\n groupArray((query, c)) as groupArr\nFROM \n(\n SELECT \n (intDiv(toUInt32(event_time), 60) * 60) * 1000 as t,\n query,\n count(*) c\n FROM $table\n PREWHERE\n $timeFilter\n WHERE \n query LIKE '%$DB%'\n GROUP BY\n t,\n query\n HAVING c > 10\n ORDER BY\n t,\n query\n)\nDESC\nGROUP BY t\nORDER BY t\nLIMIT 100",
"rawQuery": true,
"refId": "A",
"round": "0s",
"table": "query_log",
"tableLoading": false
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "query [count]",
"tooltip": {
"shared": true,
"sort": 2,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": true,
"dashLength": 10,
"dashes": false,
"datasource": "$ch_server",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 13,
"w": 24,
"x": 0,
"y": 15
},
"hiddenSeries": false,
"id": 4,
"legend": {
"alignAsTable": true,
"avg": false,
"current": true,
"max": true,
"min": false,
"rightSide": true,
"show": true,
"sort": "max",
"sortDesc": true,
"total": false,
"values": true
},
"lines": false,
"linewidth": 1,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"database": "system",
"dateColDataType": "event_date",
"dateLoading": false,
"dateTimeColDataType": "event_time",
"dateTimeType": "DATETIME",
"datetimeLoading": false,
"format": "time_series",
"formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
"intervalFactor": 1,
"query": "SELECT\n t,\n groupArray((query, c)) as groupArr\nFROM \n(\n SELECT \n (intDiv(toUInt32(event_time), 60) * 60) * 1000 as t,\n query,\n max(query_duration_ms) c\n FROM $table\n PREWHERE\n $timeFilter\n WHERE\n query LIKE '%$DB%'\n GROUP BY\n t,\n query\n HAVING c > 1000\n ORDER BY\n t,\n query\n)\nDESC\nGROUP BY t\nORDER BY t\nLIMIT 100",
"rawQuery": true,
"refId": "A",
"round": "0s",
"table": "query_log",
"tableLoading": false
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "query_duration > 1000 ms",
"tooltip": {
"shared": true,
"sort": 2,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"decimals": 0,
"format": "ms",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": true,
"dashLength": 10,
"dashes": false,
"datasource": "$ch_server",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 13,
"w": 24,
"x": 0,
"y": 28
},
"hiddenSeries": false,
"id": 6,
"legend": {
"alignAsTable": true,
"avg": false,
"current": true,
"max": true,
"min": false,
"rightSide": true,
"show": true,
"sort": "max",
"sortDesc": true,
"total": false,
"values": true
},
"lines": false,
"linewidth": 1,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"database": "system",
"dateColDataType": "event_date",
"dateLoading": false,
"dateTimeColDataType": "event_time",
"dateTimeType": "DATETIME",
"datetimeLoading": false,
"format": "time_series",
"formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t",
"intervalFactor": 1,
"query": "SELECT\n t,\n groupArray((query, c)) as groupArr\nFROM \n(\n SELECT \n (intDiv(toUInt32(event_time), 60) * 60) * 1000 as t,\n query,\n max(memory_usage) c\n FROM $table\n PREWHERE\n $timeFilter\n GROUP BY\n t,\n query\n HAVING c > 104857600\n ORDER BY\n t,\n query\n)\nDESC\nGROUP BY t\nORDER BY t\n",
"rawQuery": true,
"refId": "A",
"round": "0s",
"table": "query_log",
"tableLoading": false
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "query: memory_usage > 100 Mb",
"tooltip": {
"shared": true,
"sort": 2,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"decimals": 0,
"format": "decbytes",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
}
],
"refresh": "1m",
"schemaVersion": 21,
"style": "dark",
"tags": [],
"templating": {
"list": [
{
"current": {
"tags": [],
"text": "ch-archive01",
"value": "ch-archive01"
},
"hide": 0,
"includeAll": false,
"label": "server",
"multi": false,
"name": "ch_server",
"options": [],
"query": "vertamedia-clickhouse-datasource",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"type": "datasource"
},
{
"allValue": "%",
"current": {
"tags": [],
"text": "nginx",
"value": "nginx"
},
"datasource": "$ch_server",
"definition": "select name from system.databases",
"hide": 0,
"includeAll": false,
"label": "DB",
"multi": false,
"name": "DB",
"options": [],
"query": "select name from system.databases",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tags": [],
"tagsQuery": "",
"type": "query",
"useTags": false
}
]
},
"time": {
"from": "now-1h",
"to": "now"
},
"timepicker": {
"refresh_intervals": [
"5s",
"10s",
"30s",
"1m",
"5m",
"15m",
"30m",
"1h",
"2h",
"1d"
]
},
"timezone": "",
"title": "ClickHouse - query_log",
"uid": "6y2lsFAZz",
"version": 33
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment