The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Regrading distributed session among nodes

We have 3 node cluster under the load balancer. But when i ran below query it will give me most of the count on single node.
SELECT distinct user_name FROM dc_requests_issued WHERE
date(time) >= date(sysdate()) ;

Please suggest ASAP. What we are missing?

Best Answer

Answers

  • Your questions seems misleading in many respects:

    • With "load balancer", do you mean Connection Load Balancer?
    • Where do you see, from the query you issue, a node name? Connection Load Balancer tries to make sure that the connections to Vertica are evenly distributed across nodes.

    If you mean Connection Load Balancing the way I put it above, try:
    SELECT node_name, COUNT(*) AS session_count FROM user_sessions WHERE session_start_timestamp >= CURRENT_DATE::DATE GROUP BY 1;
    ... to check all nodes that have been used as the connection node today....

  • I mean to say number of queries are hitting to only one server. How i can manage this?

  • how do you see that the queries only hit one server? which query? Did you run the query I wrote above? and which is its output?

  • By "which query" , I mean , which query did you run to get the info that the queries only hit one server?

  • By this query
    SELECT distinct node_name,count(*) FROM dc_requests_issued WHERE
    date(time) >= date(sysdate()) group by 1;

    2006 - Node1
    3823 - Node2
    3441- Node3

  • Result from below query
    SELECT node_name, COUNT(*) AS session_count FROM user_sessions WHERE session_start_timestamp >= CURRENT_DATE::DATE GROUP BY 1;

    896 - Node 3
    1138 - Node 1
    510 - Node 2

  • SruthiASruthiA Employee

    What is the output of below queries?
    SELECT currentclientbalancingpolicy FROM vs_global_settings;
    SELECT LOAD_BALANCE_POLICY FROM V_CATALOG.DATABASES;

  • It looks to quite some degree that we have a lot of clients that don't have ConnectionLoadBalancing=TRUE in the setup of their ODBC or JDBC connections. Or simply, vsql called without the "-C" switch to force connection load balancing.
    This is my test:
    $ for i in {1..3} ; do vsql -C -At -h 172.16.61.128 -u dbadmin -W passwd -d sbx_marco -c "select node_name from current_session"; done
    v_sbx_marco_node0001
    v_sbx_marco_node0002
    v_sbx_marco_node0003

    If you omit the "-C" switch, it will always be the first node ...

  • Hey SruthiA,

    Roundrobin is result of both queries.

  • So dc_requests_issued (and with it, also the official query_requests system table, I'm sure) - returns this distribution:

    2006 - Node1
    3823 - Node2
    3441- Node3
    

    And user_sessions returns:

     896 - Node 3
    1138 - Node 1
     510 - Node 2
    

    Each query request issued originates from one session. So we had considerably more connections to node 1 , but they must have been idle (or sending long running, heavy queries) while nodes 2 and 3 got less connections. Node 2 issued the biggest number of queries from the lowest number of connections.

    This is what your reports tell us. All 3 nodes are busy, albeit not in a perfectly balanced manner, but definitely no node is being left out.

    What remains to clarify is where you now see a problem to which we should "suggest ASAP". Can you explain?

  • As you said,
    All 3 nodes are busy, albeit not in a perfectly balanced manner, but definitely no node is being left out.
    We want to balance in perfect manner.

  • @hsaxena20,
    What is your definition of "balance in perfect manner"? Exactly same number of connections on each node? That's meaningless!
    Remember it is Load-Balancer and not connection-Balancer and from your metrics it seems load is perfectly balanced.
    Thx

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.