Skip to content

Instantly share code, notes, and snippets.

@pi0neerpat

pi0neerpat/postgres.json

Created August 11, 2020 19:46
Show Gist options
  • Save pi0neerpat/9e9e2356b2e7db37e05173e03fa9a662 to your computer and use it in GitHub Desktop.
Save pi0neerpat/9e9e2356b2e7db37e05173e03fa9a662 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres.json
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": "-- Grafana --",
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"editable": true,
"gnetId": null,
"graphTooltip": 0,
"id": 3,
"links": [],
"panels": [
{
"columns": [],
"datasource": "postgres",
"fontSize": "100%",
"gridPos": {
"h": 8,
"w": 24,
"x": 0,
"y": 0
},
"id": 4,
"links": [],
"pageSize": null,
"scroll": true,
"showHeader": true,
"sort": {
"col": 1,
"desc": true
},
"styles": [
{
"alias": "Age",
"align": "auto",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"pattern": "age",
"type": "number",
"unit": "s"
},
{
"alias": "",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "/.*/",
"thresholds": [],
"type": "number",
"unit": "short"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "-- grafana ignore\nselect application_name,\n extract(epoch from age(now(), xact_start)) as age,\n query from pg_stat_activity\n where query not like '%grafana ignore%'\n and state='active'\norder by query_start desc",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Active Queries",
"transform": "table",
"type": "table"
},
{
"columns": [],
"datasource": "postgres",
"fontSize": "100%",
"gridPos": {
"h": 5,
"w": 13,
"x": 0,
"y": 8
},
"id": 9,
"links": [],
"pageSize": null,
"pluginVersion": "6.2.1",
"scroll": true,
"showHeader": true,
"sort": {
"col": 1,
"desc": false
},
"styles": [
{
"alias": "",
"align": "auto",
"dateFormat": "YYYY-MM-DD",
"pattern": "last_autovacuum",
"type": "date"
},
{
"alias": "tx left",
"align": "auto",
"colorMode": "row",
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": null,
"pattern": "tx_before_wraparound_vacuum",
"thresholds": [
"20000000",
"40000000"
],
"type": "number",
"unit": "short"
},
{
"alias": "",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 2,
"mappingType": 1,
"pattern": "xid_age",
"thresholds": [],
"type": "number",
"unit": "short"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "-- grafana ignore\nSELECT\n relname as table,\n least((SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') - age(relfrozenxid), \n (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid))\n tx_before_wraparound_vacuum,\n pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,\n age(relfrozenxid) AS xid_age,\n mxid_age(relminmxid) AS mxid_age\nFROM\n pg_class c\nWHERE\n c.relname in ('ethereum_blocks', 'eth_call_cache','subgraph_deployment')\n and c.relfrozenxid != 0\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Transactions until wraparound vacuum",
"transform": "table",
"type": "table"
},
{
"columns": [],
"datasource": "postgres",
"fontSize": "100%",
"gridPos": {
"h": 3,
"w": 11,
"x": 13,
"y": 8
},
"id": 11,
"pageSize": null,
"showHeader": true,
"sort": {
"col": 0,
"desc": true
},
"styles": [
{
"alias": "Tables needing vacuum",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 0,
"pattern": "tables_needing_vacuum",
"thresholds": [],
"type": "number",
"unit": "none"
},
{
"alias": "Txns past",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 0,
"mappingType": 1,
"pattern": "txns_past",
"thresholds": [],
"type": "number",
"unit": "locale"
},
{
"alias": "Last autovacuum",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 2,
"mappingType": 1,
"pattern": "last_autovacuum",
"thresholds": [],
"type": "date",
"unit": "short"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "-- grafana ignore\nselect count(*) as tables_needing_vacuum,\n -min(tx_before_wraparound_vacuum) as txns_past,\n min(last_autovacuum) as last_autovacuum\n from (\n select oid::regclass::text AS table,\n least(\n (select setting::int\n from pg_settings\n where name = 'autovacuum_freeze_max_age') - age(relfrozenxid),\n (select setting::int\n from pg_settings\n where name = 'autovacuum_multixact_freeze_max_age')\n - mxid_age(relminmxid)) as tx_before_wraparound_vacuum,\n pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum,\n age(relfrozenxid) AS xid_age,\n mxid_age(relminmxid) AS mxid_age\n from pg_class\n where relfrozenxid != 0\n and oid > 16384\n and relkind='r') a where a.tx_before_wraparound_vacuum < 0;\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Autovacuum pressure",
"transform": "table",
"type": "table"
},
{
"columns": [],
"datasource": "postgres",
"fontSize": "100%",
"gridPos": {
"h": 9,
"w": 24,
"x": 0,
"y": 13
},
"id": 7,
"links": [],
"pageSize": null,
"scroll": true,
"showHeader": true,
"sort": {
"col": 4,
"desc": false
},
"styles": [
{
"alias": "Granted",
"align": "auto",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 0,
"mappingType": 1,
"pattern": "granted",
"preserveFormat": false,
"sanitize": false,
"thresholds": [
""
],
"type": "string",
"unit": "none",
"valueMaps": [
{
"text": "✓",
"value": "true"
},
{
"text": "—",
"value": "false"
}
]
},
{
"alias": "Age",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "age",
"thresholds": [],
"type": "number",
"unit": "s"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "-- grafana ignore\nSELECT a.application_name,\n coalesce(extract(epoch from age(now(), a.xact_start)), 0) as age,\n l.relation::regclass,\n l.mode,\n l.GRANTED::varchar,\n l.locktype \"Target\"\nFROM pg_stat_activity a\nJOIN pg_locks l ON l.pid = a.pid\nwhere pg_backend_pid() != a.pid\norder by granted asc, age desc;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Active locks",
"transform": "table",
"type": "table"
},
{
"columns": [],
"datasource": "postgres",
"fontSize": "100%",
"gridPos": {
"h": 8,
"w": 24,
"x": 0,
"y": 22
},
"id": 5,
"links": [],
"pageSize": null,
"scroll": true,
"showHeader": true,
"sort": {
"col": 4,
"desc": false
},
"styles": [
{
"alias": "Age",
"align": "auto",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"pattern": "age",
"type": "number",
"unit": "s"
},
{
"alias": "",
"align": "auto",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "/.*/",
"thresholds": [],
"type": "number",
"unit": "short"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "-- grafana ignore\nselect client_addr,\n application_name,\n usename,\n state,\n extract(epoch from age(now(), xact_start)) as age\n from pg_stat_activity\n where query not like '%grafana ignore%'\n and state like '%idle in transaction%'\norder by query_start desc",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Idle transactions",
"transform": "table",
"type": "table"
}
],
"schemaVersion": 22,
"style": "dark",
"tags": [],
"templating": {
"list": []
},
"time": {
"from": "now-6h",
"to": "now"
},
"timepicker": {
"refresh_intervals": [
"5s",
"10s",
"30s",
"1m",
"5m",
"15m",
"30m",
"1h",
"2h",
"1d"
],
"time_options": [
"5m",
"15m",
"1h",
"6h",
"12h",
"24h",
"2d",
"7d",
"30d"
]
},
"timezone": "",
"title": "Postgres Statistics",
"uid": "Mo6FxoiWz",
"variables": {
"list": []
},
"version": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment