How to avoid a huge query result causing an OutOfMemoryException

Here's my situation:

From my Grails controller, I call a service, which que开发者_JAVA百科ries a database read-only, transforms the result into JSON, and returns the result.

Specs are: JDK 1.6, Tomcat 5.5, Grails 1.3.4, DB via JNDI

Tomcats MaxPermSize is set to 256m and Xmx to 128m.

EDIT: Increasing the memory should be the last resort

The service method:

  String queryDB(String queryString) {
    StringWriter writer = new StringWriter()
    JSonBuilder json = new JSonBuilder(writer)
    def queryResult = SomeDomain.findAllBySomePropIlike("%${queryString}%")

    json.whatever {
      results {
        queryResult.eachWithIndex { qr, i ->
          // insert domain w/ properties
        }
      }
    }
    queryResult = null
    return writer.toString()
  }

Now, when queryString == 'a' the result set is huge and I end up with this:

[ERROR] 03/Nov/2010@09:46:39,604 [localhost].[/grails-app-0.1].[grails] - Servlet.service() for servlet grails threw exception
java.lang.OutOfMemoryError: GC overhead limit exceeded
    at org.codehaus.groovy.util.ComplexKeyHashMap.init(ComplexKeyHashMap.java:81)
    at org.codehaus.groovy.util.ComplexKeyHashMap.<init>(ComplexKeyHashMap.java:46)
    at org.codehaus.groovy.util.SingleKeyHashMap.<init>(SingleKeyHashMap.java:29)
    at groovy.lang.MetaClassImpl$Index.<init>(MetaClassImpl.java:3381)
    at groovy.lang.MetaClassImpl$MethodIndex.<init>(MetaClassImpl.java:3364)
    at groovy.lang.MetaClassImpl.<init>(MetaClassImpl.java:140)
    at groovy.lang.MetaClassImpl.<init>(MetaClassImpl.java:190)
    at groovy.lang.MetaClassImpl.<init>(MetaClassImpl.java:196)
    at groovy.lang.ExpandoMetaClass.<init>(ExpandoMetaClass.java:298)
    at groovy.lang.ExpandoMetaClass.<init>(ExpandoMetaClass.java:333)
    at groovy.lang.ExpandoMetaClassCreationHandle.createNormalMetaClass(ExpandoMetaClassCreationHandle.java:46)
    at groovy.lang.MetaClassRegistry$MetaClassCreationHandle.createWithCustomLookup(MetaClassRegistry.java:139)
    at groovy.lang.MetaClassRegistry$MetaClassCreationHandle.create(MetaClassRegistry.java:122)
    at org.codehaus.groovy.reflection.ClassInfo.getMetaClassUnderLock(ClassInfo.java:165)
    at org.codehaus.groovy.reflection.ClassInfo.getMetaClass(ClassInfo.java:182)
    at org.codehaus.groovy.runtime.callsite.ClassMetaClassGetPropertySite.<init>(ClassMetaClassGetPropertySite.java:35)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.createClassMetaClassGetPropertySite(AbstractCallSite.java:308)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.createGetPropertySite(AbstractCallSite.java:258)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.acceptGetProperty(AbstractCallSite.java:245)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGetProperty(AbstractCallSite.java:237)
    at org.codehaus.groovy.grails.plugins.web.filters.FilterToHandlerAdapter.accept(FilterToHandlerAdapter.groovy:196)
    at org.codehaus.groovy.grails.plugins.web.filters.FilterToHandlerAdapter$accept.callCurrent(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallCurrent(CallSiteArray.java:44)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:143)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:159)
    at org.codehaus.groovy.grails.plugins.web.filters.FilterToHandlerAdapter.preHandle(FilterToHandlerAdapter.groovy:107)
    at org.springframework.web.servlet.HandlerInterceptor$preHandle.call(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:133)
    at org.codehaus.groovy.grails.plugins.web.filters.CompositeInterceptor.preHandle(CompositeInterceptor.groovy:42)
    at org.codehaus.groovy.grails.web.servlet.GrailsDispatcherServlet.doDispatch(GrailsDispatcherServlet.java:282)

One approach I found on the web regards some leaks in Hibernate and domain validation, explained here and in detail here. I'm just about to test it, but I don't know if this is really the solution for my problem and (if it is) at which point it's best to clean up GORM.

Or is there another memory leak in my code? Ideas anyone?

EDIT: As far as I am now, the exception occurs at the point where the finder method is called. That means that GORM isn't able to handle the amount of data returned by the database, right? Sorry for asking like a greenhorn, but I have never encountered such a problem, even with very large result sets.

Sun (this link isn't valid anymore) had documented this OutOfMemoryError as follows:

The parallel / concurrent collector will throw an OutOfMemoryError if too much time is being spent in garbage collection: if more than 98% of the total time is spent in garbage collection and less than 2% of the heap is recovered, an OutOfMemoryError will be thrown. This feature is designed to prevent applications from running for an extended period of time while making little or no progress because the heap is too small. If necessary, this feature can be disabled by adding the option -XX:-UseGCOverheadLimit to the command line.

In other words, that error is a feature, a hint to increase available memory (which is not a preferred option in your case, as you've mentioned). Some developers consider this feature not to be useful in every use case, so check out turning it off.

Another option to those already suggested would be to work in pages of results. Instead of using the dynamic finder, use Criteria and page through the results yourself. Here's a naive, pseudocode example:

def offset = 0
def max = 50
while(stillMoreResults) {
    def batch = SomeDomain.findAllBySomePropIlike("%${queryString}%", [max: max, offset: offset])
    appendBatchToJsonResult(batch)
    offset += max
}

You could tweak the batch size according to your memory requirements. This would avoid having to adjust the memory.

Edit

I just re-read Fletch's answer and noticed that he mentioned this as a solution and you commented on it. I'll leave mine here since it's got an example, but if Fletch adds a paging example to his, I'll delete this answer since he mentioned it before I did.

If you don't want to increase memory, maybe you should only search strings larger than a certain amount. I guess this is some kind of type-ahead/suggestion function; maybe you could start searching when there are three characters or so. Otherwise, maybe paged results is an option?

By the way architecturally the controller is intended to handle interaction with the outside world and its formats, i.e. you would probably want your service just to return the objects and your controller to do the JSON conversion. But this won't solve your current problem.

I would also suggest you only return the properties you need with this query type ahead query and get the full domain object with the actual data the user requires.

The JSON builder is going to create alot of objects and comsume memory. For example in a type-ahead for users, I would only return basic name information and an id instead of the complete object

In a Grails app using a MySQL database (MySQL having been installed through Homebrew), I got this same problem, oddly enough, only by running the app without having started the MySQL server first. Thus simply running

mysql.server start

fixed the problem for me.