MongoDB. Why are index queries so slow?

Question

You need your help with the following things:

I have a MongoDB collection that contains over 26 million documents. The structure of the document is constant (pastebin.com/iBzW0Fkz)

The collection includes an index in the users field

db.conversations.getIndexes(); 

RESULT - pastebin.com/xHecpw00

All requests for "users" of the field are very slow (more than 100 ms)

 db.getCollection('conversations').find({users: {$all: ["5942328", "9082468"]}}); 

EXPLAIN - pastebin.com/0C11Cr9F

 db.getCollection('conversations').find({users: "9163099"}); 

EXPLAIN - pastebin.com/CmvuDf10

A list of questions:

• Why are index queries so slow?
• How to warm up indexes after restarting a server or mongodb process? The Touch () command is no longer available in the WiredTiger engine.

Additional information is provided here.

 db.stats(); 

RESULT - pastebin.com/9JZF8ChQ

 db.getCollection('conversations').stats(); 

RESULT - pastebin.com/17yV4Fsi

 db.conversations.getIndexes(); 

RESULT - pastebin.com/xHecpw00

Server Information:

lscpu - pastebin.com/k7wUE4gH

lshw -short - pastebin.com/w5XYuY3U

There is an assumption that the read operation from the hard disk is a bottleneck, and the SSD may solve this problem, but there is no way to test it.

Thanks in advance.

Answer 1

• I think the problem is that you have a lot of data.
• Your index is in the array field, which is inefficient because your index becomes very large, since MongoDB indexes each value of the array, so you can query for individual elements.

You might want to look into the markup to split your data into multiple instances, but note that you cannot select users as the fragment key