Was this helpful?
Sizing the X100 System
Determine the Core Count
For a production system that will run X100 queries, we recommend at least 8 cores if hyper-threading is disabled or 16 cores if hyper-threading is enabled.
The core count is determined by a combination of two factors:
1. What degree of parallelism (DOP) 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 system-wide configuration parameter that you can change, 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. X100 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, 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 System Administrator Guide.
X100 parallel execution adapts so as 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 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 X100 databases in an installation or multiple installations concurrently, then consider 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.
Hyper-threading
Many modern CPUs support some form of hyper-threading that makes multiple logical CPU cores visible to the operating system when only a single physical CPU core is available. Hyper-threading enables the operating system to schedule multiple tasks concurrently on a single core, and the CPU core will multi-task between the multiple tasks when a task temporarily does not need CPU resources or when it stalls.
X100 makes maximum use of CPU core resources and generally does not benefit from hyper-threading. X100 configuration parameters num_cores and max_parallelism_level should always be set based on the number of physical cores, excluding hyper-threading. The cpucount utility returns the number of physical cores and is used to set default values at install time.
Last modified date: 04/03/2024