HANA CPU vs Thread

[Former Member]

Hi Gurus,

We had one issue in our production systems regarding high CPU.Niw we have requitement from customer side that they want to limit the CPU consumption of our Hana DB for any possible situation means limit the workload for CPU so we are planning for workload class for CPU consumption but not able to decide the limit value of threads.

I have checked note-2222250 - (FAQ: SAP HANA Workload Management). Also raised the concern with SAP and waiting for reply.

I have done few checks from my end and executed the script HANA_Threads_ThreadSamples_StatisticalRecords_1.00.120 and sorted the values by PX_MAX(Maximum recorded parallelism (i.e. maximum number of threads concurrently working on the database request)). There were few rows where PX_MAX was more than 120,but if i check the CPU usage at that time I can't find any peak in performance tab of Hana studio.

Below is system CPU info

Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte

CPU(s): 120 On-line

CPU(s) list: 0-119

Thread(s) per core: 1

Core(s) per socket: 15

Socket(s): 8

From above info you can see that one core can handle one thread at a time,so during PX_MAX was high(ex-120) CPU should also be high but CPU usage is not following the same trend.

Please Help me in understanding the issue.

[lbreddemann]

Not quite sure what you're asking here.

Is it that you want to see high CPU usage statistics for statements with a high maximum parallelisation degree?

If so: the px_max tells you how many pieces of the work can be done in parallel - not how much total work there is to do.

For the CPU utilisation to go up, there needs to be a lot of work, so that the CPU gets used for a longer period of time. E.g. if you want a 1-second sample of 100% CPU usage, the CPU has to be used for the full second. In case your sampling is more coarse, you will typically see an average value, which in most cases will be below 100% - sometimes even a lot below that. That means that

a) your parallel workload might not have been as CPU intensive in the first place
and

b) the CPU utilisation sampling might have hidden the CPU usage spike in averaged values.