CODEWITHSUNDEEP
javamysqljdbc
randyumi
randyumi

Reputation: 47

MySQL JDBC loadbalance and failover

i am using jdbc:mysql:loadbalance://... in my Java application. jdbc url is like this.

jdbc:mysql:loadbalance://xxx.xxx.xxx.xx,yyy.yyy.yyy.yyy.../database?zeroDateTimeBehavior=convertToNull&autoReconnect=true

I can get a connection equally from this url when all DB servers are alive and mysqlds are running. Moreover, I can also do this when all DB servers alive(ping reachable) and one mysqld is not running(i.e. mysqld has some problem or crashed) and the others are running.

But there is a problem when one of the servers is not alive. For example, i shut down yyy server and attempt to get a connection. Failover mechanism seemed to work correctly and I could get a connection finally. However it took too long(about 75 - 85 seconds).

How can i shorten this waiting time?

Upvotes: 2

Views: 2446

Answers (1)

Narendra Pathai
Narendra Pathai

Reputation: 41945

As @randyumi has stated in the comments that my solution worked for him, I am adding this as a legitimate answer.

The reason for the delay in fallback is due to the fact that mysql tries to connect to the primary database instance and waits for it to respond for a certain amount of time i.e. connection timeout. Once that connection timeout expires then mysql switches to the fallback.

So reducing the default connection timeout can reduce the latency.

Upvotes: 2

Related Questions