0

Counting the number of records in a collection that is matched by a query even on an indexed field takes too much time. For example lets say there is a collection consists of 10000 records, and there is an index on the creationDate field of this collection. Getting the last ten records from the collection is faster than counting the number of records created on the last day. It takes more than 5 seconds, sometimes even up to 70 seconds to return the result of the count query. Do you have any idea how to solve this problem, what is the best way to solve this issue?

Btw we also use morphia, and we saw that getting the count through morphia is even slower, so for count queries, we transform the morphia query to the java driver query. Did anyone encounter a similar situation, why does morphia response even slower? Does this happen only for count queries or is it slow in general compared to using only java driver?

Help, suggestions or work-arounds would be really appreciated, our application relies heavily on count queries and the slowness of system is really annoying for us right now.

Thanks in advance.

cubbuk
  • 7,800
  • 4
  • 35
  • 62
  • And the count query is using an index? What MongoDB version? What Java Driver version? Can you post an explain of the query use for the count? This should get us started troubleshooting it – Sammaye Dec 27 '12 at 22:16
  • Mongodb version: 2.2, java driver: 2.7.3 (I should double check this), morphia 0.99. We have an index on the field that we use in the query. So basically I have a simple date range query on an indexed field and we call the countAll function on the collection. I am out of office right now, I will post the explain query later too. – cubbuk Dec 27 '12 at 22:28
  • 1
    Morphia 0.99 is pretty old. Have you considered updating to the [fork](https://github.com/jmkgreen/morphia), which includes some patches? Not sure if that helps with the performance, but it shouldn't hurt. And the ``.explain()`` output of your query should help getting to the root problem - that query should never take that long. – xeraa Dec 28 '12 at 01:51
  • 1
    next version (2.4) will improve the performance of count significantly. See https://jira.mongodb.org/browse/SERVER-1752 – Asya Kamsky Dec 28 '12 at 04:31
  • `"cursor" : "BtreeCursor updatedAt", "isMultiKey" : false, "n" : 52221, "nscannedObjects" : 52221, "nscanned" : 52221, "nscannedObjectsAllPlans" : 52221, "nscannedAllPlans" : 52221, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 396, "nChunkSkips" : 0, "millis" : 14713, "indexBounds" : { "ua" : [ [ ISODate("2012-12-17T22:00:00Z"), ISODate("2012-12-28T22:00:00Z") ] ] }` – cubbuk Dec 28 '12 at 09:52
  • Could you add the query and ``.getIndexes()`` as well? And ideally everything in the original question - comments are not that well suited for that :) – xeraa Dec 28 '12 at 12:29
  • The Query is : {ua: {"$gte": start, "$lt": end}}, {ua:1}}. And we have index on 14 different fields and ua is one of those fields. I know having this many index might create problems, but our application needs to have that much querying =) – cubbuk Dec 28 '12 at 12:48
  • In general this looks pretty good (n* are all equal, ``scanAndOrder: false``), only the ``nYields`` is pretty high. The query pauses 396 times to let other queries run, but if the query takes so long and you have some others running, this is to be expected. Would be interesting if the fix in 2.4 would help, but I can't really spot any major problem otherwise. Do your indexes fit into RAM (``db.stats()``)? – xeraa Dec 28 '12 at 14:01
  • Unfortunately we have almost 6GB of index and it seems like it doesn't fit into RAM. But interestingly the server that holds mongo always have 1-2 GB of available RAM, do you think if we move mongo to a server with higher memory, there will be a significant improvement? It seems to me the main problem is not with the memory. – cubbuk Dec 28 '12 at 14:56

2 Answers2

1

While this might not be the final answer, let's get started and evolve this further:

  1. Your indexes should always fit into RAM, otherwise you will get really bad performance.

  2. To evaluate how much RAM is used, you can either use 10gen's MMS or check with various tools. For a description plus possible reasons for low (resident) memory usage see http://www.kchodorow.com/blog/2012/05/10/thursday-5-diagnosing-high-readahead/. Or you simply haven't accessed enough data yet in which case you can use MongoDB's touch (but I doubt that since you're already having performance issues).

Besides adding RAM and making sure you use all the available RAM, you could also drop unused indexes or use compound indexes where possible.

xeraa
  • 10,456
  • 3
  • 33
  • 66
  • We will try composite indexes, seems like most of our indexes are not being used because most of our queries are composition of date field and another field where as we had indexes on individual fields. And thanks for the article about low memory usage, it resembles our situation too, we will take the recommendations on that article into account. – cubbuk Dec 31 '12 at 13:14
  • The suggestion in the blog worked like a charm =) We also saw that most of our indexes are not used after all until we convert them into composite indexes, right now the system is working much efficiently, thanks for the suggestions and help =) – cubbuk Jan 02 '13 at 13:56
0

Wait for a fix.

As Asya Kamsky commented out, count performance is really bad on 2.2. The only workaround we've found is to avoid them as much as possible.

(There are other things that are unexplainably slow with mongodb - such as aggregate queries - most of those have associated JIRA issues and are being worked on/scheduled)

ptyx
  • 4,074
  • 1
  • 19
  • 21
  • Seems like beside the count issue of mongo, the main problem resides in our own architecture, we will re-evaluate our system, choosing correct indexes seems like crucial for us. Btw we are looking forward to new release to see how the performance is for count and aggregation framework. – cubbuk Dec 31 '12 at 13:16