MongoDB Querying Large Collections

Question

I have a MongoDB collection called 'Prices' that I am trying to query for prices between a 'startDate' and 'endDate'.

The collection stores prices every 10 seconds, however when querying this collection for graphing purposes only the price every 1-2 minutes is really important.

I've tried to write this query in two different ways:

Method 1: Using {$gte: startDate, $lte: endDate}

 function getResultsInRange(startDate, endDate) {
    if(typeof startDate !== 'undefined' && typeof endDate !== 'undefined') {
        Price.find({timestamp: {$gte: startDate, $lte: endDate}}, 'price timestamp exchange')
        .sort('-timestamp')
        // .populate('exchange')
        .exec(function(err, prices) {
            if(err) {
                res.jsonp({'error': err});
            } else {
                console.log("Found: " + prices.length + " prices");
                res.jsonp(prices);
            }
        });

    }
}

This method throws the following error:

{"error":{"name":"MongoError","$err":"Executor error: Overflow sort stage buffered data usage of 33554490 bytes exceeds internal limit of 33554432 bytes","code":17144}}

If I remove the sort('-timestamp") line, and run this query again I get the following error:

GET /prices/graph/minute - - ms - -
FATAL ERROR: CALL_AND_RETRY_LAST Allocation failed - process out of memory

I have also tried using indexes in this query to try and avoid the 32MB in memory sorting limitation, but haven't been able to get it going. I am wondering if it's caused by the issue:

"The total size of an index entry, which can include structural overhead depending on the BSON type, must be less than 1024 bytes."

described here: (http://docs.mongodb.org/manual/reference/limits/#indexes)

Method 2: Using a While Loop to Query for thte Last Price Each X Minutes (eg. 2)

function getResultsInRange(startDate, endDate) {
    if(typeof startDate !== 'undefined' && typeof endDate !== 'undefined') {

        var currentDate = startDate;
        currentDate.setSeconds(0);

        var priceData = {};

        while(currentDate < endDate) {                
            Price.findOne({'timestamp': {$lte: currentDate}}, 'price timestamp exchange')
            .sort('-timestamp')
            .exec(function(err, price) { 
                if(err) {
                    console.log('ERROR: ' + err);
                } else if (price !== null) {               
                    // PUSH DATA TO ARRAY HERE                        
                }
            });

            // Increment Current Date.
            currentDate.setMinutes(currentDate.getMinutes() + 2);
        }

        res.jsonp(priceData);
    }//end if startDate and endDate are defined (giving us a valid date range).
}// end getResultsInRange()

However, this method does not work, it seems like for each iteration of the loop 'currentDate' = 'startDate' so it's only querying for the last price recorded in the database before 'startdate'.

Edit: Method 3: Using Stream() I've also tried using .stream().

 var query = Price.find({timestamp: {$gte: startDate, $lte: endDate}}, 'price timestamp exchange').populate('exchange').stream();
 query.on('data', function(price) {
     // ADD TO ARRAY
 }).on('error', function(err) {
     console.log("ERROR: " + err);
 }).on('close', function() {
     res.jsonp(priceData); 
 });

Any help would be much appreciated!

Googling your error, you have seen [this bug](http://askubuntu.com/a/503151), right? In your second method, you could try querying between two timestamps instead, `{$and: {{$gte: currentDate}, {$gte: currentDatePlusTwo}} }`. ([Indexing seems to be the way to go](http://askubuntu.com/a/503151)) — ippi, Aug 20 '15 at 02:48
Thanks for your suggestion with $and! It doesn't quite work though. I get the error **Error: Can't use $and with Date.** when using: _{$and: [{$gte: currentDate}, {lte: currentDate}]}_ though. — Dave Rich, Aug 20 '15 at 15:36

score 0 · Answer 1 · answered Aug 20 '15 at 17:06

I figured this out sort of.

I managed to get the index working with by adding an index to the Mongoose Schema:

timestamp: {
    type: Date,
    index: true,
    default: Date.now
},

Then using the following function to perform the query.

function getResultsInRange(startDate, endDate) {
    if(typeof startDate !== 'undefined' && typeof endDate !== 'undefined') {
        Price.find({timestamp: {$gte: startDate, $lte: endDate}}, 'price timestamp exchange')
        .sort('-timestamp')
        .populate('exchange')
        .exec(function(err, prices) {
            if(err) {
                res.jsonp({'error': err});
            } else {
                res.jsonp(prices);
            }
        });    
    }
}

The above works with up to a 14 day range between startDate, and endDate, although it takes about 20 seconds to run even with the index.

MongoDB Querying Large Collections

1 Answers1