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

Query performance very poor when using $all

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 1.6.2
    • Fix Version/s: None
    • Component/s: Performance
    • Labels:
      None
    • Environment:
      Ubuntu lucid server with mongodb-stable package from 10gen's apt repository
    • Operating System:
      ALL

      Description

      At random (seemingly) the performance of all of our queries using $all (and potentially others) have dropped significantly. Queries that were taking 4 to 20 milliseconds are now taking anywhere from 4 to 10 seconds (sometimes even longer). mongostat only shows a blip on the radar for the queries however (ie: it happens in under a second according to mongostat). Here's an example query:

      ----- code ---------
      db.products.count({ "variations.attributeIds" :

      { "$all" : [ 3873 , 3898]}

      , "enabled" : true});
      ----- / code ---------

      another convenient query for timing is:
      ----- code -----
      function timeThis() {
      var ret = new Date()+"\n";
      ret = db.products.count({ "variations.attributeIds" : { "$all" : [ 3873 , 3898]}})"\n";
      ret += new Date();
      return ret;
      }
      timeThis();
      ----- /code -----

      That query takes anywhere from 4 to 8 seconds to execute. CPU usage by the mongod process raises significantly (I'm not sure if this is a bad thing). I've copied the dataset to a machine with 70GB of RAM (yes, 70 gigabytes) and 8 processor cores just to eliminate any potential hardware issues and the problems sill persist.

      the following indexes exist:
      ----- code ---------
      db.products.ensureIndex(

      {"uid":1}

      ,

      {unique:true, dropDups : true}

      );
      db.products.ensureIndex(

      {weight:-1}

      );

      db.products.ensureIndex(

      {"dateCreated": 1, "weight": -1}

      );
      db.products.ensureIndex(

      {"dateCreated": -1, "weight": -1}

      );

      db.products.ensureIndex(

      {"variations.price": 1, "weight": -1}

      );
      db.products.ensureIndex(

      {"variations.price": -1, "weight": -1}

      );

      db.products.ensureIndex(

      {"variations.salePercent": 1, "weight": -1}

      );
      db.products.ensureIndex(

      {"variations.salePercent": -1, "weight": -1}

      );

      db.products.ensureIndex(

      {"keywords": 1, "weight": -1}

      );
      db.products.ensureIndex(

      {"variations.attributeIds":1}

      );
      db.products.ensureIndex(

      {"variations.attributeIds":1, "enabled":1}

      );
      ----- / code ---------

      The dataset attached is half of the collection that is in production that is causing issues, i did the following to reduce it to half:

      ----- code ---------
      function reduceByHalf(colName) {
      var i = 0;
      var c = 0;
      db[colName].find().forEach(function(doc) {
      if ((i%2)==0) {
      db[colName].remove({_id:doc._id});
      c++;
      }
      i++;
      });
      return "Deleted "c" documents";
      }
      reduceByHalf("products");
      ----- / code ---------

      The dataset has roughly 16k documents in it and is attached to this issue in tar/gz format

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                3 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: