[Prototype] TSDB For Oracle Integration #4848

agithomas · 2022-12-16T09:21:38Z

[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

The text was updated successfully, but these errors were encountered:

agithomas · 2023-01-10T15:42:07Z

agithomas · 2023-01-10T15:42:52Z

{
  "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 · 2023-01-10T15:45:27Z

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 · 2023-01-10T15:47:46Z

agithomas · 2023-01-10T15:48:21Z

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

agithomas · 2023-01-11T03:12:17Z

This task is related to similar effort put under #4749

agithomas · 2023-01-11T03:42:53Z

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 · 2023-01-11T03:57:57Z

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 · 2023-01-11T04:10:07Z

PR : #4966

jsoriano · 2023-01-11T08:08:03Z

@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 · 2023-01-11T08:38:24Z

@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

ruflin · 2023-01-11T12:27:53Z

@agithomas I like the idea that Kibana in the UI would indicate if something is a dimension or not. Could you file an issue in Kibana for this?

For the SNAPSHOT, make sure to pull down the most recent versions. Quite a few fixes landed over the last few days.

What I often do for debbuging purpose is looking at the mapping of the current write index of the data stream (instead of the template).

agithomas · 2023-01-23T04:08:58Z

Closing this issue. A meta issue is created under Observability-dev to track in detail

agithomas self-assigned this Dec 16, 2022

agithomas added the Team:Service-Integrations Label for the Service Integrations team label Dec 16, 2022

agithomas changed the title ~~[Draft] TSDB For Oracle Integration : TSDB settings are enabled for Oracle integration in Kibana for Enabling and disabling.~~ TSDB For Oracle Integration Dec 16, 2022

agithomas changed the title ~~TSDB For Oracle Integration~~ [Prototype] TSDB For Oracle Integration Jan 11, 2023

agithomas mentioned this issue Jan 19, 2023

[TSDB] Need Indicators for dimension fields when TSDB is enabled. elastic/kibana#149180

Open

agithomas closed this as completed Jan 23, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Prototype] TSDB For Oracle Integration #4848

[Prototype] TSDB For Oracle Integration #4848

agithomas commented Dec 16, 2022 •

edited

Loading

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023 •

edited

Loading

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023

agithomas commented Jan 11, 2023 •

edited

Loading

agithomas commented Jan 11, 2023

agithomas commented Jan 11, 2023 •

edited

Loading

agithomas commented Jan 11, 2023

jsoriano commented Jan 11, 2023

agithomas commented Jan 11, 2023

ruflin commented Jan 11, 2023 •

edited

Loading

agithomas commented Jan 23, 2023

[Prototype] TSDB For Oracle Integration #4848

[Prototype] TSDB For Oracle Integration #4848

Comments

agithomas commented Dec 16, 2022 • edited Loading

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023 • edited Loading

agithomas commented Jan 10, 2023

agithomas commented Jan 10, 2023

agithomas commented Jan 11, 2023 • edited Loading

agithomas commented Jan 11, 2023

agithomas commented Jan 11, 2023 • edited Loading

agithomas commented Jan 11, 2023

jsoriano commented Jan 11, 2023

agithomas commented Jan 11, 2023

ruflin commented Jan 11, 2023 • edited Loading

agithomas commented Jan 23, 2023

agithomas commented Dec 16, 2022 •

edited

Loading

agithomas commented Jan 10, 2023 •

edited

Loading

agithomas commented Jan 11, 2023 •

edited

Loading

agithomas commented Jan 11, 2023 •

edited

Loading

ruflin commented Jan 11, 2023 •

edited

Loading