Mysql – Opening MySQL connection is sometimes very slow

connectivityMySQLmysql-5.0

I have a very weird issue with an older MySQL server (v5.0.45) and a Java application.

From time to time getting a connection from the database takes 7 seconds, 15 seconds, 30 seconds etc (most of them are 15 seconds) or sometimes times out. This happens just from time to time. I don't see a pattern when it does happen. The rest of the time connections are created in 2-3 milliseconds.

The database is running on Linux CentOS 6.4. The servers that have issues while connecting are on Linux CentOS 7. I'm using the latest MySQL connector (currently at v5.1.38), Java 8, with a connection pool (be it that of Tomcat or c3p0) or with no connection pool (just opening and closing connections), server in load or with few clients, a few simultaneous connections or up to 100 connections. It's the same result.

Multiple applications on multiple servers connect to this database. Only on two of them this occurs, the rest have no issues, the network seems fine, the other machines are in the same network.

QUESTIONS

  • How can I debug this issue and find the cause?
  • Has anyone seen this behaviour before?

EDIT: We've norrowed down the issue to the actual servers. Don't know if it's a hardware problem or an OS/kernel/drivers one. Still investigating.

Best Answer

Slow connections could be caused by the amount of memory consumed by each DB Connection being allocated.

Back on April 24, 2012, I answered the post How costly is opening and closing of a DB connection?. In that post, I mentioned how there are many buffers connected to a single DB session.

If your Java application strictly closes DB Connections and opens new ones with out, think about deallocating multiple data buffers and communication packets connected to that old connection.

Combine that with the DB Connection entering in infamous TIME_WAIT state (See my post Too Many Connections) and you get Connections that linger in the OS for quite a while before its resources are released (even if mysqld closed the connection already).

You may need to decrease the size of your sort_buffer_size, read_buffer_size, and join_buffer_size.

If you have any queries that run multiple joins in a single statement, that connection will have multiple join buffers (See my post Fast alternative for "NOT IN"). In that event, you have two choices

  • Increase the join_buffer_size to make sure an entire join level fits in memory (requires more RAM in the DB Server)
  • Decompose multiple join query that joins more than 2 tables into individual joins between 2 tables
Related Question