query performance slow on 3 node cluster

ashu123ashu123 Registered User

Hello,
Initially, I have a single node vertica recently added 2 nodes to it. After making 3 node cluster query performance is very very slow, sometimes select query takes 1hour, what I have to do?

Comments

  • ashu123ashu123 Registered User
    db_testing_0=> select count(*) from cluster;
      count   
    ----------
     20000011
    (1 row)
    
    Time: First fetch (1 row): 541.088 ms. All rows formatted: 541.117 ms
    db_testing_0=> select count(*) from cluster where cy=5;
     count 
    -------
     66801
    (1 row)
    
    Time: First fetch (1 row): 16364.492 ms. All rows formatted: 16364.523 ms
    

    why 3 node cluster taking this much of time , Please help I'm stuck

  • ashu123ashu123 Registered User

    created projection on the same table, and the result is like this

    db_testing_0=> select count(*) from cluster_p_b0 where cy=5;
     count 
    -------
     66801
    (1 row)
    
    Time: First fetch (1 row): 34703.515 ms. All rows formatted: 34703.539 ms
    
  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited March 12

    Verify the current k-safety of your DB:

    select node_count, node_down_count, current_fault_tolerance from system;

    Verify the table projections are up to date:

    select projection_name, node_name, verified_fault_tolerance, is_up_to_date, is_segmented from projections where anchor_table_name = 'cluster';

  • ashu123ashu123 Registered User
    user_testing=> select node_count, node_down_count, current_fault_tolerance from system;
     node_count | node_down_count | current_fault_tolerance 
    ------------+-----------------+-------------------------
              3 |               0 |                       1
    (1 row)
    
    user_testing=> select projection_name, node_name, verified_fault_tolerance, is_up_to_date, is_segmented from projections where anchor_table_name = 'cluster';
     projection_name | node_name | verified_fault_tolerance | is_up_to_date | is_segmented 
    -----------------+-----------+--------------------------+---------------+--------------
     cluster_p_b0    |           |                        1 | t             | t
     cluster_p_b1    |           |                        1 | t             | t
     cluster_b0      |           |                        1 | t             | t
     cluster_b1      |           |                        1 | t             | t
    (4 rows)
    
  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    That all looks fine.

    So what do the projections looks like?

    select export_objects('','cluster');
    select export_objects('','cluster_p');
    

Leave a Comment

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