2014년 12월 13일 토요일

Issue in max_pool_size and New instance MongoClient.

We have a large number of requests to the database(write/read, almost write(90%)).
I Create singleton method for instance of MongoClient and I set max_pool_size in None.
this my singleton:

@classmethod
@_checkExistMongo
def get_connection(cls):
    """
        Singleton method for running Mongo instance
    """
    if cls.__db is None:
        config = db_handle.getDBHandleMongoDB()
        cls.__DBName = config['db_name']
        if config['turn'] != 'On':
            toLog("Mongo config turn is not set to 'On'!", LOG_ERROR)
        url = "mongodb://%s:%s@%s:%s" % (config['db_username'],
            config['db_password'], config['db_host'],
            config['db_port']
        )
        cls.__db = MongoClient(url, max_pool_size=None)
    return cls.__db



Just when the query runs, unexpected time consuming. 

db.find({'login_time': {'$gte': datetime.datetime(2014, 10, 14, 16, 45)}}, {'unique_id_value': 0, '_id': 0, 'group_id': 0, 'unique_id': 0, 'isp_id': 0}).skip(0).limit(20).sort('logout_time', 1)
It takes a very long time(bottleneck).
And explain this query:

 {u'nYields': 17272, u'millisShardTotal': 16533, u'millisShardAvg': 5511, u'numQueries': 3, u'nChunkSkips': 0, u'shards': {u'localhost:57017': [{u'nYields': 3994, u'stats': {u'works': 511348, u'isEOF': 0, u'needFetch': 0, u'needTime': 0, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'PROJECTION', u'children': [{u'works': 511348, u'isEOF': 0, u'needFetch': 0, u'needTime': 511328, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'KEEP_MUTATIONS', u'children': [{u'works': 511348, u'isEOF': 0, u'locsForgotten': 0, u'needFetch': 0, u'needTime': 511328, u'matchTested_0': 0, u'matchTested_1': 0, u'dupsDropped': 0, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'OR', u'children': [{u'works': 511348, u'memUsage': 10703, u'memLimit': 33554432, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 511327, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'SORT', u'children': [{u'works': 511327, u'isEOF': 1, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'SHARDING_FILTER', u'children': [{u'works': 511326, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 3994, u'alreadyHasObj': 0, u'invalidates': 5, u'unyields': 3994, u'type': u'FETCH', u'children': [{u'works': 511326, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 1, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 511326, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 3994, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 5, u'unyields': 3994, u'type': u'IXSCAN', u'children': [], u'advanced': 511326, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 511326}], u'advanced': 511326}], u'advanced': 20}, {u'works': 0, u'memUsage': 0, u'memLimit': 33554432, u'isEOF': 0, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 0, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'SORT', u'children': [{u'works': 0, u'isEOF': 0, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 3994, u'invalidates': 5, u'unyields': 3994, u'type': u'SHARDING_FILTER', u'children': [{u'works': 0, u'isEOF': 0, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 3994, u'alreadyHasObj': 0, u'invalidates': 5, u'unyields': 3994, u'type': u'FETCH', u'children': [{u'works': 0, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 0, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 3994, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 5, u'unyields': 3994, u'type': u'IXSCAN', u'children': [], u'advanced': 0, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 0}], u'advanced': 0}], u'advanced': 0}], u'advanced': 20, u'dupsTested': 20}], u'advanced': 20}], u'advanced': 20}, u'filterSet': False, u'allPlans': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 511326, u'clauses': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 511326, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 511326}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'nscanned': 511326}], u'millis': 4291, u'nChunkSkips': 0, u'n': 20, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 511326, u'clauses': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 511326, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 511326}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'server': u'pptsd055:57017', u'nscanned': 511326, u'nscannedObjectsAllPlans': 511326, u'nscannedAllPlans': 511326}], u'localhost:37017': [{u'nYields': 0, u'stats': {u'works': 6, u'isEOF': 1, u'needFetch': 0, u'needTime': 0, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'PROJECTION', u'children': [{u'works': 6, u'isEOF': 1, u'needFetch': 0, u'needTime': 5, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'KEEP_MUTATIONS', u'children': [{u'works': 6, u'isEOF': 1, u'locsForgotten': 0, u'needFetch': 0, u'needTime': 5, u'matchTested_0': 0, u'matchTested_1': 0, u'dupsDropped': 0, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'OR', u'children': [{u'works': 3, u'memUsage': 0, u'memLimit': 33554432, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 1, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'SORT', u'children': [{u'works': 1, u'isEOF': 1, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'SHARDING_FILTER', u'children': [{u'works': 1, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 0, u'alreadyHasObj': 0, u'invalidates': 0, u'unyields': 0, u'type': u'FETCH', u'children': [{u'works': 1, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 1, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 0, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 0, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 0, u'unyields': 0, u'type': u'IXSCAN', u'children': [], u'advanced': 0, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 0}], u'advanced': 0}], u'advanced': 0}, {u'works': 3, u'memUsage': 0, u'memLimit': 33554432, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 1, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'SORT', u'children': [{u'works': 1, u'isEOF': 1, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 0, u'invalidates': 0, u'unyields': 0, u'type': u'SHARDING_FILTER', u'children': [{u'works': 1, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 0, u'alreadyHasObj': 0, u'invalidates': 0, u'unyields': 0, u'type': u'FETCH', u'children': [{u'works': 1, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 1, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 0, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 0, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 0, u'unyields': 0, u'type': u'IXSCAN', u'children': [], u'advanced': 0, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 0}], u'advanced': 0}], u'advanced': 0}], u'advanced': 0, u'dupsTested': 0}], u'advanced': 0}], u'advanced': 0}, u'filterSet': False, u'allPlans': [{u'nChunkSkips': 0, u'n': 0, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 0, u'clauses': [{u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'nscanned': 0}], u'millis': 0, u'nChunkSkips': 0, u'n': 0, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 0, u'clauses': [{u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'server': u'pptsd055:37017', u'nscanned': 0, u'nscannedObjectsAllPlans': 0, u'nscannedAllPlans': 0}], u'localhost:47017': [{u'nYields': 13278, u'stats': {u'works': 1699613, u'isEOF': 0, u'needFetch': 0, u'needTime': 0, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'PROJECTION', u'children': [{u'works': 1699613, u'isEOF': 0, u'needFetch': 0, u'needTime': 1699593, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'KEEP_MUTATIONS', u'children': [{u'works': 1699613, u'isEOF': 0, u'locsForgotten': 0, u'needFetch': 0, u'needTime': 1699593, u'matchTested_0': 0, u'matchTested_1': 0, u'dupsDropped': 0, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'OR', u'children': [{u'works': 1699613, u'memUsage': 10580, u'memLimit': 33554432, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 1699592, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'SORT', u'children': [{u'works': 1699592, u'isEOF': 1, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'SHARDING_FILTER', u'children': [{u'works': 1699591, u'isEOF': 1, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 13278, u'alreadyHasObj': 0, u'invalidates': 0, u'unyields': 13278, u'type': u'FETCH', u'children': [{u'works': 1699591, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 1, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 1699591, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 13278, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 0, u'unyields': 13278, u'type': u'IXSCAN', u'children': [], u'advanced': 1699591, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 1699591}], u'advanced': 1699591}], u'advanced': 20}, {u'works': 0, u'memUsage': 0, u'memLimit': 33554432, u'isEOF': 0, u'forcedFetches': 0, u'needFetch': 0, u'needTime': 0, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'SORT', u'children': [{u'works': 0, u'isEOF': 0, u'needFetch': 0, u'needTime': 0, u'chunkSkips': 0, u'yields': 13278, u'invalidates': 0, u'unyields': 13278, u'type': u'SHARDING_FILTER', u'children': [{u'works': 0, u'isEOF': 0, u'forcedFetches': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'yields': 13278, u'alreadyHasObj': 0, u'invalidates': 0, u'unyields': 13278, u'type': u'FETCH', u'children': [{u'works': 0, u'isMultiKey': 0, u'dupsTested': 0, u'yieldMovedCursor': 0, u'isEOF': 0, u'needFetch': 0, u'matchTested': 0, u'needTime': 0, u'keysExamined': 0, u'seenInvalidated': 0, u'dupsDropped': 0, u'yields': 13278, u'boundsVerbose': u"field #0['login_time']: [new Date(9223372036854775807), new Date(1413305100000)], field #1['logout_time']: [MaxKey, MinKey]", u'invalidates': 0, u'unyields': 13278, u'type': u'IXSCAN', u'children': [], u'advanced': 0, u'keyPattern': u'{ login_time: -1, logout_time: -1 }'}], u'advanced': 0}], u'advanced': 0}], u'advanced': 0}], u'advanced': 20, u'dupsTested': 20}], u'advanced': 20}], u'advanced': 20}, u'filterSet': False, u'allPlans': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 1699591, u'clauses': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 1699591, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 1699591}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'nscanned': 1699591}], u'millis': 12242, u'nChunkSkips': 0, u'n': 20, u'cursor': u'QueryOptimizerCursor', u'scanAndOrder': False, u'nscannedObjects': 1699591, u'clauses': [{u'nChunkSkips': 0, u'n': 20, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 1699591, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 1699591}, {u'nChunkSkips': 0, u'n': 0, u'cursor': u'BtreeCursor login_time_-1_logout_time_-1', u'scanAndOrder': True, u'indexBounds': {u'logout_time': [[{u'$maxElement': 1}, {u'$minElement': 1}]], u'login_time': [[datetime.datetime(53970, 8, 17, 7, 12, 55, 807000), datetime.datetime(2014, 10, 14, 16, 45)]]}, u'nscannedObjects': 0, u'isMultiKey': False, u'indexOnly': False, u'nscanned': 0}], u'server': u'pptsd055:47017', u'nscanned': 1699591, u'nscannedObjectsAllPlans': 1699591, u'nscannedAllPlans': 1699591}]}, u'n': 40, u'cursor': u'QueryOptimizerCursor', u'numShards': 3, u'clusteredType': u'ParallelSort', u'nscannedAllPlans': 2210917, u'nscannedObjectsAllPlans': 2210917, u'millis': 12243, u'nscanned': 2210917, u'nscannedObjects': 2210917}

sorry for long block code!

We have made these index on the collection:
mongos> db.connection_log.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"user_id" : 1
},
"name" : "user_id_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"ras_id" : 1
},
"name" : "ras_id_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"retry_count" : 1
},
"name" : "retry_count_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"credit_used" : 1
},
"name" : "credit_used_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"details.mac" : 1
},
"name" : "details.mac_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"username" : 1
},
"name" : "username_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"login_time" : -1,
"logout_time" : -1
},
"name" : "login_time_-1_logout_time_-1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"type_details.in_bytes" : 1,
"type_details.out_bytes" : 1
},
"name" : "type_details.in_bytes_1_type_details.out_bytes_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"details.kill_reason" : 1
},
"name" : "details.kill_reason_1",
"ns" : "IBSng.connection_log"
},
{
"v" : 1,
"key" : {
"details.terminate_cause" : 1
},
"name" : "details.terminate_cause_1",
"ns" : "IBSng.connection_log"
}
]


