[IQUG] IQUG Digest, Vol 59, Issue 34

Mark Mumy markdmumy at gmail.com
Thu Nov 16 09:35:06 MST 2017


No worries.  I’ve bene through this enough, that I hate to see you suffer another week.  The option has a higher likelihood of easing the pain.

Mark

> On Nov 16, 2017, at 10:25, Steve Shen <sshen at sscinc.com> wrote:
> 
> Hi Mark,
> 
> I agree with your good points.  Thank you very much.
> 
> Regards,
> 
> Steve
> 
> -----Original Message-----
> From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of iqug-request at iqug.org
> Sent: Thursday, November 16, 2017 11:03 AM
> To: iqug at iqug.org
> Subject: IQUG Digest, Vol 59, Issue 34
> 
> Send IQUG mailing list submissions to
>        iqug at iqug.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
>        http://iqug.org/mailman/listinfo/iqug
> or, via email, send a message with subject or body 'help' to
>        iqug-request at iqug.org
> 
> You can reach the person managing the list at
>        iqug-owner at iqug.org
> 
> When replying, please edit your Subject line so it is more specific than "Re: Contents of IQUG digest..."
> 
> 
> Today's Topics:
> 
>   1. Re: IQUG Digest, Vol 59, Issue 16 (Mark Mumy)
> 
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Thu, 16 Nov 2017 10:03:35 -0600
> From: Mark Mumy <markdmumy at gmail.com>
> To: Steve Shen <sshen at sscinc.com>
> Cc: "iqug at iqug.org" <iqug at iqug.org>
> Subject: Re: [IQUG] IQUG Digest, Vol 59, Issue 16
> Message-ID: <538412AB-A9D7-438A-91D5-A9E97C26C9FC at gmail.com>
> Content-Type: text/plain; charset="utf-8"
> 
> I would change your steps.  I would reduce sort_pinnable_cache_percent first.  This shouldn?t negatively affect queries given your RAM size.  What it does is makes sure that concurrent users don?t consume so much.  Remember that the statement below was written many, many years ago when systems didn?t have hundreds of GB of RAM or more.
> 
> Leaving this at default means that the first HG will automatically take 24GB of temp cache (20% of your 120gb temp cache)!  That?s massive and wasteful for systems with large memory.  If you add more temp cache, you just increase the amount of memory that we will grab from 24gb to 36gb for the first HG.  You haven?t solved much other than given yourself a little bit extra room.
> 
> Mark
> 
>> On Nov 16, 2017, at 09:49, Steve Shen <sshen at sscinc.com> wrote:
>> 
>> Hi Mark,
>> 
>> I am still at IQ version 15.4.  So I do not need to concern about LMA and RLV.
>> 
>> The technical document below seemed incorrect based on your expertise:
>> 
>> ?
>> This option is primarily for use by Sybase Technical Support. If you change the value of SORT_PINNABLE_CACHE_PERCENT, do so with extreme caution.
>> 
>> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocent
>> er.dc00801.1510/html/iqrefso/CIHGJBCI.htm
>> <http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocen
>> ter.dc00801.1510/html/iqrefso/CIHGJBCI.htm>
>> ?
>> 
>> Currently I allocated 180 GB to the main cache and 120 GB to the temp cache.  The top command showed that there were around 95 GB or 98 GB of free memory.
>> 
>> I am planning to increase the temp cache this weekend.  If it does not alleviate the issue, then I will lower SORT_PINNABLE_CACHE_PERCENT <> next weekend.
>> 
>> If I increase the temp cache by additional 80 GB, it will leave 15 GB or 18 GB of free memory.  Will this cause the issues in O/S?
>> 
>> Notes: I cannot test this in the UAT IQ host because there are only 384 GB of RAM.
>> 
>> Thank you.
>> 
>> Regards,
>> 
>> Steve Shen
>> 
>> From: Mark Mumy [mailto:markdmumy at gmail.com]
>> Sent: Wednesday, November 15, 2017 7:20 PM
>> To: Baker, Chris
>> Cc: Steve Shen; iqug at iqug.org
>> Subject: Re: [IQUG] IQUG Digest, Vol 59, Issue 16
>> 
>> Adding HG indexes will generally make this issue worse, not better.
>> 
>> Lower sort pinnacle cache percent from 20 down to 2-5.  This is recommended when we have these issues.
>> 
>> Your concurrent user shouldn?t affect this, though it does impact IQ in other ways.  -gm is the number of users that can connect.  The concurrency is actually controlled by -iqgovern (2*number of cores + 10, I believe).  Make sure that you have set -iqnumbercpus to match the physical core count (40), not the logical core count (80).  This is a must for hyperthreaded systems.
>> 
>> Which IQ version?  With IQ 16, you would want 150gb RAM (30% of 512gb) allocated to each of the 3 main caches (main, temp, LMA).  120gb is well below our recommended minimum of 30% of total RAM.
>> 
>> Second, you are at the bottom end of the RAM per core range of 12-16gb per core for IQ 16.
>> 
>> Here are some of my older notes on the sort pinable cache percent:
>> 
>> Could not execute statement.
>>  Insufficient buffers for 'Sort'.
>>  -- (s_csort.cxx 808)
>>  SQLCODE=-1009134, ODBC 3 State="HY000"
>> 
>> Two things can contribute to this.  First temp cache may be too small.  Doubtful since it runs successful the second time.  Second, the setting for sort_pinnable_cache_percent (default of 20%).  The option Sort_pinnable_cache_percent specifies how much temp cache ONE sort can get; it is NOT that how much ALL sorts can get.
>> 
>> Here is a simplified mathematical analysis of temp cache usage:
>>  . the 1st HG requested 20% of available temp cache, 80% remaining
>>  . the 2nd HG gets .8*.2 = 16%, left 64% of originally available temp cache.
>>  . ...
>>  . after 20th HG, 0.8^20 = 0.0115 = 1% temp cache left
>> 
>> And so on for al HG indexes in the table.
>> 
>> If there are other loads (insert select, select into, load table) running at the same time, the amount of temp cache that is available is already reduced and may lead to the insufficient buffers error.  IQ does contain code to adjust/handle situations like this, but there are cases where that is not aggressive enough.
>> 
>> You may find that lowering the option gives you enough free temp cache to run the loads without issue.  Alternatively, you could remove HG indexes (how many are on the tables being loaded?), but that's a bit drastic at this time.
>> 
>> Mark
>> 
>> 
>> Mark Mumy
>> Director, Enterprise Architecture, Global HANA CoE |  SAP M +1
>> 347-820-2136 | E mark.mumy at sap.com <mailto:mark.mumy at sap.com>
>> 
>> On Nov 15, 2017, at 16:10, Baker, Chris <c.baker at sap.com <mailto:c.baker at sap.com>> wrote:
>> 
>> If you are running the same queries in UAT as in PROD, then the index advisor will still give advice for missing indexes, regardless of load.
>> 
>> 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 <mailto:c.baker at sap.com> | www.sap.com
>> <http://www.sap.com/>
>> 
>> https://sap.na.pgiconnect.com/I826572
>> <https://sap.na.pgiconnect.com/I826572>
>> Conference tel: 1-866-312-7353,,9648565377#
>> 
>> -----Original Message-----
>> From: Steve Shen [mailto:sshen at sscinc.com <mailto:sshen at sscinc.com>]
>> Sent: Wednesday, November 15, 2017 4:31 PM
>> To: Baker, Chris <c.baker at sap.com <mailto:c.baker at sap.com>>;
>> iqug at iqug.org <mailto:iqug at iqug.org>
>> Subject: RE: IQUG Digest, Vol 59, Issue 16
>> 
>> 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 <mailto:c.baker at sap.com>]
>> Sent: Wednesday, November 15, 2017 4:01 PM
>> To: Steve Shen; iqug at iqug.org <mailto: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 <mailto:c.baker at sap.com> | www.sap.com
>> <http://www.sap.com/>
>> 
>> https://sap.na.pgiconnect.com/I826572
>> <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>
>> [mailto: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 <mailto: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 <mailto:IQUG at iqug.org>
>> http://iqug.org/mailman/listinfo/iqug
>> <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.
>> _______________________________________________
>> IQUG mailing list
>> IQUG at iqug.org <mailto:IQUG at iqug.org>
>> http://iqug.org/mailman/listinfo/iqug <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.
> 
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://iqug.org/pipermail/iqug/attachments/20171116/19e2ba76/attachment.html>
> 
> ------------------------------
> 
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org
> http://iqug.org/mailman/listinfo/iqug
> 
> End of IQUG Digest, Vol 59, Issue 34
> ************************************
> 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



More information about the IQUG mailing list