I have a MongoDB replica set with one primary, one arbiter, one vote bearing secondary, and two no vote bearing secondary servers.
The two non-vote bearing secondaries are bare metal mongo installs to help with heavy aggregation queries. (each 16cores/128GBRam/SSD)
The replica set member configuration is at the bottom of the question.
The following query is run multiple times a minute (around 100) against different collections (~10).
The query used to take no longer than 2 seconds at worst.
Since about a week ago I have noticed that applications that depend on that query were erroring on timeout. I have run the query against the replica set connection (mongo+srv), or individual members - with the same result - "timeout"
I cannot recall any events to have caused the issue and I am wondering if the query might be constructed somehow fundamentally wrong to be causing the issue.
The query is selecting the least extracted item that has not been deleted and has not been archived.
I have indexed the fields "attributes", "attributes.deleted", "attributes.archieved", "last_extracted" but this has not helped.
The size of the collections is between 2gb/50K records to 10gb/150k records.
Is there anything wrong with this query?
db.getCollection("threads").aggregate(
[{'$match':
{'$and':
[{'$or':
[{'attributes':
{'$exists': false}},
{'attributes.deleted':
{'$exists': false}},
{'attributes.deleted': {'$eq': false}}]},
{'$or':
[{'attributes.archived':
{'$exists': false}},
{'attributes.archived':
{'$eq': false}}]}
]}},
{'$project': {'_id': 1, 'url': 1, 'last_extraction': 1}},
{'$sort': {'last_extraction': 1}},
{'$limit': 1}
])
Replica Set Members Configuration
rs0:SECONDARY> cfg.members
[
{
"_id" : 1,
"host" : "XXXXXX1:27777",
"arbiterOnly" : false,
"buildIndexes" : true,
"hidden" : false,
"priority" : 0.8,
"tags" : {
},
"slaveDelay" : NumberLong(0),
"votes" : 1
},
{
"_id" : 2,
"host" : "XXXXX2:27777",
"arbiterOnly" : false,
"buildIndexes" : true,
"hidden" : false,
"priority" : 0.2,
"tags" : {
},
"slaveDelay" : NumberLong(0),
"votes" : 1
},
{
"_id" : 3,
"host" : "XXXXX3:27777",
"arbiterOnly" : true,
"buildIndexes" : true,
"hidden" : false,
"priority" : 0,
"tags" : {
},
"slaveDelay" : NumberLong(0),
"votes" : 1
},
{
"_id" : 4,
"host" : "XXXXXX4:27777",
"arbiterOnly" : false,
"buildIndexes" : true,
"hidden" : false,
"priority" : 0,
"tags" : {
},
"slaveDelay" : NumberLong(0),
"votes" : 0
},
{
"_id" : 5,
"host" : "XXXXXXX5:27776",
"arbiterOnly" : false,
"buildIndexes" : true,
"hidden" : false,
"priority" : 0,
"tags" : {
},
"slaveDelay" : NumberLong(0),
"votes" : 0
}
]