Load balancing policy

bhanu_kishore

Hi All,

             I have 3 node vertica cluster .. i followed native connection load balancing policy and tried to set load balancing policy to ROUNDROBIN .. but getting below error .. i tried below command as super user only..

tdaadmin=> SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');
ERROR 3457:  Function SET_LOAD_BALANCE_POLICY(unknown) does not exist, or permission is denied for SET_LOAD_BALANCE_POLICY(unknown)

Navin_C

Hi Bhanu,

What version of Vertica are you using.

The load balancing feature was introduced in Vertica 7

In my case

nnani=> SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');                            SET_LOAD_BALANCE_POLICY
--------------------------------------------------------------------------------
 Successfully changed the client initiator load balancing policy to: roundrobin
(1 row)

And then you can check what is the current  load_balance policy for your database.

select database_name,load_balance_policy from databases;

Hope this helps.

bhanu_kishore

Hi Navin ,
                Sorry  I ran above command in vertica 6 .. got to know it is introduced in vertica 7.. I tried in vertica 7 and it got worked..

=> SELECT SET_LOAD_BALANCE_POLICY('ROUNDROBIN');                            SET_LOAD_BALANCE_POLICY
--------------------------------------------------------------------------------
 Successfully changed the client initiator load balancing policy to: roundrobin
(1 row)

And i run below java code to check to which server connection is made .. but it is getting connected to node 2  only ...

import java.sql.*;import java.util.Properties;


public class test {
    public static void main(String[] args) {


        Properties myProp = new Properties();
        myProp.put("user", "admin");
        myProp.put("password", "w1r3fr33");
        myProp.put("ConnectionLoadBalance", 1);
        Connection conn;
        for (int x=1; x <= 20; x++) {
            try {
                Class.forName("com.vertica.jdbc.Driver");

                System.out.print("Connect attempt #" + x + "...");
                conn = DriverManager.getConnection("jdbc:vertica://10.10.7.142:5433/wtdadb", myProp);
                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());
                //ResultSet rs1= stmt.executeQuery("SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES;");
                //rs1.next();
                //System.out.println(" policy " + rs1.getString(1).trim());
                conn.close();
            } catch (Exception e) {
                System.out.println("Error!");
                e.printStackTrace();
            }
        }
    }
}

java -cp .:/opt/bhanu/lib/vertica-jdk5-6.0.0-0.jar test
o/p

Connect attempt #1...Connected to node v_verticadb_node0002Connect attempt #2...Connected to node v_verticadb_node0002
Connect attempt #3...Connected to node v_verticadb_node0002
Connect attempt #4...Connected to node v_verticadb_node0002
Connect attempt #5...Connected to node v_verticadb_node0002
Connect attempt #6...Connected to node v_verticadb_node0002
Connect attempt #7...Connected to node v_verticadb_node0002
Connect attempt #8...Connected to node v_verticadb_node0002
Connect attempt #9...Connected to node v_verticadb_node0002
Connect attempt #10...Connected to node v_verticadb_node0002
Connect attempt #11...Connected to node v_verticadb_node0002
Connect attempt #12...Connected to node v_verticadb_node0002
Connect attempt #13...Connected to node v_verticadb_node0002
Connect attempt #14...Connected to node v_verticadb_node0002
Connect attempt #15...Connected to node v_verticadb_node0002
Connect attempt #16...Connected to node v_verticadb_node0002
Connect attempt #17...Connected to node v_verticadb_node0002
Connect attempt #18...Connected to node v_verticadb_node0002
Connect attempt #19...Connected to node v_verticadb_node0002
Connect attempt #20...Connected to node v_verticadb_node0002

still any other setting i should change to distribute connection requests across all nodes.

Navin_C

Hi Bhanu,

I think you are using Vertica JDBC driver vertica-jdk5-6.0.0-0.jar with this program,
If yes, then you need to upgrade / install Vertica Driver for Vertica 7 'vertica-jdbc-7.0.1-0'

Again, this feature was introduced in Vertica 7 Drivers.

Hope this helps 

bhanu_kishore

Hi Navin ,
                Yes  i tried with vertica-jdbc-7.0.1-0 .. it worked .. thank you very much