Formula for calculating the maximum number of concurrent queries concurrent queries depending on ha
Is there any formula to calculate the number of concurrent queries depending on hardware specification of the Vertica cluster?
0
Comments
This calculation will depend on a couple things:
- Number of cores (per node)
- Memory (per node)
- The type of queries you are looking to run concurrently
The number of cores vs. the amount of memory defines the memory available per query. Reducing plannedconcurrency increases memory available per query (and vice versa when increasing it). For the most part, the plannedconcurrency value is best set to AUTO, which will let Vertica decide what resources to allocate per query. If you start running into resource issues, we will be able to tune resource pool parameters to better serve your queries. Note: maxconcurrency is only to allow you to set a hard concurrency limit. Are you currently running into resource allocation issues?
Target Memory Determination for Queries in Concurrent Environments:
The resource pool parameters of MEMORYSIZE and PLANNEDCONCURRENCY (CREATE RESOURCE POOL in the SQL Reference Manual) provide the options that let you tune the target memory allocated to queries. The query_budget_kb column in the V_MONITOR.RESOURCE_POOL_STATUS system table shows the target memory for queries executed on the associated pool. Normally, queries do not require any specific tuning, but if needed, the general formula for computing query_budget_kb is as follows:
- If MEMORYSIZE is set to 0, in which case the pool borrows all memory as needed from the GENERAL pool, the target amount of memory for the query is calculated using the Queueing Threshold of the GENERAL pool / PLANNEDCONCURRENCY.
- If the resource pool for the query has the MEMORYSIZE parameter set, and the pool is standalone (i.e. cannot borrow from General pool) then the target memory is to use the amount of memory in the Queueing Threshold of the pool / PLANNEDCONCURRENCY.
- Otherwise, if MEMORYSIZE is set but the pool is not standalone, the target memory is set to MEMORYSIZE / PLANNEDCONCURRENCY of the pool.
Therefore, by carefully tuning the MEMORYSIZE and PLANNEDCONCURRENCY parameters, it is possible to restrict the amount of memory used by a query to a desired size.
I have included some more info on the plannedconcurrency:
This parameter specifies the typical number of queries running concurrently in the system. Set PLANNEDCONCURRENCY to AUTO to specify that HP Vertica should calculate this number. HP Vertica takes the lower of these two values:
- Number of cores
- Memory/2GB
The minimum value for PLANNEDCONCURRENCY is 4.
HP Vertica advises changing this value only after evaluating performance over a period of time.
The Tuple Mover draws its resources from the TM pool. For the TM pool, the PLANNEDCONCURRENCY parameter must be proportional to the size of the RAM, the CPU, and the storage subsystem. Depending on the storage type, if you increase PLANNEDCONCURRENCY for the Tuple Mover threads, you might create storage I/O bottleneck. Monitor the storage subsystem; if it becomes saturated with long I/O queues, more than two I/O queues, and long latency in read and write, adjust the PLANNEDCONCURRENCY parameter to keep the storage subsystem resources below saturation level. In addition, you might need to:
- Partition storage data files
- Adjust block-size optimization on storage subsystems such as RAID 5 or RAID 10
Identify the optimal number of disks in the RAID array.
Regards
Bhawana