-
Type: Task
-
Resolution: Done
-
Priority: Major - P3
-
None
-
Affects Version/s: 1.11
-
Component/s: Performance
-
Environment:62 GB memory on the host, Linux 7.2, VM Host, 4 cpu
The Database has 2 collection Call Log and ErrorLog.
The CallLog is Indexed on CreateDateTime and is the one that we are having performance issues with.
The Following Query is taking almost 5 - 10 minutes to execute when date range of 1 day is specified. The MongoDB has around 19,000 transactions per hour.
Here is the MongoDB Query in C#
string funcList = ConfigurationManager.AppSettings["FilterTransactionFunctionList"] as string; var filterList = funcList.Split(','); var wclLogCursor = wclMongoDB.GetCollection<CallLog>("CallLog").Find( Query<CallLog>.Where(p => p.CreateDateTime >= start.ToUniversalTime() && p.CreateDateTime <= end.ToUniversalTime()) ); var match = new BsonDocument { { "$match", new BsonDocument {{ "CreateDateTime", new BsonDocument { {"$gte", start.ToUniversalTime() }, {"$lte", end.ToUniversalTime() } }} } }}; var group = new BsonDocument { { "$group", new BsonDocument { { "_id", new BsonDocument {{"FunctionSessionId", "$FunctionSessionId" }}}, {"CallLogKey", new BsonDocument {{"$first", "$CallLogKey" }}}, {"CallSessionId", new BsonDocument {{"$first", "$CallSessionId" }}}, {"CreateDateTime", new BsonDocument {{"$first", "$CreateDateTime" }}}, {"LastCreateDateTime", new BsonDocument {{"$last", "$CreateDateTime" }}}, {"FunctionName", new BsonDocument {{"$first", "$FunctionName" }}}, {"Duration", new BsonDocument {{"$first", "$Duration" }}}, {"Direction", new BsonDocument {{"$first", "$Direction" }}}, {"Target", new BsonDocument {{"$first", "$Target" }}}, {"UICode", new BsonDocument {{"$first", "$UICode" }}}, {"UserName", new BsonDocument {{"$first", "$UserName" }}}, {"IsSucceed", new BsonDocument {{"$first", "$IsSucceed" }}}, {"FunctionCount", new BsonDocument {{"$sum", 1}}}, {"Request", new BsonDocument {{"$first", "$CallParameters" }}}, {"Response", new BsonDocument {{"$last", "$CallParameters" }}} } } }; var sort = new BsonDocument { { "$sort", new BsonDocument { {"CreateDateTime", 1} , {"CreateDateTime.getTime()", 1}, } } }; var filterFunctions = new BsonDocument { { "$match", new BsonDocument {{ "FunctionName", new BsonDocument { {"$nin", new BsonArray(filterList) } }}} } }; var project = new BsonDocument { { "$project", new BsonDocument { {"_id", 0}, {"CallLogKey", "$CallLogKey"}, {"CallSessionId", "$CallSessionId"}, {"CreateDateTime","$CreateDateTime"}, {"FunctionSessionId","$_id.FunctionSessionId"}, {"FunctionName","$FunctionName"}, {"Duration", new BsonDocument( "$subtract", new BsonArray() {"$LastCreateDateTime","$CreateDateTime"} )}, {"Direction","$Direction"}, {"Target","$Target"}, {"UICode","$UICode"}, {"UserName","$UserName"}, {"IsSucceed","$IsSucceed"}, {"FunctionCount","$FunctionCount"}, {"Request","$Request"}, {"Response","$Response"}, } } }; var limit = new BsonDocument("$limit", 1000); var pipeline = new AggregateArgs() { AllowDiskUse = true, OutputMode = AggregateOutputMode.Cursor, Pipeline = new[] { match, sort, group, filterFunctions, project } }; var result = wclLogCursor.Collection.Aggregate(pipeline);