3

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.

Improve this question
5
  • Are you running on Windows?
    – Rick James
    Commented Mar 26, 2016 at 20:50
  • @RickJames: the database is running on Linux CentOS 6.4. The servers that have issues while connecting are Linux CentOS 7.
    – bdem
    Commented Mar 27, 2016 at 10:14
  • "Seconds" is quite unusual. What is the 'ping' time between the servers?
    – Rick James
    Commented Mar 27, 2016 at 16:36
  • @RickJames: less than 1 millisecond
    – bdem
    Commented Mar 27, 2016 at 19:12
  • check the pool_size greater pool size takes longer time to connect
    – kabindra_kc
    Commented Jul 18, 2022 at 16:55

2 Answers 2

Reset to default
2

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
Improve this answer
2

Resolving host names may cause such an issue as I met before.

You might be able to fix it by adding these 2 options to your my.cnf:

skip-host-cache
skip-name-resolve

Then restart the MySQL server.

Or you can increase the HOST_CACHE_SIZE.

Before you do this, check the Manual to ensure you know what will happen when you disabled name-resolve.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.