# Integration Performance Query for MySQL or PostgreSQL
###### tags: `research` `devops` `tutorials`
## Azure Setup MySQL-Flexible Server
**1. Configure slow querry logs**
Enable some require in Severparameter
--> Config it stay here **Server parameters**
**2. Setup dianostics**
* Have a note
- Create Log Analytics workspace
- Create a storage account
- Create a identity for access storage account for database for probally it can READ AND WRITE INTO Storage Account
- Create dianostics to pipe your log
**3. View Query insights by using Log Analytics**
Waiting for 10 min for get log from data sink storage accounnt and Analysis. But something is missing so for fix issue not log anything so we need config
- Server logs - Enable check
- Config Some another Server Parameter(***log_queries_not_using_indexes, log_slow_admin_statements, log_slow_slave_statements***, if ***log_output=None*** you can config it into ***log_output=File***)
**4. After that you can put the querry into log or using workingbook to view SlowQueryEvent**
- View Using Log Analystic throught Query of Azure 
- View through Workbook  
## Azure Setup PostgreSQL-Server
This is not have something slow query log configure, just collect and export throught .log file
**1. Setup server parameter**
- On default postgreSQL in Azure export Log and you need config some option for export the log [slow query](https://ubiq.co/database-blog/how-to-enable-slow-query-log-in-postgresql/)
- After setup the db config like
**2. Setup the storage and analytic**
- Live above and add new dianosic into PostgreSQL
**3. Query and see the result**
But no result is return and i see the something metric output in storage account
So maybe it can be thing i not handle it LOL. So skip this find the way we get the log into AKS cluster. So it just i not enable the qs view for return on PostgreSQL and everything can monitoring by QuerryStore and can analytic via Azure Analytic workspace.
## Deploy AKS and Test with some datasource
**1. Directly and Ajust throught identity of Cluster**
- Not available because K8s just work with internal pod and don't make sense with another resource
- So aftertime google --> I findout the new method Azure release for do assigning identity for pod in k8s. See down below next session
**2. Directly and Ajust throught pod-identity of cluster**
- That is quite a lack of sentence, idk how we give identity for resource is not Azure thing.
- Start from, i find something datasource for grafana can access and query information of Azure by Azure Monitor
- With Azuremonitor we can do so much thing with Azure Cloud like
- See Metric collect from account
- Azure Monitoring Log: It quite important for situation for get the log from PostgreSQL or MySQL
- Get the graph: This quite to visuallize we can get it from cloud
- But hold on this datasource need IDENTITY and have 2 way to do it
**1. CONFIGURE AD AUTHENTICATION**
**2. CONFIGURE MANAGED IDENTITY**
But not thing easy, the method just configured for VM, VMSS or AppService and Cluster is not.
In situation we just got cluster and pod inside particular Grafana but not identity to import datasource. But luckily, Azure just release a smt call **Pod-Identity Concept** and to reproduce this u can click [Youtube Video](https://www.youtube.com/watch?v=R6XNXua-jzQ) or [Azure Doc](https://learn.microsoft.com/en-us/azure/aks/use-azure-ad-pod-identity)
So let reproduce with my situation
1. Create AKS or just use exist aks
2. Update or enable new concept for cluster like for me update feature for exist K8s
```
az aks update \
> -g $MY_RESOURCE_GROUP \
> -n $MY_CLUSTER \
> --enable-pod-identity\
> --enable-pod-identity-with-kubenet
```
So after apply you will get the different, pod-identity is enabled in AKS
3. Create Managed Identity for add into pod with role contributor for storage account
4. So add into cluster
```
az aks pod-identity add \
> --resource-group myResourceGroup --cluster-name myAKSCluster \
> --namespace ${POD_IDENTITY_NAMESPACE} --name ${POD_IDENTITY_NAME} \
> --identity-resource-id ${IDENTITY_RESOURCE_ID}
```
And wait it to succeed but yesterday i meet the trouble in here and not grant permission to cluster
So it just the problem i can not grant permission in higher role because sometime idk what much role i had. So after add this to pod --> We can grant pod can association with Azure Resource thorugh idenity assign
==> What should we do --> Find a way to create a AAD and Assign it for me because i stay in another subscription and don't make anything do if refer to old AAD Or work with new concept **Workload Identity**.
**3. Directly and Ajust throught workload-identity of cluster**
- It work for identity but it just for own yalm and not pass through helm because it releate something about label and anotation for pods run with right workflow
- So in my situation the grafana using like assemble thing so it not work and on some circumstances we must to change whole thing on values come from helm chart --> That not pretty good
- But that is nice concept for doing project from start point, cool stuff !!!
- The concept and reproduce come from below link so --> [:link:](https://learn.microsoft.com/en-us/azure/aks/learn/tutorial-kubernetes-workload-identity)
**4. The another way i think it should work is using the resource from azure and it call Microsoft Sentinal and WorkBook**
- So give a shout out for guy from [charbelnemnom](https://charbelnemnom.com/how-to-import-export-and-share-workbooks-in-azure-sentinel/) for comming with pretty good solution.
- For my situation, we not need care sentinal which take care bunch of thing about security, audit, Mitre(CVE), Threat Intellegent,...We will focus **workbook** and it quite easy to using with it, one thing we need is wait and analysis the log by KQL Language for Query Data come from analytics of Azure.
- So it quite easy, you can do it with what ever analytic with any resource you want to get metric, diagnostic, ...
- With KQL like this
```
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where user_id_s != "10"
| where OperationName == "QueryStoreRuntimeStatisticsEvent"
| project TimeGenerated, OperationName, user_id_s, query_id_s, todouble(min_time_s), todouble(max_time_s), toint(calls_s)
```
and this what you got
So you can do anything with another design like a chart, grid, ... like this
and in the end you just save anything workbook and bring share workbook for people get the role to access this workbook and workbook is share through Microsoft Sentinal so take care authentication for this
Or some stuff base on time generate time 3 day ago and get count 5m for this time when generate
```
AzureDiagnostics
| where Category == "QueryStoreRuntimeStatistics"
| where todouble(mean_time_s) >= 10.0
| where TimeGenerated >= ago(3d)
| project TimeGenerated, user_id_s, query_id_s, mean_time_s
| summarize SlowQueries=count() by bin(TimeGenerated,5m), user_id_s, query_id_s
```
## **Updating for new !!!!**
So after working on stuff about PostgreSQL on last couple week ago, I Have learn some new technical for detection the why the slow and help you detect it via grafana
- So easily you can monitoring that kind by use a postgres datasource on grafana
- After you go to that, create a new resource by clicking add new datasource and search postgres
- You just need to filling up by empty or what it need `host` `databasename` `user` `password`, remember database you need configure to `azure_sys` that will help you find out the table contain helpful for monitoring progress
- Go back a portal azure and turn on some parameter help you can use this datasource postgress inside the Grafana
* pg_qs.query_capture_mode (Default: None). Choose one of the TOP or ALL (recommendation TOP for reducing not necessary information). The options will be give me information about what long queries, Top queries by call, ...
* pgms_wait_sampling.query_capture_mode (Default: None). Set the value to ALL for get the queries about the Wait Statistics of DB
* shared_preload_libraries parameters for configuration pg_stat_statements extension. More detail about that kind in [Link](https://www.postgresql.org/docs/current/pgstatstatements.html#:~:text=F.32.-,pg_stat_statements,-F.32.1.%20The) and [Official document Azure](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions). `Notice: For default this extension is not enabled in PostgreSQL and it will be disabled when you want config if default account_admin setup on provisioning process is used`
* `azure.extensions` with some situation you need to turn on `pg_stat_statements` on this param, IDK but if you create extension fail, you need to do that
- After paramerter is configured, you need to go to the postgreSQL for enable pg_stat_statements extension inside through connection_string with azure_sys is choosen for selection Database. Example:
```
psql -h <database_location> -U <username> -d azure_sys
```
- Type the password for connection and doing the queries after that:
```
CREATE EXTENSION pg_stat_statements;
```
- For check the exetension is actually working, just doing some queries:
```
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
```
- If result is return, your configuration will actually work successfully
- So before you write a some scenerio for queries what you want you need to understand
* pg_stat_statement: [Offical Documentation](https://www.postgresql.org/docs/current/pgstatstatements.html)
* pg_stat_activity: [Official Documentation](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW:~:text=28.2.3.-,pg_stat_activity,-The%20pg_stat_activity%20view) , [Detail Explaining](https://www.depesz.com/2022/07/05/understanding-pg_stat_activity/)
- So that all you can import my optimize dashboard and get the result
```
{
"__inputs": [
{
"name": "DS_POSTGRESQL",
"label": "PostgreSQL",
"description": "",
"type": "datasource",
"pluginId": "postgres",
"pluginName": "PostgreSQL"
}
],
"__elements": {},
"__requires": [
{
"type": "panel",
"id": "barchart",
"name": "Bar chart",
"version": ""
},
{
"type": "panel",
"id": "bargauge",
"name": "Bar gauge",
"version": ""
},
{
"type": "panel",
"id": "gauge",
"name": "Gauge",
"version": ""
},
{
"type": "grafana",
"id": "grafana",
"name": "Grafana",
"version": "9.1.7"
},
{
"type": "panel",
"id": "piechart",
"name": "Pie chart",
"version": ""
},
{
"type": "datasource",
"id": "postgres",
"name": "PostgreSQL",
"version": "1.0.0"
},
{
"type": "panel",
"id": "table",
"name": "Table",
"version": ""
}
],
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": {
"type": "grafana",
"uid": "-- Grafana --"
},
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"target": {
"limit": 100,
"matchAny": false,
"tags": [],
"type": "dashboard"
},
"type": "dashboard"
}
]
},
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
"id": null,
"links": [],
"liveNow": false,
"panels": [
{
"collapsed": true,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 0
},
"id": 47,
"panels": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Long-running transactions can consume CPU resources that can lead to high CPU utilization.",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 7,
"w": 11,
"x": 0,
"y": 1
},
"id": 45,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT pid, usename, datname, query, now() - xact_start as duration \nFROM pg_stat_activity \nWHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active') \nORDER BY duration DESC; ",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Long-running transactions",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Helps identify queries that consume I/O on the server",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 7,
"w": 13,
"x": 11,
"y": 1
},
"id": 53,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT userid::regrole, dbid, query, calls\nFROM pg_stat_statements \nORDER BY blk_read_time + blk_write_time desc \nLIMIT 5;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": " Identify high I/O utilization",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "A large number of connections to the database is also another issue that might lead to increased CPU and memory utilization.\n\n",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlPu"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 6,
"x": 0,
"y": 8
},
"id": 49,
"options": {
"displayMode": "gradient",
"minVizHeight": 10,
"minVizWidth": 0,
"orientation": "horizontal",
"reduceOptions": {
"calcs": [],
"fields": "",
"values": true
},
"showUnfilled": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT state, count(*) \nFROM pg_stat_activity \nWHERE pid <> pg_backend_pid() \nGROUP BY 1 ORDER BY 1;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Total number of connections and number connections by state",
"transparent": true,
"type": "bargauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Keeping table statistics up to date helps improve query performance. Monitor whether regular autovacuuming is being carried out.",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 18,
"x": 6,
"y": 8
},
"id": 51,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select schemaname,relname,n_dead_tup,n_live_tup,last_vacuum,last_analyze,last_autovacuum,last_autoanalyze \nfrom pg_stat_all_tables where n_live_tup > 0;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Monitoring vacuum and table stats",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "It provides a resultset sorted by the mist CPU-intensive queries in descending order",
"fieldConfig": {
"defaults": {
"color": {
"mode": "thresholds"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 10,
"w": 24,
"x": 0,
"y": 17
},
"id": 55,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "Select pss.userid, pss.dbid, pd.datname as db_name, pss.total_time,\n pss.calls,\n pss.mean_time as mean,\n round((100 * (pss.total_time) / sum ((pss.total_time)::numeric) OVER ()) :: numeric, 2) as cpu_portion_pctg, pss.query\nFrom pg_stat_statements as pss, pg_database as pd\nwhere pd.oid = pss.dbid\norder by pss.total_time\ndesc limit 30;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "How much each query in each database uses the CPU",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "the top frequent PostgreSQL queries run the following SQL query ",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 10,
"w": 24,
"x": 0,
"y": 27
},
"id": 57,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": [
{
"desc": true,
"displayName": "runs_per_second"
}
]
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "with\na as (select dbid, queryid, query, calls s from pg_stat_statements),\nb as (select dbid, queryid, query, calls s from pg_stat_statements, pg_sleep(1))\nselect\n pd.datname as db_name, \n substr(a.query, 1, 2048) as the_query, \n sum(b.s-a.s) as runs_per_second\nfrom a, b, pg_database pd\nwhere \n a.dbid= b.dbid \nand \n a.queryid = b.queryid \nand \n pd.oid=a.dbid\ngroup by 1, 2\norder by 3 desc;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": " Running frequent SQL queries",
"transparent": true,
"type": "table"
}
],
"title": "Identify root causes (Use if has accident)",
"type": "row"
},
{
"collapsed": false,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 1
},
"id": 29,
"panels": [],
"title": "Database Info and Special Info",
"type": "row"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Provide the size of database postgreSQL in currently",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"unit": "MB"
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 9,
"x": 0,
"y": 2
},
"id": 31,
"options": {
"displayMode": "lcd",
"minVizHeight": 10,
"minVizWidth": 0,
"orientation": "horizontal",
"reduceOptions": {
"calcs": [],
"fields": "",
"values": true
},
"showUnfilled": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n datname as database_name,\n pg_database_size(datname)/1024/1024 as size\nFROM\n pg_database\nWHERE\n datistemplate = false;\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Size of a PostgreSQL Database",
"transparent": true,
"type": "bargauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "The PostgreSQL cache area is known as shared_buffers, which acts as the database layer cache on top of the cache provided by the operating system",
"fieldConfig": {
"defaults": {
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green",
"value": null
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 15,
"x": 9,
"y": 2
},
"id": 33,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": false
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n sum(heap_blks_read) as reads,\n sum(heap_blks_hit) as hits,\n ROUND(\n sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)),\n 4\n ) as hit_ratio\nFROM\n pg_statio_user_tables;\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Database Cache-Hit Ratios",
"transparent": true,
"type": "gauge"
},
{
"collapsed": false,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 11
},
"id": 2,
"panels": [],
"title": "Statistic Queries",
"type": "row"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Total time of queries, There one queries give for running on whole system",
"fieldConfig": {
"defaults": {
"color": {
"mode": "palette-classic"
},
"custom": {
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
}
},
"mappings": []
},
"overrides": [
{
"__systemRef": "hideSeriesFrom",
"matcher": {
"id": "byNames",
"options": {
"mode": "exclude",
"names": [
"percent",
"total",
"calls",
"mean",
"stddev_time",
"UPDATE \"PlatformInboxEventBusMessage\" SET \"ConcurrencyUpdateToken\" = $1, \"ConsumeStatus\" = $2, \"ConsumerBy\" = $3, \"CreatedDate\" = $4, \"JsonMessage\" = $5, \"LastConsumeDate\" = $6, \"LastConsumeError\" = $7, \"MessageTypeFullName\" = $8, \"NextRetryProcessAfter\" = $9, \"ProduceFrom\" = $10, \"RetriedProcessCount\" = $11, \"RoutingKey\" = $12\nWHERE \"Id\" = $13 AND \"ConcurrencyUpdateToken\" = $14 percent",
"WITH \"s\" AS (\n INSERT INTO \"hangfire\".\"state\" (\"jobid\", \"name\", \"reason\", \"createdat\", \"data\")\n VALUES ($1, $2, $3, $4, $5) RETURNING \"id\"\n )\n UPDATE \"hangfire\".\"job\" \"j\"\n SET \"stateid\" = s.\"id\", \"statename\" = $2\n FROM \"s\"\n WHERE \"j\".\"id\" = $6 percent",
"SELECT t0.\"Id\", t0.\"HasInactiveParent\", t0.\"HierarchyPath\", t0.\"Name\", t0.\"OrganizationStatus\", t0.\"ParentOrganizationalUnitId\", t0.\"RootId\", t1.\"Id\", t1.\"CompanyId\", t1.\"CreatedBy\", t1.\"CreatedDate\", t1.\"LastUpdatedBy\", t1.\"LastUpdatedDate\", t1.\"TimeZone\", t1.\"AutoLock_IsEnable\", t1.\"AutoLock_LastCheckToTriggerLockTimeSheetCycle\", t1.\"AutoLock_LockTime\", t1.\"AutoLock_SendAfterDays\", t1.\"CycleSetting_EndDay\", t1.\"CycleSetting_IsLastDay\", t1.\"CycleSetting_NumberOfDisplay\", t1.\"CycleSetting_StartDay\", t1.\"Notification_LockNotification\", t1.\"Notification_UnLockNotification\"\nFROM (\n SELECT o.\"Id\", o.\"HasInactiveParent\", o.\"HierarchyPath\", o.\"Name\", o.\"OrganizationStatus\", o.\"ParentOrganizationalUnitId\", o.\"RootId\"\n FROM (\n SELECT NULL AS empty\n ) AS e\n LEFT JOIN \"OrganizationalUnit\" AS o ON TRUE\n WHERE (o.\"Id\" = o.\"RootId\" OR ((o.\"Id\" IS NULL) AND (o.\"RootId\" IS NULL))) AND NOT (EXISTS (\n SELECT 1\n FROM \"TimeSheetCycle\" AS t\n WHERE (o.\"Id\" IS NOT NULL) AND o.\"Id\" = t.\"CompanyId\" AND t.\"EndDate\" >= $1 AND t.\"StartDate\" <= $1))\n ORDER BY o.\"Id\"\n LIMIT $2\n) AS t0\nLEFT JOIN \"TimeSheetSetting\" AS t1 ON t0.\"Id\" = t1.\"CompanyId\"\nORDER BY t0.\"Id\", t1.\"Id\" total"
],
"prefix": "All except:",
"readOnly": true
}
},
"properties": [
{
"id": "custom.hideFrom",
"value": {
"legend": false,
"tooltip": false,
"viz": true
}
}
]
}
]
},
"gridPos": {
"h": 10,
"w": 9,
"x": 0,
"y": 12
},
"id": 12,
"options": {
"displayLabels": [
"value"
],
"legend": {
"displayMode": "list",
"placement": "right",
"showLegend": false,
"values": []
},
"pieType": "donut",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "/^total$/",
"values": true
},
"tooltip": {
"mode": "single",
"sort": "none"
}
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select round(total_time::numeric, 2) as total,\n calls,\n round(mean_time::numeric, 2) as mean,\n stddev_time,\n query_sql_text as query\nfrom query_store.qs_view\nwhere (now() - start_time) <= interval '7 days'\nand query_sql_text not ilike '%query_store%'\norder by total_time DESC\nlimit 10;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Top 10 most of time in total queries last 7 days",
"transformations": [],
"transparent": true,
"type": "piechart"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Chart will show the top 10 of slowest queries last 7 days",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 10,
"w": 15,
"x": 9,
"y": 12
},
"id": 17,
"options": {
"footer": {
"enablePagination": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select db_id as database_id, pd.datname, CASE\n WHEN query_sql_text ILIKE 'SELECT%' OR query_sql_text ILIKE '%\\n%SELECT%' THEN 'SELECT'\n WHEN query_sql_text ILIKE 'INSERT%' OR query_sql_text ILIKE '%\\n%INSERT%' THEN 'INSERT'\n WHEN query_sql_text ILIKE 'UPDATE%' OR query_sql_text ILIKE '%\\n%UPDATE%' THEN 'UPDATE'\n WHEN query_sql_text ILIKE 'DELETE%' OR query_sql_text ILIKE '%\\n%DELETE%' THEN 'DELETE'\n ELSE 'OTHER'\n END AS query_type, query_sql_text, calls, (total_time/calls)::integer AS avg_time_ms\nfrom query_store.qs_view, pg_database as pd\nwhere db_id = pd.oid and calls > 1000 and query_sql_text not ILIKE '%query_store%'\nand (now() - start_time) <= interval '7 days'\nORDER BY avg_time_ms DESC\nLIMIT 10;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "The 10 slowest queries with over a 1000 calls last 7 days",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Amounts of queries with running longer than 5 minutes last 7 days",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 9,
"x": 0,
"y": 22
},
"id": 27,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT count(*)\nFROM pg_stat_activity\nWHERE (now() - pg_stat_activity.query_start) > interval '5 seconds' \nAND (now() - pg_stat_activity.query_start) <= interval '7 days'\nAND datname NOT ILIKE 'azure_sys';",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Total queries running longer than 5 sec last 7 days",
"transparent": true,
"type": "gauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "This chart tell us about what queries is running longer than 5 sec on all database.\n\n#### Please use the *query_type* filter on head of table for get type of queries you want.",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-GrYlRd"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 15,
"x": 9,
"y": 22
},
"id": 14,
"options": {
"footer": {
"enablePagination": false,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n pid,\n datname as database,\n user,\n CASE\n WHEN query ILIKE 'SELECT%' OR query ILIKE '%\\n%SELECT%' THEN 'SELECT'\n WHEN query ILIKE 'INSERT%' OR query ILIKE '%\\n%INSERT%' THEN 'INSERT'\n WHEN query ILIKE 'UPDATE%' OR query ILIKE '%\\n%UPDATE%' THEN 'UPDATE'\n WHEN query ILIKE 'DELETE%' OR query ILIKE '%\\n%DELETE%' THEN 'DELETE'\n ELSE 'OTHER'\n END AS query_type,\n pg_stat_activity.query_start,\n now() - pg_stat_activity.query_start AS query_time,\n query,\n state,\n wait_event_type,\n wait_event\nFROM pg_stat_activity\nWHERE (now() - pg_stat_activity.query_start) > interval '5 seconds' \nAND (now() - pg_stat_activity.query_start) <= interval '7 days'\nAND datname NOT ILIKE 'azure_sys';",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Detail queries running longer than 5 sec on last 7 days",
"transparent": true,
"type": "table"
},
{
"collapsed": false,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 31
},
"id": 6,
"panels": [],
"title": "Live Queries",
"type": "row"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Querying to Check PostgreSQL Open Connections .You need to consider about:\n\n**backend_type** - *Type of current backend*. Possible types are: \n\n“Client backend\" is basically your normal connection from application. All other connections are special processes that “do stuff\":\n\n- archiver – runs archive_command (or it's equivalent in newer pgs)\n- autovacuum launcher – starts autovacuum workers when it's necessary to vacuum/analyze something\n- background writer – writes data to tables/indexes in the background\n- checkpointer handles checkpoints – basically every now and then writing all modified data to table/index files\n- logical replication launcher – manages processes related to logical replication\n- walwriter – writes changes in data to WAL\n\nAside from these you can also see:\n\n- autovacuum worker – actually does some vacuum/analyze work for autovacuum\n- logical replication worker – works on logical replication\n- parallel worker – special backend started by client backend where query is being processes in parallel\n- startup – applies WAL from somewhere, this is the process that is responsible for recovery and streaming/wal replication\n- walreceiver – receives wal stream from source in streaming replication setups\n- walsender – sends wal via stream to some replica\n\nYou can also see some other types if you use specialized extensions.\n\n*For more detail go to this [link](https://www.depesz.com/2022/07/05/understanding-pg_stat_activity/#:~:text=Last%20column%20is%20backend_type)*\n",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [
{
"options": {
"": {
"color": "super-light-yellow",
"index": 0
}
},
"type": "value"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
},
"unit": "none"
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 7,
"x": 0,
"y": 32
},
"id": 8,
"options": {
"displayMode": "gradient",
"minVizHeight": 10,
"minVizWidth": 0,
"orientation": "horizontal",
"reduceOptions": {
"calcs": [],
"fields": "/^connections$/",
"values": true
},
"showUnfilled": true,
"text": {}
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT\n COUNT(*) as connections,\n backend_type\nFROM\n pg_stat_activity\nGROUP BY\n backend_type\nORDER BY\n connections DESC",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Open Connections",
"transformations": [],
"transparent": true,
"type": "bargauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 5,
"x": 7,
"y": 32
},
"id": 23,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT count(*)\n FROM pg_stat_activity\n WHERE query NOT ILIKE '%pg_stat_activity%' and datname NOT ILIKE '%azure_sys%' and application_name != '';",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Total processes of DB",
"transparent": true,
"type": "gauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "This chart tell about the what active is current running. \nSome of information need to consider:\n\n**State** - *Current overall state of this backend*. Possible values are\n\n- active: The backend is executing a query.\n- idle: The backend is waiting for a new client command.\n- idle in transaction: The backend is in a transaction, but is not currently executing a query.\n- idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.\n- fastpath function call: The backend is executing a fast-path function.\n- disabled: This state is reported if track_activities is disabled in this backend.\n\n*For more detail go to this [link](https://www.postgresql.org/docs/current/monitoring-stats.html#:~:text=Table%C2%A028.13.-,state,-text)*\n\n**Database** - 'some special db you have seen in table chart'\n- *postgres* - A default database you can connect to once your server is created.\n- *azure_maintenance* - This database is used to separate the processes that provide the managed service from user actions. You do not have access to this database.\n- *azure_sys* - A database for the Query Store. This database does not accumulate data when Query Store is off; this is the default setting. For more information, see the [Query Store overview](https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/postgresql/single-server/concepts-query-store.md).",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"custom": {
"align": "auto",
"displayMode": "auto",
"filterable": true,
"inspect": true,
"minWidth": 50
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 12,
"x": 12,
"y": 32
},
"id": 10,
"options": {
"footer": {
"enablePagination": true,
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true,
"sortBy": []
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT datname as Database, pid, application_name, state, query, backend_type\n FROM pg_stat_activity\n WHERE query NOT ILIKE '%pg_stat_activity%' and datname NOT ILIKE '%azure_sys%' and application_name != '';",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Process in currently of DB",
"transparent": true,
"type": "table"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Current infomation about state of process in DB",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
"axisLabel": "",
"axisPlacement": "auto",
"fillOpacity": 67,
"gradientMode": "hue",
"hideFrom": {
"legend": false,
"tooltip": false,
"viz": false
},
"lineWidth": 4,
"scaleDistribution": {
"type": "linear"
}
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
},
"unit": "short"
},
"overrides": [
{
"matcher": {
"id": "byName",
"options": "count"
},
"properties": [
{
"id": "color",
"value": {
"fixedColor": "blue",
"mode": "fixed"
}
}
]
}
]
},
"gridPos": {
"h": 7,
"w": 7,
"x": 4,
"y": 41
},
"id": 37,
"options": {
"barRadius": 0.25,
"barWidth": 0.06,
"groupWidth": 0.7,
"legend": {
"calcs": [],
"displayMode": "list",
"placement": "bottom",
"showLegend": true
},
"orientation": "auto",
"showValue": "never",
"stacking": "none",
"tooltip": {
"mode": "single",
"sort": "none"
},
"xField": "state",
"xTickLabelRotation": 0,
"xTickLabelSpacing": 100
},
"pluginVersion": "9.7.1",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT state, COUNT(*) FROM pg_stat_activity \nWHERE pid <> pg_backend_pid() AND query NOT ILIKE '%pg_stat_activity%' and datname NOT ILIKE '%azure_sys%' and application_name != ''\nGROUP BY 1;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "State of process in DB",
"transparent": true,
"type": "barchart"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Currently about the number of application_name in DB. Some situation about the chart:\n- If it not exist `application_name`. The `count` column will represent for it.\n- The chart will focus visuallize the `application_name` connect what database in `currently` process.",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlPu"
},
"mappings": [],
"noValue": "Unknown",
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 7,
"w": 9,
"x": 12,
"y": 41
},
"id": 39,
"options": {
"displayMode": "gradient",
"minVizHeight": 10,
"minVizWidth": 0,
"orientation": "horizontal",
"reduceOptions": {
"calcs": [],
"fields": "",
"values": true
},
"showUnfilled": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT application_name, COUNT(*) FROM pg_stat_activity \nWHERE pid <> pg_backend_pid() and query NOT ILIKE '%pg_stat_activity%' and datname NOT ILIKE '%azure_sys%' and application_name != ''\nGROUP BY 1 ORDER BY 1;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Application_name on process in DB",
"transparent": true,
"type": "bargauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Number of connection have blocking by lock",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 8,
"x": 0,
"y": 48
},
"id": 35,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT count(distinct pid) FROM pg_locks WHERE granted = false;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Connections waiting for a lock",
"transparent": true,
"type": "gauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Lots of long-running queries can cause your database to be slow or non-responsive.",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 8,
"x": 8,
"y": 48
},
"id": 19,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT count(*) \nFROM pg_stat_activity \nWHERE state != 'idle' \nAND query_start < (NOW() - INTERVAL '60 seconds');",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Totals queries have been running for longer than a minute",
"transparent": true,
"type": "gauge"
},
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Too many write queries to the same (especially large) table can cause lock-contention.",
"fieldConfig": {
"defaults": {
"color": {
"mode": "continuous-BlYlRd"
},
"mappings": [],
"thresholds": {
"mode": "percentage",
"steps": [
{
"color": "green"
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 8,
"w": 8,
"x": 16,
"y": 48
},
"id": 21,
"options": {
"orientation": "auto",
"reduceOptions": {
"calcs": [
"lastNotNull"
],
"fields": "",
"values": false
},
"showThresholdLabels": false,
"showThresholdMarkers": true
},
"pluginVersion": "9.1.7",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT count(*) FROM pg_stat_activity\nWHERE state != 'idle'\nAND query NOT ILIKE '%SELECT%'\nAND query ILIKE '%some_big_table%'\nAND query NOT ILIKE '%pg_stat_activity%';",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Total queries are currently writing to the same table",
"transparent": true,
"type": "gauge"
},
{
"collapsed": true,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
"y": 56
},
"id": 43,
"panels": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"description": "Choose the ID in search box variable for get the query",
"fieldConfig": {
"defaults": {
"custom": {
"align": "auto",
"displayMode": "auto",
"inspect": false
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green"
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 9,
"w": 24,
"x": 0,
"y": 56
},
"id": 41,
"options": {
"footer": {
"fields": "",
"reducer": [
"sum"
],
"show": false
},
"showHeader": true
},
"pluginVersion": "9.7.1",
"targets": [
{
"datasource": {
"type": "postgres",
"uid": "${DS_POSTGRESQL}"
},
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id='$query_current_id';\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"title": "Get query from ID",
"transparent": true,
"type": "table"
}
],
"title": "Querry Searching",
"type": "row"
}
],
"refresh": "1m",
"schemaVersion": 37,
"style": "dark",
"tags": [],
"templating": {
"list": [
{
"current": {
"selected": false,
"text": "",
"value": ""
},
"description": "ID for select what query text",
"hide": 0,
"label": "Query ID",
"name": "query_current_id",
"options": [
{
"selected": true,
"text": "",
"value": ""
}
],
"query": "",
"skipUrlSync": false,
"type": "textbox"
}
]
},
"time": {
"from": "now-5m",
"to": "now"
},
"timepicker": {},
"timezone": "",
"title": "Azure PostgreSQL Queries",
"uid": "arCsn36Vk",
"version": 14,
"weekStart": ""
}
```
- This Dashboard will have 4 Rows for explain including:
1. Database info and Special Info: Size of the database, Cache-Hit ratios of PostgreSQL
2. Statistic Queries: Statistic about infomation of queries like total time spent, slowest queries, ...
3. Live Queries: Statistic about the live process and queries have working on current database
4. Querry Searching: Location for view what the query which you want to find
- Tip for use this dashboard
1. You can have close and open the rows for minimize what you want to finding
2. With each of row, on the each dashboard if you have seen the symbol, so you can hold into that for view description about that one
3. With the pie chart on dashboard, you can hold part of edge pie to get more infomation about the query have that value
4. With the table chart on dashboard, each header of column will have the filter to determine what type of value you want and you click symbol on the each cell for see details about that cell value
5. One of lasthing to consider about, It the Query ID box for get the selected with you query you can want to inspect
## Conclusion
So what we are having in this stuff :stuck_out_tongue: for collection and have vision about identity for k8s in azure. It not easy like i think so work hard and update the new solution or also may be we can discuss the topic in as soon feature. I hope this article bring you some cool stuff idk What ever drop the comment below and help me update it. Thanks you !!! Good luck for Operation, Hacking and besafe !!!. Adittional, Happily with my dashboard :P
## Reference
* [Log Analytics tutorial](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-tutorial)
* [Query Performance Insight for Azure Database for MySQL - Flexible Server](https://learn.microsoft.com/en-us/azure/mysql/flexible-server/tutorial-query-performance-insights#set-up-diagnostics)
* [Slow query logs in Azure Database for MySQL - Flexible Server](https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-slow-query-logs#configure-slow-query-logging)
* [PostgreSQL-Query Performance Insight](https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-query-performance-insight)
* [Detect postgresql](https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/)
* [Using AAD Pod Identity in your Azure Kubernetes Clusters — what to watch out for!](https://medium.com/itnext/using-aad-pod-identity-in-your-azure-kubernetes-clusters-what-to-watch-out-for-73d5d73960f)
* [The Trustworthy Computing Security Development Lifecycle](https://learn.microsoft.com/en-us/previous-versions/ms995349(v=msdn.10))
* [Pod-Identity Concept](https://learn.microsoft.com/en-us/azure/aks/use-azure-ad-pod-identity)
* [AKS Review - 2.1: Identity & Access Control - Cluster, Operator & Pod Identity](https://techcommunity.microsoft.com/t5/fasttrack-for-azure/aks-review-2-1-identity-amp-access-control-cluster-operator-amp/ba-p/3716906)
* [Azure AD Domain Services documentation](https://learn.microsoft.com/en-us/azure/active-directory-domain-services/tutorial-create-instance)
* [Microsoft Entra Workload Identities documentation](https://learn.microsoft.com/en-us/azure/active-directory/workload-identities/)
* [Tutorial: Use a workload identity with an application on Azure Kubernetes Service (AKS)](https://learn.microsoft.com/en-us/azure/aks/learn/tutorial-kubernetes-workload-identity)
* [Diagnostic settings in Azure Monitor](https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/diagnostic-settings?WT.mc_id=Portal-Microsoft_Azure_Monitoring&tabs=portal)
* [Monitor performance with the Query Store](https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-query-store)
* [KQL](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/)
* [Microsoft Sentinal](https://learn.microsoft.com/en-us/azure/sentinel/quickstart-onboard#enable-azure-sentinel-)
* [Create a Log Analytics workspace](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/quick-create-workspace?WT.mc_id=charbelnemnom.com-blog-cnemnom&tabs=azure-portal)
* [Roles and permissions in Microsoft Sentinel](https://learn.microsoft.com/en-us/azure/sentinel/roles)
* [Best practices for managing the Query Store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms)
* [Enable query logging in Azure database for PostgreSQL](https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/enable-query-logging-in-azure-database-for-postgresql/ba-p/3639626)
* [Dealing With Slow Queries With PostgreSQL](https://pgdash.io/blog/slow-queries-postgres.html)
* [3 ways to detect slow queries in PostgreSQL](https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/)
* [Log data ingestion time in Azure Monitor](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/data-ingestion-time)
Sign in with Wallet
Connect another wallet