Feature request: Option to index Mongo DB automagically

[ilarimikkonen]

Story: As a system administrator or system owner, I'd like have an option to do Mongo indexing to get maximum performance out of the system so that my resources are efficiently used. I do not want to do this manually, as it is error prone and ties up human resources.

Options which have come up in the discussion:

• Command line parameter to automatically index (by _id) every new database as they are created
• Offer an API to do this

related to #3254

Related discussion

[kzangeli]

I was under the impression that mongo "gives us" an index over _id ... I might be wrong. In general it's not possible (well, not a good idea) to automize the election of indices as the broker doesn't know what its users are interested in - what kind of queries. Remember that every index may make searches faster (depending on the query), but that it definitely makes inserts slower (as the index has to be rebuilt). What we did in Orion was to supply docs on indexing in mongo and IMHO the best way to improve this is improving those docs. (Unless I'm wrong and mongo doesn't have an "implicit" index for the _id field)

[ilarimikkonen]

to be more precise, the indexing we've been using:

db.entities.createIndex({"_id.id": 1});

and this seems to improve writes (POST / PATCH to Orion) quite dramatically.

[kzangeli]

ok, that is definitely interesting

[fgalan]

I agree with @kzangeli : indexes configuration depends on usage and indexes introduce tradeoffs which Orion cannot infer itself. The best approach is to provide good documentation about it. I think the documentation we have now at https://fiware-orion.readthedocs.io/en/master/admin/perf_tuning/index.html#database-indexes is pretty good, but of course if @ilarimikkonen you have suggestions about how to improve it, let us know.

Even in the case Orion would implement some kind of indexing feature, note that when used in multitenancy mode (i.e. -multiservice), which is the usual one in production environments, per-service databases (pe. orion-myservice) are created on the fly first time an entity/subscription/registration is created in that service. This would introduce additional challenges regarding indexes management, such as:

• Should Orion ensure the index in each entity creation (as we are currently doing with the "functional" geo-index)?
• Should Orion keep some kind of memory to avoid re-creating each time? How to deal with out-of-band operations in this case (e.g. somebody drops and index that Orion, based on its memory, thinks already exists)?
• Should Orion implement some kind of "provisioning API" to create per-service databases (an, as part of the database, the index creation will be included)?

[fgalan]

With regards to

Should Orion keep some kind of memory to avoid re-creating each time? How to deal with out-of-band operations in this case (e.g. somebody drops and index that Orion, based on its memory, thinks already exists)?

I have found this issue https://github.com/telefonicaid/fiware-orion/issues/3721 that describes some ideas, but in the context of the functional geo-index used by Orion for geo-location features.