Trying to build Timelion charts with metricbeat data

Elastic Stack Kibana
1

Hey Folks,

I have installed Kibana v5 and I'm trying to build some nice Timelion charts using postgres metrics data collected via Metricbeat. I have noticed a strange issue, maybe I'm missing the point here:

I'm using the following query to draw two metricsets located on the same chart:
.es(metric=cardinality:postgresql.database.rows.deleted).label('rows deleted'), .es(metric=cardinality:postgresql.database.rows.updated).label('rows inserted')

Due some strange reason the chart looks like this:

2016-11-04_125728.png872×476 10.7 KB

So the request for 'rows deleted' seems to be fine (although i don't understand why the y-axis counter has only a range from 0-70). The line for 'rows inserted' is definetly not what i would expect that it should look like. Can someone tell me why?

Heres a part of "raw" data:

"postgresql": {
  "database": {
    "blocks": {
      "hit": 2924945303,
      "read": 395660,
      "time": {
        "read": {
          "ms": 0
        },
        "write": {
          "ms": 0
        }
      }
    },
    "conflicts": 0,
    "deadlocks": 0,
    "name": "xxx",
    "number_of_backends": 34,
    "oid": 16414,
    "rows": {
      "deleted": 11493269,
      "fetched": 2173275568,
      "inserted": 13233679,
      "returned": 42991645961,
      "updated": 33534467
    },
2

Hi,

If you are referring to the sudden rise and drop at the ends of the "rows inserted" series, that might be because the x-axis time buckets at those points contain partial data. Try adding .trim() to the series definition expression.

Also, this might be irrelevant, but I just noticed that the series labeled "rows inserted" is actually using the postgresql.database.rows.updated field. Should it be using the postgresql.database.rows.inserted field instead?

Shaunak

1 Like
3

Ok, maybe i was a bit unprecise here :slight_smile:

The obvious thing about this chart is, that the line for 'rows inserted' is just straight. There is no fluctuation in it, it is defnitely not representing the data as it is. Here is some example data:

'rows deleted' 'rows inserted'
99 78
13,549,210 11,762,099
99 78
0 0
99 78
13,549,204 11,762,098
0 0
99 78
99 78
0 0
99 78
13,549,194 11,762,098
99 78
13,549,184 11,762,098
99 78
0 0
13,549,176 11,762,098
99 78
0 0
99 78

And yes, you are right... in my example I have accidentally used 'postgresql.database.rows.updated' for 'rows inserted'. The reason is that i have tested several 'postgresql.database.rows.*' fields, all of them are showing pretty the same fluctuation as you can see in the expample data above. But only the field 'postgresql.database.rows.deleted' is showing a reasonable graph.

4

I think this is happening because you are using the "cardinality" metric. This gives you the count of unique values in a field. So, for example, in the example data you provided, there are 4 unique values for the "rows inserted" field: 78, 11762099, 0, and 11762098.

I suspect what you want to use instead of "cardinality" is "sum" or possibly "avg".