Uploaded image for project: 'Mender'
  1. Mender
  2. MEN-3646

[deployments] missing index for (deviceid, created, status)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: (None)
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
    • Days in progress:
      1

      Description

      We have an issue in production due to a missing index; we have this kind of queries:

      db.devices.find({"deviceid": "5cb74485f276ea0001967f04", "status": {"$in": ["pending", "downloading", "installing", "rebooting"]}}).sort({created: 1}).limit(1)

      This is the index the query is using:

      db.devices.ensureIndex({"deviceid": 1, "status": 1, "created": 1})

      As we filter by status, then sort by "created", mongo id splitting the query into multiple subqueries, one for each status, then sort_merging the results before applying the limit; this is suboptimal and it is leading to high I/O usage.
       
      Inverting the status and the created columns improves performances a lot, because the query can filter by device id, gets the records sorted by created and then navigate through the index to select the first document matching the status:

      db.devices.ensureIndex({"deviceid": 1, "created": 1, "status": 1})

        Attachments

          Activity

            People

            • Assignee:
              tranchitella Fabio Tranchitella
              Reporter:
              tranchitella Fabio Tranchitella
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Summary Panel