Determine the Core Count
The core count is determined by a combination of two factors:
1. What degree of parallelism do you plan to use to satisfy query response times?
2. How many operations do you want to be executed concurrently (at maximum performance)?
In theory, if a system is not currently experiencing bottlenecks, then setting parallelism to factor x will roughly reduce the query execution time linearly by a factor x. In practice you will sometimes see less than this.
Degree of parallelism is a changeable, system-wide configuration parameter, but you can also set a desired level of parallelism on a per-query basis using the WITH MAX_PARALLEL n clause at the end of the query. A first-cut estimate of the Degree Of Parallelism (DOP) you should set can be calculated by the following formula:
DOP = column data retrieved in GB (uncompressed) / desired response time / 1.5
Note 1: This formula assumes the maximum data processing rate is 1.5 GB/s per core, which may be too high or too low for your query workload. Adjust the 1.5 factor in the formula if necessary.
Note 2: Only calculate the size of the data in the columns involved in the query. Vector uses column-based storage and does not process column data that is not needed in the query.
For example, if the largest query in the system runs against a single denormalized table with a billion rows and the query summarizes sales by product code with:
• Product code that is on average 20 bytes
• Sales number that is on average 10 bytes
• Required response time is 5 seconds
then the DOP should be set to:
DOP = (20+10 Bytes) * (1 KB / 1024 Bytes) * (1 MB / 1024 KB) * (1 GB / 1024 MB) * 1,000,000,000 / 5 sec / 1.5 GB/s = 3.8147 = ~4
When you set DOP to 4 then every query will use 4 CPU cores for the duration of the query, and the number of concurrent queries that can be executed at maximum performance is:
number of concurrent queries = number of cores / DOP
DOP is set with the max_parallelism_level configuration parameter. For details, see the Vector User Guide.
Vector parallel execution is adaptive to minimize context switching for maximum throughput and to prevent system overload. Queries will automatically get a reduced degree of parallelism if the database is busy processing other operations. If you want to maximize the performance for queries and are willing to accept a greater variance in query response times then you can set a higher degree of parallelism. If consistent response times are more important to you then you set a lower degree of parallelism. To disable parallel query set the value for parameter max_parallelism_level to 1.
Balance the required query response times (via the degree of parallelism you set) with the number of concurrent queries you want to run at maximum performance in order to determine the ideal core count for your system. Many applications, most notably ones that require user input such as ad-hoc query applications, do not execute queries continuously. Users will query some data, analyze results, and run more queries. As a result, you will often support many more concurrent users than the number of concurrent queries executing at any point in time.
Keep in mind that a user may submit multiple concurrent queries to the system, so every distinct, concurrent operation, independent of its origin, must be counted.
If you plan to run multiple databases within an installation or multiple installations concurrently then take into account that every database and installation will each use CPU resources. At present you cannot control resource allocation between databases so the OS will decide the priorities.