Vertica7 Native Connection Load Balance, vertica7native

Vertica7 Native Connection Load Balance, vertica7native

Original article: Vertica7 Native Connection Load Balance

In versions earlier than Vertica7, Vertica implements Server Load balancer through the Virtual IP address of Linux. However, in Vertica7x, Vertica provides the Server Load balancer function for connections, this function is also very convenient to use. Let's take a look at how to use this function.

1. First install N Vertica7 nodes, and then run the following command as a database administrator to allow Vertica to use this function.

SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');

If you need the disable function, run the following command:

SELECT SET_LOAD_BALANCE_POLICY('NONE');

If you need to check whether the current enable function is enabled, you can use the following command to view

SELECT GET_LOAD_BALANCE_POLICY();

There are so many server-side settings. Let's take a look at the steps required by the client.

2. You must first use the JDBC driver of vertica7, which can be downloaded from the official website of Vertica.

3. Write a test program to test the connection.

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties; public class VerticaTest {    private static String USERNAME = "<user>";    private static String PASSWORD = "<password>";    private static String URL = "jdbc:vertica://<ip>:5433/<db>";         public static void main(String[] args) throws Exception {        Class.forName("com.vertica.jdbc.Driver");        Properties props = new Properties();        props.put("user", USERNAME);        props.put("password", PASSWORD);        props.put("ConnectionLoadBalance", 1);        for (int x = 1; x <= 10; x++) {            try {                Connection conn = DriverManager.getConnection(URL, props);                Statement stmt = conn.createStatement();                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 (SQLException ex) {                ex.printStackTrace();            }        }    }}

Pay special attention to the following sentence. This sentence sets a connection property to inform the JDBC driver of Vertica to use the Server Load balancer function.

props.put("ConnectionLoadBalance", 1);

If the enable Server Load balancer function is not available on the server, this attribute does not affect normal functions, but does not use the Server Load balancer function.

4. Test
Run this code. You can see that the Vertica nodes connected to each node change in sequence.

You can also use the following SQL statement to query all current connections.

select node_name, client_hostname from sessions;