Common load balancing schemes include F5, HAProxy, and LOADBalance in JDBC. F5 is not something that developers can play on their own. HAProxy needs to be deployed independently and there is additional network overhead if deployed on machines outside the database cluster. JDBC loadBalance is built into the program, directly configure the URL, simple configuration, but also saves the extra network overhead. Usage:
jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]... [/[database]] [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]Copy the codeConnector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or source-source replication deployments. You can dynamically configure load-balanced connections, with no service outage. In-process transactions are not lost, and no application exceptions are generated if any application is trying to use that particular server instance.
The trial
Using JDBC LoadBalance in TiDB, write a simple program that sets the number of threads to 40 to access the database. Insert while true, so it should be 40 long connections. However, monitoring shows that in a cluster with 4 TiDB nodes, each instance has 40 Connections, which is somewhat counterintuitive.
Using HAProxy, the number of connections was not very balanced, but the number was as expected.
Create a link
Try to see the JDBC source, loadbalance logic is roughly in LoadBalancedConnectionProxy. In Java,
A proxy for a dynamic com.mysql.cj.jdbc.JdbcConnection implementation that load balances requests across a series of MySQL JDBC connections
There are three load balancing modes in JDBC, and loadBalanceStrategy needs to be configured in URL. There are three optional parameters, which are random, bestResponseTime, and serverAffinity. They both implement the BalanceStrategy interface, with only one abstract method, pickConnection. The default is random mode, so I’m only looking at the logic here.
abstract JdbcConnection pickConnection(InvocationHandler proxy, List<String> configuredHosts, Map<String, JdbcConnection> liveConnections,long[] responseTimes, int numRetries) throws SQLException;
Copy the codeJust as its name implies is to realize the load balance which one to choose the host open connection point, corresponding equalizer is: RandomBalanceStrategy, BestResponseTimeBalanceStrategy, ServerAffinityStrategy.
In [email protected] will case different load balancing mode
switch (strategy) {
case "random":
this.balancer = new RandomBalanceStrategy();
break;
case "bestResponseTime":
this.balancer = new BestResponseTimeBalanceStrategy();
break;
case "serverAffinity":
this.balancer = new ServerAffinityStrategy(props.getProperty(PropertyKey.serverAffinityOrder.getKeyName(), null));
break;
default:
this.balancer = (BalanceStrategy) Class.forName(strategy).newInstance();
}
Copy the codePickConnection () is executed. If this.currentConnection == null, the connection has just been initialized and the host connection needs to be obtained by the selected mechanism.
this.currentConnection = this.balancer.pickConnection(this, hostPortList, Collections.unmodifiableMap(this.liveConnections),this.responseTimes.clone(), this.retriesAllDown);
Copy the codeCame to RandomBalanceStrategy. Java, you can see the JDBC will get a random number, random range is the hostList can now use the size of size, according to the index to randomly access to create the connection after the host, Then go to the liveConnections map to obtain the existing connection, reuse if there is, create a new.
int random = (int) Math.floor((Math.random() * whiteList.size()));
String hostPortSpec = whiteList.get(random);
ConnectionImpl conn = (ConnectionImpl) liveConnections.get(hostPortSpec);
if (conn == null) {
conn = ((LoadBalancedConnectionProxy) proxy).createConnectionForHost(hostPortSpec);
}
return conn;
Copy the codeThis logic seems silly, not considering whether there are already many connections on the selected node. At this point, the loadBalance logic is not finished.
Release the link
In the case of AutoCOMMIT =false, JDBC causes the current client thread to re-select Connection after each COMMIT or rollback. If autoCOMMIT =true, the original connection will always be used and will not be re-selected.
if ("commit".equals(methodName) || "rollback".equals(methodName)) {
this.inTransaction = false;
pickNewConnection();
}
Copy the codeSo keep executing pickNewConnection(), again picking connection logic above, randomly picking host to create or reuse conneciton. This is why the number of connections will multiply. Assuming that there are host1 and host2, because the host is re-selected at the end of each commit, no matter how many hosts there are, as long as the program runs long enough, they will always be selected at least once. Connection count = thread * host; most connections are idle. The puzzle here is that, in fact, this is not a complete load balancing. After all, it is random. Only when there are enough threads, it will tend to be balanced. Another problem is that re-selecting Connection each time may incur additional overhead. Ask this question at Connector/J’s official Slack, and the answer is yes
This mechanism is very reasonable. If there are three nodes and only one thread accesses the database, it should also be allowed to randomly select hosts from the three nodes to access the database. It should not be tied to a node to access the database.
Reform attempt
A connection multiplier created may result in a multiplier increase in TiDB node resource utilization, which in some cases may have a performance cost. Decided to give it a try. We fork the JDBC repO and simply implement a new balanced pattern lasting, which is selected based on the number of connections on each host. That is, select the host with the fewest connections each time. No longer re-pickConneciton after commit.
The test results
Accessing the database with 1000 concurrent requests: Random mode first, Lasting mode second.
As you can see, in chevy mode, the number of goroutines and memory usage of TiDB nodes are reduced by a large percentage. Connection Idle Duration has also dropped considerably. It seems to be having some effect.
Appendix – Build Connector/J 8
Install Ant
brew install ant
Copy the codeThird-party libraries
Download the following third-party libraries and place them in a file,
Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/or… Search.maven.org/artifact/co… Search.maven.org/artifact/co… Search.maven.org/artifact/or… Search.maven.org/artifact/or…
Clone jdbc
Git clone - branch in release 8.0 / https://github.com/mysql/mysql-connector-j.gitCopy the codeTo configure the build properties
Notice that the official website says the following, but you don’t really need to add path_to, just write the following
In the directory, create a file named build.properties to indicate to Ant the locations of the root directories for your JDK 1.8.x installation, as well as the location of the extra libraries. The file should contain the following property settings, with the “path_to_*” parts replaced by the appropriate file paths:
The default JDK directory on the MAC OS is /usr/libexec/java_home -v.
com.mysql.cj.build.jdk=8 jdk_1.
com.mysql.cj.extra.libs=folder_for_extra_libraries
Copy the codeBuild
Buildfile: /Users/yuyang/IdeaProjects/mysql-connector-j/build.xml -extra-libs-check: [taskdef] Could not load definitions from resource org/jacoco/ant/antlib.xml. It could not be found. -jdk-check: -compiler-check: clean: [delete] Deleting directory /Users/yuyang/IdeaProjects/mysql-connector-j/build -load-info-properties: -init-copy-common: [mkdir] Created dir: /Users/yuyang/IdeaProjects/mysql-connector-j/build [copy] Copying 532 files to / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT - init - filter - license: -init-no-crypto: -init-license-headers: -init-copy: -init-notices-commercial: -init-notices-gpl: [get] Getting: file:./LICENSE [get] To: / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/LICENSE [get]. [copy] Copying the file to/Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT -init-info-files: [echo] ## INFO_BIN ## [echo] build-date: 2021-04-26 16:29:27 +0800 [echo] os-info: Mac OS X x86_64 11.2.3 [echo] Compiler: Javac 1.8.0_251 [echo] build-tool: Compiled on April 12 2021 [echo] ## INFO_SRC ## [echo] version: 8.0.22-SNAPSHOT [echo] branch: release/8.0 [echo] Date: 2020-08-07 22:42:18 +0100 [echo] commit: d64b664fa93e81296a377de031b8123a67e6def2 [echo] short: d64b664f init: -clean-output: compile-driver: [echo] Compiling MySQL Connector/J JDBC implementation with '/ Library/Java/JavaVirtualMachines jdk1.8.0 _251. JDK/Contents/Home' to 'build/mysql connector - Java - 8.0.22 - the SNAPSHOT' [javac] Compiling 510 source files to / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT (javac) Creating the empty / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/com/mysql/cj/xdevapi/package - inf o.class [javac] Creating empty / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/com/mysql/cj/x/protobuf package - info.class [java] Applying CommonChecks. [java] Applying TranslateExceptions. [java] Applying AddMethods. -compile-integration-c3p0: [echo] Compiling MySQL Connector/J-c3p0 integration with '/ Library/Java/JavaVirtualMachines jdk1.8.0 _251. JDK/Contents/Home' to 'build/mysql connector - Java - 8.0.22 - the SNAPSHOT' [javac] Compiling 1 source file to / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT compile - integration: the compile: dist: [mkdir] Created dir: / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/meta-inf/services/copy Copying 4 files to/Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/meta-inf/jar Building jar: / Users/yuyang IdeaProjects j/build/mysql/mysql - connector - - connector - Java - 8.0.22 - the SNAPSHOT/mysql connector - Java - 8.0.22 - SNAP SHOT.jar BUILD SUCCESSFUL Total time: 17 secondsCopy the code