-
Notifications
You must be signed in to change notification settings - Fork 15
Activie sessions realtime ‐ session workload
김은호 프로 edited this page May 2, 2024
·
1 revision
session workload
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "datasource",
"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": 144,
"links": [],
"liveNow": false,
"panels": [
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "dark-purple",
"mode": "fixed"
},
"displayName": "Buffer Hit Ratio",
"links": [
{
"title": "[$clustername] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
}
],
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "super-light-purple",
"value": null
}
]
}
},
"overrides": []
},
"id": 6,
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"text": {
"titleSize": 20,
"valueSize": 20
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"targets": [
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"groupBy": [
{
"params": [
"$__interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"measurement": "db_health",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"buffer_hitratio\") FROM \"db_health\" WHERE (\"dbname\" =~ /^$clustername$/) \nAND $timeFilter\n GROUP BY time($__interval) fill(null)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffer_hitratio"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$clustername$/"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "dark-yellow",
"mode": "fixed"
},
"links": [
{
"title": "[$clustername] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
}
],
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "super-light-green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 3,
"w": 6,
"x": 6,
"y": 0
},
"id": 8,
"interval": "5m",
"links": [],
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"text": {
"titleSize": 20,
"valueSize": 20
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"repeatDirection": "h",
"targets": [
{
"alias": "Block Read",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"measurement": "db_stats",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT non_negative_derivative(mean(\"blks_read\"), $agg_interval) FROM \"db_stats\" WHERE \"dbname\" =~ /^$dbname$/ AND \"cluster_name\" =~ /^$clustername$/ AND $timeFilter GROUP BY time($agg_interval), cluster fill(none)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"tup_deleted"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "dark-yellow",
"mode": "fixed"
},
"links": [
{
"title": "[$clustername] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
}
],
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "super-light-green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 3,
"w": 6,
"x": 12,
"y": 0
},
"id": 22,
"interval": "5m",
"links": [],
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"text": {
"titleSize": 20,
"valueSize": 20
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"repeatDirection": "h",
"targets": [
{
"alias": "WAL rate",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"measurement": "db_stats",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT derivative(mean(\"xlog_location_b\"), 1s) FROM \"wal\" WHERE \n\"dbname\" =~ /^$clustername$/ \n-- AND \"cluster_name\" =~ /^$clustername$/ \nAND $timeFilter GROUP BY time($agg_interval), cluster_name fill(none)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"tup_deleted"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
}
],
"type": "stat"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "<p><Click> an indicator to go to <font color=\"#00ccff\"> statements statistics</font> in the current time range. </p>\n",
"fieldConfig": {
"defaults": {
"color": {
"fixedColor": "#9ec41f",
"mode": "fixed"
},
"links": [
{
"title": "[${clustername}] Go to statements statistics.",
"url": "/d/experdb-statements-statistics/statements-statistics?orgId=1&refresh=1m&${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${__url_time_range}"
}
],
"mappings": [
{
"options": {
"match": "null",
"result": {
"index": 0,
"text": "No calls Data"
}
},
"type": "special"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 3,
"w": 6,
"x": 18,
"y": 0
},
"id": 24,
"interval": "",
"links": [],
"options": {
"colorMode": "background",
"graphMode": "area",
"justifyMode": "auto",
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"text": {
"titleSize": 20,
"valueSize": 20
},
"textMode": "auto"
},
"pluginVersion": "9.4.7",
"targets": [
{
"alias": "Calls",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "cpu_load",
"orderByTime": "ASC",
"policy": "default",
"query": "/*\nSELECT non_negative_derivative(mean(\"calls\"), 1h) FROM \"stat_statements\" WHERE \"cluster_name\" =~ /^$clustername$/ AND \"dbname\" =~ /^$dbname$/ AND $timeFilter AND calls >= 0 GROUP BY time($agg_interval) fill(none)\n*/\n\n\nSELECT sum(\"non_negative_derivative\") as \"sum_top_query\"\nFROM (\n SELECT top(\"non_negative_derivative\", \"queryid\", \"query\", 10000) as \"non_negative_derivative\"\n FROM (\n SELECT\n non_negative_derivative(mean(\"calls\"), 180s) as \"non_negative_derivative\"\n FROM \"stat_statements\"\n WHERE\n $timeFilter\n AND \"cluster_name\" =~ /^$clustername$/\n AND \"dbname\" =~ /^$dbname$/\n AND \"query\" !~ /extract.*::int8\\s+as\\s+epoch_ns\\s*,/\n AND \"query\" !~ /\\/\\*\\s*experdb_generated\\s*\\*\\//\n GROUP BY time(5m), \"queryid\", \"query\" fill(null)\n )\n WHERE \"non_negative_derivative\" > 0\n) GROUP BY time(5m) fill(null)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"load_5min"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": []
}
],
"type": "stat"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "<p>Clicking on the point will take you to the [$clustername]'s <font color=\"#00ccff\">single cluster</font> with the same time range</p>",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 10,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "never",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"decimals": 1,
"links": [
{
"title": "[${clustername}] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
}
],
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "percent"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "load_5"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "#BA43A9",
"mode": "fixed"
}
}
]
},
{
"matcher": {
"id": "byName",
"options": "Query Runtime"
},
"properties": [
{
"id": "unit",
"value": "ms"
},
{
"id": "decimals",
"value": 2
}
]
}
]
},
"gridPos": {
"h": 5,
"w": 12,
"x": 0,
"y": 3
},
"id": 15,
"interval": "",
"links": [],
"maxDataPoints": 100,
"options": {
"legend": {
"calcs": [
"max",
"mean"
],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "multi",
"sort": "none"
}
},
"pluginVersion": "9.0.7",
"targets": [
{
"alias": "$clustername",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"hide": false,
"query": "SELECT mean(cpu_utilization) as load_average \n FROM \"psutil_cpu\" \nWHERE\n $timeFilter\n AND \"cluster_name\" =~ /^$clustername$/ \n GROUP BY time($agg_interval), dbname",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series"
}
],
"title": "CPU",
"type": "timeseries"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "<p>Clicking on the point will take you to the [$clustername]'s <font color=\"#00ccff\">single cluster</font> with the same time range.</p>\n",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 10,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 2,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "never",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"links": [
{
"title": "[$clustername] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
},
{
"title": "[$clustername] Go to Table overview.",
"url": "/d/experdb-table-overview/table-overview?orgId=1&${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${top_queries:queryparam}&${__url_time_range}"
}
],
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "none"
},
"overrides": []
},
"gridPos": {
"h": 5,
"w": 12,
"x": 12,
"y": 3
},
"id": 19,
"interval": "",
"links": [],
"options": {
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "multi",
"sort": "none"
}
},
"pluginVersion": "9.0.7",
"targets": [
{
"alias": "$col",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"hide": false,
"measurement": "cpu_load",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT \n\tnon_negative_derivative(mean(\"seq_scan\"), 1m) AS seq_scan, \n\tnon_negative_derivative(mean(\"idx_scan\"), 1m) AS idx_scan \nFROM \n\t\"kpi\" \nWHERE \n\t\"dbname\" =~ /^$dbname$/\n\tAND \"cluster_name\" =~ /^$clustername$/\n\tAND $timeFilter \nGROUP BY time($agg_interval) FILL(none)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"load_5min"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": []
}
],
"title": "Seq. / Idx. scans",
"type": "timeseries"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "<p><font color=\"#00ccff\">Session by state</font> is Current overall state of this backend. Possible values are: <br /></p>\n<table>\n <thead>\n <tr>\n <th>state</th>\n <th>desc</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <td>idle</td>\n <td>The backend is waiting for a new client command.</td>\n </tr>\n <tr>\n <td>idle in transaction</td>\n <td>he backend is in a transaction, but is not currently executing a query.</td>\n </tr>\n <tr>\n <td>waiting</td>\n <td>Session is in a waiting state for other processes or resources</td>\n </tr>\n <tr>\n <td>active</td>\n <td>The backend is executing a query.</td>\n </tr>\n <tr>\n <td>idle in transaction (aborted)</td>\n <td>The backend is in a transaction, but is not currently executing a query.</td>\n </tr>\n <tr>\n <td>av_workers</td>\n <td>\"The state indicating a session running an Auto-Vacuum Worker process.</td>\n </tr>\n </tbody>\n</table>\n<p>Clicking on the point will take you to the [$clustername]'s <font color=\"#00ccff\">single cluster</font> with the same time range</p>",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"barAlignment": 0,
"drawStyle": "line",
"fillOpacity": 10,
"gradientMode": "none",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineInterpolation": "linear",
"lineWidth": 1,
"pointSize": 5,
"scaleDistribution": {
"type": "linear"
},
"showPoints": "never",
"spanNulls": false,
"stacking": {
"group": "A",
"mode": "none"
},
"thresholdsStyle": {
"mode": "off"
}
},
"links": [
{
"title": "[${clustername}] Go to single cluster.",
"url": "/d/experdb-single-cluster/single-cluster?${group:queryparam}&${clustername:queryparam}&${dbname:queryparam}&${agg_interval:queryparam}&orgId=1&refresh=30s&${__url_time_range}"
}
],
"mappings": [],
"min": 0,
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "short"
},
"overrides": []
},
"gridPos": {
"h": 6,
"w": 12,
"x": 0,
"y": 8
},
"id": 4,
"interval": "",
"links": [],
"options": {
"legend": {
"calcs": [
"max",
"min",
"mean"
],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"tooltip": {
"mode": "multi",
"sort": "desc"
}
},
"pluginVersion": "8.5.6",
"targets": [
{
"alias": "idle",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$agg_interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "backends",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"idle\") FROM \"backends\" WHERE (\"dbname\" =~ /^$dbname$/)\n -- AND \"cluster\" =~ /^$clustername$/\n AND $timeFilter GROUP BY time($agg_interval) fill(none)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"idle"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
},
{
"alias": "idle in transaction",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$agg_interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "backends",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"idleintransaction\") FROM \"backends\" WHERE (\"dbname\" =~ /^$dbname$/)\n-- AND \"cluster_name\" =~ /^$clustername$/ \nAND $timeFilter GROUP BY time($agg_interval) fill(none)",
"rawQuery": true,
"refId": "B",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"idleintransaction"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
},
{
"alias": "waiting",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$agg_interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "backends",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"waiting\") FROM \"backends\" WHERE (\"dbname\" =~ /^$dbname$/)\n-- AND \"cluster_name\" =~ /^$clustername$/ \nAND $timeFilter GROUP BY time($agg_interval) fill(none)",
"rawQuery": true,
"refId": "C",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"waiting"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
},
{
"alias": "active",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$agg_interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "backends",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"active\") FROM \"backends\" WHERE (\"dbname\" =~ /^$dbname$/) \n-- AND \"cluster_name\" =~ /^$clustername$/ \nAND $timeFilter GROUP BY time($agg_interval) fill(none)",
"rawQuery": true,
"refId": "D",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"active"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
},
{
"alias": "av_workers",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"dsType": "influxdb",
"groupBy": [
{
"params": [
"$agg_interval"
],
"type": "time"
},
{
"params": [
"none"
],
"type": "fill"
}
],
"measurement": "backends",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT mean(\"av_workers\") FROM \"backends\" WHERE (\"dbname\" =~ /^$dbname$/) \n-- AND \"cluster_name\" =~ /^$clustername$/ \nAND $timeFilter GROUP BY time($agg_interval) fill(none)",
"rawQuery": true,
"refId": "E",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"av_workers"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": [
{
"key": "dbname",
"operator": "=~",
"value": "/^$dbname$/"
}
]
}
],
"title": "Sessions by state ",
"type": "timeseries"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "- SQL response time >= 1 secend\n- <p><Click> on a point in the graph, then go to the <font color=\"#00ccff\">session history</font> panel.</p>",
"gridPos": {
"h": 6,
"w": 12,
"x": 12,
"y": 8
},
"id": 35,
"options": {
"baidu": {
"callback": "bmapReady",
"key": ""
},
"editor": {
"format": "auto",
"height": 600
},
"editorMode": "code",
"gaode": {
"key": "",
"plugin": "AMap.Scale,AMap.ToolBar"
},
"getOption": "const from = replaceVariables('$__from');\nconst to = replaceVariables('$__to');\n//const clustername = replaceVariables('$clustername');\n//const dbname = replaceVariables('$dbname');\nconst toDate = new Date(parseInt(to));\nconst fromDate = new Date(parseInt(from));\nlet lastClickTime = 0;\nconst clickDelay = 1000; // Set the delay time in milliseconds\nconst group = replaceVariables('$group');\n\nconst clustername = replaceVariables('$clustername');\nconst dbname = replaceVariables('$dbname');\n\nfunction handleChartClick(params) {\n const currentTime = new Date().getTime();\n if (currentTime - lastClickTime > clickDelay) {\n const pid = params.seriesName;\n const targetUrl = '/d/experdb-session-history/session-history?orgId=1' +\n '&var-group=' + group +\n '&var-clustername=' + clustername +\n '&var-dbname=' + dbname +\n '&var-pid=' + pid +\n '&from=' + replaceVariables('$__from') +\n '&to=' + replaceVariables('$__to');\n if (dbname == '$dbname') {\n } else {\n // Use locationService.push() to navigate to the new URL\n locationService.push(targetUrl);\n }\n\n\n lastClickTime = currentTime; // Update last click time\n }\n}\n\n// Register the event listener only once\nechartsInstance.on('click', handleChartClick);\n\n/**\n * Enable Data Zoom by default\n */\nsetTimeout(() => echartsInstance.dispatchAction({\n type: 'takeGlobalCursor',\n key: 'dataZoomSelect',\n dataZoomSelectActive: true,\n}), 500);\n\n/**\n * Update Time Range on Zoom\n */\nechartsInstance.on('datazoom', function (params) {\n const startValue = params.batch[0]?.startValue;\n const endValue = params.batch[0]?.endValue;\n locationService.partial({ from: startValue, to: endValue });\n});\n\nconst series = data.series.map((s) => {\n const sData = s.fields.find((f) => f.type === 'number').values.buffer;\n const sTime = s.fields.find((f) => f.type === 'time').values.buffer;\n\n return {\n name: s.name,\n type: 'scatter',\n showSymbol: false,\n areaStyle: {\n opacity: 0.5,\n },\n lineStyle: {\n width: 1,\n },\n data: sData.map((d, i) => [sTime[i], d.toFixed(2)]),\n };\n});\n\nconst axisOption = {\n axisTick: {\n show: false,\n },\n axisLine: {\n show: false,\n },\n axisLabel: {\n //color: 'rgba(128, 128, 128, .9)',\n overflow: 'truncate',\n },\n splitLine: {\n lineStyle: {\n color: 'rgba(128, 128, 128, .2)',\n },\n },\n};\n\nreturn {\n backgroundColor: 'transparent',\n tooltip: {\n //trigger: 'axis',\n trigger: 'item',\n axisPointer: { type: 'cross' },\n responsive: true,\n //position: 'top',\n //formatter: '{a} : {a1}',\n //backgroundColor: '#fafcfe',\n borderColor: '#c8e2f7',\n borderWidth: '0.8',\n textStyle: {\n color: '#5d6f80'\n }\n },\n /* legend: {\n left: '0',\n bottom: '0',\n data: data.series.map((s) => s.name),\n textStyle: {\n color: 'rgba(128, 128, 128, .9)',\n },\n },*/\n xAxis: Object.assign(\n {\n type: 'time',\n min: fromDate,\n max: toDate,\n splitNumber: 5\n },\n axisOption,\n ),\n yAxis: Object.assign(\n {\n type: 'value',\n min: 'dataMin',\n },\n axisOption\n ),\n toolbox: {\n orient: 'vertical',\n itemSize: 12,\n top: 15,\n right: -6,\n feature: {\n dataZoom: {\n // yAxisIndex: 'none',\n icon: {\n zoom: 'path://', // hack to remove zoom button\n back: 'path://', // hack to remove restore button\n },\n },\n },\n },\n grid: {\n left: 1.5,\n right: 16,\n top: 6,\n bottom: 24,\n containLabel: true,\n },\n series,\n replaceMerge: ['xAxis', 'yAxis', 'series'],\n};",
"google": {
"callback": "gmapReady",
"key": ""
},
"map": "none",
"renderer": "canvas",
"themeEditor": {
"config": "{}",
"height": 400,
"name": "custom"
},
"visualEditor": {
"code": "console.log(context);\nreturn {\n dataset: context.editor.dataset,\n series: context.editor.series,\n xAxis: {\n type: 'time',\n },\n yAxis: {\n type: 'value',\n min: 'dataMin',\n },\n}\n",
"dataset": [],
"series": []
}
},
"pluginVersion": "5.3.0",
"targets": [
{
"alias": "$tag_pid",
"datasource": {
"type": "influxdb",
"uid": "0"
},
"query": "--show tag keys from \"stat_activity_realtime\"\nselect max(\"duration_s\") \n from \"stat_activity_realtime\" \nwhere $timeFilter \n AND \"dbname\" =~ /^$dbname$/ \n AND \"duration_s\" >= 1\n group by \"pid\", \"query_start\" fill(none)",
"rawQuery": true,
"refId": "A",
"resultFormat": "time_series"
}
],
"title": "SQL Response time",
"type": "volkovlabs-echarts-panel"
},
{
"datasource": {
"type": "influxdb",
"uid": "0"
},
"description": "<p>Clicking on the PID will take you to the [$clustername]'s <font color=\"#00ccff\"> session history</font> with the same time range.</p>",
"fieldConfig": {
"defaults": {
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 13,
"w": 24,
"x": 0,
"y": 14
},
"id": 33,
"options": {
"afterRender": " const buttonsDiv = document.getElementById('buttons');\r\n // var selectAllCheckbox = document.getElementById(\"selectAll\");\r\n // var rowCheckboxes = document.getElementsByClassName(\"rowCheckbox\");\r\n // var allChecked = true;\r\n // var allChecked2 = false;\r\n // if(selectAllCheckbox){\r\n // // Check if all checkboxes in the table body are checked\r\n // for (var i = 0; i < rowCheckboxes.length; i++) {\r\n // if (!rowCheckboxes[i].checked) {\r\n // allChecked = false;\r\n // }\r\n // if (rowCheckboxes[i].checked) {\r\n // allChecked2 = true;\r\n // }\r\n // }\r\n // if(rowCheckboxes.length == 1 && rowCheckboxes[0].checked){\r\n // this.allChecked = true;\r\n // }\r\n // // Update the state of the \"Select All\" checkbox\r\n // if(!allChecked || allChecked2){\r\n // selectAllCheckbox.checked = allChecked;\r\n // }\r\n // }\r\n\r\n \r\n\r\n\r\n if(this.refreshed){\r\n const selectAllCheckbox = document.getElementById('selectAll');\r\n if(selectAllCheckbox){\r\n //selectAllCheckbox.checked = this.allChecked;\r\n }\r\n }\r\n \r\n\r\n if(buttonsDiv){\r\n if (!this.checked && !this.refreshed){\r\n if(this.timefilter.includes('now')){\r\n this.buttons = true;\r\n buttonsDiv.style.display = 'block'; // show the buttons\r\n }else{\r\n this.buttons = false;\r\n buttonsDiv.style.display = 'none'; // hide the buttons\r\n }\r\n }else if (!this.checked) {\r\n this.buttons = true;\r\n buttonsDiv.style.display = 'block'; // Show the buttons\r\n }else if(this.checked && this.refreshed && this.timefilter && this.from){\r\n const timestampRegex = /\\d+(?=ms)/g; // Match one or more digits followed by 'ms'\r\n const matches = this.timefilter.match(timestampRegex);\r\n if (matches && matches.length >= 2) {\r\n const toTimestamp = (matches[0]);\r\n if(toTimestamp == this.from){\r\n this.buttons = true;\r\n buttonsDiv.style.display = 'block'; // Show the buttons\r\n // Clear the existing rows in the tbody\r\n const tbody = document.querySelector('tbody');\r\n tbody.innerHTML = '';\r\n // Iterate over each row in this.rows and append it to the tbody\r\n this.rows.forEach(row => {\r\n tbody.appendChild(row.cloneNode(true));\r\n });\r\n }else{\r\n this.buttons = false;\r\n buttonsDiv.style.display = 'none'; // hide the buttons\r\n }\r\n } \r\n }else{\r\n this.buttons = false;\r\n buttonsDiv.style.display = 'none'; // hide the buttons\r\n }\r\n }\r\n \r\n\r\n // Get all hidden query elements\r\n const hiddenQueries = document.querySelectorAll('#query_hidden');\r\n\r\n // console.log('Hidden queries count:', hiddenQueries.length);\r\n\r\n // Loop through each hidden query element\r\n hiddenQueries.forEach(hiddenQuery => {\r\n // Get the full query from the hidden element's text content\r\n const fullQuery = hiddenQuery.textContent.trim();\r\n\r\n // Get the corresponding query cell\r\n const queryCell = hiddenQuery.nextElementSibling;\r\n\r\n // Set the full query as the title attribute of the query cell\r\n if(fullQuery.length > 47){\r\n\t\t\t\t queryCell.title = fullQuery;\r\n }\r\n });",
"content": "<div>\n <div id=\"buttons\" style=\"float:left; width:100%; margin-bottom: 10px;\">\n <button class=\"kill-button\" onclick=\"openConfirmationModal('{{variable \"‘${__user.login}’\"}}','{{variable \"__user.id\"}}','kill')\">Kill process</button>\n <button class=\"cancel-button\"\" onclick=\"openConfirmationModal('{{variable \"‘${__user.login}’\"}}','{{variable \"__user.id\"}}', 'cancel')\">Cancel query</button>\n {{#if (isRefresh (variable \"__to\"))}}\n <button id=\"refresh\" class=\"custom-button\" onclick=\"stopRefresh()\">Restart Refresh</button>\n {{else}} \n <button id=\"refresh\" class=\"custom-button\" onclick=\"stopRefresh()\">Hold Refresh</button>\n {{/if}}\n</div>\n<div>\n<table style=\"width: 12%; height: 110px;\">\n <thead>\n <tr>\n <th style=\"width: 30px;\"><input type=\"checkbox\" id=\"selectAll\" onclick=\"selectAllRows()\"></th>\n {{!-- <th id=\"dbname\" style=\"min-width: 150px\" class=\"sortable\" onclick=\"sortTable('dbname')\">dbname</th>\n <th id=\"cluster_name\" style=\"min-width: 150px\" onclick=\"sortTable('cluster_name')\">cluster name</th> --}}\n <th id=\"pid\" style=\"min-width:100px\" class=\"sortable\" onclick=\"sortTable('pid')\">pid</th>\n <th id=\"user\" style=\"min-width:160px\" onclick=\"sortTable('user')\">user</th>\n <th id=\"appname\" style=\"min-width: 160px\" onclick=\"sortTable('appname')\">appname</th>\n <th id=\"client_ip\" style=\"min-width: 180px\" onclick=\"sortTable('client_ip')\">client ip</th>\n <th id=\"blocking_pids\" style=\"min-width: 180px\" onclick=\"sortTable('blocking_pids')\">blocking pids</th>\n\t\t\t<th id=\"state\" style=\"min-width:210px\" onclick=\"sortTable('state')\">state</th> \n <th id=\"wait_event\" style=\"min-width:150px\" onclick=\"sortTable('wait_event')\">wait event</th>\n <th id=\"backend_start\" style=\"min-width: 200px\" class=\"sortable\" onclick=\"sortTable('backend_start')\">backend start</th>\n <th id=\"duration\" style=\"min-width:100px\" onclick=\"sortTable('duration')\">duration</th>\n <th id=\"query\" style=\"min-width: 380px\" onclick=\"sortTable('query')\">query</th>\n <th id=\"bind_variables\" style=\"min-width: 180px\" onclick=\"sortTable('bind_variables')\">bind variables</th>\n <th id=\"queryid\" style=\"min-width: 200px\" onclick=\"sortTable('queryid')\">query id</th> \n </tr>\n </thead>\n <tbody>\n {{#each data}}\n <tr>\n <td style=\"width: 30px;\"><input type=\"checkbox\" class=\"rowCheckbox\" onClick=\"updateSelectAllCheckbox()\"></td>\n {{!-- <td data-column=\"dbname\" style='color:#73BF69;min-width:150px;'>{{dbname}}</td>\n <td data-column=\"cluster_name\" style='color:#73BF69;min-width:150px;'>{{cluster_name}}</td> --}}\n <td data-column=\"pid\" style='color:#73BF69;min-width:100px;'>\n\t\t\t <a title=\"[pid : {{pid}}] Go to session history.\" \n\t\t\t id=\"pid{{@index}}\" style='color:#73BF69;min-width:100px; cursor: pointer; text-decoration: underline; ' onclick=\"goLink('pid', '{{pid}}', '{{cluster_name}}', '{{dbname}}')\">{{pid}}</a>\n\t\t\t</td>\n <td title=\"{{ title user 15 }}\" data-column=\"user\" style='color:#73BF69;min-width:160px;'>\n {{reduce2 user}}</td>\n <td title=\"{{ title appname 15 }}\" data-column=\"appname\" style='color:#73BF69;min-width:160px;'>\n {{reduce2 appname}}</td>\n <td data-column=\"client_ip\" style='color:#73BF69;min-width:180px;'>\n {{client_ip}}</td>\n <td title=\"{{ title blocking_pids 20 }}\" data-column=\"blocking_pids\" style='color:#73BF69;min-width:180px;'>\n {{reduce3 blocking_pids}}</td>\n <td data-column=\"state\" style='color:#73BF69;min-width:210px;'>\n {{state}}</td>\n <td data-column=\"wait_event\" style='color:#73BF69;min-width:150px;'>\n {{wait_event}}</td>\n <td data-column=\"backend_start\" style='color:#73BF69;min-width:200px;'>{{backend_start}}</td>\n <td data-column=\"duration\" style='color:#73BF69;min-width:100px;'>\n {{#if (isNull duration)}}\n 0s\n {{else}}\n {{\"duration\"}}s\n {{/if}}</td>\n <td id=\"query_hidden\" hidden>{{query}}</td> \n <td id=\"queryCell\" class=\"query-cell\" data-column=\"query\" style='color:#73BF69;min-width:380px;'>\n {{reduce query}}</td> \n <td title=\"{{ title bind_variables 20 }}\" data-column=\"bind_variables\" style='color:#73BF69;min-width:180px;'>\n {{reduce3 bind_variables}}</td>\n {{#if (isNull queryid)}}\n <td data-column=\"queryid\" style='color:#73BF69;min-width:200px;'>\n\t\t {{queryid}}</td>\n {{else}}\n <td data-column=\"queryid\" style='color:#73BF69;min-width:200px;'>\n\t\t\t <a title=\"[QueryID : {{queryid}}] Go to statements details.\"\n style='color:#73BF69;min-width:200px; cursor: pointer; text-decoration: underline;' onclick=\"goLink('queryid', '{{queryid}}', '{{cluster_name}}', '{{dbname}}')\">{{queryid}}\n\t\t\t\t\t\t\t</a></td>\n {{/if}}\n\t\t\t<td id=\"dbname{{@index}}\" hidden>{{dbname}}</td>\n\t\t\t<td id=\"clustername{{@index}}\" hidden>{{cluster_name}}</td>\n <td id=\"time\" hidden>{{Time}}</td>\n </tr>\n {{/each}}\n </tbody>\n</table>\n</div>\n</div>\n<!-- Modal -->\n<div id=\"confirmationModal\" class=\"modal\" hidden>\n <div class=\"modal-content\">\n\t <p style=\"color:white;\" id=\"modalMessage\"></p>\n\t <p id=\"username\" hidden></p>\n <p id=\"pids\" hidden></p>\n <p id=\"userid\" hidden></p>\n <p id=\"status\" hidden></p>\n\t <div class=\"button-container\">\n\t <button style=\"border-color:transparent;\" onclick=\"confirmAction()\">Confirm</button>\n\t <button style=\"border-color:transparent;\" onclick=\"closeModal('confirmationModal')\">Cancel</button>\n </div>\n </div>\n</div>\n\n<!-- Modal -->\n<div id=\"promptModal\" class=\"modal\" hidden>\n <div class=\"modal-content\">\n <p style=\"color:white;\" id=\"promptMessage\"></p>\n <input type=\"text\" id=\"promptInput\" placeholder=\"Enter your reason...\">\n <div class=\"button-container\">\n <button style=\"border-color:transparent;\" onclick=\"fn_SessionKill('{{variable \"clustername\"}}','{{variable \"dbname\"}}','{{pid}}','{{variable \"‘${__user.login}’\"}}','{{variable \"__user.id\"}}')\">Confirm</button>\n <button style=\"border-color:transparent;\" onclick=\"closeModal('promptModal')\">Cancel</button>\n </div>\n </div>\n</div>\n\n<!-- Modal -->\n<div id=\"alertModal\" class=\"modal\" hidden>\n <div class=\"modal-content\">\n <p style=\"color:white;\" id=\"alertMessage\"></p>\n <div class=\"button-container\">\n <button style=\"border-color:transparent;\" onclick=\"closeModal('alertModal')\">Confirm</button>\n </div>\n </div>\n</div>\n\n<!-- Modal -->\n<div id=\"confirmationModal2\" class=\"modal\" hidden>\n <div class=\"modal-content\">\n <p style=\"color:white;\" id=\"modalMessage2\"></p>\n <div class=\"button-container\">\n <button style=\"border-color:transparent;\" onclick=\"movePage()\">Confirm</button>\n <button style=\"border-color:transparent;\" onclick=\"closeModal('confirmationModal2')\">Cancel</button>\n </div>\n </div>\n</div>\n",
"defaultContent": " <div id=\"buttons\" style=\"float:left; width:100%\">\r\n</div>\r\n<div id=\"error\">\r\n <p id=\"errtxt\" style=\"align-items: center; color:#8D8E9A;font-size:18px;\">No data</p>\r\n</div>\r\n",
"editor": {
"format": "none",
"height": 593,
"language": "handlebars"
},
"editors": [
"helpers",
"styles",
"afterRender"
],
"externalScripts": [],
"externalStyles": [],
"helpers": "let bind_flag = false\r\nlet click = 0;\r\nlet originalOrder = null;\r\nlet sortOrders = {}; // Object to store sort order for each column\r\nlet sortedColumn = null;\r\nconst timefilter = replaceVariables('$timeFilter');\r\nconst timerange = replaceVariables('${__url_time_range}');\r\nlet count = false;\r\nconst from = replaceVariables('$__from');\r\nconst to = replaceVariables('$__to');\r\nconst toDate = new Date(parseInt(to));\r\nconst fromDate = new Date(parseInt(from));\r\nconst group = replaceVariables('$group');\r\nconst clustername = replaceVariables('$clustername');\r\nconst dbname = replaceVariables('$dbname');\r\n\r\n\r\n\r\nthis.timefilter = timefilter;\r\n\r\n\r\nupdateSelectAllCheckbox = () => {\r\n var selectAllCheckbox = document.getElementById(\"selectAll\");\r\n var rowCheckboxes = document.getElementsByClassName(\"rowCheckbox\");\r\n var allChecked = true;\r\n var allChecked2 = false;\r\n\r\n // Check if all checkboxes in the table body are checked\r\n for (var i = 0; i < rowCheckboxes.length; i++) {\r\n if (!rowCheckboxes[i].checked) {\r\n allChecked = false;\r\n }\r\n if (rowCheckboxes[i].checked) {\r\n allChecked2 = true;\r\n }\r\n }\r\n if(rowCheckboxes.length == 1 && rowCheckboxes[0].checked){\r\n this.allChecked = true;\r\n }\r\n\r\n // Update the state of the \"Select All\" checkbox\r\n if(!allChecked || allChecked2){\r\n selectAllCheckbox.checked = allChecked;\r\n }\r\n // if (this.buttons) {\r\n // if(!allChecked2){\r\n // stopRefresh();\r\n // }else if (!this.refreshed){\r\n // stopRefresh();\r\n // }else{\r\n // }\r\n // }\r\n}\r\n\r\nselectAllRows = () => {\r\n const checkboxes = document.querySelectorAll('.rowCheckbox');\r\n const selectAllCheckbox = document.getElementById('selectAll');\r\n checkboxes.forEach(checkbox => {\r\n checkbox.checked = selectAllCheckbox.checked;\r\n });\r\n // if (this.buttons) {\r\n // if(selectAllCheckbox.checked){\r\n // this.allChecked = true;\r\n // stopRefresh();\r\n // }else{\r\n // this.allChecked = false;\r\n // stopRefresh();\r\n // }\r\n // }\r\n\r\n}\r\n\r\n\r\n\r\nstopRefresh = () => {\r\n const refreshButton = document.getElementById('refresh');\r\n \r\n if (timefilter.includes('now')) {\r\n this.checked = true;\r\n this.refreshed = true;\r\n // Get the URL from the window location\r\n const url = window.location.href;\r\n // Parse the URL using URLSearchParams\r\n const params = new URLSearchParams(url);\r\n // Get the value of the 'from' parameter\r\n const fromValue = params.get('from');\r\n this.originalFrom = fromValue;\r\n this.originalTo = to;\r\n // Push the new URL to the browser's history without reloading the page\r\n this.from = from\r\n this.to = to\r\n this.timefilter = timefilter\r\n // Initialize an empty array to store the rows\r\n this.rows = [];\r\n\r\n // Get all the rows in the tbody\r\n const rows = document.querySelectorAll('tbody tr');\r\n\r\n // Iterate over each row and push it into the this.rows array\r\n rows.forEach(row => {\r\n this.rows.push(row);\r\n });\r\n locationService.partial({ from: from, to: to });\r\n refreshButton.innerText = 'Restart Refresh';\r\n } else {\r\n this.checked = false;\r\n this.refreshed = false;\r\n // Update the URL with the original 'from' parameter\r\n locationService.partial({ from: this.originalFrom, to: 'now', refresh: '10s' });\r\n }\r\n}\r\n\r\n\r\n\r\nsortTable = (column) => {\r\n const table = document.querySelector('table');\r\n const rows = Array.from(table.querySelectorAll('tbody tr'));\r\n\r\n if (click == 0) {\r\n originalOrder = [...rows];\r\n click += 1;\r\n }\r\n if (sortedColumn === column) {\r\n sortOrders[column] *= -1;\r\n if (sortOrders[column] === 1) {\r\n if(rows){\r\n rows.length = 0;\r\n rows.push(...originalOrder);\r\n sortedColumn = null;\r\n }else{\r\n rows.length = 0;\r\n rows.push(...originalOrder);\r\n sortedColumn = null;\r\n }\r\n }\r\n } else {\r\n sortOrders[column] = 1;\r\n sortedColumn = column;\r\n }\r\n\r\n const allColumnHeaders = table.querySelectorAll('th');\r\n allColumnHeaders.forEach(header => {\r\n header.classList.remove('sorted');\r\n removeSortIcon(header);\r\n });\r\n\r\n if (sortedColumn) {\r\n const currentSortedColumn = table.querySelector(`th[id=\"${column}\"]`);\r\n if (currentSortedColumn) {\r\n currentSortedColumn.classList.add('sorted');\r\n addSortIcon(currentSortedColumn, sortOrders[column]);\r\n }\r\n }\r\n\r\n rows.sort((a, b) => {\r\n const aValueElement = a.querySelector(`td[data-column=\"${column}\"]`);\r\n const bValueElement = b.querySelector(`td[data-column=\"${column}\"]`);\r\n\r\n const aValue = aValueElement ? aValueElement.textContent.trim() : '';\r\n const bValue = bValueElement ? bValueElement.textContent.trim() : '';\r\n\r\n if (column === 'duration') {\r\n const aDuration = parseDuration(aValue);\r\n const bDuration = parseDuration(bValue);\r\n\r\n return sortOrders[column] * (aDuration - bDuration);\r\n } \r\n else if (column === 'blocking_pids') {\r\n // Custom sorting for 'blocking_pids' column\r\n if (aValue === '' && bValue === '') {\r\n return 0;\r\n } else if (aValue === '') {\r\n return -1;\r\n } else if (bValue === '') {\r\n return 1;\r\n } else {\r\n // If both values are not blank, use the sortOrder\r\n return sortOrders[column] * (parseFloat(aValue) - parseFloat(bValue));\r\n }\r\n } \r\n else if (isNaN(aValue) || isNaN(bValue)) {\r\n return sortOrders[column] * aValue.localeCompare(bValue, 'en', { sensitivity: 'base' });\r\n } else {\r\n return sortOrders[column] * (parseFloat(aValue) - parseFloat(bValue));\r\n }\r\n });\r\n\r\n if(!sortedColumn){\r\n rows.length = 0;\r\n rows.push(...originalOrder);\r\n }\r\n const tbody = table.querySelector('tbody');\r\n tbody.innerHTML = '';\r\n rows.forEach(row => tbody.appendChild(row));\r\n};\r\n\r\n// Function to add the sort icon to the header\r\nfunction addSortIcon(header, sortOrder) {\r\n const icon = document.createElement('i');\r\n icon.classList.add('material-icons'); // You can adjust the icon class based on your icon library\r\n icon.textContent = sortOrder === 1 ? '▲' : '▼';\r\n icon.style.textAlign = 'right';\r\n header.appendChild(icon);\r\n}\r\n\r\n// Function to remove the sort icon from the header\r\nfunction removeSortIcon(header) {\r\n const icon = header.querySelector('.material-icons');\r\n if (icon) {\r\n header.removeChild(icon);\r\n }\r\n}\r\n// Function to parse duration string and convert it to seconds\r\nfunction parseDuration(durationString) {\r\n const durationParts = durationString.split(' ');\r\n let totalSeconds = 0;\r\n\r\n for (const part of durationParts) {\r\n const match = part.match(/(\\d*\\.?\\d+)([a-z]+)/i); // Updated regex to allow floating-point numbers\r\n if (match) {\r\n const value = parseFloat(match[1]); // Parse float instead of int\r\n const unit = match[2].toLowerCase();\r\n\r\n // Convert different time units to seconds\r\n switch (unit) {\r\n case 's':\r\n totalSeconds += value;\r\n break;\r\n case 'm':\r\n totalSeconds += value * 60;\r\n break;\r\n case 'h':\r\n totalSeconds += value * 60 * 60;\r\n break;\r\n // Add more cases if needed (e.g., 'd' for days)\r\n }\r\n }\r\n }\r\n\r\n return totalSeconds;\r\n}\r\n\r\n// Function to parse duration string and convert it to seconds\r\nfunction parseBlocking(blockingString) {\r\n const value = parseInt(blockingString);\r\n return value;\r\n}\r\n\r\nkillall = async (username, userid,status) => {\r\n let reason = \"kill all\"\r\n if(status == \"cancel\"){\r\n\t\t\t\t\treason = \"cancel all\";\r\n\t\t\t}\r\n\t const checkboxes = document.querySelectorAll('.rowCheckbox');\r\n\t const selectedRows = [];\r\n\t\t\tconst currentURL = window.location.href;\r\n\t\t // Create a URL object\r\n\t\t const urlObject = new URL(currentURL);\r\n\t\t const redirectURL = `${urlObject.protocol}//${urlObject.host}`;\r\n\t\t url_workbench = redirectURL;\r\n\t\t // Change the port to 9030\r\n\t\t urlObject.port = '9030';\r\n\t\t // Extract the base URL\r\n\t\t const baseURL = `${urlObject.protocol}//${urlObject.host}`;\r\n\t\t var url = baseURL + \"/processKill\";\r\n if(status == \"cancel\"){\r\n\t\t\t\turl = baseURL + \"/processStop\";\r\n\t\t\t}\r\n\t checkboxes.forEach((checkbox, i) => {\r\n\t if (checkbox.checked) {\r\n\t selectedRows.push(i);\r\n\t }\r\n\t });\r\n for (const [index, selectedIndex] of selectedRows.entries()) {\r\n await new Promise((resolve) => {\r\n setTimeout(() => {\r\n let pid = document.getElementById(\"pid\" + selectedIndex).innerText;\r\n let dbname = document.getElementById(\"dbname\" + index).innerText;\r\n let clustername = document.getElementById(\"clustername\" + index).innerText;\r\n const data = {\r\n hostname: clustername,\r\n dbname: dbname,\r\n pid: pid,\r\n user: username,\r\n userid: userid,\r\n reason: reason,\r\n url: \"\"\r\n };\r\n fn_SessionKill_multi(data, url).then((result) => {\r\n if (result === 1) {\r\n if (index === selectedRows.length - 1) {\r\n if(status == \"cancel\"){\r\n showAlertModal(\"Cancel query success\");\r\n } else {\r\n showAlertModal(\"Process Kill success\");\r\n }\r\n }\r\n resolve();\r\n } else {\r\n if(status == \"cancel\"){\r\n showAlertModal(\"Fail to Cancel Query\");\r\n } else {\r\n showAlertModal(\"Fail to Kill process\");\r\n }\r\n }\r\n })\r\n }, 1);\r\n });\r\n }\r\n};\r\n\r\nfn_SessionKill_multi = (data,url) => {\r\n\treturn new Promise((resolve) => {\r\n setTimeout(() => {\r\n fetch(url, {\r\n\t\t\t\t\t\t\t method: 'post',\r\n\t\t\t\t\t\t\t body: JSON.stringify(data)\r\n\t\t\t\t\t\t\t })\r\n\t\t\t\t\t\t\t .then(res => res.json())\r\n\t\t\t\t\t\t\t .then(res => {\r\n\t\t\t\t\t\t\t if (res.success) {\r\n\t\t\t\t\t\t\t resolve(1);\r\n\t\t\t\t\t\t\t } else if (res == 1) {\r\n\t\t\t\t\t\t\t\t\t\t\tresolve(1);\r\n\t\t\t\t\t\t\t } else if (res == 0) {\r\n\t\t\t\t\t\t\t resolve(0);\r\n\t\t\t\t\t\t\t } else if (res == -1) {\r\n\t\t\t\t\t\t\t resolve(0);\r\n\t\t\t\t\t\t\t } else if (res == -2) {\r\n\t\t\t\t\t\t\t\t resolve(0);\r\n\t\t\t\t\t\t\t } else {\r\n\t\t\t\t\t\t\t\t resolve(0);\r\n\t\t\t\t\t\t\t }\r\n\t\t\t\t\t\t\t })\r\n .catch((error) => {\r\n // Check if the error is due to CORS\r\n if (error.name === 'TypeError' && error.message === 'Failed to fetch') {\r\n //console.error('CORS Error occurred:', error);\r\n resolve(1);\r\n } else {\r\n // Handle other errors\r\n console.error('Error occurred:', error);\r\n resolve(0);\r\n }\r\n \r\n });\r\n }, 50);\r\n });\r\n} \r\n\r\n\r\n\r\n\r\n\r\ngetUser = (clustername, dbname, pid, userLogin, userId, action, port) => {\r\n const currentURL = window.location.href;\r\n const urlObject = new URL(currentURL);\r\n const redirectURL = `${urlObject.protocol}//${urlObject.host}`;\r\n const url = redirectURL + '/api/user/orgs';\r\n action_temp = action;\r\n fetch(url, {\r\n method: 'get',\r\n })\r\n .then(res => res.json())\r\n .then(res => {\r\n if (res[0].role == 'Viewer') {\r\n var question = '';\r\n if (action == 'kill') {\r\n question = \"Viewers do not have the privilege to kill this session.\"\r\n } else {\r\n question = \"Viewers do not have the privilege to stop this query.\"\r\n }\r\n showAlertModal(question);\r\n } else {\r\n openConfirmationModal(clustername, dbname, pid, userLogin, userId, action, port);\r\n }\r\n })\r\n .catch(error => {\r\n console.error('Error fetching user data:', error);\r\n });\r\n}\r\n\r\ngoLink= (category, params) => {\r\n\tvar link = '';\r\n\t// Get the current URL\r\n const currentURL = window.location.href;\r\n // Create a URL object\r\n const urlObject = new URL(currentURL);\r\n const redirectURL = `${urlObject.protocol}//${urlObject.host}`;\r\n\tif(category == 'pid'){\r\n link = '/d/experdb-session-history?orgId=1&var-clustername=' +\r\n clustername + '&var-dbname=' + dbname + '&var-group=' + group +\r\n '&var-pid=' +\r\n params\r\n + '&' + timerange\r\n\t}else{\r\n\t link = '/d/experdb-query-details/query-details?orgId=1&var-clustername=' +\r\n clustername + '&var-dbname=' + dbname + '&var-group=' + group +\r\n '&var-queryid=' +\r\n params\r\n + '&' + timerange\r\n\t}\r\n window.location.href = redirectURL + link\r\n}\r\n\r\n\r\n\r\n\r\nopenConfirmationModal = (username, userid,status) => {\r\n closeModal('alertModal','not');\r\n const role = this.grafanaBootData.user.orgRole;\r\n var action = status\r\n if(role == 'Viewer'){\r\n var question = '';\r\n this.message = \"Pid does not exist\"\r\n if (action == 'kill') {\r\n question = \"Viewers do not have the privilege to kill this session.\"\r\n } else {\r\n question = \"Viewers do not have the privilege to stop this query.\"\r\n }\r\n showAlertModal(question);\r\n }else{\r\n const checkboxes = document.querySelectorAll('.rowCheckbox');\r\n const selectedRows = [];\r\n var pids = [];\r\n checkboxes.forEach((checkbox, i) => {\r\n if (checkbox.checked) {\r\n selectedRows.push(i);\r\n }\r\n });\r\n for (const [index, selectedIndex] of selectedRows.entries()) {\r\n let pid = document.getElementById(\"pid\" + selectedIndex).innerText;\r\n pids.push(pid)\r\n }\r\n\r\n if (pids.length) {\r\n var question = '';\r\n var pid_new = pids;\r\n if (pids.length > 8) {\r\n pids = pids.slice(0, 8);\r\n pid_new = pids.join(', ');\r\n pid_new += \"...\";\r\n }\r\n if (action == 'kill') {\r\n question = \"Are you sure you want to kill this session?\"\r\n } else {\r\n question = \"Are you sure you want to cancel this query?\"\r\n }\r\n question += \"\\n pid: \" + pid_new \r\n this.message = question\r\n\r\n const dynamicMessage = question;\r\n document.getElementById('modalMessage').innerText = dynamicMessage;\r\n document.getElementById('username').innerText = username;\r\n document.getElementById('userid').innerText = userid;\r\n document.getElementById('status').innerText = status;\r\n document.getElementById('pids').innerText = pids;\r\n\r\n // Show the modal\r\n const modal = document.getElementById('confirmationModal');\r\n modal.removeAttribute('hidden');\r\n } else {\r\n this.message = \"Pid does not exist\"\r\n showAlertModal(\"Pid does not exist\");\r\n }\r\n }\r\n}\r\n\r\nconfirmAction = () => {\r\n var username = document.getElementById('username').innerText;\r\n var userid= document.getElementById('userid').innerText ;\r\n var status = \tdocument.getElementById('status').innerText ;\r\n // After performing the action, close the modal\r\n closeModal('confirmationModal');\r\n killall(username, userid,status);\r\n}\r\n\r\nopenPromptModal = (clustername, dbname, pid, userLogin, userId, action, message) => {\r\n // Set dynamic message based on parameters\r\n const dynamicMessage = message;\r\n document.getElementById('promptMessage').innerText = dynamicMessage;\r\n // Show the modal\r\n const modal = document.getElementById('promptModal');\r\n modal.removeAttribute('hidden');\r\n}\r\n\r\nfn_SessionKill = (hostname, dbname, pid, username, userid) => {\r\n\thostname = document.getElementById('cluster_name').innerText\r\n\tdbname = document.getElementById('dbname').innerText \r\n pid = document.getElementById('pid').innerText;\r\n var userInput = $('#promptInput').val();\r\n closeModal('promptModal');\r\n var dbname_temp = dbname;\r\n var pid = pid;\r\n // Get the current URL\r\n const currentURL = window.location.href;\r\n // Create a URL object\r\n const urlObject = new URL(currentURL);\r\n const redirectURL = `${urlObject.protocol}//${urlObject.host}`;\r\n url_workbench = redirectURL;\r\n // Change the port to 9030\r\n urlObject.port = '9030';\r\n // Extract the base URL\r\n const baseURL = `${urlObject.protocol}//${urlObject.host}`;\r\n var status = document.getElementById('action').innerText;\r\n \r\n\r\n var url = baseURL + \"/processKill\";\r\n //var url = \"http://localhost:9030/processKill\";\r\n if (status == 'stop') {\r\n url = baseURL + \"/processStop\";\r\n //userInput = $('#promptInput').val();\r\n //url = \"http://localhost:9030/processStop\";\r\n } \r\n\r\n const data = {\r\n hostname: hostname,\r\n dbname: dbname,\r\n pid: pid,\r\n url: redirectURL,\r\n user: username,\r\n userid: userid,\r\n reason: userInput\r\n };\r\n // User clicked \"OK\" or confirmed\r\n fetch(url, {\r\n method: 'post',\r\n body: JSON.stringify(data)\r\n })\r\n .then(res => res.json())\r\n .then(res => {\r\n var process = 'Process ';\r\n var process2 = ' process';\r\n if (status == 'stop') {\r\n process = 'Query '\r\n process2 = ' query'\r\n status = 'cancel'\r\n }\r\n if (res.success) {\r\n showAlertModal(process + status + \" success\");\r\n } else if (res == 1) {\r\n showAlertModal(process + status + \" success\");\r\n hostname_workbench = hostname;\r\n dbname_workbench = dbname;\r\n showPageModal(\"Do you want to move to session workload page?\");\r\n } else if (res == 0) {\r\n showAlertModal(\"Failed to \" + status + process2);\r\n } else if (res == -1) {\r\n showAlertModal(\"Failed to connect to the database\");\r\n } else if (res == -2) {\r\n //showAlertModal (\"Viewer cannot \" + status + process2);\r\n } else {\r\n showAlertModal(\"Failed to \" + status + process2);\r\n }\r\n })\r\n\r\n}\r\nmovePage = () => {\r\n // User clicked \"OK\"\r\n window.location.href = url_workbench + '/d/experdb-session-workload/session-workload?orgId=1&var-clustername='\r\n + hostname_workbench\r\n + '&var-dbname='\r\n + dbname_workbench\r\n + '&var-agg_interval=10s&var-top_queries=10';\r\n // Add your logic for the \"OK\" case here\r\n}\r\n\r\nshowAlertModal = (question) => {\r\n const dynamicMessage = question;\r\n document.getElementById('alertMessage').innerText = dynamicMessage;\r\n\r\n // Show the modal\r\n const modal = document.getElementById('alertModal');\r\n modal.removeAttribute('hidden');\r\n}\r\n\r\nshowPageModal = (question) => {\r\n const dynamicMessage = question;\r\n document.getElementById('modalMessage2').innerText = dynamicMessage;\r\n\r\n // Show the modal\r\n const modal = document.getElementById('confirmationModal2');\r\n modal.removeAttribute('hidden');\r\n}\r\n\r\ncloseModal = (id,not) => {\r\n // Hide the modal\r\n const modal = document.getElementById(id);\r\n modal.setAttribute('hidden', true);\r\n if(id == 'alertModal'){\r\n if(this.message == \"Pid does not exist\"){\r\n }else if (not == 'not'){\r\n }else{\r\n const selectAllCheckbox = document.getElementById('selectAll');\r\n selectAllCheckbox.checked = false;\r\n this.allChecked = false;\r\n // Get the current URL\r\n const currentURL = window.location.href;\r\n // Create a URL object\r\n const urlObject = new URL(currentURL);\r\n const redirectURL = `${urlObject.protocol}//${urlObject.host}`;\r\n link = '/d/experdb-session-workload/2-1-session-workload?orgId=1&var-clustername=' +\r\n clustername + '&var-dbname=' + dbname + '&from=30m&to=now' \r\n //window.location.href = redirectURL + link\r\n locationService.partial({ from: this.originalFrom, to: 'now', refresh: '10s' });\r\n } \r\n\t}\r\n}\r\n\r\n// Register a custom Handlebars helper\r\n\r\n\r\nhandlebars.registerHelper('title', function (value, length) {\r\n if(value){\r\n if(value.length > length){\r\n return value;\r\n }else{\r\n return null;\r\n }\r\n }else{\r\n return null;\r\n }\r\n});\r\n\r\nhandlebars.registerHelper('isRefresh', function (value) {\r\n if(timefilter.indexOf('now()') == -1 ){\r\n return true;\r\n }else{\r\n return false;\r\n }\r\n});\r\n\r\nhandlebars.registerHelper('isCurrent', function (value) {\r\n const current = new Date(); // Use new Date() to get the current date and time\r\n const timeDifferenceInSeconds = Math.abs(Math.floor((current - toDate) / 1000));\r\n\r\n // Check if the difference is less than 10 seconds\r\n if (timeDifferenceInSeconds < 10) { \r\n return false;\r\n } else {\r\n return true;\r\n }\r\n});\r\n\r\n\r\nhandlebars.registerHelper('reduce3', function (value) {\r\n if(value){\r\n\t\t\tif(value.length > 20){\r\n\t\t\t\treturn value.substr(0,20) + \"...\";\r\n }else{\r\n\t\t\t\treturn value;\r\n\t\t\t}\r\n\t\t}else{\r\n\t\t\treturn value;\r\n\t\t}\r\n\r\n});\r\n\r\n\r\nhandlebars.registerHelper('reduce2', function (value) {\r\n if(value){\r\n\t\t\tif(value.length > 15){\r\n\t\t\t\treturn value.substr(0,15) + \"...\";\r\n }else{\r\n\t\t\t\treturn value;\r\n\t\t\t}\r\n\t\t}else{\r\n\t\t\treturn value;\r\n\t\t}\r\n\r\n});\r\n\r\n\r\nhandlebars.registerHelper('reduce', function (value) {\r\n if(value){\r\n value = value.trim();\r\n\t\t\tif(value.length > 47){\r\n\t\t\t\treturn value.substr(0,47) + \"...\";\r\n }else{\r\n\t\t\t\treturn value;\r\n\t\t\t}\r\n\t\t}else{\r\n\t\t\treturn value;\r\n\t\t}\r\n\r\n});\r\n\r\nhandlebars.registerHelper('isNull', function (value) {\r\n return value === null;\r\n});\r\n\r\nhandlebars.registerHelper('replace', function (context, pattern, replacement) {\r\n var context_temp = \"\";\r\n if (context) {\r\n context_temp = context.replaceAll(pattern, replacement);\r\n }\r\n return context_temp;\r\n});\r\n\r\nhandlebars.registerHelper('merge', (str, sep, query) => {\r\n let tempStr = \"\";\r\n\r\n let tempQuery = \"\";\r\n if (str) {\r\n tempStr = str.split(sep)\r\n tempStr.map((v, i) => tempQuery = tempQuery.replace(\"$\" + (i + 1), v))\r\n tempQuery = query[0];\r\n } else {\r\n\r\n }\r\n\r\n return tempQuery\r\n});\r\n\r\nhandlebars.registerHelper('time_formatter', (time) => {\r\n let tempTime = new Date(time)\r\n let month = tempTime.getMonth() + 1;\r\n let day = tempTime.getDate();\r\n let hour = tempTime.getHours();\r\n let minute = tempTime.getMinutes();\r\n let second = tempTime.getSeconds();\r\n\r\n month = month >= 10 ? month : '0' + month;\r\n day = day >= 10 ? day : '0' + day;\r\n hour = hour >= 10 ? hour : '0' + hour;\r\n minute = minute >= 10 ? minute : '0' + minute;\r\n second = second >= 10 ? second : '0' + second;\r\n\r\n let formatted_date = tempTime.getFullYear() + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second;\r\n return formatted_date;\r\n});\r\n",
"renderMode": "allRows",
"styles": "\r\n/* Adjustments for Dark Mode */\r\n@media (prefers-color-scheme: dark) {\r\n :root {\r\n --background-color: var(--background-dark);\r\n --text-color: var(--text-dark);\r\n }\r\n\r\n /* Additional styling for dark mode */\r\n thead, tbody tr {\r\n background-color: var(--background-dark);\r\n }\r\n}\r\n\r\n/* Adjustments for Light Mode */\r\n@media (prefers-color-scheme: light) {\r\n :root {\r\n --background-color: var(--background-light);\r\n --text-color: var(--text-light);\r\n }\r\n\r\n /* Additional styling for light mode */\r\n thead, tbody tr {\r\n background-color: var(--background-light);\r\n }\r\n\r\n .cancel-button {\r\n color: rgb(6, 6, 6); \r\n }\r\n}\r\n\r\n\r\n.panel-loading{\r\n display:none !important;\r\n}\r\n\r\nthead {\r\n max-height: 35px !important;\r\n display: flex;\r\n //background: black;\r\n}\r\n\r\n\r\nth {\r\n text-align: left;\r\n background-color: #f2f2f2;\r\n height: 35px;\r\n line-height: 30px;\r\n}\r\n\r\n\r\nthead tr {\r\n display: flex;\r\n width: 100%;\r\n table-layout: fixed;\r\n max-height: 54px !important;\r\n}\r\n\r\ntbody tr {\r\n display: flex;\r\n width: 100%;\r\n table-layout: fixed;\r\n max-height: 30px !important;\r\n //background-color: #111214;\r\n \r\n}\r\ntbody {\r\n\t//border-color: rgb(30, 33, 37);\r\n}\r\n\r\n\r\ntable {\r\n border-collapse: collapse;\r\n width: 100%;\r\n height: 70px !important;\r\n //border-color: rgb(30, 33, 37);\r\n}\r\n\r\ntable th {\r\n //display: flex;\r\n background: transparent;\r\n width: 100%;\r\n table-layout: fixed;\r\n max-height: 54px !important;\r\n}\r\ntable td {\r\n //display: flex;\r\n width: 100%;\r\n table-layout: fixed;\r\n max-height: 30px !important;\r\n}\r\n\r\n\r\ntd {\r\n //font-size: 9pt;\r\n font-size: 1rem;\r\n //border-color: rgb(30, 33, 37) !important;\r\n}\r\n\r\n.custom-button {\r\n -webkit-text-size-adjust: 100%;\r\n -webkit-tap-highlight-color: rgba(0, 0, 0, 0);\r\n font-kerning: normal;\r\n color-scheme: dark;\r\n --sash-size: 4px;\r\n font-weight: 400;\r\n letter-spacing: 0.01071em;\r\n font-family: Inter, Helvetica, Arial, sans-serif;\r\n box-sizing: inherit;\r\n touch-action: manipulation;\r\n display: inline-block;\r\n position: relative;\r\n font-size: 14px;\r\n height: 32px;\r\n line-height: 26px;\r\n padding: 0px 16px;\r\n border-radius: 2px;\r\n background: rgba(0, 0, 0, 0.5); /* 다크 테마에 맞는 배경색 */\r\n cursor: pointer;\r\n z-index: 1;\r\n flex: 0 0 auto;\r\n text-align: center;\r\n user-select: none;\r\n white-space: nowrap;\r\n color: rgb(204, 204, 220);\r\n border: none; /* 테두리 제거 */\r\n margin-bottom: 5px;\r\n}\r\n\r\n\r\n.kill-button {\r\n -webkit-text-size-adjust: 100%;\r\n -webkit-tap-highlight-color: rgba(0, 0, 0, 0);\r\n color-scheme: dark;\r\n --sash-size: 4px;\r\n box-sizing: inherit;\r\n font: inherit;\r\n overflow: visible;\r\n text-transform: none;\r\n -webkit-appearance: button;\r\n touch-action: manipulation;\r\n margin: 0;\r\n letter-spacing: 0.01071em;\r\n display: inline-flex;\r\n -webkit-box-align: center;\r\n align-items: center;\r\n font-size: 14px;\r\n font-weight: 500;\r\n font-family: Inter, Helvetica, Arial, sans-serif;\r\n padding: 0px 16px;\r\n height: 32px;\r\n line-height: 30px;\r\n vertical-align: middle;\r\n cursor: pointer;\r\n border-radius: 2px;\r\n background: rgb(209, 14, 92);\r\n color: rgb(204, 204, 220);\r\n border: 1px solid transparent;\r\n transition: background-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, box-shadow 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, border-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;\r\n margin-right: 5px;\r\n margin-bottom: 5px;\r\n}\r\n\r\n.cancel-button {\r\n -webkit-text-size-adjust: 100%;\r\n -webkit-tap-highlight-color: rgba(0, 0, 0, 0);\r\n color-scheme: dark;\r\n --sash-size: 4px;\r\n visibility: visible;\r\n box-sizing: inherit;\r\n font: inherit;\r\n overflow: visible;\r\n text-transform: none;\r\n -webkit-appearance: button;\r\n touch-action: manipulation;\r\n margin: 0;\r\n letter-spacing: 0.01071em;\r\n display: inline-flex;\r\n -webkit-box-align: center;\r\n align-items: center;\r\n font-size: 14px;\r\n font-weight: 500;\r\n font-family: Inter, Helvetica, Arial, sans-serif;\r\n padding: 0px 16px;\r\n height: 32px;\r\n line-height: 22px;\r\n vertical-align: middle;\r\n cursor: pointer;\r\n border-radius: 2px;\r\n background: rgba(204, 204, 220, 0.16);\r\n border: 1px solid transparent;\r\n transition: background-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, box-shadow 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, border-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;\r\n margin-right: 5px;\r\n margin-bottom: 5px;\r\n color: rgb(204, 204, 220);\r\n}\r\n\r\n\r\ncustom-button2 {\r\n -webkit-text-size-adjust: 100%;\r\n -webkit-tap-highlight-color: rgba(0, 0, 0, 0);\r\n color-scheme: dark;\r\n --sash-size: 4px;\r\n pointer-events: auto;\r\n box-sizing: inherit;\r\n font: inherit;\r\n overflow: visible;\r\n text-transform: none;\r\n -webkit-appearance: button;\r\n touch-action: manipulation;\r\n margin: 0;\r\n letter-spacing: 0.01071em;\r\n display: inline-flex;\r\n -webkit-box-align: center;\r\n align-items: center;\r\n font-size: 14px;\r\n font-weight: 500;\r\n font-family: Inter, Helvetica, Arial, sans-serif;\r\n padding: 0px 15px;\r\n height: 32px;\r\n line-height: 30px;\r\n vertical-align: middle;\r\n cursor: pointer;\r\n border-radius: 2px;\r\n background: transparent;\r\n color: rgb(204, 204, 220);\r\n border: 1px solid rgba(204, 204, 220, 0.25);\r\n transition: background-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, border-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;\r\n margin-bottom: 5px;\r\n}\r\n\r\n\r\n\r\n.custom-button2 {\r\n margin-top: 9px;\r\n display: inline-flex;\r\n font-size:11px;\r\n //font-weight: 500;\r\n font-family: Inter, Helvetica, Arial, sans-serif;\r\n //padding: 0px 15px;\r\n height: 32px;\r\n line-height: 30px;\r\n cursor: pointer;\r\n border-radius: 2px;\r\n background: rgba(204, 204, 220, 0.16);\r\n color: rgb(204, 204, 220);\r\n border: 1px solid transparent;\r\n transition: background-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, box-shadow 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, border-color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms, color 250ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;\r\n}\r\n.button-container {\r\n display: flex; /* Use flexbox to make the buttons appear in the same row */\r\n gap: 10px; /* Optional: Add some space between the buttons */\r\n}\r\n\r\n\r\n\r\n#confirmationModal,\r\n#confirmationModal2,\r\n#confirmationModal3,\r\n#confirmationModal4,\r\n#promptModal,\r\n#promptModal_s {\r\n background-color: black;\r\n display: flex;\r\n flex-direction: column;\r\n align-items: center;\r\n position: fixed;\r\n top: 40%;\r\n //left: 50%;\r\n transform: translate(50%, -50%) translateX(-50%);\r\n}\r\n\r\n#alertModal {\r\n background-color: black;\r\n display: flex;\r\n flex-direction: column;\r\n align-items: center;\r\n position: fixed;\r\n top: 40%;\r\n //left: 50%;\r\n transform: translate(50%, -50%) translateX(-50%);\r\n}\r\n\r\n\r\n#error {\r\n display: flex;\r\n align-items: center;\r\n justify-content: center;\r\n height: 16vh;\r\n margin: 0;\r\n overflow: hidden; /* Set overflow to hidden */\r\n}\r\n\r\n#errtxt {\r\n text-align: center;\r\n overflow: hidden; /* Set overflow to hidden */\r\n margin: 0; /* Remove default margin */\r\n}",
"wrap": true
},
"pluginVersion": "4.5.0",
"targets": [
{
"datasource": "0",
"groupBy": [
{
"params": [
"$__interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"hide": false,
"orderByTime": "ASC",
"policy": "default",
"query": " SELECT\n last(\"user\") AS \"user\",\nlast(\"backend_start_text\") as \"backend_start\",\n last(\"appname\") AS \"appname\",\n last(\"client_ip\") AS \"client_ip\",\n last(\"duration_s\") AS \"duration\",\n -- last(\"waiting\") AS \"waiting\",\n last(\"blocking_pids\") AS \"blocking_pids\",\n last(\"query_text\") AS \"query\",\n last(\"state\") AS \"state\",\nlast(\"bind_variables\") AS \"bind_variables\"\n FROM\n${condition}\n WHERE\n $timeFilter\n AND duration_s >= 1\n AND \"cluster_name\" =~ /^$clustername$/ \n AND \"dbname\" =~ /^$dbname$/\n GROUP BY pid, wait_event,queryid, dbname,cluster_name\nLIMIT $top_queries",
"rawQuery": true,
"refId": "B",
"resultFormat": "table",
"select": [
[
{
"params": [
"value"
],
"type": "field"
},
{
"params": [],
"type": "mean"
}
]
],
"tags": []
}
],
"timeFrom": "11s",
"title": "Active sessions realtime",
"transformations": [],
"type": "marcusolsson-dynamictext-panel"
},
{
"datasource": {
"type": "postgres",
"uid": "1"
},
"description": "",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "left",
"cellOptions": {
"type": "color-text"
},
"filterable": false,
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "pid"
},
"properties": [
{
"id": "unit",
"value": "string"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.align"
},
{
"id": "custom.width",
"value": 79
},
{
"id": "links",
"value": [
{
"targetBlank": false,
"title": "[pid : ${__data.fields[pid]}] Go to session history",
"url": "/d/experdb-session-history?orgId=1&var-clustername=${clustername}&var-dbname=${__data.fields[dbname]}&var-pid=${__data.fields[pid]}&${__url_time_range}"
}
]
}
]
},
{
"matcher": {
"id": "byName",
"options": "action"
},
"properties": [
{
"id": "unit",
"value": "string"
},
{
"id": "decimals",
"value": 1
},
{
"id": "custom.cellOptions",
"value": {
"type": "color-text"
}
},
{
"id": "custom.align"
},
{
"id": "thresholds",
"value": {
"mode": "absolute",
"steps": [
{
"color": "rgba(50, 172, 45, 0.97)"
},
{
"color": "rgba(237, 129, 40, 0.89)",
"value": 60
},
{
"color": "rgba(245, 54, 54, 0.9)",
"value": 300
}
]
}
},
{
"id": "custom.width",
"value": 100
},
{
"id": "mappings",
"value": [
{
"options": {
"pg_kill": {
"index": 0,
"text": "killed"
},
"pg_stop": {
"index": 1,
"text": "cancelled"
}
},
"type": "value"
}
]
}
]
},
{
"matcher": {
"id": "byName",
"options": "waiting"
},
"properties": [
{
"id": "unit",
"value": "short"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.cellOptions",
"value": {
"type": "color-text"
}
},
{
"id": "custom.align"
},
{
"id": "thresholds",
"value": {
"mode": "absolute",
"steps": [
{
"color": "rgba(50, 172, 45, 0.97)"
},
{
"color": "#C4162A",
"value": 1
},
{
"color": "rgba(245, 54, 54, 0.9)",
"value": 2
}
]
}
},
{
"id": "mappings",
"value": [
{
"options": {
"0": {
"index": 1,
"text": "no"
},
"1": {
"index": 0,
"text": "yes"
}
},
"type": "value"
}
]
},
{
"id": "custom.width",
"value": 70
}
]
},
{
"matcher": {
"id": "byName",
"options": "blocking_pids"
},
"properties": [
{
"id": "unit",
"value": "none"
},
{
"id": "custom.align",
"value": "center"
},
{
"id": "custom.width",
"value": 120
},
{
"id": "noValue"
}
]
},
{
"matcher": {
"id": "byName",
"options": "Time"
},
"properties": [
{
"id": "unit",
"value": "short"
},
{
"id": "decimals",
"value": 2
},
{
"id": "custom.align"
},
{
"id": "custom.hidden",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "user"
},
"properties": [
{
"id": "custom.width",
"value": 120
}
]
},
{
"matcher": {
"id": "byName",
"options": "query"
},
"properties": [
{
"id": "custom.width"
},
{
"id": "custom.inspect",
"value": true
}
]
},
{
"matcher": {
"id": "byName",
"options": "client_ip"
},
"properties": [
{
"id": "custom.width",
"value": 288
}
]
},
{
"matcher": {
"id": "byName",
"options": "appname"
},
"properties": [
{
"id": "custom.width",
"value": 243
}
]
},
{
"matcher": {
"id": "byName",
"options": "duration_s"
},
"properties": [
{
"id": "custom.width",
"value": 218
}
]
}
]
},
"gridPos": {
"h": 8,
"w": 24,
"x": 0,
"y": 27
},
"id": 25,
"options": {
"footer": {
"countRows": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": []
},
"pluginVersion": "9.4.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "1"
},
"editorMode": "code",
"format": "table",
"rawQuery": true,
"rawSql": "select sl_pid as \"pid\", sl_hostname as \"clustername\", sl_dbname as \"dbname\", \r\nsl_unique_name as \"username\",sl_reason as \"reason\", sl_status as \"action\", \r\n sl_timestamp AT TIME ZONE 'KST' as \"timestamp\" from session_log where \r\n sl_hostname = '$clustername'\r\n AND sl_dbname = '$dbname'\r\n -- AND sl_timestamp AT TIME ZONE 'Asia/Seoul' > current_timestamp - INTERVAL '6 hour';\r\n AND $__timeFilter(sl_timestamp AT TIME ZONE 'Asia/Seoul')",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
},
"table": "session_log"
}
],
"title": "Managed session history",
"transformations": [
{
"id": "merge",
"options": {
"reducers": []
}
}
],
"type": "table"
}
],
"refresh": "10s",
"revision": 1,
"schemaVersion": 38,
"style": "dark",
"tags": [
"experdb"
],
"templating": {
"list": [
{
"current": {
"selected": false
},
"datasource": {
"type": "postgres",
"uid": "1"
},
"definition": "SELECT 'All' AS __text, 0 AS __value where $role_id in (2, 3) \nUNION ALL\nselect g_name AS __text, g_id AS __value from cluster_group cg where $role_id in (2, 3) or exists (select 1 from cluster_group_user cgu where cgu.g_id = cg.g_id and cgu.u_id = ${__user.id}) ",
"hide": 0,
"includeAll": false,
"multi": false,
"name": "group",
"options": [],
"query": "SELECT 'All' AS __text, 0 AS __value where $role_id in (2, 3) \nUNION ALL\nselect g_name AS __text, g_id AS __value from cluster_group cg where $role_id in (2, 3) or exists (select 1 from cluster_group_user cgu where cgu.g_id = cg.g_id and cgu.u_id = ${__user.id}) ",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
},
{
"current": {
"selected": false
},
"datasource": {
"type": "postgres",
"uid": "1"
},
"definition": "select ms.ms_unique_name \n from monitored_server ms \n join monitored_db md on ms.ms_unique_name = md.md_unique_name \n left join cluster_group_server cgs on ms.ms_unique_name = cgs.ms_unique_name \n where md_is_enabled = true and ($group = 0 or cgs.g_id=$group);",
"description": "",
"hide": 0,
"includeAll": false,
"multi": false,
"name": "clustername",
"options": [],
"query": "select ms.ms_unique_name \n from monitored_server ms \n join monitored_db md on ms.ms_unique_name = md.md_unique_name \n left join cluster_group_server cgs on ms.ms_unique_name = cgs.ms_unique_name \n where md_is_enabled = true and ($group = 0 or cgs.g_id=$group);",
"refresh": 2,
"regex": "",
"skipUrlSync": false,
"sort": 1,
"type": "query"
},
{
"allValue": "",
"current": {
"selected": false
},
"datasource": {
"type": "influxdb",
"uid": "0"
},
"definition": "select last(continuous_discovery_prefix) from configured_dbs where cluster_name =~ /^$clustername$/ group by dbname ",
"hide": 0,
"includeAll": false,
"multi": false,
"name": "dbname",
"options": [],
"query": "select last(continuous_discovery_prefix) from configured_dbs where cluster_name =~ /^$clustername$/ group by dbname ",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 1,
"tagValuesQuery": "",
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"auto": true,
"auto_count": 30,
"auto_min": "5s",
"current": {
"selected": false
},
"hide": 2,
"name": "agg_interval",
"options": [
{
"selected": true,
"text": "auto",
"value": "$__auto_interval_agg_interval"
},
{
"selected": false,
"text": "5s",
"value": "5s"
},
{
"selected": false,
"text": "10s",
"value": "10s"
},
{
"selected": false,
"text": "30s",
"value": "30s"
},
{
"selected": false,
"text": "1m",
"value": "1m"
},
{
"selected": false,
"text": "5m",
"value": "5m"
},
{
"selected": false,
"text": "15m",
"value": "15m"
},
{
"selected": false,
"text": "1h",
"value": "1h"
},
{
"selected": false,
"text": "3h",
"value": "3h"
},
{
"selected": false,
"text": "6h",
"value": "6h"
},
{
"selected": false,
"text": "12h",
"value": "12h"
},
{
"selected": false,
"text": "1d",
"value": "1d"
},
{
"selected": false,
"text": "7d",
"value": "7d"
},
{
"selected": false,
"text": "30d",
"value": "30d"
}
],
"query": "5s,10s,30s,1m,5m,15m,1h,3h,6h,12h,1d,7d,30d",
"queryValue": "",
"refresh": 2,
"skipUrlSync": false,
"type": "interval"
},
{
"current": {
"selected": false,
"text": "10",
"value": "10"
},
"hide": 0,
"includeAll": false,
"multi": false,
"name": "top_queries",
"options": [
{
"selected": true,
"text": "10",
"value": "10"
},
{
"selected": false,
"text": "15",
"value": "15"
},
{
"selected": false,
"text": "20",
"value": "20"
},
{
"selected": false,
"text": "30",
"value": "30"
},
{
"selected": false,
"text": "60",
"value": "60"
},
{
"selected": false,
"text": "120",
"value": "120"
}
],
"query": "10,15,20,30,60,120",
"queryValue": "",
"skipUrlSync": false,
"type": "custom"
},
{
"hide": 2,
"name": "min",
"query": "0.1",
"skipUrlSync": false,
"type": "constant"
},
{
"hide": 2,
"name": "max",
"query": "10000",
"skipUrlSync": false,
"type": "constant"
},
{
"current": {
"selected": false
},
"datasource": {
"type": "postgres",
"uid": "1"
},
"definition": "SELECT CASE WHEN '$preset' LIKE '%ext' THEN 'stat_activity_realtime' ELSE 'stat_activity_realtime' END AS condition",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "condition",
"options": [],
"query": "SELECT CASE WHEN '$preset' LIKE '%ext' THEN 'stat_activity_realtime' ELSE 'stat_activity_realtime' END AS condition",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
},
{
"current": {
"selected": false,
"text": "3",
"value": "3"
},
"datasource": {
"type": "postgres",
"uid": "2"
},
"definition": "select role_id from user_role where user_id = ${__user.id} and org_id = 1",
"hide": 2,
"includeAll": false,
"multi": false,
"name": "role_id",
"options": [],
"query": "select role_id from user_role where user_id = ${__user.id} and org_id = 1",
"refresh": 1,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"type": "query"
}
]
},
"time": {
"from": "now-30m",
"to": "now"
},
"timepicker": {
"hidden": false,
"refresh_intervals": [
"5s",
"10s",
"30s",
"1m",
"5m",
"10m",
"15m"
]
},
"timezone": "browser",
"title": "2.1. Session workload",
"uid": "experdb-session-workload",
"version": 0,
"weekStart": ""
}#exclude detail
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, 0 as CPU_LOGICAL_ID --PT_04_02_001
, 0 AS CORE_CPU_RATE
--, D.CPU_LOGICAL_ID --PT_04_02_001
--, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
--, (SELECT SUM(PROC_CPU_UTIL) FROM TB_BACKEND_RSC RSC WHERE RSC.REG_DATE = B.REG_DATE AND RSC.RSC_REG_SEQ = B.RSC_REG_SEQ) AS PGSQL_UTIL_RATE --PT_04_05_001(CPU_PG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
--AND A.INSTANCE_ID IN ({0})
AND B.REG_DATE = C.REG_DATE
AND C.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND C.RSC_REG_SEQ = E.RSC_REG_SEQ
#include detail
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, 0 as CPU_LOGICAL_ID --PT_04_02_001
, 0 AS CORE_CPU_RATE
--, D.CPU_LOGICAL_ID --PT_04_02_001
--, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
--, (SELECT SUM(PROC_CPU_UTIL) FROM TB_BACKEND_RSC RSC WHERE RSC.REG_DATE = B.REG_DATE AND RSC.RSC_REG_SEQ = B.RSC_REG_SEQ) AS PGSQL_UTIL_RATE --PT_04_05_001(CPU_PG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_CPU_STAT_DETAIL D, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
--AND A.INSTANCE_ID IN ({0})
AND B.REG_DATE = C.REG_DATE
AND C.REG_DATE = D.REG_DATE
AND D.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND C.RSC_REG_SEQ = D.RSC_REG_SEQ
AND D.RSC_REG_SEQ = E.RSC_REG_SEQ
tuned query
with RC as (
SELECT /* SELECTCPUMEMINFO */ A.INSTANCE_ID
, C.COLLECT_DT AS REG_DATE
, B.reg_date as RDATE
, b.rsc_reg_seq
, CASE WHEN C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE > 100 THEN 100 ELSE C.USER_UTIL_RATE + C.NICE_UTIL_RATE + C.SYS_UTIL_RATE + C.WAIT_UTIL_RATE END AS CPU_MAIN --PT_03_03_001, PT_04_02_001(AVG)
, C.WAIT_UTIL_RATE --PT_04_05_001(CPU_WAIT)
, CASE WHEN E.MEM_TOTAL_KB != 0 THEN (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB +E.MEM_BUFFERED_KB + E.MEM_CACHED_KB)) / E.MEM_TOTAL_KB ) * 100)::int ELSE E.MEM_TOTAL_KB END AS MEM_USED_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN E.SWP_TOTAL_KB != 0 THEN (((E.SWP_TOTAL_KB - (E.SWP_FREE_KB + E.SWP_CACHED_KB)) / E.SWP_TOTAL_KB) * 100)::int ELSE E.SWP_TOTAL_KB END AS SWP_USED_RATE --PT_03_03_001(MEM SWAP_RATE), PT_04_05_001(PG_MEMORY)
, (E.MEM_TOTAL_KB / 1024)::int AS MEM_TOTAL_MB --PT_04_05_001(TOTAL MEM)
, (E.MEM_USED_KB / 1024)::int AS MEM_USED_MB --PT_04_05_001(USED MEM)
, (E.MEM_FREE_KB / 1024)::int AS MEM_FREE_MB --PT_04_05_001(FREE MEM)
, (E.SHM_KB / 1024)::int AS SHM_MB --PT_04_05_001(SHARED MEM)
, (E.MEM_BUFFERED_KB / 1024)::int AS MEM_BUFFERED_MB --PT_04_05_001(BUFFERS MEM)
, (E.MEM_CACHED_KB / 1024)::int AS MEM_CACHED_MB --PT_04_05_001(CACHED MEM)
, (E.SWP_TOTAL_KB / 1024)::int AS SWP_TOTAL_MB --PT_04_05_001(SWAP_TOTAL MEM)
, (E.SWP_USED_KB / 1024)::int AS SWP_USED_MB --PT_04_05_001(SWAP_USED MEM)
, (E.SWP_FREE_KB / 1024)::int AS SWP_FREE_MB --PT_04_05_001(SWAP_FREE MEM)
, (E.SWP_CACHED_KB / 1024)::int AS SWP_CACHED_MB --PT_04_05_001(SWAP_CACHED MEM)
, (((E.MEM_TOTAL_KB - (E.MEM_FREE_KB )) / E.MEM_TOTAL_KB ) * 100) AS BUFFER_RATE --PT_03_03_001, PT_04_02_001(MEM_AVG)
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, A.HA_GROUP
, CASE WHEN 0=0 THEN TO_CHAR(A.INSTANCE_ID, '00')||A.HOST_NAME ELSE TO_CHAR(A.INSTANCE_ID, '00')||A.CONN_NAME END AS SORTED_HOST_NAME
FROM TB_INSTANCE_INFO A
, (SELECT MAX(REG_DATE) AS REG_DATE, MAX(REG_TIME) AS REG_TIME, MAX(RSC_REG_SEQ) AS RSC_REG_SEQ, INSTANCE_ID
FROM TB_RSC_COLLECT_INFO
WHERE REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND RSC_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 1 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM RSC_REG_SEQ)
GROUP BY INSTANCE_ID) B
, TB_CPU_STAT_MASTER C, TB_MEMORY_STAT E
WHERE A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
and B.REG_DATE=TO_CHAR(NOW(),'YYYYMMDD')
AND B.REG_DATE = C.REG_DATE
AND B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.RSC_REG_SEQ = C.RSC_REG_SEQ
AND B.RSC_REG_SEQ = E.RSC_REG_SEQ
)
SELECT RC.*
, D.CPU_LOGICAL_ID --PT_04_02_001
, CASE WHEN D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE > 100 THEN 100 ELSE D.USER_UTIL_RATE + D.NICE_UTIL_RATE + D.SYS_UTIL_RATE + D.WAIT_UTIL_RATE END AS CORE_CPU_RATE --PT_04_02_001
FROM RC
join TB_CPU_STAT_DETAIL D on D.REG_DATE=TO_CHAR(NOW(),'YYYYMMDD') and RC.RDATE = D.reg_date and RC.rsc_reg_seq = D.rsc_reg_seq
/* SELECTBACKEND /
WITH PGSTA AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE instance_id IN (1,2,3,4) AND (EXTENSIONS & 4) = 0),
PGACT AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE instance_id IN (1,2,3,4) AND (EXTENSIONS & 4) > 0)
SELECT BACKEND. , CASE WHEN (BACKEND.SQL1 = '') THEN Q.QUERY ELSE BACKEND.SQL1 END AS SQL
FROM
(
SELECT A.INSTANCE_ID
, E.COLLECT_DT AS REG_DATE
, ROW_NUMBER() OVER (PARTITION BY A.INSTANCE_ID ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) AS RNUM
, E.DB_NAME --PT_04_04_001(RESRCUTILPERBACK : DB)
, COALESCE(E.USER_NAME, '') AS USER_NAME
, E.CLIENT_ADDR
, E.CLIENT_APP
, CASE WHEN (E.STATE = 'active') THEN 'active' ELSE 'idle' END AS "STATE"
, E.PROCESS_ID --PT_04_04_001(RESRCUTILPERBACK : PID)
, E.CURRENT_PROC_READ_KB --PT_03_06_001(BACKEND PROCS : MEM USEAGE READ KB)
, E.CURRENT_PROC_WRITE_KB --PT_04_04_001(RESRCUTILPERBACK : MEM USAGE WRITE KB)
, ROUND(E.PROC_CPU_UTIL / 100,2) AS "CPU_USAGE" --PT_03_06_001(BACKEND PROCS : CPU_USAGE PER), PT_04_04_001(RESRCUTILPERBACK : CPU_USAGE PER)
, E.QUERY_START AS "START_TIME" --PT_03_06_001(BACKEND PROCS : QUERY_START)
, CASE WHEN (E.STATE = 'active' AND E.ELAPSED_TIME >= 0 ) THEN E.ELAPSED_TIME ELSE '0' END AS "ELAPSED_TIME" --PT_03_06_001(BACKEND PROCS : )
, E.SQL AS SQL1 --PT_03_06_001(BACKEND PROCS : SQL), PT_04_04_001(RESRCUTILPERBACK : SQL)
, E.ELAPSED_TIME
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, E.CURRENT_PROC_READ_KB + E.CURRENT_PROC_WRITE_KB AS "MEM_USAGE_KB"
, B.ACTV_REG_SEQ
, CD.CODE_NAME AS WAIT_EVENT
, E.QUERYID
FROM TB_INSTANCE_INFO A,
(SELECT MAX(REG_DATE) AS REG_DATE, MAX(ACTV_REG_SEQ) AS ACTV_REG_SEQ, INSTANCE_ID, MAX(REG_TIME) AS REG_TIME
FROM TB_ACTV_COLLECT_INFO
WHERE REG_DATE = '20201117'
--AND ACTV_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 3 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM ACTV_REG_SEQ)
AND reg_time BETWEEN '15:00:00' AND '15:30:00'
GROUP BY INSTANCE_ID) B, TB_BACKEND_RSC E, TB_SYS_CODE CD
WHERE B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.ACTV_REG_SEQ = E.ACTV_REG_SEQ
AND A.INSTANCE_ID IN (SELECT INSTANCE_ID FROM PGSTA)
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
AND E.SQL != 'BACKGROUND PROC'
AND E.SQL IS NOT NULL
--AND a.INSTANCE_ID = 1
AND COALESCE(E.WAIT_EVENT, 200) = CD.CODE
ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC
) BACKEND,
(SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q
--TB_QUERY_INFO Q
WHERE BACKEND.RNUM <= 8
AND Q.INSTANCE_ID = BACKEND.INSTANCE_ID
AND Q.QUERYID = BACKEND.QUERYID
AND ELAPSED_TIME >= 0
WITH PGSTA AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE (EXTENSIONS & 4) = 0),
PGACT AS (SELECT INSTANCE_ID FROM TB_INSTANCE_INFO WHERE (EXTENSIONS & 4) > 0)
SELECT BACKEND.* , CASE WHEN (BACKEND.SQL1 = '') THEN Q.QUERY ELSE BACKEND.SQL1 END AS SQL
FROM
(SELECT A.INSTANCE_ID
, E.COLLECT_DT AS REG_DATE
, ROW_NUMBER() OVER (PARTITION BY A.INSTANCE_ID ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) AS RNUM
, E.DB_NAME --PT_04_04_001(RESRCUTILPERBACK : DB)
, COALESCE(E.USER_NAME, '') AS USER_NAME
, E.CLIENT_ADDR
, E.CLIENT_APP
, CASE WHEN (E.STATE = 'active') THEN 'active' ELSE 'idle' END AS "STATE"
, E.PROCESS_ID --PT_04_04_001(RESRCUTILPERBACK : PID)
, E.CURRENT_PROC_READ_KB --PT_03_06_001(BACKEND PROCS : MEM USEAGE READ KB)
, E.CURRENT_PROC_WRITE_KB --PT_04_04_001(RESRCUTILPERBACK : MEM USAGE WRITE KB)
, ROUND(E.PROC_CPU_UTIL / 100,2) AS "CPU_USAGE" --PT_03_06_001(BACKEND PROCS : CPU_USAGE PER), PT_04_04_001(RESRCUTILPERBACK : CPU_USAGE PER)
, E.QUERY_START AS "START_TIME" --PT_03_06_001(BACKEND PROCS : QUERY_START)
, CASE WHEN (E.STATE = 'active' AND E.ELAPSED_TIME >= 0 ) THEN E.ELAPSED_TIME ELSE '0' END AS "ELAPSED_TIME" --PT_03_06_001(BACKEND PROCS : )
, E.SQL AS SQL1 --PT_03_06_001(BACKEND PROCS : SQL), PT_04_04_001(RESRCUTILPERBACK : SQL)
, E.ELAPSED_TIME
, CASE WHEN 0=0 THEN A.HOST_NAME ELSE A.CONN_NAME END AS HOST_NAME
, E.CURRENT_PROC_READ_KB + E.CURRENT_PROC_WRITE_KB AS "MEM_USAGE_KB"
, B.ACTV_REG_SEQ
, CD.CODE_NAME AS WAIT_EVENT
, E.QUERYID
FROM TB_INSTANCE_INFO A,
(SELECT MAX(REG_DATE) AS REG_DATE, MAX(ACTV_REG_SEQ) AS ACTV_REG_SEQ, INSTANCE_ID, MAX(REG_TIME) AS REG_TIME
FROM TB_ACTV_COLLECT_INFO
WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND ACTV_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 3 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM ACTV_REG_SEQ)
GROUP BY INSTANCE_ID) B, TB_BACKEND_RSC E, TB_SYS_CODE CD
WHERE B.REG_DATE = E.REG_DATE
AND A.INSTANCE_ID = B.INSTANCE_ID
AND B.ACTV_REG_SEQ = E.ACTV_REG_SEQ
AND A.INSTANCE_ID IN (SELECT INSTANCE_ID FROM PGSTA)
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
AND E.SQL != 'BACKGROUND PROC'
AND E.SQL IS NOT NULL
AND COALESCE(E.WAIT_EVENT, 200) = CD.CODE
ORDER BY E.ELAPSED_TIME DESC, E.PROC_CPU_UTIL DESC) BACKEND, (SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q --TB_QUERY_INFO Q
WHERE BACKEND.RNUM <= 1000
AND Q.INSTANCE_ID = BACKEND.INSTANCE_ID
AND Q.QUERYID = BACKEND.QUERYID
AND ELAPSED_TIME >= 0
order by instance_id, rnum
(SELECT DISTINCT instance_id, QUERYID, QUERY FROM TB_QUERY_INFO) Q to (SELECT DISTINCT QUERYID, QUERY FROM TB_QUERY_INFO)
------------------------SELECTREPLICATIONCURR
--
WITH MAXS AS (
SELECT INSTANCE_ID, MAX(REPL_REG_SEQ) REPL_REG_SEQ, MAX(COLLECT_DT) COLLECT_DT FROM TB_REPLICATION_INFO
WHERE REG_DATE = TO_CHAR(NOW(),'YYYYMMDD')
--AND INSTANCE_ID IN ({0})
AND COLLECT_DT >= (now() - ((5000/500)::varchar)::INTERVAL)
GROUP BY INSTANCE_ID
)
, MAXR AS
(
SELECT /* SELECTREPLICATIONCURR */
B.INSTANCE_ID,
C.REPL_INSTANCE_ID,
B.COLLECT_DT COLLECT_DT,
C.REPLAY_LAG REPLAY_LAG,
B.REPL_REG_SEQ REPL_REG_SEQ,
--C.REPL_REG_SEQ,
(C.REPLAY_LAG_SIZE / 1024 / 1024) AS REPLAY_LAG_SIZE
FROM TB_INSTANCE_INFO A, MAXS B JOIN TB_REPLICATION_LAG_INFO C
ON B.INSTANCE_ID = C.INSTANCE_ID
AND B.REPL_REG_SEQ = C.REPL_REG_SEQ
WHERE C.reg_date = TO_CHAR(NOW(),'YYYYMMDD')
and A.INSTANCE_ID = B.INSTANCE_ID
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
ORDER BY B.INSTANCE_ID, REPL_REG_SEQ
)
SELECT A.INSTANCE_ID INSTANCE_ID,
A.COLLECT_DT COLLECT_DT,
B.REPLAY_LAG REPLAY_LAG,
A.REPL_REG_SEQ REPL_REG_SEQ,
B.REPLAY_LAG_SIZE
FROM MAXS A LEFT OUTER JOIN MAXR B
ON A.INSTANCE_ID = B.REPL_INSTANCE_ID
---------------\
SELECT /* SELECTLOCKINFOACCUM */
MAX(C.COLLECT_DT) AS REG_DATE
,DB_NAME
,BLOCKING_USER
,BLOCKING_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND INSTANCE_ID IN (1) AND QUERYID = BLOCKING_QUERY LIMIT 1) BLOCKING_QUERY
,BLOCKED_USER
,BLOCKED_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND INSTANCE_ID IN (1) AND QUERYID = BLOCKED_QUERY LIMIT 1) BLOCKED_QUERY
,MAX(BLOCKED_DURATION) AS BLOCKED_DURATION
,LOCK_MODE
,QUERY_START
,XACT_START
,MAX(C.ACTV_REG_SEQ) AS ACTV_REG_SEQ
FROM TB_INSTANCE_INFO A, TB_ACTV_COLLECT_INFO B, TB_CURRENT_LOCK C
WHERE B.REG_DATE = '20210112'
AND B.REG_TIME BETWEEN '03:59:00' AND '04:01:00'
AND A.INSTANCE_ID = B.INSTANCE_ID
AND A.INSTANCE_ID IN (1)
AND B.REG_DATE = C.REG_DATE
AND B.ACTV_REG_SEQ = C.ACTV_REG_SEQ
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
GROUP BY A.INSTANCE_ID, B.REG_DATE, DB_NAME, BLOCKING_USER, XACT_START, blocking_pid, BLOCKING_QUERY, BLOCKED_USER, blocked_pid, BLOCKED_QUERY, LOCK_MODE, QUERY_START, ORDER_NO
ORDER BY B.REG_DATE, DB_NAME, blocking_pid, ORDER_NO, XACT_START LIMIT 1000;
SELECT A.INSTANCE_ID, MAX(A.REPL_REG_SEQ) MAX_REG_SEQ, ha_role
FROM TB_REPLICATION_INFO A
WHERE A.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND REPL_REG_SEQ >= (SELECT (LAST_VALUE - (SELECT COUNT(*) * 2 + 5 FROM TB_INSTANCE_INFO)) + 1 AS LAST_VALUE FROM REPL_REG_SEQ)
GROUP BY A.INSTANCE_ID, ha_role
)
SELECT C.INSTANCE_ID --PT_03_01_001
, COLLECT_REG_DATE AS REG_DATE
, B.COLLECT_REG_SEQ AS REG_SEQ
, C.HCHK_NAME AS HCHK_NAME
, CASE WHEN B.VALUE IS NULL THEN 0 ELSE ROUND(B.VALUE,2) END AS VALUE
, (CASE /* FIXED_THRESHOLD ='0' CRITICAL -> 300 WARNING -> 200 NORMAL -> 100 */
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 300
WHEN (B.VALUE > WARNING_THRESHOLD AND B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 200
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='0') THEN 100
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 100
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND B.VALUE < WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 200
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='0') THEN 300
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='1') THEN 200
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='1') THEN 100
WHEN (B.VALUE > WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='1') THEN 100
WHEN (B.VALUE <= WARNING_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='1') THEN 200
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='2') THEN 300
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='0' AND FIXED_THRESHOLD ='2') THEN 100
WHEN (B.VALUE >= CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='2') THEN 100
WHEN (B.VALUE < CRITICAL_THRESHOLD AND IS_HIGHER ='1' AND FIXED_THRESHOLD ='2') THEN 300
WHEN (FIXED_THRESHOLD ='9') THEN 100
WHEN B.VALUE IS NULL THEN 100
ELSE 999
END) HCHK_VALUE
FROM TB_HCHK_COLLECT_INFO B
RIGHT OUTER JOIN TB_HCHK_THRD_LIST C
ON B.INSTANCE_ID = C.INSTANCE_ID
AND B.HCHK_NAME = C.HCHK_NAME
AND B.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD')
AND B.HCHK_REG_SEQ = (SELECT MAX(X.HCHK_REG_SEQ) FROM TB_HCHK_COLLECT_INFO X WHERE X.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD'))
, TEMP_MAX_SEQ D
WHERE B.INSTANCE_ID IN (1);
select instance_id,hchk_name,unit,is_higher,warning_threshold,critical_threshold,fixed_threshold from tb_hchk_thrd_list thtl where instance_id = 1
SELECT /* SELECTLOCKINFOACCUM */
--A.INSTANCE_ID
MAX(C.COLLECT_DT) AS REG_DATE,
DB_NAME
,BLOCKING_USER
,BLOCKING_PID
,BLOCKING_QUERY
,BLOCKED_QUERY
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = '20220408' AND INSTANCE_ID IN (50) AND QUERYID = BLOCKING_QUERY LIMIT 1) BLOCKING_QUERY
,BLOCKED_USER
,BLOCKED_PID
,(SELECT query FROM TB_QUERY_INFO WHERE REG_DATE = '20220408' AND INSTANCE_ID IN (50) AND QUERYID = BLOCKED_QUERY LIMIT 1) BLOCKED_QUERY
,MAX(BLOCKED_DURATION) AS BLOCKED_DURATION
,LOCK_MODE
,QUERY_START
,XACT_START
,MAX(C.ACTV_REG_SEQ) AS ACTV_REG_SEQ
FROM TB_INSTANCE_INFO A, TB_ACTV_COLLECT_INFO B, TB_CURRENT_LOCK C
WHERE B.REG_DATE = '20220408'
AND B.REG_TIME between '2022-04-08 13:10' and '2022-04-08 13:15'
AND A.INSTANCE_ID = B.INSTANCE_ID
AND A.INSTANCE_ID IN (50)
AND B.REG_DATE = C.REG_DATE
AND B.ACTV_REG_SEQ = C.ACTV_REG_SEQ
AND A.COLLECT_YN = 'Y'
AND A.DELETE_YN = 'N'
GROUP BY A.INSTANCE_ID, B.REG_DATE, DB_NAME, BLOCKING_USER, XACT_START, blocking_pid, BLOCKING_QUERY, BLOCKED_USER, blocked_pid, BLOCKED_QUERY, LOCK_MODE, QUERY_START, ORDER_NO
-------------=-=-=-=-
SELECT p.pid, now() - a.xact_start AS duration, coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting, CASE WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound' WHEN a.query ~*'^vacuum' THEN 'user' ELSE 'regular' END AS mode, p.datname AS database, p.relid::regclass AS table, p.phase, pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned, pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed, round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct, round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct, p.index_vacuum_count, round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a using (pid) ORDER BY now() - a.xact_start DESC;
WITH IST AS (SELECT * FROM TB_INSTANCE_INFO WHERE COLLECT_YN = 'Y' AND DELETE_YN='N' AND INSTANCE_ID = 42)
,TTE AS (
SELECT O.INSTANCE_ID, O.REG_DATE, O.OBJT_REG_SEQ, CAST(MAX(T.MAXAGE) AS REAL) M1, MAX(T.COLLECT_DT) COLLECT_DT, relid
FROM IST,
TB_OBJT_COLLECT_INFO O,
TB_TABLE_EXT_INFO T
WHERE O.REG_DATE='20220621'
and O.REG_DATE=T.reg_date
--AND O.OBJT_REG_SEQ = 2583
AND O.OBJT_REG_SEQ = (SELECT MAX(X.OBJT_REG_SEQ) FROM TB_OBJT_COLLECT_INFO X WHERE X.REG_DATE = TO_CHAR(NOW(), 'YYYYMMDD') AND X.INSTANCE_ID=O.INSTANCE_ID )
AND O.OBJT_REG_SEQ = T.OBJT_REG_SEQ
AND O.INSTANCE_ID = IST.INSTANCE_ID
GROUP BY O.REG_DATE,O.OBJT_REG_SEQ,O.INSTANCE_ID, relid
order by m1 desc
limit 1
)
SELECT DB_NAME, M1::NUMERIC(12,0) FROZENMAXAGE, TTE.COLLECT_DT
FROM TTE, TB_TABLE_INFO TTI
WHERE TTE.REG_DATE = TTI.REG_DATE
AND TTE.OBJT_REG_SEQ = TTI.OBJT_REG_SEQ
and tte.relid = tti.relid
LIMIT 1
create table svc_mntg_alarm ( alarm_id varchar(5) not null , alarm_prop varchar(2000) not null , alarm_val varchar(2000) not null , alarm_cntt varchar(2000) , occ_dts timestamp with time zone );
COMMENT ON column svc_mntg_alarm.alarm_id IS '알람ID' ; COMMENT ON column svc_mntg_alarm.alarm_prop IS '알람항목' ; COMMENT ON column svc_mntg_alarm.alarm_val IS '알람값' ; COMMENT ON column svc_mntg_alarm.alarm_cntt IS '알람내용' ; COMMENT ON column svc_mntg_alarm.occ_dts IS '발생일시' ; COMMENT ON TABLE svc_mntg_alarm IS '서비스모니터링알람' ;
alter table svc_mntg_alarm add constraint svc_mntg_alarm_pk primary key (alarm_id);
temp