Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-17054

Index not being utilized, on occasion.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical - P2
    • Resolution: Duplicate
    • Affects Version/s: 2.6.5
    • Fix Version/s: None
    • Component/s: Indexing, Performance, Querying
    • Labels:
      None
    • Operating System:
      Linux
    • Steps To Reproduce:
      Hide

      Sporadic issue that happens during normal and low load. We have the following query against a user collection.

      { "$query" : { "a_token" : "*******************"} , "$orderby" : { "_id" : 1}}

      The orderby is generated by Mongoid, not needed but shouldn't affect the outcome.

      Here are the indexes on this collection.

      [
              {
                      "v" : 1,
                      "key" : {
                              "_id" : 1
                      },
                      "name" : "_id_",
                      "ns" : "database.user"
              },
              {
                      "v" : 1,
                      "key" : {
                              "a_token" : 1
                      },
                      "name" : "a_token_1",
                      "ns" : "database.user",
                      "background" : true
              }
      ]

      Most of the time the query returns in under 1ms. On occasion we run up to 2000+ms. running the query manually during these times shows the same. If I remove the orderby the query returns as expected.

      What is going on here? The DB is not being stressed and it seems the query is being run out of order (perhaps trying to return all _id's, ordering, then selecting the a_token). The sporadic nature is most worrying, we are having a very hard time tracking down the cause.

      Show
      Sporadic issue that happens during normal and low load. We have the following query against a user collection. { "$query" : { "a_token" : "*******************"} , "$orderby" : { "_id" : 1}} The orderby is generated by Mongoid, not needed but shouldn't affect the outcome. Here are the indexes on this collection. [ { "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "database.user" }, { "v" : 1, "key" : { "a_token" : 1 }, "name" : "a_token_1", "ns" : "database.user", "background" : true } ] Most of the time the query returns in under 1ms. On occasion we run up to 2000+ms. running the query manually during these times shows the same. If I remove the orderby the query returns as expected. What is going on here? The DB is not being stressed and it seems the query is being run out of order (perhaps trying to return all _id's, ordering, then selecting the a_token). The sporadic nature is most worrying, we are having a very hard time tracking down the cause.

      Description

      Sporadic performance issue affecting a simple query.

      { "$query" : { "a_token" : "*******************"} , "$orderby" : { "_id" : 1}}

      No index misses are being reported. Most of the time the query returns in under 1ms. On occasion we run up to 2000+ms. running the query manually during these times shows the same. If I remove the orderby the query returns as expected. Seems to be running out of order.

        Attachments

        1. dataset.json.bz2
          22.17 MB
          Ramon Fernandez Marina

          Issue Links

            Activity

              People

              Assignee:
              rassi J Rassi
              Reporter:
              enigmnaphoenix Kamil Keski
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: