cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Irv Salisbury <irv.salisb...@gmail.com>
Subject Re: SQLTransformer makes Cocoon hang under high load
Date Fri, 01 Jul 2005 14:08:05 GMT
On 7/1/05, Fabrizio Sitzia <fabrizio.sitzia@chem.lu> wrote:
> Hello again,
> 
> The deadlock situation you describe is a plausible explanation for the
> experienced Cocoon 'hangs'!
> 
> I took a deep breath and had a look into SQLTransformer.java, and I stumbled
> upon the following code snippet in the executeQuery() method:
> 
>     try {
>         if (index == 0) {
>             if (this.conn == null) // The first top level execute-query
>                 this.conn = query.getConnection();
>             // reuse the global connection for all top level queries
>             conn = this.conn;
>         }
>         else // index > 0, sub queries are always executed in an own connection
>             conn = query.getConnection();
> 
>         query.setConnection(conn);
>         query.execute();
>     } ...
> 
> 
> Effectively, a new connection is being requested for every nested subquery!
> 
> What perplexes me, is that the getConnection() method appears to go to quite
> some lengths to ensure that it won't block if it can't acquire a connection
> after waiting/retrying a few times.
> Normally it should complain (ie. write to the logs) and abort, but that never
> happens!
> 
> I am going to post this as a bug report.
> 
> 
> Thanks for the prompt response,
> Fabrizio
> 
> 
> >> I'm developing a dynamic Cocoon webapp that performs a lot of (nested)
> >> queries
> >> using the SQLTransformer.
> >>
> >> The SQLTransformer performs SELECT operations only, and it is configured to
> >> use a JDBC database connection pool that is managed by Cocoon itself.
> >> Excerpt from cocoon.xconf:
> >>
> >>   <datasources>
> >>     <jdbc logger="core.datasources.db" name="db">
> >>       <pool-controller min="5" max="50"/>
> >>       ...
> >>     </jdbc>
> >>   </datasources>
> >>
> >>
> >> At some point, I decided to stress-test the webapp using the 'ab'-tool
> >> (Apache
> >> benchmark) to simulate a large number of concurrent requests.
> >>
> >> When the number of concurrent requests is heading towards the 'max' number
> >> of
> >> pooled database connections, the following occurs:
> >>
> >> - The 'ab' benchmark is aborted with a timeout error.
> >>
> >> - Using a database monitoring tool, you will notice that the 'max' number of
> >> pooled database connections have been left open.
> >>
> >> - Cocoon 'hangs' indefinitely!
> >> Any further requests to the webapp will timeout, and nothing gets written to
> >> the logs (access.log, error.log...)
> >> There are no warnings, nor any error messages in the logs that would
> >> indicate
> >> why the hang occured in the first place. Up to the hang, everything appears
> >> to
> >> run normally!
> >>
> >> That's scary!
> >
> > It sounds like SQLTransformer naively gets a new connection per query.
> > This in turn requires that if your page has 4 nested queries then
> > SQLTransformer is using 4 of your 50 connections.  It only takes 13
> > simultaneous requests to use up all the connections at that rate, and if
> > all your requests have anywhere between 1 and 3 connections reserved and
> > are not finished yet, none of the pages will release the connection and
> > a deadlock occurs.  If you have as many as 10 nested queries it is
> > obvious to see how something like this could occur.
> >
> > I think the ESQL logicsheet suffered from this many moons ago, but
> > resolved it by requesting one connection per page.  BTW, Microsoft's
> > feeble driver is limited to one connection per query--so your choice is
> > to use jTDS or some other company's version.
> >
> > Nonetheless, it seems like there is a bug in SQLTransformer.  Submit a
> > bug report, and hopefully it can get resolved.
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
> 
> 

I don't know if this helps or not, but we have 2 other SQLTransformers
we have written that might help you if you are looking for an
alternative.  Each of them has a reason for existence.  The first one
is geared at things that need to do paging.  So, we basically married
our SQLTransformer with paging.  The second one is more geared at
prepared statements and callable statements.  Both of them support
batching and transactions as well.  We have used them successfully on
a few projects now.  Sorry, we have never used the nested querying
feature, so we didn't put that in ours.  (Seems there is often a more
database efficient way to accomplish the same thing versus making
multiple nested queries)

Irv

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org


Mime
View raw message