[Prototype] TSDB For Oracle Integration

[agithomas]

• [Prototype] TSDB For Oracle Integration #4848

• TSDB settings are enabled for Oracle integration in Kibana for Enabling and disabling.

• Validate that if TSDB is already enabled, switch back to standard index type is disabled.

• Enable Timeseries settings in manifest.yml for performance datastream

elasticsearch:
  index_mode: "time_series"
  index_template:
    settings:
      # Defaults to 16
      index.mapping.dimension_fields.limit: 32

• Define the dimensions for the performance datastream
• Verify the data ingestion and data querying (exploration) for performance datastream

PR Details : #4966

[agithomas]

[agithomas]

{
  "template": {
    "settings": {
      "index": {
        "lifecycle": {
          "name": "metrics"
        },
        "mode": "time_series",
        "codec": "best_compression",
        "routing": {
          "allocation": {
            "include": {
              "_tier_preference": "data_hot"
            }
          }
        },
        "mapping": {
          "total_fields": {
            "limit": "10000"
          },
          "dimension_fields": {
            "limit": "16"
          }
        },
        "time_series": {
          "end_time": "2023-01-10T17:42:25.000Z",
          "start_time": "2023-01-10T13:42:25.000Z"
        },
        "final_pipeline": ".fleet_final_pipeline-1",
        "query": {
          "default_field": [
            "ecs.version",
            "service.address",
            "service.type",
            "oracle.performance.machine",
            "oracle.performance.buffer_pool",
            "oracle.performance.username",
            "oracle.performance.wait.wait_class"
          ]
        },
        "default_pipeline": "metrics-oracle.performance-1.9.4",
        "routing_path": [
          "service.address"
        ]
      }
    },
    "mappings": {
      "_meta": {
        "managed_by": "fleet",
        "managed": true,
        "package": {
          "name": "oracle"
        }
      },
      "_source": {
        "mode": "stored"
      },
      "dynamic_templates": [
        {
          "strings_as_keyword": {
            "match_mapping_type": "string",
            "mapping": {
              "ignore_above": 1024,
              "type": "keyword"
            }
          }
        }
      ],
      "date_detection": false,
      "properties": {
        "@timestamp": {
          "type": "date"
        },
        "data_stream": {
          "properties": {
            "dataset": {
              "type": "constant_keyword"
            },
            "namespace": {
              "type": "constant_keyword"
            },
            "type": {
              "type": "constant_keyword"
            }
          }
        },
        "ecs": {
          "properties": {
            "version": {
              "type": "keyword",
              "ignore_above": 1024
            }
          }
        },
        "event": {
          "properties": {
            "agent_id_status": {
              "type": "keyword",
              "ignore_above": 1024
            },
            "dataset": {
              "type": "constant_keyword",
              "value": "oracle.performance"
            },
            "ingested": {
              "type": "date",
              "format": "strict_date_time_no_millis||strict_date_optional_time||epoch_millis"
            },
            "module": {
              "type": "constant_keyword",
              "value": "sql"
            }
          }
        },
        "host": {
          "properties": {
            "ip": {
              "type": "ip"
            }
          }
        },
        "oracle": {
          "properties": {
            "performance": {
              "properties": {
                "buffer_pool": {
                  "type": "keyword",
                  "ignore_above": 1024
                },
                "cache": {
                  "properties": {
                    "buffer": {
                      "properties": {
                        "hit": {
                          "properties": {
                            "pct": {
                              "type": "double",
                              "meta": {
                                "metric_type": "gauge",
                                "unit": "percent"
                              }
                            }
                          }
                        }
                      }
                    },
                    "get": {
                      "properties": {
                        "consistent": {
                          "type": "long",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        },
                        "db_blocks": {
                          "type": "long",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        }
                      }
                    },
                    "physical_reads": {
                      "type": "long",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    }
                  }
                },
                "cursors": {
                  "properties": {
                    "avg": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    },
                    "cache_hit": {
                      "properties": {
                        "pct": {
                          "type": "double",
                          "meta": {
                            "metric_type": "gauge",
                            "unit": "percent"
                          }
                        }
                      }
                    },
                    "max": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    },
                    "opened": {
                      "properties": {
                        "current": {
                          "type": "long",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        },
                        "total": {
                          "type": "long",
                          "meta": {
                            "metric_type": "counter"
                          }
                        }
                      }
                    },
                    "parse": {
                      "properties": {
                        "real": {
                          "type": "double",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        },
                        "total": {
                          "type": "long",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        }
                      }
                    },
                    "session": {
                      "properties": {
                        "cache_hits": {
                          "type": "double",
                          "meta": {
                            "metric_type": "gauge"
                          }
                        }
                      }
                    },
                    "total": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    }
                  }
                },
                "failed_db_jobs": {
                  "type": "double",
                  "meta": {
                    "metric_type": "gauge"
                  }
                },
                "io_reloads": {
                  "type": "double",
                  "meta": {
                    "metric_type": "gauge"
                  }
                },
                "lock_requests": {
                  "type": "double",
                  "meta": {
                    "metric_type": "gauge"
                  }
                },
                "machine": {
                  "type": "keyword",
                  "ignore_above": 1024
                },
                "pin_requests": {
                  "type": "double",
                  "meta": {
                    "metric_type": "gauge"
                  }
                },
                "session_count": {
                  "properties": {
                    "active": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    },
                    "inactive": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    },
                    "inactive_morethan_onehr": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge"
                      }
                    }
                  }
                },
                "username": {
                  "type": "keyword",
                  "ignore_above": 1024
                },
                "wait": {
                  "properties": {
                    "pct_time": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge",
                        "unit": "percent"
                      }
                    },
                    "pct_waits": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge",
                        "unit": "percent"
                      }
                    },
                    "time_waited_secs": {
                      "type": "double",
                      "meta": {
                        "metric_type": "gauge",
                        "unit": "s"
                      }
                    },
                    "total_waits": {
                      "type": "double",
                      "meta": {
                        "metric_type": "counter"
                      }
                    },
                    "wait_class": {
                      "type": "keyword",
                      "ignore_above": 1024
                    }
                  }
                }
              }
            }
          }
        },
        "service": {
          "properties": {
            "address": {
              "type": "keyword",
              "time_series_dimension": true
            },
            "type": {
              "type": "keyword",
              "ignore_above": 1024
            }
          }
        }
      }
    },
    "aliases": {}
  }
}

