What are Resource Pool HARD CPU Limit, SOFT CPU Limit, and default_pool?
In the HARD CPU limit, Resource pools will not exceed their specified limit even if the system is idle. However, Resource Pools can temporarily exceed their CPU limit if some CPU is idle in soft CPU limits.
HARD CPU Limit is more for environments like POC setup where one wants to get performance guarantees to ensure we have adequately provisioned the cluster under worst-case scenarios.
We either create a Resource Pool in the SOFT CPU or HARD CPU limit. Unfortunately, we can't do both at the same time. This means that It's either All HARD or All SOFT!
If we perform
SET GLOBAL resource_governor_cpu_limit_mode = 'HARD';all the existing Resource Pools CPU Limit will be converted to HARD CPU Limit and vice versa with SOFT CPU Limit.
resource_governor_cpu_limit_mode, important Points to remember,
- A soft CPU limit is recommended for most applications, as they typically want to utilize CPU resources from other Resource Pools if such resources are available.
- A HARD CPU limit is practical when you want to ensure maximum predictability of CPU usage at the expense of not utilizing extra CPU resources that may be available.
- One can create up to ten Resource Pools, not including the built-in Resource Pools.
Click here to learn more about the CPU Limit Mode.
Regarding the default_pool,
The default_pool doesn't have any restrictions. However, if you want to utilize RG, avoid running queries under the default_pool.
Any Resource Pool with a 100% CPU limit is not subjected to CPU governance, i.e., queries running inside that Resource Pool will use any CPU cores. So if you have two Resource Pools, A(40% CPU) and B(40% CPU), and you have queries running inside the default_pool, queries running on the default Resource Pool can run on any and all core. All queries running inside Resource Pool A will be restricted to be scheduled on only half the total number of cores.
You don't want to run any queries inside the default_pool for CPU governance to be effective. The default_pool is mainly for someone who doesn't want to use RG. Therefore, anyone who wants to use RG should not run queries inside the default_pool.