Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: domain of cashtag@fastmail.fm designates
 66.111.4.26 as permitted sender)
Message-Id: <1201719088.8102.1234163847@webmail.messagingengine.com>
From: "Harvey Kim" <cashtag@fastmail.fm>
To: "ibatis user" <user-java@ibatis.apache.org>
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="iso-8859-1"
MIME-Version: 1.0
References: <C3C6031E.3DA7%clamey@localmatters.com>
   <1201716326.31524.1234151879@webmail.messagingengine.com>
   <2f55db670801301036l5f890babl37e8ec52aea7559d@mail.gmail.com>
Subject: Re: Exit out of rowHandler
In-Reply-To: <2f55db670801301036l5f890babl37e8ec52aea7559d@mail.gmail.com>
Date: Wed, 30 Jan 2008 10:51:28 -0800

Here is the code that is causing the memory leak:

  sqlMapClient.queryWithRowHandler("getSomeList", queryObject, this); 
  public void handleRow (Object valueObject)
  {
    ;
  }   

As you can see, I do nothing except issue the query.  my "handleRow" is
empty and it eventually runs out of memory.

Now, here is how I found out about the memory problem - as the number of
rows started to increase, the available memory started going down.

  public void handleRow (Object valueObject)
  {
    try
    {

      Runtime r = Runtime.getRuntime(); 
      if (rowCount++ % 10000 == 0)
      {
        System.out.println("***********************************************
          " + rowCount);
        System.out.println("free memory is: " + r.totalMemory() + " : "
        + r.freeMemory());       
        //r.gc(); 
        //System.out.println("free memory is: " + r.totalMemory() + " :
        " + r.freeMemory());
      }
    }
    catch (Exception e) 
    {
      logger.error("DownloadAction: handleRow ", e);
    } 
  }   

I understand your position but if all I'm doing is issuing the query and
my handleRow method does absolutely nothing - AND using JDBC goes
through without a memory problem - I honestly don't know where else to
look because I don't have any other code to look at.  It's simply ibatis
and a "handleRow" call.

Here is my JDBC equivalent.  Essentially same as above.

Context context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("jdbc/XXXXX");
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement =
connection.prepareStatement(sqlString);
ResultSet resultSet = preparedStatement.executeQuery();
          
while (resultSet.next())
{
/* java stuff */
}


On Wed, 30 Jan 2008 11:36:05 -0700, "Nathan Maves"
<nathan.maves@gmail.com> said:
> I too have used RH'ers over very large data sets and never saw any memory
> issues.  Be careful when issuing statements like "And there is absolutely
> no
> doubt that the culprit is ibatis".   I can't tell you how many times we
> have
> seen claim of ibatis failures only to find our that it was not.
> 
> Give us some actual code and test cases that cause this issue.
> 
> Nathan
> 
> On Jan 30, 2008 11:05 AM, Harvey Kim <cashtag@fastmail.fm> wrote:
> 
> > You are absolutely right - I can let it ride it out.  And I would agree
> > with you if the query came back within a second or two.  But million
> > rows don't come back that quickly and the user will want to continue
> > with other operation after they cancel.  Unfortunately, all buttons are
> > disabled during the download so I need to detect when the download is
> > finished to re-enable them.  Otherwise, the user will have to wait.
> > Unfortunately, one of the first operation that QA did during the
> > download was "cancel" :).  Just my luck ...
> >
> > Anyway, sqlMap.endConnection() forces handleRow to throw an Exception
> > which ends the "handleRow" processing.  At that point, my javascript via
> > an Ajax call can detect the "cancel" and re-enable the necessary
> > buttons.  And I agree with you, I don't want to forcibly disconnect
> > ibatis connection but only other option I see is to put in the user's
> > manual that if they cancel a download, they'll need to wait a minute or
> > two before they can continue.
> >
> > But this whole thing maybe moot.  If the memory leak problem is not
> > resolved, I may have to resort to JDBC.  Than I can end the loop at my
> > discretion :).
> >
> >
> >
> > On Wed, 30 Jan 2008 10:34:06 -0700, "Christopher Lamey"
> > <clamey@localmatters.com> said:
> > > Interesting - I don't think I used a groupBy with my large volume
> > > RowHandlers.
> > >
> > > If you're writing to the output stream from the RowHandler and the user
> > > Cancels the download, wouldn't the stream get closed and cause an
> > > exception
> > > the next time you tried to write?
> > >
> > > It seems like you're going through a lot of ugly work to handle the
> > > 'Cancel'
> > > action - is it absolutely necessary?  Worst case is you have a Thread
> > > spinning through the millions of rows for no reason because the user
> > said
> > > 'No' halfway through the operation.  How often is that expected to
> > > happen?
> > > How many users do you have that are expected to do the download?  How
> > > loaded
> > > up is your application that having a Thread finish the processing would
> > > be
> > > noticeable and cause a problem?
> > >
> > > Maybe you could have something like this:
> > >
> > > private boolean noop = false;
> > >
> > > public void handleRow (Object valueObject)
> > >    throws SQLException {
> > >
> > >     if (noop) {
> > >         // if first time to hit this, do any cleanup here
> > >         return;
> > >     }
> > >
> > >     // Rest of your code
> > > }
> > >
> > > public void setNoop(boolean b) {
> > >     this.noop =  b;
> > > }
> > >
> > > When you detect the Cancel operation you call 'setNoop(true)' on the
> > > RowHandler and it just spins through the rest of the records.  That
> > > Thread
> > > would then be busy running through the rest of the results, but might be
> > > ok
> > > since no user is waiting on it.
> > >
> > > Generally I tend to think that any time you find yourself messing around
> > > with the connections iBATIS is using, you're doing something wrong.
> > >
> > >
> > > On 1/30/08 10:18 AM, "Harvey Kim" <cashtag@fastmail.fm> wrote:
> > >
> > > > Thanks - that's good to know.  That gives me a warm fuzzy feeling.
> > > >
> > > > I guess the issue now is to figure out what type of query in ibatis is
> > > > causing the memory leak.  And there is absolutely no doubt that the
> > > > culprit is ibatis.  JDBC with the same query left no residue of memory
> > > > leaks.  And rearranging the query by eliminating the groupBy clause in
> > > > the ibatis xml file eliminated 90% of the memory leak.  Also, straight
> > > > forward query from one table that returns millions of rows from ibatis
> > > > yielded NO memory leaks.  So there's something about joins but more
> > > > significantly - group by clause.  My guess is that ibatis must be
> > using
> > > > some sort of a Map to implement the groupBy clause and is simply
> > letting
> > > > it grow indefinitely.
> > > >
> > > > One more bit of information.  Just throwing an Exception alone in the
> > > > "handleRow" will not get you out of the "loop".  I had to issue
> > > > "sqlMap.endConnection()" to force the calling routine within ibatis to
> > > > throw a NullPtrException in order to exit.  If anybody has any other
> > > > ideas, I'd appreciate it.  It does work but it's very ugly.
> > > >
> > > > On Tue, 29 Jan 2008 20:24:13 -0700, "Chris Lamey"
> > > > <clamey@localmatters.com> said:
> > > >> I have processed millions of rows with a rowhandler and no memory
> > leaks
> > > >> in several different applications.
> > > >>
> > > >>
> > > >> -----Original Message-----
> > > >> From: Harvey Kim [mailto:cashtag@fastmail.fm]
> > > >> Sent: Tue 1/29/2008 5:23 PM
> > > >> To: ibatis user
> > > >> Subject: Re: Exit out of rowHandler
> > > >>
> > > >> Thanks, I'm currently throwing an exception but that is really ugly.
> >  I
> > > >> was hoping for more "graceful" solution.  But I guess I can live with
> > > >> it.  Oh, and "Cancel" button won't be a problem - I got that covered.
> > > >>
> > > >> Another thing - I've issued this question few months back but there
> > ws
> > > >> no reply.  Does anybody know anything about memory leak problem when
> > > >> using the RowHandler?  After processing about 200,000 rows or so, I
> > > >> noticed that "available memory" starts to decrease.  This is more
> > > >> prevalent when using a "groupBy" clause.  When I took out the groupBy
> > > >> clause, memory leak appeared to go have gone away but it slowly
> > started
> > > >> appearing at 200,000th row (give or take few thousands).
> > > >>
> > > >> Just for kicks, I converted everything to JDBC with exactly the same
> > > >> code and it successfully processed 1 million rows with absolutely no
> > > >> memory leaks.  Meaning, instead of calling ibatis with RH, I simply
> > put
> > > >> it in a loop after issuing the same SQL generated by ibatis via JDBC.
> > > >> Meaning, my code was exactly the same except instead of being inside
> > the
> > > >> "handleRow", it was inside the loop.  With this evidence, I became
> > > >> suspicious of the ibatis RH.
> > > >>
> > > >> I guess what I'm asking is if anybody ever successfully processed
> > > >> millions of row using RH without a memory leak .  I'd hate to resort
> > > >> back to JDBC when ibatis makes everything so much easier.
> > > >>
> > > >> I'd appreciate any input,
> > > >>
> > > >> thanks,
> > > >>
> > > >> -Harvey
> > > >>
> > > >>
> > > >> On Tue, 29 Jan 2008 16:52:02 -0700, "Larry Meadors"
> > > >> <lmeadors@apache.org> said:
> > > >>> On Jan 29, 2008 4:34 PM, Harvey Kim <cashtag@fastmail.fm> wrote:
> > > >>>> Anybody know how to gracefully exit out of "handleRow" method?
> >  Before
> > > >>>> anybody suggests using "queryFor*" methods, I need to be able to
> > query
> > > >>>> for potentially 12 million rows.  It's a straight download to an
> > excel
> > > >>>> file so nothing is being displayed (thank god).  Anyway, I'm trying
> > to
> > > >>>> exit out of "handleRow" method when the user clicks on the "cancel"
> > > >>>> button from the standard download dialogue box.
> > > >>>
> > > >>> Hm, red flag there - I assume you're talking web app, but I don't
> > > >>> think the request is canceled when the user clicks on the cancel
> > > >>> button, it'll still going to run.
> > > >>>
> > > >>> In any case, the only current way to cancel RH processing is to
> > throw
> > > >>> an exception from the handleRow method. Not sexy, but it works. :-)
> > > >>>
> > > >>> Larry
> > > >>
> > > >> --
> > > >> http://www.fastmail.fm - Email service worth paying for. Try it for
> > free
> > > >>
> > > >>
> > >
> >
> > --
> > http://www.fastmail.fm - Access all of your messages and folders
> >                          wherever you are
> >
> >

-- 
http://www.fastmail.fm - Does exactly what it says on the tin