[agithomas]

metrics-oracle.performance@package

{
  "index": {
    "lifecycle": {
      "name": "metrics"
    },
    "codec": "best_compression",
    "default_pipeline": "metrics-oracle.performance-1.9.4",
    "mapping": {
      "total_fields": {
        "limit": "10000"
      },
      "dimension_fields": {
        "limit": "16"
      }
    },
    "query": {
      "default_field": [
        "ecs.version",
        "service.address",
        "service.type",
        "oracle.performance.machine",
        "oracle.performance.buffer_pool",
        "oracle.performance.username",
        "oracle.performance.wait.wait_class"
      ]
    }
  }
}

[agithomas]

[agithomas]

elasticsearch:
  source_mode: "synthetic"
  index_mode: "time_series"
  index_template:
    settings:
      # Defaults to 16
      index.mapping.dimension_fields.limit: 16

[agithomas]

This task is related to similar effort put under #4749

[agithomas]

One problem i face is , i do not know if a field is a dimension or not. It may be good that in kibana discover view, there exist an icon if the field is a dimension field or not. @ruflin , do you agree?

service.address is a dimension field for me and i have no way if it is really considered as a dimension or not.

[agithomas]

Going with the documentation , the document must have a _tsid field

But, the document in my performance datastream does not contain this field. This makes me wonder, if it is really a timeseries document!!

Also, the _tsid field does not appear in the UI , under meta fields

