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

Query performance very poor when using $all

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • 1.6.2
    • None
    • Performance
    • None
    • Ubuntu lucid server with mongodb-stable package from 10gen's apt repository
    • 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

        1. dump.tar.gz
          66.15 MB
          Brian C. Dilley

        Issue Links

          Activity

            People

              aaron Aaron Staple
              briandilley Brian C. Dilley
              Votes:
              3 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: