EXECUTIONPARALLELISM in resource Pool
hsaxena20
Community Edition User
Whats does it mean EXECUTIONPARALLELISM? and how we can set it?
Why it is required?
0
hsaxena20
Community Edition User
Whats does it mean EXECUTIONPARALLELISM? and how we can set it?
Why it is required?
Comments
EXECUTIONPARALLELISM – imposes a limit on the maximum number of threads that any single request can use.
Use this parameter to limit the number of threads that would be used to process any single query issued in this resource pool.
Reducing this parameter may increase the throughput of short queries issued in the pool, especially if the queries are executed concurrently. If you choose the default of AUTO, Vertica sets this value for you.
If you choose to manually set this parameter, set it to a value between 1 and the number of cores.
Run the below query to set the executionparallelism.
Syntax)
ALTER RESOURCE POOL pool‑name [ parameter‑name setting ]…
ex)
ALTER RESOURCE POOL recovery executionparallelism 10;
ALTER RESOURCE POOL
I am in version 12 and I would like to know what it will choose in AUTO? Does it choose on the fly on every query or does it choose once? If I upgraded the server and have more cores, will it automatically update?
ExecutionParallelism is a pretty edge-case tuning knob. It's just as likely to hurt a query as help it, and even then, you're not likely to see significant differences in performance one way or the other. So, you're best just leaving it at AUTO.
And like other resource pool settings, they're all evaluated at runtime, so yes, it will adjust if your cores adjust.
Please correct me if I'm wrong, It might hurt the query but reduce the overall load on the cluster. I've a case where there are several queries that consume 100% cpu on all nodes in the cluster for approx. 30 min. During this time, the cluster is stuck and simple queries from system tables and other tables do not complete in reasonable time.
is reducing EXECUTIONPARALLELISM can help in this case? I tried other options which didn't work (CPU affinity, cascade to a low priority pool, etc.)
Thanks
@lybhrwn742 : Depending on the type of queries running in that pool, you can change executionparallelism. Please find the below link which has some scenarios and how to tune resource pools
https://www.vertica.com/kb/BestPracticesforManagingResourcePools/Content/BestPractices/BestPracticesforManagingResourcePools.htm