Database Indexes
  • 21 Nov 2024
  • Dark
    Light
  • PDF

Database Indexes

  • Dark
    Light
  • PDF

Article summary

Index Collections

IAP requires indexes to be created in the MongoDB collections for optimal performance. The following collections, with the corresponding application enclosed within parentheses, contain predefined indexes that must be created.

  • accounts (pronghorn-core)
  • catalog_store (app-service_catalog)
  • forms (app-form_builder)
  • groups (pronghorn-core)
  • iap_profiles (pronghorn-core)
  • integration_models (pronghorn-core)
  • jobs (app-workflow_engine)
  • job_history (app-workflow_engine)
  • mop_templates (app-mop)
  • mop_analytic_templates (app-mop)
  • roles (pronghorn-core)
  • service_configs (pronghorn-core)
  • tags (tags)
  • tasks (app-workflow_engine)
  • transformations (app-jst)
  • ucm_configs (app-configuration_manager)
  • wfe_job_metrics (app-workflow_engine)
  • wfe_task_metrics (app-workflow_engine)
  • workflows (app-workflow_engine)

Indexing APIs and Seeding

Indexing within IAP is no longer done via indexing scripts, but rather via indexing APIs that can be called while IAP is running. If an application is not installed, any indexes related to its collections are not required.

It is also important to note that collections that do not already exist will automatically be created and properly indexed when IAP starts. For all new installations, manual indexing is not required. However, when additional indexes are required or an index is accidentally dropped, it is recommended that indexing APIs are used to validate the existing indexes and create the missing or misnamed indexes.

Initial Index Validation API

To verify that a collection is properly indexed, Itential recommends using the GET /indexes/:collectionName/status API which will return an object containing all missing or misnamed indexes. When used on the jobs collection, for example, this would look like GET /indexes/jobs/status. Any collections which are incorrectly indexed should go through the index creation API.

Index Creation API

Itential recommends that you create indexes only during a maintenance window, especially for large collections like jobs and tasks. Indexing these collections may take some time and have an impact on MongoDB performance until they are fully created.

If a collection is not properly indexed, the POST /indexes/:collectionName API will create any missing indexes and also drop then recreate any indexes with an incorrect name. For the jobs collection this will look something like POST /indexes/jobs with an empty request body. All collections that are properly indexed or not included in the predefined indexes will be ignored. Likewise, if the index creation process has already been started once, this will not cause a second index creation to be inititated in MongoDB. Finally, this API initiates the index creation, but will not wait for it to complete before responding since index creation can take a significant amount of time.

Index Validation

Index Collection Status API

After running the index creation API, you should validate the indexes are created successfully. For large collections, such as jobs and tasks, the index creation process may take some time, so the progress information available in GET /indexes/:collectionName/status can show how far along it is as well as when the process is completely finished.

Legacy Node Script Validation (Deprecated)

While the API solution above is the preferred way to manage indexing, there is currently another way to create indexes. The legacy mechanism is to use the npm run index node script in the various applications that support it.

The applications that support this method are:

  • Command Templates/MOP
  • Service Catalog
  • Workflow Engine

Example

The following outlines how to work with the npm run index script in Workflow Engine.

cd /opt/pronghorn/current/node_modules/@itential/app-workflow_engine
npm run index

Automation Engine Indexes

Once the Automation Engine scripts have completed, run the following commands on MongoDB to verify the indexes have been created.

Commands

db.jobs.getIndexes()
db.tasks.getIndexes()
db.workflows.getIndexes()

Example

$ mongo mongo01.zone1.itential.io:27017/pronghorn --ssl -u pronghorn
MongoDB shell version v3.4.18
Enter password:
connecting to: mongodb://mongo01.zone1.itential.io:27017/pronghorn
MongoDB server version: 3.4.18
rs0:PRIMARY> db.jobs.getIndexes()

[{
    "v": 2,
    "key": {
      "_id": 1
    },
    "name": "_id_",
    "ns": "pronghorn.jobs"
  },
  {
    "v": 2,
    "key": {
      "name": 1,
      "type": 1,
      "groups": 1,
      "status": 1,
      "metrics.start_time": -1,
      "metrics.progress": 1,
      "metrics.user": 1
    },
    "name": "name_1_type_1_groups_1_status_1_metrics.start_time_-1_metrics.progress_1_metrics.user_1",
    "ns": "pronghorn.jobs",
    "background": true
  },
  {
    "v": 2,
    "key": {
      "name": 1,
      "type": 1,
      "watchers": 1,
      "status": 1,
      "metrics.start_time": -1,
      "metrics.progress": 1,
      "metrics.user": 1
    },
    "name": "name_1_type_1_watchers_1_status_1_metrics.start_time_-1_metrics.progress_1_metrics.user_1",
    "ns": "pronghorn.jobs",
    "background": true
  }
]

rs0: PRIMARY > db.tasks.getIndexes()[{
  "v": 2,
  "key": {
    "_id": 1
  },
  "name": "_id_",
  "ns": "pronghorn.tasks"
}, {
  "v": 2,
  "key": {
    "name": 1,
    "status": 1,
    "groups": 1,
    "type": 1,
    "job.name": 1,
    "job._id": 1,
    "job.task": 1,
    "metrics.owner": 1,
    "metrics.start_time": -1
  },
  "name": "name_1_status_1_groups_1_type_1_job.name_1_job._id_1_job.task_1_metrics.owner_1_metrics.start_time_-1",
  "ns": "pronghorn.tasks",
  "background": true
  },
  {
  "key": {
    "status": 1,
    "locked": 1
  },
  "background": true,
}, {
  "key": {
    "job._id": 1,
    "job.task": 1
  },
  "background": true
}]

rs0: PRIMARY > db.workflows.getIndexes()[{
  "v": 2,
  "key": {
    "_id": 1
  },
  "name": "_id_",
  "ns": "pronghorn.workflows"
}, {
  "v": 2,
  "unique": true,
  "key": {
    "name": 1,
    "type": 1
  },
  "name": "name_1_type_1",
  "ns": "pronghorn.workflows",
  "background": true
}, {
  "v": 2,
  "key": {
    "groups": 1,
    "created": -1,
    "created_by": 1,
    "last_updated": -1,
    "last_updated_by": 1
  },
  "name": "groups_1_created_-1_created_by_1_last_updated_-1_last_updated_by_1",
  "ns": "pronghorn.workflows",
  "background": true
}]

Command Template/MOP Indexes

The final set of indexes for the Command Template/MOP application may be applied using the following npm script.

Example

$ cd /opt/pronghorn/current/node_modules/@itential/app-mop
$ npm run index
...
...

Service Catalog Indexes

The final set of indexes for the Service Catalog application may be applied using the following npm script.

Example

$ cd /opt/pronghorn/current/node_modules/@itential/app-service_catalog
$ npm run index
...
...

Was this article helpful?

What's Next
Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.