{
  "_index": ".ds-metrics-oracle.performance-default-2023.01.10-000002",
  "_id": "Msz4noUBd-0GTnQ0F1fU",
  "_version": 1,
  "_score": 0,
  "_source": {
    "@timestamp": "2023-01-11T03:55:36.240Z",
    "agent": {
      "ephemeral_id": "0dfdc584-9439-4be4-b056-ed0881ba4f94",
      "id": "ba24b081-61a0-4daf-b14c-f7107c28286e",
      "name": "service-integration-dev-idc-1",
      "type": "metricbeat",
      "version": "8.7.0"
    },
    "data_stream": {
      "dataset": "oracle.performance",
      "namespace": "default",
      "type": "metrics"
    },
    "ecs": {
      "version": "8.0.0"
    },
    "elastic_agent": {
      "id": "ba24b081-61a0-4daf-b14c-f7107c28286e",
      "snapshot": true,
      "version": "8.7.0"
    },
    "event": {
      "agent_id_status": "verified",
      "dataset": "oracle.performance",
      "duration": 53460723,
      "ingested": "2023-01-11T03:55:37Z",
      "module": "sql"
    },
    "host": {
      "name": "service-integration-dev-idc-1"
    },
    "metricset": {
      "name": "query",
      "period": 60000
    },
    "oracle": {
      "performance": {
        "buffer_pool": "DEFAULT",
        "cache": {
          "buffer": {
            "hit": {
              "pct": 0.9978013184434321
            }
          },
          "get": {
            "consistent": 19976037,
            "db_blocks": 2492883
          },
          "physical_reads": 49402
        }
      }
    },
    "service": {
      "address": "0.0.0.0:1521/ORCLCDB.localdomain",
      "type": "sql"
    }
  },
  "fields": {
    "elastic_agent.version": [
      "8.7.0"
    ],
    "service.type": [
      "sql"
    ],
    "agent.type": [
      "metricbeat"
    ],
    "event.module": [
      "sql"
    ],
    "oracle.performance.cache.buffer.hit.pct": [
      0.9978013184434321
    ],
    "agent.name": [
      "service-integration-dev-idc-1"
    ],
    "elastic_agent.snapshot": [
      true
    ],
    "host.name": [
      "service-integration-dev-idc-1"
    ],
    "event.agent_id_status": [
      "verified"
    ],
    "oracle.performance.cache.get.consistent": [
      19976037
    ],
    "oracle.performance.buffer_pool": [
      "DEFAULT"
    ],
    "oracle.performance.cache.physical_reads": [
      49402
    ],
    "elastic_agent.id": [
      "ba24b081-61a0-4daf-b14c-f7107c28286e"
    ],
    "data_stream.namespace": [
      "default"
    ],
    "metricset.period": [
      60000
    ],
    "data_stream.type": [
      "metrics"
    ],
    "metricset.name": [
      "query"
    ],
    "event.duration": [
      53460723
    ],
    "event.ingested": [
      "2023-01-11T03:55:37.000Z"
    ],
    "@timestamp": [
      "2023-01-11T03:55:36.240Z"
    ],
    "agent.id": [
      "ba24b081-61a0-4daf-b14c-f7107c28286e"
    ],
    "ecs.version": [
      "8.0.0"
    ],
    "service.address": [
      "0.0.0.0:1521/ORCLCDB.localdomain"
    ],
    "data_stream.dataset": [
      "oracle.performance"
    ],
    "agent.ephemeral_id": [
      "0dfdc584-9439-4be4-b056-ed0881ba4f94"
    ],
    "agent.version": [
      "8.7.0"
    ],
    "event.dataset": [
      "oracle.performance"
    ],
    "oracle.performance.cache.get.db_blocks": [
      2492883
    ]
  }
}

@ruflin , @jsoriano , have you seen the _tsid field in the document in your testing?

[agithomas]

PR : #4966

[jsoriano]

@agithomas what version of Kibana are you using? It seems that support for index_mode: "time_series" comes in 8.7.0, see elastic/kibana#148292, cc @nchaulet.

[agithomas]

@agithomas what version of Kibana are you using? It seems that support for index_mode: "time_series" comes in 8.7.0, see elastic/kibana#148292, cc @nchaulet.

docker.elastic.co/kibana/kibana:8.7.0-SNAPSHOT is what i am using at present