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

Index not being utilized, on occasion.

    • Linux
    • 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.

      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.

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

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

              Created:
              Updated:
              Resolved: