CODEWITHSUNDEEP
javajdbcload-balancingmysql-connectormysql-cluster
Tay Wen Bin
Tay Wen Bin

Reputation: 111

Load balancing via MySQL JDBC driver (Connector/J)

I have been exploring MySQL JDBC driver and understand that it is able to do load balancing with this url ("jdbc:mysql:loadbalance://").

I looked into the documentation and it seems that only random and best response time methods are used for load balancing (link: http://dev.mysql.com/doc/refman/5.0/es/connector-j-reference-configuration-properties.html syntax is loadBalanceStrategy).

Questions

  1. Is there a way to configure roundrobin as the load balancing strategy for the JDBC driver? I understand that other load balancing methods may be better but I am trying to use the round-robin method for my experiments.
  2. Is it a good idea to implement load balancing (I am open to whichever load balancing strategy) for MySQL ndb cluster (Multimaster with synchronous replication)? By the way, I have only 2 machines acting as masters and both are able to receive writes and reads. Although MySQL mentioned the use of the JDBC driver with ndb cluster (refer to quote 1 below), I was wondering if using load balancing for a multimaster ndb cluster would actually worsen the performance (refer to quote 2)?

  1. Quoted from https://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-basics.html - "MySQL clients using a MySQL Cluster as a data source can be modified to take advantage of the ability to connect with multiple MySQL servers to achieve load balancing and failover. For example, Java clients using Connector/J 5.0.6 and later can use jdbc:mysql:loadbalance:// URLs (improved in Connector/J 5.1.7) to achieve load balancing transparently; for more information about using Connector/J with MySQL Cluster, see Using Connector/J with MySQL Cluster."

  2. Quoted from https://dba.stackexchange.com/questions/11789/load-balanced-mysql-cluster-without-load-balancer - "replication delay on production servers might be up to a full second - I have tested this in a remote colocation and in our datacenter and for like 99% of the time it's 0, but sometimes mysql shows 1s. On massive traffic I had many collisions due to client application making two requests resulting in two queries, insert and select. For some cases, the row just wasn't there yet, so We used hash of the userID and it fixed the problem"

In response to Howard's reply, these are some of the things I have done prior to posting this question - I have tried to use roundRobinLoadBalance as part of the JDBC connection string (URL) but an error occurred stating that it was not supported. A search on the internet also shows that other people also face problem with trying to use roundRobinLoadBalance. Hence I was trying to see if there is another way to do it.

Upvotes: 2

Views: 5519

Answers (1)

Sully
Sully

Reputation: 14943

Q1) Is there a way to configure roundrobin as the load balancing strategy?

Round-roubin's load balancing algorithm gives each server an equal load. When all servers' performances are the same then it makes sense to use it.

Weighted round-robin on the other hand allows load balancing configuration for each server. This makes sense when you have servers with different performances.

For example for Glassfish read: Weighted Round Robin

Q2) Is it a good idea to implement load balancing (I am open to whichever load balancing strategy) for MySQL ndb cluster (Multimaster with synchronous replication)? By the way, I have only 2 machines acting as masters and both are able to receive writes and reads. Although MySQL mentioned the use of the JDBC driver with ndb cluster (refer to quote 1 below), I was wondering if using load balancing for a multimaster ndb cluster would actually worsen the performance (refer to quote 2)?

If the two servers are identical in performance, there is no need to configure the load balancing. It will distrbute the load to each node equally.

In round-robin, load-balancing will utilize each server equally.

If one server is faster and more powerful than the other, you might want to use the weighted round-robin to configure more load on the faster server.

References:

http://docs.oracle.com/cd/E26576_01/doc.312/e24934/http-load-balancing.htm http://msdn.microsoft.com/en-us/library/ff648960.aspx

Upvotes: 1

Related Questions