The database server's multiprogramming level is the maximum number of tasks that can be active at a time, and is controlled through the specification of the -gn server option. An active task is one that is currently being executed by a thread (or fiber) in the database server. An active task may be executing an access plan operator, or performing some other useful work, but may also be blocked, waiting for a resource (such as an I/O operation, or a lock on a row). An unscheduled task is one that is ready to execute, but is waiting for an available thread or fiber. The number of active tasks that can execute simultaneously depends on the number of database server threads and the number of logical processors in use on the computer.
The multiprogramming level remains constant during server execution, and applies to all databases on that server. The default is 20 active tasks for the network database server and for the personal database server, except on Windows CE where the default is 3.
It can be difficult to determine when to raise or lower the multiprogramming level. For example, if a database application makes use of Java stored procedures, or if intra-query parallelism is enabled, then the additional server tasks created to process these requests may exceed the multiprogramming limit, and execution of these tasks will wait until another request completes. In this case, raising the multiprogramming level may be appropriate. In many cases, increases to the multiprogramming level will correspondingly increase the database server's overall throughput, as doing so permits additional tasks (requests) 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 thread (in the case of Windows and Linux, a lightweight thread called a fiber) and additional bookkeeping structures to control its scheduling. In addition, each active task requires the preallocation of address space for its execution stack. The size of the stack varies by platform, but is roughly 1 MB on 32-bit platforms, and larger on 64-bit platforms. On Windows systems, the allocation of stack space affects the address space of the server process, but the stack memory is allocated on demand. On Unix platforms, including Linux, the backing memory for the stack is allocated immediately. Hence, setting a higher multiprogramming level increases the server's memory footprint, and reduces the amount of memory available for the cache 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. This can occur, for example, when too many active requests are competing for space in the database cache, but the cache is insufficiently large to accommodate the working set of database pages used by the set of active requests. 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 must ensure adequate resources exist at all times to handle the maximal number of tasks as specified by the -gn option. The most critical resource is memory. Query execution operators such as hash join or sorting can require significant amounts of memory to execute efficiently.
A memory usage governor exists for each task that limits the amount of memory that should be used for this task. This soft limit is defined by the following formula:
(total size of the database cache) / (multiprogramming level)
If a task exceeds this threshold, the database kernel requests any query execution operators for that task to free unnecessary memory buffers (if possible) so that the limit is no longer exceeded. This can result in execution operators switching to low-memory execution strategies at run time, which conserve memory at the expense of slower execution times.
The query optimizer takes this memory threshold into account when costing access plans, and refrains from choosing execution strategies that rely on larger amounts of memory than this threshold. In addition, each task has a limit of the following:
(1/4 maximum cache size) / (number of currently active tasks)
If this limit is exceeded, the statement fails with an error.
Reducing the database server's multiprogramming level by lowering the number of concurrently-executing tasks usually lowers the 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.
In SQL Anywhere, threads (fibers) execute tasks in a cooperative fashion. Once a task has completed, the thread (fiber) is free to pick up the next task awaiting execution. However, if a task is blocked, for example when waiting for row lock, the thread (fiber) is also blocked.
When the multiprogramming level is set too low, this can cause thread deadlock. Suppose that the database server has n threads (fibers). Thread deadlock occurs when n-1 threads are blocked, and the last thread is about to block. The database server's kernel cannot permit this last thread to block, since doing so would result in all threads being blocked, and the server would hang. Rather, the database server terminates the task that is about to block the last thread with SQLSTATE 40W06.
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 consequently impairs scalability. One example of this 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 consequence is that it effectively serializes all of 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.
It is recommended that you experiment with your application's workload to analyze the effects of the server's multiprogramming level on server throughput and request response time. Various performance counters are available as either property functions, or through the Windows Performance Monitor on Windows, to help you analyze database server behavior when testing your application. The performance counters related to active and unscheduled requests are particularly important to this analysis.
If the number of active requests is always less than the value of the -gn database server option, you can consider lowering the multiprogramming level, but you must take into account the effects of intra-query parallelism, which adds additional tasks to the server's execution queues. If the effect of intra-query parallelism is marginal, lowering the multiprogramming level can be done safely without reducing overall system throughput. However, if the number of total requests (active + unscheduled) is often larger than -gn, then an increase in the multiprogramming level may be warranted, subject to the tradeoffs outlined above. Note that the Performance Monitor is not available for NetWare, Unix, or Linux platforms.