Skip to content

Instantly share code, notes, and snippets.

@FranckPachot
Created November 8, 2022 13:30
Show Gist options
  • Save FranckPachot/405ec017332841ac37061ca9dd4b97ce to your computer and use it in GitHub Desktop.
Save FranckPachot/405ec017332841ac37061ca9dd4b97ce to your computer and use it in GitHub Desktop.
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": 16,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 2,
"w": 12,
"x": 0,
"y": 0
},
"id": 1,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": false
},
"pluginVersion": "9.2.3-997144b7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"format": "table",
"group": [],
"key": "Q-32168074-cc81-49df-a9f3-563e7f83d5a5-0",
"metricColumn": "none",
"rawQuery": true,
"rawSql": "create table if not exists demo (id bigint primary key, value text);\nexplain (analyse, costs off) insert into demo select generate_series(1,42),rpad('x',100*1024,'x');",
"refId": "A",
"select": [
[
{
"params": [
"rate"
],
"type": "column"
}
]
],
"table": "ybwr_report",
"timeColumn": "ts",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Insert",
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 2,
"w": 12,
"x": 12,
"y": 0
},
"id": 3,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": false
},
"pluginVersion": "9.2.3-997144b7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"format": "table",
"group": [],
"key": "Q-32168074-cc81-49df-a9f3-563e7f83d5a5-0",
"metricColumn": "none",
"rawQuery": true,
"rawSql": "explain (analyse, costs off) delete from demo;",
"refId": "A",
"select": [
[
{
"params": [
"rate"
],
"type": "column"
}
]
],
"table": "ybwr_report",
"timeColumn": "ts",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Delete",
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 20,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "auto",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "normal"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "bytes"
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 24,
"x": 0,
"y": 2
},
"id": 2,
"options": {
"legend": {
"calcs": [
"lastNotNull"
],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.2.3-997144b7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"format": "time_series",
"group": [],
"key": "Q-32168074-cc81-49df-a9f3-563e7f83d5a5-0",
"metricColumn": "none",
"rawQuery": true,
"rawSql": "create table if not exists metric_table_size(time_column timestamp, size bigint, name text);\ndelete from metric_table_size where not($__timeFilter(time_column));\ninsert into metric_table_size\nselect now() at time zone 'UTC',pg_table_size(c.oid),format('%I.%I',nspname,relname) \nfrom pg_class c natural join (select oid relnamespace, nspname from pg_namespace) as ns \nwhere relkind='r' and relowner!=10\n;\nSELECT $__time(time_column), name, size FROM metric_table_size \nWHERE name = 'public.demo' and $__timeFilter(time_column)\norder by 1,2,3\n;\n\n",
"refId": "A",
"select": [
[
{
"params": [
"rate"
],
"type": "column"
}
]
],
"table": "ybwr_report",
"timeColumn": "ts",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Table Size",
"type": "timeseries"
},
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "points",
"fillOpacity": 20,
"gradientMode": "opacity",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineStyle": {
"dash": [
0,
10
],
"fill": "dot"
},
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "always",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "ms"
},
"overrides": []
},
"gridPos": {
"h": 14,
"w": 24,
"x": 0,
"y": 10
},
"id": 4,
"options": {
"legend": {
"calcs": [
"lastNotNull"
],
"displayMode": "table",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.2.3-997144b7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "qEe8N2_Gk"
},
"format": "time_series",
"group": [],
"key": "Q-32168074-cc81-49df-a9f3-563e7f83d5a5-0",
"metricColumn": "none",
"rawQuery": true,
"rawSql": "create table if not exists metric_query(time_column timestamp, ms float, rows int, name text);\ndelete from metric_query where not($__timeFilter(time_column));\n\ndo $$ \n declare\n plan json; \n query record;\n --query text := $sql$ select * from demo order by id fetch first 10 rows only for update skip locked $sql$;\n begin\n for query in (select value from json_array_elements_text('${queries:json}'::json))\n loop\n execute format('explain (format json, analyze) %s',query.value) into plan;\n insert into metric_query values(\n now() at time zone 'UTC',\n (plan->0->'Plan'->>'Actual Total Time')::float,\n (plan->0->'Plan'->>'Actual Rows')::int,\n query.value\n );\n end loop; end; $$;\n\nSELECT $__time(time_column), format('%s (%s rows)',name,rows) as name, ms FROM metric_query \nWHERE $__timeFilter(time_column)\norder by 1,2,3\n;",
"refId": "A",
"select": [
[
{
"params": [
"rate"
],
"type": "column"
}
]
],
"table": "ybwr_report",
"timeColumn": "ts",
"timeColumnType": "timestamp",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Query time",
"type": "timeseries"
}
],
"refresh": "5s",
"schemaVersion": 37,
"style": "dark",
"tags": [],
"templating": {
"list": [
{
"current": {
"selected": false,
"text": "None",
"value": ""
},
"definition": "drop table demo; drop table metric_table_size; drop table metric_query;",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "query0",
"options": [],
"query": "drop table demo; drop table metric_table_size; drop table metric_query;",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
},
{
"current": {
"selected": true,
"text": [
"select * from demo where id=1",
"select * from demo order by id fetch first 5 rows only",
"select * from demo order by id fetch first 5 rows only for update",
"select * from demo order by id fetch first 5 rows only for update skip locked"
],
"value": [
"select * from demo where id=1",
"select * from demo order by id fetch first 5 rows only",
"select * from demo order by id fetch first 5 rows only for update",
"select * from demo order by id fetch first 5 rows only for update skip locked"
]
},
"description": "Queries to run and measure",
"hide": 0,
"includeAll": false,
"multi": true,
"name": "queries",
"options": [
{
"selected": true,
"text": "select * from demo where id=1",
"value": "select * from demo where id=1"
},
{
"selected": true,
"text": "select * from demo order by id fetch first 5 rows only",
"value": "select * from demo order by id fetch first 5 rows only"
},
{
"selected": true,
"text": "select * from demo order by id fetch first 5 rows only for update",
"value": "select * from demo order by id fetch first 5 rows only for update"
},
{
"selected": true,
"text": "select * from demo order by id fetch first 5 rows only for update skip locked",
"value": "select * from demo order by id fetch first 5 rows only for update skip locked"
}
],
"query": "select * from demo where id=1, select * from demo order by id fetch first 5 rows only, select * from demo order by id fetch first 5 rows only for update, select * from demo order by id fetch first 5 rows only for update skip locked",
"queryValue": "",
"skipUrlSync": false,
"type": "custom"
}
]
},
"time": {
"from": "now-1h",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": Tombstone Blog",
"uid": "uPPNp2v4k",
"version": 7,
"weekStart": ""
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment