M
M
Maxim Boltik2016-11-23 16:47:20
MongoDB
Maxim Boltik, 2016-11-23 16:47:20

MongoDB. Why are index queries so slow?

Hello.
I ask for help in solving the following problem:
There is a collection of 26 million documents in size.
The structure of the document is fixed - pastebin.com/iBzW0Fkz
An index is built on the users field - db.conversations.getIndexes() - pastebin.com/xHecpw00
Queries on the users field are 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
Questions:
1. Why are index queries so slow?
2. How to warm up (load into RAM) indexes after restarting the mongod server/process? In WiredTiger, the touch() command has been removed.
More information on collection and database:
db.stats() - pastebin.com/9JZF8ChQ
db.getCollection('conversations').stats() - pastebin.com/17yV4Fsi
db.conversations.getIndexes() - pastebin.com/xHecpw00
Server information:
lscpu - pastebin.com/k7wUE4gH
lshw -short - pastebin.com/w5XYuY3U
There is a suggestion that reading from the HDD is very slow and the solution would be to install an SSD, but we need to explicitly confirm this.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
L
lega, 2016-11-23
@lega

Try to select not the entire document:

db.conversations.find({users: "9163099"}, {_id: 1})
или
db.conversations.find({users: "9163099"}, {users: 1})

What does free -m show?
If I were you, I would buy a VPS in DO for half an hour, and check it - it costs a penny (5-30 rubles) and it’s a matter of a minute * (maybe the problem is not only in the HDD), if it’s a hetzner, then everything is clear.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question