How to decrease MySQL connection time

Question

I have a JSP page. It used to work well but it becomes very slow (15 seconds to load) after the Systems team upgraded the Solaris 10 OS server.

I checked all the queries in that page and every query works fine. In fact they are all very simple queries. And there are only about 300 entries in each related table.

The only special thing, there were about 60 connections in that page. I managed to decrease the connections by 30 after I found it is very slow. After this optimization the loading time decreased to 6 seconds. But, still very slow! And what's worse, I am unable to decrease the connections any more if I don't want to re-construct half of the application.

Another JSP page (not in the same application) worked well but now it becomes slow too. It has only 1 connection but this page is very time sensitive. Thus I can see it becomes slower.

Can anyone tell me how to configure mysql or/and tomcat to decrease mysql connection time?

Answer 1

Why are you making more than one database connection per page load? That should not be the general case. Make one connection and use it for all the rendering of that page.

Answer 2

Are you saying "connection" when you mean "query"? Or are you making a new database connection for each query? You should not do that. In a Tomcat app server, you should use connection pooling, which will reduce the overhead of connections a lot.

Another common issue is that the MySQL Server tries to resolve your client's hostname from its IP address. This is called DNS hostname resolution, and it can be the source of a lot of overhead when making a new connection. You can configure your MySQL Server to skip DNS hostname resolution without much downside.