Load Balancing in JDBC
Native Connection Load Balancing
Native connection load balancing helps spread the overhead caused by client connections on the hosts in the Vertica database. Both the server and the client must enable native connection load balancing. If enabled by both, then when the client initially connects to a host in the database, the host picks a host to handle the client connection from a list of the currently up hosts in the database, and informs the client which host it has chosen.
If the initially-contacted host does not choose itself to handle the connection, the client disconnects, then opens a second connection to the host selected by the first host. The connection process to this second host proceeds as usual—if SSL is enabled, then SSL negotiations begin, otherwise the client begins the authentication process. See About Native Connection Load Balancing for details.
To enable native load balancing on your client, set the ConnectionLoadBalance connection parameter to true. The following example demonstrates:
- Connecting to the database several times with native connection load balancing enabled.
- Fetching the name of the node handling the connection from the V_MONITOR.CURRENT_SESSION system table.
import java.sql.*;
import java.util.Properties;
import java.sql.*;
import java.util.Properties;
public class JDBCLoadingBalanceExample {
public static void main(String[] args) {
Properties myProp = new Properties();
myProp.put("user", "dbadmin");
myProp.put("password", "example_password123");
myProp.put("loginTimeout", "35");
myProp.put("ConnectionLoadBalance", "1");
Connection conn;
for (int x = 1; x <= 4; x++) {
try {
System.out.print("Connect attempt #" + x + "...");
conn = DriverManager.getConnection(
"jdbc:vertica://node01.example.com:5433/vmart", myProp);
Statement stmt = conn.createStatement();
// Set the load balance policy to round robin before testing the database's load balancing.
stmt.execute("SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');");
// Query system to table to see what node we are connected to. Assume a single row
// in response set.
ResultSet rs = stmt.executeQuery("SELECT node_name FROM v_monitor.current_session;");
rs.next();
System.out.println("Connected to node " + rs.getString(1).trim());
conn.close();
} catch (SQLTransientConnectionException connException) {
// There was a potentially temporary network error
// Could automatically retry a number of times here, but
// instead just report error and exit.
System.out.print("Network connection issue: ");
System.out.print(connException.getMessage());
System.out.println(" Try again later!");
return;
} catch (SQLInvalidAuthorizationSpecException authException) {
// Either the username or password was wrong
System.out.print("Could not log into database: ");
System.out.print(authException.getMessage());
System.out.println(" Check the login credentials and try again.");
return;
} catch (SQLException e) {
// Catch-all for other exceptions
e.printStackTrace();
}
}
}
}
Running the previous example produces the following output:
Connect attempt #1...Connected to node v_vmart_node0002 Connect attempt #2...Connected to node v_vmart_node0003 Connect attempt #3...Connected to node v_vmart_node0001 Connect attempt #4...Connected to node v_vmart_node0002
Hostname-based Load Balancing
You can load balance workloads by resolving a single hostname to multiple IP addresses. When you specify the hostname for the DriverManager.getConnection() method, the hostname resolves to a random listed IP address from the each connection.
For example, the hostname verticahost.example.com has the following entries in etc/hosts:
192.0.2.0 verticahost.example.com 192.0.2.1 verticahost.example.com 192.0.2.2 verticahost.example.com
Specifying verticahost.example.com as the connection for DriverManager.getConnection() randomly resolves to one of the listed IP address.