I don't know what our Systems team did to mysql. But one of my jsp pages takes about 15 seconds to load. It took only 1 second before upgrade.
There are only about 200 entries in related tables. And the page connects about 60 times to the database. It is weird that such small page has this issue.
Other JSP pages that query mysql have this issue too.
I want to know how to "DEBUG" this issue so I can tell our Systems team how to change. Your reply is highly appreciated!
try with
explain select <your query>
here is link to go with it http://dev.mysql.com/doc/refman/5.1/en/explain.html
One of the first things to try is running the same queries from the mysql console, and seeing if they have the same performance problems. If so, you can use "EXPLAIN" to see the query analyzer and see if it is doing something bad. With only 200 entries though, it probably isn't an index issue.
Your page should only physically connect to the DB once. If you're doing it 60 times, that isn't good.
It could be an issue with connection pooling, that the pool size is set too small, so it is blocking waiting on a connection to become available. Is this performance problem consistent?