Also We have one server, and run 3 shard on local host with tree mongod.:
mongos> sh.status()
--- Sharding Status --- 
  sharding version: {
"_id" : 1,
"version" : 4,
"minCompatibleVersion" : 4,
"currentVersion" : 5,
"clusterId" : ObjectId("5438cdba616746630d59a412")
}
  shards:
{  "_id" : "shard0000",  "host" : "localhost:37017" }
{  "_id" : "shard0001",  "host" : "localhost:47017" }
{  "_id" : "shard0002",  "host" : "localhost:57017" }
  databases:
{  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
{  "_id" : "IBSng",  "partitioned" : true,  "primary" : "shard0000" }
IBSng.connection_log
shard key: { "_id" : 1 }
chunks:
shard0000 42
shard0002 48
shard0001 45
too many chunks to print, use verbose if you want to force print
{  "_id" : "test",  "partitioned" : false,  "primary" : "shard0000" }
{  "_id" : "db",  "partitioned" : false,  "primary" : "shard0002" }


And now...
But, When I create other instance of mongoclient in my serivce, and then I execute this query,it is very fast(time< 0.1s).

Where is my bottleneck?
Will I have problems in connecting to the database with MonogClient and define max_pool_size?



Your query is slow because the shards must search over 2 million documents to find the 20 you're looking for:

'nscanned': 2210917

If you look deeper into the explain, you'll see one shard has no matching documents, one has 511,326 matches, and one has 1,699,591 matches. These 2 million documents in total must then be searched for the oldest 20 logouts.

I believe your query is best served by an index on {logout_time: 1, login_time: 1}. Your index puts "login_time" before "logout_time" but an index in the opposite order is better. Sort fields should come before range-query fields in indexes, see my article here:

http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/#method
Consider dropping the old index or using a "hint" to force the use of the new one:


PyMongo's max_pool_size is not relevant to your bottleneck: the problem is your index. If you see a query finish in milliseconds sometimes and 20 seconds other times I think you're talking about different queries: even MongoDB can't search through 2 million documents in a millisecond.



In addition to what Jesse said about the index, it looks like you are
sharding on _id field.   If your _id is the MongoDB generate
ObjectId() then you are sharding on a monotonically increasing field
and you are likely to run into issues with distributing your write
evenly (it's possible that uneven distribution of documents that have
recent login times is related to that as well).



Thank you very much.



@alsa
has 10gen considered reversing the bits in the ID,  or supporting a function on sharding to use reversal of the bits in the key as the sharding key. that would solve that problem and allow the use of the ID as a natural sharding key. 



Tim, a reversed ObjectId would be close to random, which is optimized for evenly distributed writes (but prohibits range queries). We have a solution for this use case: hashed shard keys.



작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)