We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Load balancing policy — Vertica Forum

Load balancing policy

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)


Comments

  • Navin_CNavin_C Vertica Customer
    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.


  • 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_CNavin_C Vertica Customer
    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 
  • Hi Navin ,
                    Yes  i tried with vertica-jdbc-7.0.1-0 .. it worked .. thank you very much 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file