You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Fabrizio Sitzia <fa...@chem.lu> on 2005/07/01 10:05:20 UTC
Re: SQLTransformer makes Cocoon hang under high load
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
Re: SQLTransformer makes Cocoon hang under high load
Posted by Irv Salisbury <ir...@gmail.com>.
On 7/1/05, Fabrizio Sitzia <fa...@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