Setting the number of CPU VPs

You can configure the number of CPU virtual processors (VPs) that the database server uses. Do not allocate more CPU VPs than there are CPU processors available to service them.

When the database server starts, the number of CPU VPs is automatically increased to half the number of CPU processors on the database server computer, unless the SINGLE_CPU_VP configuration parameter is enabled. However, you might want to change the number of CPU VPs based on your performance needs.

You can enable the database server to add CPU VPs as needed, up to the number of CPU processors on the computer. Include the autotune=1 option in the VPCLASS setting:

VPCLASS cpu,autotune=1

If you do not set the VPCLASS configuration parameter to autotune=1, use the following guidelines to set the number of CPU VPs.

Use the following guidelines to set the number of CPU VPs.

Uniprocessor computers

For uniprocessor computers, specify one CPU VP:

VPCLASS cpu,num=1

Dual-processor computers

For dual-processor systems, you might improve performance by running with two CPU VPs. To test if performance improves, set the num field of the VPCLASS configuration parameter to 1 in the onconfig file and then add a CPU VP dynamically at run time by running the onmode -p command.

Multiprocessor computers that are primarily database servers

For multiprocessor systems with four or more CPUs that are primarily used as database servers, set the num option of the VPCLASS configuration parameter in the onconfig file to one less than the total number of processors. For example, if you have four CPUs, use the following specification:

VPCLASS cpu,num=3

When you use this setting, one processor is available to run the database server utilities or the client application.

Multiprocessor computers that are not primarily database servers

For multiprocessor systems that you do not use primarily to support database servers, you can start with somewhat fewer CPU VPs to allow for other activities on the system and then gradually add more if necessary.

Multi-core or hardware multithreading computers with logical CPUs

For multiprocessor systems that use multi-core processors or hardware multithreading to support more logical CPUs than physical processors, you can assign the number of CPU VPs according to the number of logical CPU VPs available for that purpose. The amount of processing that an additional logical CPU can provide might be only a fraction of what a dedicated physical processor can support.

On systems, where multi-core processors are installed, the optimal configuration in most cases is the same as for systems with a number of individual processors equal to the total number of cores. Setting the number of CPU VPs to N-1, where N is number of cores is close to optimal for CPU-intensive workloads.

On computers where the CPU uses multiple threads per core, operating systems show more logical processors than actual processing cores. To take advantage of more CPU threads, the database server must be configured with the number of CPU VPs in the range between N and M, where N is number of cores and M is total number of logical CPUs reported by system. The number of CPU VPs where optimal performance is achieved depends on the workload.

When increasing the number of CPU VPs to use more threads per core, the expected gain in performance is only a fraction of what dedicated physical processor or core can provide.

If you are migrating OneDB from multi-CPU/multicore systems to systems with multiple threads per core, take special care in regard to processor affinity. When binding OneDB CPU VPs to the logical processors of the operating system, you must be aware of the architecture for the CPU. If you are not sure, do not use the CPU affinity so that the operating system schedules CPU VPs to logical processors with available resources. Using affinity without understanding the relationship between the logical CPUs and processing cores can result in severe performance degradation.

For example, to bind each of 8 configured CPU VPs to a separate core on an 8-core system with two threads per core (16 logical CPUs), use the following setting:

VPCLASS cpu,num=8,aff=(0-14/2)