Vertica Cluster Work balance

Ghayaty

I have a vertica cluster - 2 nodes - how to make sure that running queries are working on the 2 nodes ?

 

Adrian_Oprea_1

 

I belive you are reffering to query plan executions ! and not user connections !

 

 

To make sure you sessions will use the processing of both nodes if your projection designs is correct.

 

Once you have projections spread around your cluster you can look at the explain plan to see what projections were used for your query.

Ghayaty

Hello Adrian,

 

I meant, when i ran query on vertica cluster, is the load balanced between the 2 nodes ?

Adrian_Oprea_1

 

Do you have load balancer enabled ?

 

For load balance info see here : Load Balancer.

 

Also to check how connections are distributed across your cluster use this query:

SELECT a.node_name, 
       a.requests, 
       ROUND((a.requests / b.total_requests) * 100, 2.0) AS percent
FROM   (SELECT node_name, 
               COUNT(*) AS requests 
        FROM   v_monitor.query_requests 
        GROUP  BY node_name) a 
       CROSS JOIN (SELECT COUNT(*) AS total_requests 
                   FROM   v_monitor.query_requests) b 
ORDER  BY percent DESC;

You can play with the query to fit our time frames. 

 

Hope is helped 

 

 

 

Ghayaty

Thanks to your note, I set the load balancer to Roundrobin that means any query run will be load balanced on the 2 nodes, please correct me.

 

Also, I ran your query,

dbadmin=> SELECT a.node_name, 
dbadmin->        a.requests, 
dbadmin->        ROUND((a.requests / b.total_requests) * 100, 2.0) AS percent
dbadmin-> FROM   (SELECT node_name, 
dbadmin(>                COUNT(*) AS requests 
dbadmin(>         FROM   v_monitor.query_requests 
dbadmin(>         GROUP  BY node_name) a 
dbadmin->        CROSS JOIN (SELECT COUNT(*) AS total_requests 
dbadmin(>                    FROM   v_monitor.query_requests) b 
dbadmin-> ORDER  BY percent DESC;
-[ RECORD 1 ]----------------------
node_name | v_vertica_test_node0001
requests  | 2472
percent   | 99.920000000000000000
-[ RECORD 2 ]----------------------
node_name | v_vertica_test_node0002
requests  | 2
percent   | 0.080000000000000000

 

First Run of the query showed only node0001 .. I ran it on 2nd node then ran it again on 1st node to get the above resuly.

 

I am still abit confused of the way the 2 nodes are sharing the effort, I need the 2 nodes to serve to reduce required time of queries.

Adrian_Oprea_1

Hi,

 

   Setting up only roundrobin would not suffice, you also need to setup you client connection to follow the same load balance .

   

  What client do you use ? 

See an example here of a client side configuration ODBC DNS config, you must list you servers ip address as the backup servers, this way your clinet will choose the list following server load balance method.