Stack size per worker

[huber1]

Hi

I experimented with the -gss database startup parameter and have set -gss 16M on a OS X server. The documentation states 8MB is the maximum for Unix (64-bit). Still, the database starts with 16MB.

http://dcx.sap.com/index.html#sqla170/en/html/3bcb85766c5f1014a4d7cbd6792bc423.html

I am asking myself, is it using only 8MB, is the documentation not up to date or does it take 16MB into account?

Regards, Robert

[johnsmirnios]

A quick peek at the code suggests that the max would be 8MB. If you specify an stack size larger than the max, it will just silently just use the max. How are you determining what size of stacks the threads are actually getting? I'm also curious why you would want such a large stack.

[Breck_Carter]

When you ask "why you would want", that's a clue...

[huber1]

Quite dangerous indeed 😉

OK, thanks for the info that the stack size will just get the max. possible even if more is specified (which makes no sense of course). I don't know how to determine the memory size the stacks are getting. Is there a way to see that? I experimented with bigger memory allocations because of the hope to make it faster. Same for the -gnl and -gnh options. What is your experience with these settings?

Best regards, Robert

[johnsmirnios]

Larger stacks will never increase performance. In some cases, if you have many threads, large stacks and limited address space (eg 32-bit database server), the address space used for stacks will reduce your maximum cache size which would make things go slower. 100 threads x 10MB stacks means that you lose 1GB of cache on a 32-bit platform. Ouch.

The reason we ignore invalid gs/gss settings is primarily so that customers who embed the product will not need to change command line options for their deployed apps if we ever change the maximums in a patch.

Playing with -gn and related switches isn't probably the first place to look for performance tuning. So long as you aren't hitting thread deadlocks, you are probably fine. Performance tuning is big area and obviously specific to your own situation, configuration, data, and queries. I'd start by finding, if you can, a single query that you believe may not be performing adequately. Post another question with this query & related schema to this forum and other contributors may be able to help you.

Regards, -john.

[huber1]

Thanks for your support, John. And the good advice I will follow.

Regards, Robert

[VolkerBarth]

100 threads x 10MB stacks means that you lose 1GB of cache on a 32-bit platform. Ouch.

Well, according to your explanation, it will currently cost "only" 0,8 GB, due to the limitation... Still ouch:)