-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Miguel,
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 -----BEGIN PGP SIGNATURE----- Version: GnuPG/MacGPG2 v2.0.17 (Darwin) Comment: GPGTools - http://gpgtools.org Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk+oOvQACgkQ9CaO5/Lv0PASbwCfXtxWE+aCAAk4COYToY4twySX szEAoLUdvfr7Qm/SOmdHm1p/NtjoVu+W =4uoI -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
