[IQUG] IQUG Digest, Vol 59, Issue 16

Steve Shen sshen at sscinc.com
Wed Nov 15 14:31:27 MST 2017


Hi Chris,

We had 40 cores based on the output from executing "cpuinfo": Detected 80 logical processor(s), 40 core(s), in 4 chip(s)

There are 512 GB of RAM. I allocated 120 GB to "-iqtc".  I do not believe that adding additional 20 GB or 40 GB will totally eliminate the issue; but I am going to try it this weekend.

Development managers will not agree to lower the number of concurrent users (-gm).  It's set to 300.

Since there is no way to simulate the workloads in the UAT environment, there is no way to tell whether any new HG indexes will improve or worsen the overall performance until we implement them in the production environment.  We have not run into this issue in the UAT IQ instance because workloads were relatively low there.

I am also going to try to set option public.force_no_scroll_coursors = 'on' after discussing this with the development managers.

Thank you very much for your advice.

Kind regards,

Steve Shen

-----Original Message-----
From: Baker, Chris [mailto:c.baker at sap.com]
Sent: Wednesday, November 15, 2017 4:01 PM
To: Steve Shen; iqug at iqug.org
Subject: RE: IQUG Digest, Vol 59, Issue 16

The other option is to reduce IQ_SYSTEM_TEMP usage by checking the index advisor to see if you are missing HG indexes that perhaps should be added, and thereby avoid sorting and temp space usage.

Sorting may not be the whole issue, however.  Temp space is also used to materialize the result set fully, before returning the results to the client.  Try setting the option force_no_scroll_cursors = 'on' to avoid this happening, if possible (some applications still open the cursor as scrollable - e.g. MicroStrategy, so don't like this, so the default is 'off').  You can set the option globally as:
        set option public.force_no_scroll_coursors = 'on'; or on a per-user basis or temporary.

Also check that any join columns, etc, have the same datatypes to avoid implicit conversions.  If your FP indexes have also rolled over to flat-FP, then the sort may take more space in temp as well, depending on the query plan.

Your increase in temp usage due to the increased connections may point to insufficient HG indexing.  You should avoid changing the 'sort_pinnable_cache_percent' as all that will do is reduce performance further.  Look at your indexing first.

How much memory and cores are you running right now?  Are you even at the suggested minimum (which I think is now about 8-12 GB/core for IQ 16- Mark may correct me here) RAM requirements?

The other thing you may want to think of is to reduce the number of concurrent users (-gm) allowed to connect, especially if resources are in short supply due to increased usage, or

Chris

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP T +1 416-226-7033 | M +1 647-224-2033 | TF +1 866-716-8860 SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2 c.baker at sap.com | www.sap.com

https://sap.na.pgiconnect.com/I826572
Conference tel: 1-866-312-7353,,9648565377#

-----Original Message-----
From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Steve Shen
Sent: Wednesday, November 15, 2017 2:37 PM
To: iqug at iqug.org
Subject: Re: [IQUG] IQUG Digest, Vol 59, Issue 16

Recently my users ran into frequent issues on " Insufficient buffers for 'Sort'" because more concurrent connections were using the production IQ instance at the peak time.

There are two solutions:
1. To increase the value of "-iqtc": It's not easy to find extra RAM!
2. To reduce the value of " Sort_Pinnable_Cache_Percent" from the default 20% to a lower percentage: The SAP/Sybase technical document strongly discouraged us from changing the value on "Sort_Pinnable_Cache_Percent".

I am wondering whether any of you had ever lowered the default values on " Sort_Pinnable_Cache_Percent" and resolved the issues for good.

If yes, what percentage did you lower to?  Please let me know.  Thank you.

Regards

Steve Shen

t: (646) 827-2102
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
_______________________________________________
IQUG mailing list
IQUG at iqug.org
http://iqug.org/mailman/listinfo/iqug
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.


More information about the IQUG mailing list