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:

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:

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:

then the DOP should be set to:

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:

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.

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.