tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <>
Subject Re: Tomcat pausing and no java process
Date Mon, 07 May 2012 21:13:24 GMT
Hash: SHA1


On 5/5/12 5:41 AM, Miguel González Castaños wrote:
>>> Yes, I have pinpointed several queries that took quite long
>>> and specially subqueries. I forwarded this info to the
>>> developer but she said it was alright. More than a second to
>>> run a query seems to be a lot to me.
>> That depends on what the query does. If it checks permissions to 
>> login, that's insane and you should get a new programmer. If it's
>> a reporting query that gathers lots of data then 1 second might
>> not be quite so far-fetched.
> I have checked directly the slow query log to pinpoint the real
> queries. I found many subqueries, which I believe is not a good
> practice, am I right?

There's nothing wrong with subqueries in general, it's just that the
MySQL query optimizer appears to be particularly stupid when it comes
to them, and you often end up with *DEPENDENT* subqueries which
essentially issue N queries instead of only 2 queries (if the subquery
for instance were to be independent, it could be evaluated first and
then essentially substituted in the outer query). Only EXPLAIN can
tell you what will happen with each query. Remember to use live data
with EXPLAIN because the query optimizer is very sensitive to the data
it will be scanning... if you have a test table with 5 records in it,
you're going to get worthless EXPLAIN results.

> Many queries take around 2 seconds and I found two queries that 
> took 16 seconds and 64 seconds to run (with many subqueries
> nested).

Those numbers are meaningless to anyone who isn't on your development
team. A 2-second query might be perfectly reasonable... depending on
the actual query, the requirements, the frequency of that query being
executed, user expectations, etc.

> Is there any way I can log if any query is causing any trouble in
> the Tomcat resources?

If a query takes 64 seconds to run, it means that a JDBC Connection
will be absent from your connection pool for *at least* that long. If
you have P pooled connections that take T seconds to execute on
average, than you can only serve P/T requests per second. If your
webapp is very database-heavy, that can be a problem depending on your
values for P and T. Note that simply increasing the number of
connections in your pool may make things worse (because you are giving
the database more work that it can't keep up with). Sometimes,
limiting the number of connections can be beneficial, because the
average response time from the database will improve, while the
average wait time for a connection from the pool will increase. It all
gets down to the resources you have.

The Tomcat DBCP (not the default one) has some nice options for
instrumentation -- you'll have to read about those on your own because
I don't have any experience with them. Tomcat and/or commons-dbcp
expose some information via JMX but I think it's fairly basic stuff
like average-wait-times for checkout and stuff like that.

Something I've done over the years is develop a habit of putting trace
logs everywhere. You can always configure the logger on the fly to log
some trace output during times where performance starts to nosedive.

Thread dumps can be very helpful as well. You might also want to look
at running 'sar' all the time to get CPU/IO/etc sampling to see if you
have any other kind of resource shortcoming.

>>> The developer is quitting so I hope a new developer is more 
>>> cooperative and we can work out these things.
>> So you're saying that you're not going to get a lot of good 
>> information out of him/her?
> Not much.

$0.02: Don't wait to fire him or her: revoke access to everything and
get them out the door. If they aren't going to be helpful, at least
make sure they aren't going to be harmful.

- -chris
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools -
Comment: Using GnuPG with Mozilla -


To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message