CODEWITHSUNDEEP
mongodbindexing
Constantin Guay
Constantin Guay

Reputation: 1664

MongoDB: Index is pretty slow on 100+ mio docs

I'm doing count on a collection with more than 100 millions documents.

My query is:

{
"domain": domain,
"categories" : "buzz",
"visit.timestamp" : { "$gte": date_from, "$lt": date_to },
}

I project only _id.

I have some indexes on it, like, per example:

{ "visit.timestamp": -1 }

and compound index like:

{ "visit.timestamp": -1, "domain": 1, "categories" : 1 }

A count based on, per example, 30 last days gives results in ~30 seconds. An explain() shows me that the query use the simplest index: { "visit.timestamp": -1 }

So I tried to force the compound index in other order:

{ "categories" : 1, "domain": 1, "visit.timestamp": -1 }
{ "domain": 1, "categories" : 1, "visit.timestamp": -1 }

Then, the query uses one of them, but the result takes much longer: ~60 seconds in first case, and for the other one, more than 241 seconds!

Note 1: It's the same result with aggregation framework, but it's not surprising.

Note 2: "visit.timestamp" is an ISODate. Each document is more recent than the previous one.

Note 3: The count returns ~1.4 million documents (among the ~105 millions) but examined 12 millions docs (see below).

Question:

1/ I don't get why a query takes longer when using an index that should covered it completely. Do you have an explanation?

2/ Do you have any hint to improve the response time of this query? The explain() shows that the query looked at:

"totalKeysExamined": 12628476,
"totalDocsExamined": 12628476,

Because, as I can understand, the index cover only the date index visit.timestamp and so all docs within the time-frame has to be examined.

Upvotes: 0

Views: 100

Answers (1)

yair
yair

Reputation: 9245

Second question:

  1. Make sure the problem is in MongoDB's scope. Isolate it from your application code and I/O. Do that by connecting to (one of) your MongoDB server(s) locally and execute the query.
  2. Happens locally? Check CPU and disk health of your server(s).
  3. CPU(s) and disk(s) all fit no sweat? Make sure your index fits in to RAM. Citing from MongoDB's FAQ:

What happens if an index does not fit into RAM?

When an index is too large to fit into RAM, MongoDB must read the index from disk, which is a much slower operation than reading from RAM. Keep in mind an index fits into RAM when your server has RAM available for the index combined with the rest of the working set.

In certain cases, an index does not need to fit entirely into RAM. For details, see Indexes that Hold Only Recent Values in RAM.

First question:

Maybe your index doesn't fit into RAM. And making it compound may increase the number of I/O operations to the disk. I'm no MongoDB expert though.

Upvotes: 1

Related Questions