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


query performance slow on 3 node cluster — Vertica Forum

query performance slow on 3 node cluster

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

  • 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

  • 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 - Select Field - Administrator
    edited March 2018

    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';

  • 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 - Select Field - Administrator

    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