The database server's multiprogramming level is the maximum number of tasks that can be active at a time, and is controlled by 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 Mobile 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. Often, 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. So, 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. Thrashing can occur, for example, when too many active requests are competing for space in the database cache, but the cache is not large enough 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 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 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 limit:
(3/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, thread deadlock can occur. 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 ends 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 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.
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 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 Unix or Linux.
| Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |