Beats: [Metricbeat] Create PostgreSQL dashboard

[sayden]

Very self descriptive. Requested here https://github.com/elastic/beats/issues/10594

I considered to include the following fields (grouped by visualization):

- name: database
  - name: transactions.commit
    description: Number of transactions in this database that have been committed.
  - name: transactions.rollback
    description: Number of transactions in this database that have been rolled back.

  - name: blocks.hit
    description: >
      Number of times disk blocks were found already in the buffer cache, so
      that a read was not necessary (this only includes hits in the PostgreSQL
      buffer cache, not the operating system's file system cache).

  - name: blocks.time.read.ms
    description: Time spent reading data file blocks by backends in this database, in milliseconds.
  - name: blocks.time.write.ms
    description: Time spent writing data file blocks by backends in this database, in milliseconds.

  - name: rows.returned
    description: Number of rows returned by queries in this database.
  - name: rows.fetched
    description: Number of rows fetched by queries in this database.

  - name: rows.inserted
    description: Number of rows inserted by queries in this database.
  - name: rows.updated
    description: Number of rows updated by queries in this database.
  - name: rows.deleted
    description: Number of rows deleted by queries in this database.

  - name: conflicts
    description: Number of queries canceled due to conflicts with recovery in this database.
  - name: deadlocks
    description: Number of deadlocks detected in this database.

- name: statement
  - name: query.calls
    description: Number of times the query has been run.
  - name: query.rows
    description: Total number of rows returned by query.

  - name: query.time.total.ms
    description: Total number of milliseconds spent running query.
  - name: query.time.min.ms
    description: Minimum number of milliseconds spent running query.
  - name: query.time.max.ms
    description: Maximum number of milliseconds spent running query.
  - name: query.time.mean.ms
    description: Mean number of milliseconds spent running query.

  - name: query.memory.shared.hit
    description: Total number of shared block cache hits by the query.
  - name: query.memory.shared.read
    description: Total number of shared block cache read by the query.

  - name: query.memory.local.hit
    description: Total number of local block cache hits by the query.
  - name: query.memory.local.read
    description: Total number of local block cache read by the query.

Total 6 visualizations for database metricset and 4 of statement.

[jsoriano]

There was a PostgreSQL dashboard by @dov0211 that was ready to be merged after updating the branch: https://github.com/elastic/beats/pull/7734

[sorantis]

@sayden, based on (some of) the proposed grouping, I made this dashboard:

Let me know if you want to add anything else to it.

[Matt-Yorkley]

@sorantis I think the "rows fetched vs. rows returned" metric should be a graph rather than a bar chart. Example:

[Matt-Yorkley]

It looks like the (abandoned?) PR #7734 made a start on views for the statement metricset:

LGTM as a layout for visualising the statement metrics in general, and specifically it covers the first half of:

  - name: query.calls
    description: Number of times the query has been run.
  - name: query.rows
    description: Total number of rows returned by query.

[sorantis]

@Matt-Yorkley thanks for the feedback. I've changed the "Rows fetched/received" to a graph and added the query.calls Top N graph.

[kcpinnaka]

@sorantis , thanks for providing dashboards for postgres, may i know when this will be available for public ? looks like latest 7.3.0 doesnt include this.

[sorantis]

@kcpinnaka The pr got merged after the 7.3.0 cut. Should be part of the next release.

[kcpinnaka]

@sorantis,
since i am planning a demo of ELK stack which includes postgres performace ,
may i know when will the next release gets out?