SAP ASE 16 - Bad performance using Threaded Kernel on IBM AIX

[pablo_benavidez]

Problem description: We are unable to achive the performance of Process Kernel (old) using the new Threaded kernel. We have done several test (entire Batch process, particular process and Update statistics) and the Threaded Kernel is between a 25%-35% slower than old Process Kernel.

Case of study: Core Banking for important Bank. ASE 16 SP02 PL 05, running on IBM AIX 7.1 with 20 P8 Cores using SMT4 (80 logical processors) or SMT 8 (160 logical processors) - 300Gb of Ram and 4Tb of SSD/Flash devices all raw devices. Nice Hardware for test...

Additional Info:We have applied all suggestions coming from white paper : Tuning ASE on IBM AIX (developed by SAP and IBM) last version posted on SAP site. However the Threaded process kernel remain slow. We have done several test using 30, 20 , 10 and 5 Threads for syb_default_pools. and all the test were done using different workloads :

1. we have run an entire Batch process (1:50hs for Process Kernel vs 2:45hs for Threaded)

2. Particular process (heavy) 15 mins (Process kernel) vs 36 Threaded Kernel

3. Update statistics for 40 DBs using 40 concurrent connections: (2:40hs for Process Kernel vs 3;22hs for Threaded)

We also have disabled the "aggresive task scheduling" and we were able to see some minor improvements (5%-8%) on threaded kernel but we still remain a 30% over Process Kernel times.

Questions:

a. Is there any specific configuration on the IBM AIX configuration about threading ,not included on the white paper? Something lile test AIXTHREAD_SCOPE=P , SPINLOOPTIME or YIELDLOOPTIME that could help the Threaded Process to perform better?

b. Any additional configuration on IBM IOCP (I/O Completition Port), I mean they are enabled but I dont know if we can tune something else.

c. Any additional suggestion?

BTW: We have a case opened, but it is moving veery slowly.

Best regards !

[Mark_A_Parsons]

Have you run the 2 tests (process vs kernel) on the same machine and same version of ASE?

Are you using the same set of ASE configurations (where possible)?

For query-based tests, have you verified the same query plans are in use (especially for the big time/cpu/disk consumers)?

[pablo_benavidez]

Hi Mark,

These are a basic questions, but the answer is yes for all your questions

Additional information: we have isolated the behavior (threaded kernel is at least 30% slower than Process Kernel) with a simple process like update statistics (I mean no query plans, etc), We have a script with 20 connections that run update statistics for several tables and columns in paralel, we just switch the kernel process and reconfigure the syb_default_pool and run a new test. Most of the new threaded process parameeters are configured . After avery test we restart ASE to be in the same conditions.

Here are the results

Process Kernel (20 Engines) take 35 mins

Threaded Kernel (20 Threads) 1:10 hs

Threaded kernel (10 Threads) 50 mins

Threaded kernel (5 threads) 45 min (this was our best time in threaded)

After that we have set "aggressive task Schedule" to 0 but no big improvements (1 min)

It "seems like" Process Kernel have a process affinity (SPID), that Threaded Kernel doesnt have and then; the context switch beween engines is killing us.on thread kernel.

Also, and because this is a new kernel calling a new AIX system calls from Native Threads, Im trying to do some fine tuning on AIX, I mean we have been working for 20 years with Process and most of AIX are well tuned for this kernel.. But Im unable to find good suggestions for AIX thread tuning and IO Completition Ports (new way to handle the IOs)

Hope the gurus could give me some lights on this .