usage of resource pools

xiaoc10

We have a 12 Vertica nodes cluster. Each node has 24 cores. I'm testing different settings for resource pool. Two settings are like following:

CREATE RESOURCE POOL parallel_24
EXECUTIONPARALLELISM 24;

CREATE RESOURCE POOL parallel_6
EXECUTIONPARALLELISM 6;

And then I run a test query using these two resource pools separately and then use the following query to verify the test query does use the correct resource pool.

SELECT * FROM query_profiles, resource_acquisitions
where resource_acquisitions.transaction_id = query_profiles.transaction_id and
resource_acquisitions.statement_id = query_profiles.statement_id and
query_profiles.transaction_id=94575592175788847 and
query_profiles.statement_id = 1

I list part of columns generated by the above query:

pool_name thread_count duration_ms
parallel_24 356 3500

pool_name thread_count duration_ms
parallel_6 362 9000

From the above result, we can clearly see that increasing the parallelism reduce the running time. But I did not understand the thread_count from two aspects: (1) why there are so many threads involved? (2) parallel_6 even has more threads than parallel_24? (3) what does thread_count really mean?

Thanks,

K_Krutiy

 

Vertica is MPP. This means that when you execute query it will try to use all cluster resources to get it done as fast as possible.

 

Vertica designed to deal with huge queries.

When you execute query it will try to use every core on every node.

Your 12 nodes multiplied by 24 cores = 288 threads already. Since statement has multiple stages and operators you will get more threads added to that number.

xiaoc10

K_Krutiy, thanks for your reply. My actually question is why parallel_6 and parallel_24 using almost the same thread_count, but parallel_24 is almost two times faster than parallel_6?

Sharon_Cutter

Don't worry about the thread counts.  PROFILE the two queries and review the number of instances of each operator_name per node in the EXECUTION_ENGINE_PROFILES table.  Start with the "clock time (us)" counter_name.  

 

For example if you are are reading data from a single fact table, with EXECUTIONPARALLELISM of 24 you'll see 24 instances of the Scan operator on each node.  With EXECUTIONPARALLELISM of 6, you'll see only 6.  

 

  --Sharon

 

xiaoc10

@Sharon, thanks so much!!!!! That's excatly whant I need. Just one more question, With EXECUTIONPARALLELISM of 6, I actually see 8 scans, With EXECUTIONPARALLELISM of 4, I actually see 6 scans, With EXECUTIONPARALLELISM of 24, I do see 24 scans. Why there are two additional scans for 4 and 8?

 

 

Sharon_Cutter

Are these queries scanning a single table/projection or multiple tables/projections?  Check the path_id values to see how the Scans correlate to different path ids within the explain plan.

 

  --Sharon