Re: Mongodb throwing error: too much data for sort() with no index. add an index or specify a smaller limit

[Adam C]

Per the limits listed here: http://docs.mongodb.org/manual/reference/limits/

MongoDB will only return sorted results on fields without an index if the sort operation uses less than 32 megabytes of memory.

You'll have to be more specific in terms of what you are returning, and ensure that the query is appropriately indexed, but you also have to make sure the result set fits in memory.

Adam

On Friday, July 27, 2012 9:48:31 AM UTC+1, Elan wrote:

Hi All,

 

We are using vaadin lazy loading table with mangodb.  At the time of doing pagination, in some specific skip and size critria, it is throwing above error and most of cases it is working good.  We have the indexes for the cololectiion as well.  Please find below logs:

 

 

Query we used

 

cursor = collection.find(query).skip(skip).limit(size).sort(

new BasicDBObject(SearchConstants.FIELD_REQUEST_ID,"requestId")

.append(SearchConstants.

FIELD_IE_SERIES,"ie_series"));

 

LOGS

com.mongodb.MongoException: too much data for sort() with no index.  add an index or specify a smaller limit
 at com.mongodb.MongoException.parse(MongoException.java:82)
 at com.mongodb.DBApiLayer$MyCollection.__find(DBApiLayer.java:312)
 at com.mongodb.DBCursor._check(DBCursor.java:369)
 at com.mongodb.DBCursor._hasNext(DBCursor.java:504)
 at com.mongodb.DBCursor.hasNext(DBCursor.java:529)
 at com.honeywell.htt.tdc.dao.mongo.TestDataDAOImpl.find(TestDataDAOImpl.java:127)
 at com.honeywell.htt.tdc.delegate.SearchDelegate.findWithFilters(SearchDelegate.java:103)
 at com.honeywell.htt.tdc.utils.SearchQueryFactory.getResult(SearchQueryFactory.java:136)
 at com.honeywell.htt.tdc.utils.SearchQuery.loadItems(SearchQuery.java:60)
 at org.vaadin.addons.lazyquerycontainer.LazyQueryView.queryItem(LazyQueryView.java:233)
 at org.vaadin.addons.lazyquerycontainer.LazyQueryView.getItem(LazyQueryView.java:211)
 at org.vaadin.addons.lazyquerycontainer.LazyQueryContainer.getItem(LazyQueryContainer.java:176)
 at com.jensjansson.pagedtable.PagedTableContainer.getItem(PagedTableContainer.java:70)
 at com.vaadin.ui.AbstractSelect.getItem(AbstractSelect.java:681)
 at com.honeywell.htt.tdc.search.results.PerformanceTestSearchResult$2.generateCell(PerformanceTestSearchResult.java:311)
 at com.honeywell.htt.tdc.search.results.PerformanceTestSearchResult$2.generateCell(PerformanceTestSearchResult.java:1)
 at com.vaadin.ui.Table.getVisibleCellsNoCache(Table.java:1840)
 at com.vaadin.ui.Table.refreshRenderedCells(Table.java:1477)
 at com.vaadin.ui.Table.refreshRowCache(Table.java:2182)
 at com.vaadin.ui.Table.containerItemSetChange(Table.java:3858)
 at com.jensjansson.pagedtable.PagedTable.setPageFirstIndex(PagedTable.java:251)
 at com.jensjansson.pagedtable.PagedTable.setCurrentPage(PagedTable.java:309)
 at com.jensjansson.pagedtable.PagedTable$6.buttonClick(PagedTable.java:139)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)

 

 

[Giovanni Gaglione]

I have the same error.

My query is on two fields (let's say field A and field B), and the sort on other two fields (let's say C and D).

I tried t build different indexes on the fields A,B,C and D (especially putting the fields C and D as last fields in the index), but the error is always the same.

Do I have necessarily to ignore the field A and B (the field on which the query is) to make the query executable without throwing the error?

On Tuesday, July 31, 2012 5:08:46 PM UTC+2, Elan wrote:

Thanks Adam. 

 

It is properly indexed for two fields and result also properly mapped with objects.

It is working sometime and failing for some specific records.  But i couldn't find anything wrong on those documents.

 

Few time it is working for all documents as well.   Please share if you have any idea on this behaviour and i didn't have any clue on this.

[Nasir Rasul]

Could you be hitting the 16 mb document limit? Just a thought.

--
--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb
 
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Giovanni Gaglione]

Are you meaning the index size? If it overcomes the 16MB limit?

2013/11/8 Nasir Rasul <nasir...@gmail.com>

You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/z8tucW0nwxE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--

Giovanni Gaglione                           

LinkedIn: http://lnkd.in/MyZGut

Telefono: +39 388 - 75.06.756
Email: giovanni...@gmail.com

Skype: bitliner 

[Giovanni Gaglione]

However I temporarily "solved" adding the field A and B (the field in the query) in the sort object (that now is based on fields C,D,A,B).

Faster than using the index based only on the fields C and D, but not enough yet (it requires 5 seconds)

 

2013/11/8 Giovanni Gaglione <giovanni...@gmail.com>

[Nasir Rasul]

I think there is a 16 mb limit for results to fit in. For aggregation queries I am certain. For sorting I am not sure.

[Giovanni Gaglione]

The limit is about a single document (not about a cursor on documents). I'm not doing aggregation, never said that.

2013/11/8 Nasir Rasul <nasir...@gmail.com>

[Nasir Rasul]

There is a limitation, I just had the wrong number in my head. Just checked the wiki

Mongodb limitations : http://docs.mongodb.org/manual/reference/limits/

Operations

Sorted Documents

MongoDB will only return sorted results on fields without an index if the sort operation uses less than 32 megabytes of memory.

Aggregation Sort Operation

$sort produces an error if the operation consumes 10 percent or more of RAM.

Cheers.
Nasir

- Nasir

[Sam Millman]

If you want to sort you must use an index if you expect to use more than 32mb, this is a peformance limitation, it is (maybe was) considered unperformant to sort further without an index.

[Giovanni Gaglione]

Did you read that I'm using an index and so this limitation is not apllying to my case?

[Sam Millman]

I did  not, the thread was long so I skipped your original reply, hence why it is normally better to make a new thread rather than necromancy old ones however, can you supply a explain() output of said query?

[Asya Kamsky]

Actually, the issue is that an index is *not* being used for sort, the question would be why?

It's likely that for some reason the optimizer is not choosing the index you have - reading your description it didn't sound like you had an index on a, b, c, d at the same time, did you?   If you did can you provide the exact query that you were using that didn't pick up this index?

An option you always have is to hint() an index, but it's best to understand why you need to before doing so, in case there is some other problem.

Asya