Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » Running the Database Server » Some common options » Threading in SQL Anywhere

Controlling threading behavior Next Page

Setting the database server's multiprogramming level


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.

Raising the multiprogramming level

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:

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.

Lowering the multiprogramming level

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.

Choosing the multiprogramming level

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.