Effects of different multiprogramming level settings

It can be difficult to determine the optimal value for the multiprogramming level. For example, if a database application uses Java stored procedures, or if intra-query parallelism is enabled, then the additional server tasks that are created to process these requests may exceed the current multiprogramming level, and execution of these tasks must wait until another request completes. In these cases, raising the multiprogramming level may be appropriate. You can adjust the multiprogramming level of a network database server at any time. However, for personal database servers you cannot change the multiprogramming level after the server has started.

Raising the multiprogramming level

Often, increases to the multiprogramming level correspondingly increase the database server's overall throughput because an increase to the multiprogramming level allows additional tasks to execute concurrently. However, there are tradeoffs in raising the multiprogramming level that should be considered. They include the following:

Increased contention By increasing the number of concurrent tasks, you may increase the probability of contention between active requests. The contention can involve resources such as schema or row locks, or on data structures and/or synchronization primitives internal to the database server. Such a situation may actually decrease server throughput.
Additional server overhead Each active task requires the allocation and maintenance of a worker and additional bookkeeping structures to control its scheduling. In addition, each worker requires the preallocation of address space for its execution stack. The size of the stack varies by platform. On Windows operating systems, the allocation of stack space affects the address space of the database server process, but the stack memory is allocated on demand. On Unix platforms, including Linux, the backing memory for the stack is allocated immediately. As a result, setting a higher multiprogramming level increases the server's memory footprint, and reduces the amount of memory available for the cache manager because the amount of available address space is reduced.
Thrashing The database server can reach a state when it uses significant resources simply to manage its execution overhead, rather than doing useful work for a specific request. This state is commonly called thrashing. Thrashing can occur, for example, when too many active tasks are competing for space in the database server cache, but the cache is not large enough to accommodate the working set of database pages used by the set of active tasks. This situation can result in page stealing, in a manner similar to that which can occur with operating systems.
Impact on query processing The database server selects a maximum number of memory-intensive requests that can be processed concurrently. Even if you increase the database server's multiprogramming level, requests may need to wait for memory to become available. See Cache and the memory governor.

Memory for data structures The database server uses resources to parse and optimize statements. For very complex statements or small cache sizes, the memory consumed for server data structures can exceed the amount that is available. A memory governor limits the amount of memory used for each task's server data structures. Each task has the following approximate limit:

(3/4 maximum-cache-size) / (number-of-active-requests)

If this limit is exceeded, the statement fails with an error.

Lowering the multiprogramming level

Reducing the database server's multiprogramming level by lowering the number of concurrently-executing tasks usually lowers the database server's throughput. However, lowering the multiprogramming level may improve the response time of individual requests because there are fewer requests to compete for resources, and there is a lower probability of lock contention.

When the multiprogramming level is set too low, thread deadlock can occur. If the multiprogramming level is at a reasonable level for the workload, the occurrence of thread deadlock is symptomatic of an application design problem that results in substantial contention, and as a result, impairs scalability. One example is a table that every application must modify when inserting new data to the database. This technique is often used as part of a scheme to generate primary keys. However, the result is that it effectively serializes all the application's insert transactions. When the rate of insert transactions becomes higher than what the server can service because of the serialization on the shared table, thread deadlock usually occurs.

The -gnl database server option sets the minimum multiprogramming level.

Effects of different multiprogramming level settings

Raising the multiprogramming level

Lowering the multiprogramming level

See also