Threaded mode SAP ASE 16 and Linux Hyper/Multi threaded cpus(cores)

[jmtorres]

Good Day, 

Reading sap kba 2450359 - 'How to configure number of thread when using threaded kernel mode? - ASE ,

it's content mentions:

'For an ASE using 'threaded' kernel mode, the recommendation is for the number of threads
to be configured as follows: Number of Threads = (Number of CPUs - 2).

For example: 16 CPUs on the host, the number of thread should be set to 14.'

This example assumes a 1-1 relationship , i.e, 1 core  spans 1 OS thread?

So if we have the same 16 cores(cpus) in which each core spans 2 threads, then we'll have 32 logical processors. In this case syb_default_pool should tune to 30?

Thank you

[Mark_A_Parsons · Accepted solution]

There's a brief section from the P&T manuals (which the KBA appears to have copied from) which also has this to say about configuring the number of threads:

+++++++++++++++++++++++++++++++

When configuring the CPU, consider that:

• Servers with high I/O loads may require that you reserve more CPU for I/O threads.

• Not all CPUs are equal,and you may not be able to reserve all subcore threads. For example, you may need to treat an 8-core, 16-thread system as if it has only 12 CPUs.

• You may be required to reserve CPU for other applications on the host.

SAP recommends that you use sp_sysmon to validate your configuration. If you see a high degree of nonvoluntary context switching, or an engine tick utilization that is higher than the OS thread utilization, you may have over-configured SAP ASE relative to the underlying CPU, which can lead to a significant loss of throughput.

+++++++++++++++++++++++++++++++

Without knowing how many threads you have allocated to other pools, or what other processing may be running on the same host, I'd recommend starting with something on the order of 20-25 threads.  Then monitor performance to see if you've under-/over-configured the number of threads.  Keep in mind you can bring new threads online in real time (ie, no need to bounce the dataserver).

As for monitoring performance ... I (still) prefer sp_sysmon (running 24x7 at 15 minute intervals), though you're welcome to utilize the MDA tables if you prefer (in which case I'd recommend ASEMON for capturing, searching and displaying MDA data).

[jmtorres · Accepted solution]

Thank you Mark.

[sladebe]

I don't have the "how many ASE threads" to configure answer, but I thought I'd mention a few other things. H/W core threading is aimed for improving concurrency in a multi-user environment. Doesn't apply so much in a dedicated database environment.   In my environment, we turn off H/W core threads.

If you have a bunch of physical cores, you might have to worry about NUMA (non uniform memory architecture)  .  This is where groups of cores are assigned they're own local bank of memory.   Memory updates that don't hit the local bank go across the global bus to the destination memory bank.   ASE is not NUMA aware.  Ie., it doesn't try and keep an ASE level threads assigned to a physical group of cores.   Some places just go with 4 or 8 memory regions/banks.   Some sites just go with 1 memory region (all updates automatically get all memory banks).  I can't find anyway command to bind a thread pool to a set of CPUs/cores (see "create thread pool").  The old CPU-to-engine binding commands no longer work in threaded mode (feel free to correct me if I'm wrong)