Change "InnoDB Buffer Pool Requests" Graph

[gordan-bobic]

It would be handy to have a cache hit ratio for the buffer pool (memory reads vs. disk reads).

Here is my first attempt at adding this:

• Changed scale to log(2) for better legibility
• Switch disk reads to the left Y axis
• Added cache hit ratio in % as the right axis for the new graph line

{
  "aliasColors": {},
  "bars": false,
  "dashLength": 10,
  "dashes": false,
  "datasource": "Prometheus",
  "decimals": 2,
  "editable": true,
  "error": false,
  "fill": 2,
  "grid": {},
  "gridPos": {
    "h": 7,
    "w": 12,
    "x": 12,
    "y": 42
  },
  "id": 82,
  "legend": {
    "alignAsTable": true,
    "avg": true,
    "current": false,
    "max": true,
    "min": true,
    "rightSide": false,
    "show": true,
    "sort": "avg",
    "sortDesc": true,
    "total": false,
    "values": true
  },
  "lines": true,
  "linewidth": 2,
  "links": [],
  "nullPointMode": "null",
  "percentage": false,
  "pointradius": 5,
  "points": false,
  "renderer": "flot",
  "seriesOverrides": [
    {
      "alias": "Cache Hit Rate",
      "yaxis": 2
    }
  ],
  "spaceLength": 10,
  "stack": false,
  "steppedLine": false,
  "targets": [
    {
      "calculatedInterval": "2m",
      "datasourceErrors": {},
      "errors": {},
      "expr": "rate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~\"$host\"}[$interval]) or irate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~\"$host\"}[5m])",
      "interval": "$interval",
      "intervalFactor": 1,
      "legendFormat": "Read Requests",
      "metric": "",
      "refId": "A",
      "step": 300,
      "format": "time_series"
    },
    {
      "calculatedInterval": "2m",
      "datasourceErrors": {},
      "errors": {},
      "expr": "rate(mysql_global_status_innodb_buffer_pool_write_requests{instance=~\"$host\"}[$interval]) or irate(mysql_global_status_innodb_buffer_pool_write_requests{instance=~\"$host\"}[5m])",
      "interval": "$interval",
      "intervalFactor": 1,
      "legendFormat": "Write Requests",
      "metric": "",
      "refId": "B",
      "step": 300,
      "format": "time_series"
    },
    {
      "calculatedInterval": "2m",
      "datasourceErrors": {},
      "errors": {},
      "expr": "rate(mysql_global_status_innodb_buffer_pool_reads{instance=~\"$host\"}[$interval]) or irate(mysql_global_status_innodb_buffer_pool_reads{instance=~\"$host\"}[5m]) ",
      "interval": "$interval",
      "intervalFactor": 1,
      "legendFormat": "Disk Reads",
      "metric": "",
      "refId": "C",
      "step": 300,
      "format": "time_series"
    },
    {
      "refId": "D",
      "expr": "(1 - ((rate(mysql_global_status_innodb_buffer_pool_reads{instance=~\"$host\"}[$interval]) or irate(mysql_global_status_innodb_buffer_pool_reads{instance=~\"$host\"}[5m]))  /\n(rate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~\"$host\"}[$interval]) or irate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~\"$host\"}[5m])))) * 100",
      "intervalFactor": 1,
      "format": "time_series",
      "legendFormat": "Cache Hit Rate",
      "interval": "$interval",
      "hide": false
    }
  ],
  "thresholds": [],
  "timeFrom": null,
  "timeShift": null,
  "title": "InnoDB Buffer Pool Requests",
  "tooltip": {
    "msResolution": true,
    "shared": true,
    "sort": 0,
    "value_type": "individual"
  },
  "type": "graph",
  "xaxis": {
    "buckets": null,
    "mode": "time",
    "name": null,
    "show": true,
    "values": []
  },
  "yaxes": [
    {
      "format": "short",
      "logBase": 2,
      "max": null,
      "min": 0,
      "show": true,
      "label": ""
    },
    {
      "format": "short",
      "logBase": 1,
      "max": "100",
      "min": 0,
      "show": true,
      "label": ""
    }
  ],
  "yaxis": {
    "align": false,
    "alignLevel": null
  },
  "timeRegions": []
}

[oblitorum]

link: https://flak.shatteredsilicon.net/graph/d/giGgrTimz/mysql-innodb-metrics?from=1681759921926&to=1681760237467&var-interval=$__auto_interval_interval&var-host=flak&orgId=1

OK, I tried to apply the changes, seems ok, except that the min value of Cache Hit Rate seems wrong? I think it's because that when memory reads is 0, the expression disk_reads / 0 goes wrong, and no value generated. Should I fix that?

[gordan-bobic]

Yes please, do fix. I just threw that together in a hurry because I wanted a clean representation of the cache hit rate on a busy sytem.

[oblitorum]

OK, copy that

[oblitorum]

Oh, and I notice that the id value has been changed, I assume that you changed it so that it won't conflict with the original one, not actually want to change it, right?

-        "id": 41,
+        "id": 82,

[gordan-bobic]

Well spotted. Yes, what a pasted was a test graph I put together on a test dashboard, so it probably got changed implicitly when I created a new graph. I would imagine id should probably stay the same as before in default SSM dashboards.

[oblitorum]

OK, got it.