Controlling threading behavior

There are five main factors that control threading behavior, each of which are governed by a server option. Not all of these options are supported on every platform.

  • Multiprogramming level (-gn server option)   The -gn option controls the server's multiprogramming level. This value determines the maximum number of tasks that may be active at one time. Each database request uses at least one task, and possibly more if intra-query parallelism is involved. Additionally, the server will occasionally schedule tasks to perform internal housekeeping activities. When the number of tasks in the server exceeds the multiprogramming level, the outstanding tasks must wait until a currently-running, or active task completes. By default, a maximum of 20 tasks can execute concurrently for the network database server and the personal database server. See -gn server option, and Setting the database server's multiprogramming level.

  • Stack size per internal execution thread (-gss server option)   You can set the stack size per internal execution thread in the server using the -gss option. The -gss option allows lowering the memory usage of the database server, which may be useful in environments with limited memory. The only Windows operating system that supports this option is Windows Mobile. See -gss server option.

  • Number of processors (-gt server option)   If you have more than one processor, you can control how many processors the threads exploit by specifying the -gt option. See -gt server option.

  • Processor concurrency (-gtc server option)   You can specify the maximum number of threads that can run concurrently on a CPU. By default, the database server runs on all hyperthreads and cores of each licensed physical processors. See -gtc server option.

Threading tips

  • Increasing -gn can reduce the chance of thread deadlock occurring. See -gn server option.

  • Setting -gt to 1 can help work around concurrency problems. See -gt server option.

  • Investigating the Performance Monitor readings for Requests: Active and Requests: Unscheduled can help you determine an appropriate value for -gn on Windows. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase the value for -gn. See Performance Monitor statistics, and -gn server option.

Processor use and threading example

The following example explains how the database server selects CPUs based on the settings of -gt and -gtc. For the purpose of the following examples, assume you have a system with 4 processors, with 2 cores on each processor. The physical processors are identified with letters, and the cores with numbers, so this system has processing units A0, A1, B0, B1, C0, C1, D0, and D1.

Scenario Network database server settings
A single CPU license or -gt 1 specified
  • -gt 1
  • -gtc 2
  • -gn 20

Threads can execute on A0 and A1.
No licensing restrictions on the CPU with -gtc 5 specified
  • -gt 4
  • -gtc 5
  • -gn 20

Threads can execute on A0, A1, B0, C0, and D0.
A database server with a 3 CPU license and -gtc 5 specified
  • -gt 3
  • -gtc 5
  • -gn 20

Threads can execute on A0, A1, B0, B1, and C0.
No licensing restrictions on the CPU with -gtc 1 specified
  • -gt 4
  • -gtc 1
  • -gn 20

Threads can execute only on A0.
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1