...
- It would be suggested to have a usable index for every query that we have. Slow queries can reduce the overall performance.
- Obtaining a list of queries would be ideal for index creation. The current indexes have not been designed with index intersection in mind.
- Common trend amongst slow queries is `count`
Improvements:
After applying the index on Nov 11, 11:05, we can see a significant improvement as expected.
...
| Expand |
|---|
| title | db.contents.stats(1024*1024) loadtest |
|---|
|
"_id_" : 290,
"parent_id_1" : 296,
"parent_ids_1" : 200,
"comment_thread_id_1" : 319,
"author_id_1" : 329,
"tags_array_1" : 293,
"last_activity_at_-1" : 245,
"author_id_1_course_id_1" : 496,
"votes.up_1__type_1" : 630,
"votes.down_1__type_1" : 596,
"commentable_id_1_created_at_-1" : 548,
"course_id_1__type_1_created_at_-1" : 933,
"_type_1_comment_thread_id_1_author_id_1_updated_at_1" : 607,
"comment_thread_id_1_sk_1" : 296,
"comment_thread_id_1_endorsed_1" : 142,
"_type_1_course_id_1_pinned_-1_created_at_-1" : 948,
"delete_spam_1" : 0,
"_type_1_course_id_1_pinned_-1_comment_count_-1_created_at_-1" : 1003,
"_type_1_course_id_1_pinned_-1_votes.point_-1_created_at_-1" : 1057,
"commentable_id_1" : 254,
"_type_1_course_id_1_context_1_pinned_-1_created_at_-1" : 1010,
"_type_1_commentable_id_1_context_1_pinned_-1_created_at_-1" : 784,
"_type_1_context_1" : 425,
"_type_-1_course_id_1_context_1_pinned_-1_last_activity_at_-1_created_at_-1" : 998,
"_type_1_course_id_1_context_1" : 860 |
Observations:
Below is a compound index that we have that is not selective enough. Whether its due to poor indexes or our queries is a different question.
| Expand |
|---|
|
db.contents.find({"_type":"CommentThread", "course_id":"BerkeleyX/GG101x/1T2014", "context":"course"}).explain()
{
"cursor" : "BtreeCursor _type_-1_course_id_1_context_1_pinned_-1_last_activity_at_-1_created_at_-1",
"isMultiKey" : false,
"n" : 76481,
"nscannedObjects" : 76481,
"nscanned" : 76481,
"nscannedObjectsAllPlans" : 77520,
"nscannedAllPlans" : 77531,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 6691,
"nChunkSkips" : 0,
"millis" : 85812,
"indexBounds" : {
"_type" : [
[
"CommentThread",
"CommentThread"
]
],
"course_id" : [
[
"BerkeleyX/GG101x/1T2014",
"BerkeleyX/GG101x/1T2014"
]
],
"context" : [
[
"course",
"course"
]
],
"pinned" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"last_activity_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"created_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "c0:27017",
"filterSet" : false
} |