MongoDB Query Execution Time Analysis

load-testingmongodb

I'm using Locustio service as load testing tool for my app. Without details, I'm using the same aggregation query with simulated 100 users, in this case RPS (requests per second) is about 20. In first 30 seconds every query time is about 10-50ms, but then (when about 65 users active) it increases to 500-1000ms. I'm using MMAPv1, so query is cached for sure, so the question is: why query execution time is increasing?

Of course, it's only test case, in real I'm doing different queries, but in that case I wasn't sure that all the queries was cached. That's why I decided to test It only with one query. Sorry for my bad explanation.

I think the problem is in connection number, still it's only 100. How cna I optimize the performance of my app?

UPDATE1

db.serverStatus about connections number:

    "connections" : {
        "current" : 20,
        "available" : 51180,
        "totalCreated" : NumberLong(32)
    },

All of my engine settings are defaults
This is my db.collection_name.findOne() part, which involved in aggregate query:

{
    "_id" : ObjectId("571764be1e31c8556e80b589"),
    "ppl_ids" : [
        19,
        1,
        20,
        21,
        22,
    ],
}

The pipeline of aggregate query itself:

{$match: {ppl_ids: {$in: [16,1245,592] } } }

The part of db.collection_name.getIndexes():

    {
        "v" : 1,
        "key" : {
            "ppl_ids" : 1
        },
        "name" : "ppl_ids_1",
        "ns" : "dbname.collection_name",
        "background" : false
    },

Best Answer

Update based on MongoEngine usage in comments:

MongoEngine defers to pymongo for the connection pool setting. Hence, based on the pymongo FAQ:

In versions before 2.6, the default max_pool_size was 10, and it did not actually bound the number of open connections; it only determined the number of connections that would be kept open when no longer in use.

Starting with PyMongo 2.6, the size of the connection pool is capped at max_pool_size, which now defaults to 100. When a thread in your application begins an operation on MongoDB, if all other connections are in use and the pool has reached its maximum, the thread pauses, waiting for a connection to be returned to the pool by another thread.

Assuming you are using a version later than 2.6 you will have a default of 100, which theoretically should be enough. However even if the responses are very quick you still have some overhead (connect, run query, return result, return connection to pool) with the pool in order to reach 100 concurrent users on the front end. Therefore, I would still look at increasing the max_pool_size parameter to see if that unblocks your load testing (say 150 to start with).

Related Solutions

Mongodb – How should I store time series in mongodb

I suggest a single time series entry per document. There are some problems with storing multiple entries per document:

a single document is limited to a certain size (currently 16 MB); this limits how many entries can be stored in a single document
as more entries are added to a document, the entire document (and time series) will needlessly be deleted and reallocated to a larger piece of memory
queries on sub-documents are limited compared to queries on regular documents
documents with very flat structures (like one sub-document for each second) are not performant
the built-in map-reduce does not work as well on sub-documents

Also note a timestamp is built-in to the default MongoDB ObjectId. You can use this if the time series precision is less than one second.

Here is an example BSON document from an event logging library that uses MongoDB:

Example format of generated bson document:
{
    'thread': -1216977216,
    'level': 'ERROR',
    'timestamp': Timestamp(1290895671, 63),
    'message': 'test message',
    'fileName': '/var/projects/python/log4mongo-python/tests/test_mongo_handler.py',
    'lineNumber': 38,
    'method': 'test_emit_exception',
    'loggerName':  'testLogger',
    'exception': {
        'stackTrace': 'Traceback (most recent call last):
                       File "/var/projects/python/log4mongo-python/tests/test_mongo_handler.py", line 36, in test_emit_exception
                       raise Exception(\'exc1\')
                       Exception: exc1',
        'message': 'exc1',
        'code': 0
    }
}

Since an event log is similar to a time series, it may be worth studying the rest of the code. There are versions in Java, C#, PHP, and Python.

Here is another similar open source project: Zarkov

[update] In response to @RockScience's comment, I've adding some more references:

Very verbose case study of how MongoDB was used to store time series. Interesting parts:
- Slide 25: how much data? 20 4-byte data points @ 1 minute intervals for 10 years (not apparent if these are actual statistics or just projected estimates)
- Slide 66: shows sample MongoDB document used to store time series data
Best practices for storing log data, straight from the creators of MongoDB

Postgresql – Using MongoDB and PostgreSQL together

Some thoughts....

Typically one does not want to store pieces of tightly interrelated information in different systems. The chances of things getting out of sync is significant and now instead of one problem on your hands you have two. One thing you can do with Mongo though is use it to pipeline your data in or data out. My preference is to keep everything in PostgreSQL to the extent this is possible. However, I would note that doing so really requires expert knowledge of PostgreSQL programming and is not for shops unwilling to dedicate to using advanced features. I see a somewhat different set of options than you do. Since my preference is not something I see listed I will give it to you.

You can probably separate your metadata into common data, data required for classes, and document data. In this regard you would have a general catalog table with the basic common information plus one table per class. In this table you would have an hstore, json, or xml field which would store the rest of the data along with columns where you are storing data that must be constrained significantly. This would reduce what you need to put in these tables per class, but would allow you to leverage constraints however you like. The three options have different issues and are worth considering separately:

hstore is relatively limited but also used by a lot of people. It isn't extremely new but it only is a key/value store, and is incapable of nested data structures, unlike json and xml.

json is quite new and doesn't really do a lot right now. This doesn't mean you can't do a lot with it, but you aren't going to do a lot out of the box. If you do you can expect to do a significant amount of programming, probably in plv8js or, if you want to stick with older environments, plperlu or plpython. json is better supported in 9.3 though at least in current development snapshots, so when that version is released things will get better.

xml is the best supported of the three, with the most features, and the longest support history. Then again, it is XML.....

However if you do decide to go with Mongo and PostgreSQL together, note that PostgreSQL supports 2 phase commit meaning you can run the write operations, then issue PREPARE TRANSACTION and if this succeeds do your atomic writes in Mongo. If that succeeds you can then COMMIT in PostgreSQL.

Best Answer

Related Solutions

Mongodb – How should I store time series in mongodb

Postgresql – Using MongoDB and PostgreSQL together

Related Question