[IQUG] [CAUTION] Re: IQ 16 loads to very wide tables

Antonian, Vahe vahe.antonian.2 at credit-suisse.com
Thu Jan 3 06:30:22 MST 2019


Problem’s back again. Even with the lower number of cpus (from 15 to 10). After a few more tries, we got the same error again with the load to the wide table. Will follow up with the SAP engineer for next steps…

Could not execute statement.
temp Bufman: All buffer cache pages are in use, ask your DBA to increase
the size of the buffer cache. Current buffer cache is 80000MB.


Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Mark Mumy <markdmumy at gmail.com>
Sent: Friday, December 28, 2018 9:36 AM
To: Antonian, Vahe (MLEA 42)
Cc: Mumy, Mark; Baker, Chris; iqug at iqug.org
Subject: Re: [IQUG] [CAUTION] Re: IQ 16 loads to very wide tables

So. But you can set a limit on threads and parallelism.

At this point, what’s the issue?  Sounds like the load works but just with lower cores settings.

You should expect a client load to be significantly slower than a server side load.  I don’t see an issue with the timings.  What I do see is an undersized system for the workload.  That’s quite a wide table to load.
Mark

========================

Sent from my mobile device

On Dec 28, 2018, at 08:25, Antonian, Vahe <vahe.antonian.2 at credit-suisse.com<mailto:vahe.antonian.2 at credit-suisse.com>> wrote:
Raises the obvious question of whether there’s a syntax to cap the number of cpu’s used for the load without lowering the instance-wide config for iqnumbercpus?

Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Antonian, Vahe (MLEA 42)
Sent: Friday, December 28, 2018 9:11 AM
To: 'Mark Mumy'
Cc: Mumy, Mark; Baker, Chris; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: RE: [IQUG] [CAUTION] Re: IQ 16 loads to very wide tables

-gn made no difference although since we can’t add memory, we tried dropping number of cpus and we seem to have eliminated the error by lowering –iqnumbercpus from 15 to 10. Will be doing some more testing to see if that might be an interim workaround for us while we figure out what’s really going on?!

Here’s what the host looks like. Would have expected 16 to be safe?

$ /bin/grep processor /proc/cpuinfo| sort -u | wc -l
32

Client side load (app host) takes 75 seconds. Server side load (from the db host) takes 28 sec. Both are loading 570K rows

The data file and the load command syntax both have 900 columns. The table has 7000+ columns


Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Sent: Thursday, December 27, 2018 3:41 PM
To: Antonian, Vahe (MLEA 42)
Cc: Mumy, Mark; Baker, Chris; iqug at iqug.org<mailto:iqug at iqug.org>
Subject: Re: [IQUG] [CAUTION] Re: IQ 16 loads to very wide tables

You don’t have -gn set.  That should be set to 1.5x the value of -gm (50*1.5=75).

You say that the load is only loading 900 columns or so.  Does that mean that the file has just 900 columns and that the LOAD statement has 900?  Or does it have more than that?

You are light on memory, too, I think.  Though that’s likely not the issue.  IQ 16 sizing shows that you want 12-16GB RAM per core.  With 15 cores, that’s 180-240GB RAM.

What is the timing to do a load if it is server side (USING FILE instead of USING CLIENT FILE)?

Mark

On Dec 27, 2018, at 1:18 PM, Antonian, Vahe <vahe.antonian.2 at credit-suisse.com<mailto:vahe.antonian.2 at credit-suisse.com>> wrote:

64K packet size made no difference. We’re still getting the “temp Bufman: All buffer cache pages are in use” error

sp_iqwho, connection and context info during the load show nothing. The temp columns (TempTableSpaceKB and TempWorkSpaceKB) are both zero for the load table connection.  I assume those are the columns you were interested in?

Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Antonian, Vahe (MLEA 42)
Sent: Wednesday, December 26, 2018 11:48 AM
To: 'Mumy, Mark'; Baker, Chris; 'iqug at iqug.org<mailto:iqug at iqug.org>'
Subject: RE: [CAUTION] Re: [IQUG] IQ 16 loads to very wide tables

Hi Mark,

Thanks for looking. We too suspect something about how the data is staged for this wide table (7000+ columns) coming from the client side load is the culprit and yes, you are right, the data file is not at all representative of the temp cache overhead b/c only a very small subset of the columns are being populated by this load (< 900 columns I think). The reset are handled by post-load DML processing (updates, joins, etc…). Here are some of the answers below. Will try to reproduce the problem and gather process/connection info when folks are back from holiday vacations.

We recently upgraded to PL21 as instructed by the SAP engineer to address some TEMP cache bugs (P1 Case 555268/2018) but have been told to downgrade to PL20 so will be scheduling that soon also.

Thanks



$ start_iq -v2

16.0.0.2909
SAP IQ/16.0.110.2909/11875/P/sp11.21/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-10-15 02:41:48

CFG file

# UNYDS31900_IQ.cfg
# ----------------------------------------------------------------------
# This file contains the ASIQ startup parameters.  All servers
# started will default to these parameters, unless overriden by contents
# of parameter list.
# ----------------------------------------------------------------------
# Must be in the format: One parameter per line
# ----------------------------------------------------------------------
# Built against Host=UNYDS31900_IQ.am.hedani.net<http://unyds31900_iq.am.hedani.net/>
# Built against Port=2340
#

-n UNYDS31900_IQ
-x tcpip{MyIP=169.36.147.19;port=2340}

-c  6144m
#-cl 512m
#-ch 1024m
-gc 60
-gd dba
-gk dba
-gl all
-gm 50
-gp 4096
#-gp 8192
-ti 4400

-iqmc 30000
-iqtc 80000
-iqlm 30000

# uncomment to log all SQL to a file
#-zo /cs/sybase/UNYDS31900_IQ/log/SQLTRAP.log
#-zr ALL
#-zn 10   # DHP-3644
#-zs 100m # DHP-3644

# limit iqmsg file to 100meg, and limits to keep 25 # DHP-3644
-iqmsgsz 100
-iqmsgnum 15

# limit srvlog size, then archive to YYMMDDnn.slg  # DHP-3644
-os 100m

# ----------------------------------------------------------------------
# CS Section
# ----------------------------------------------------------------------

#-- Limit the number of cpus to ignore hyperthreads
-iqnumbercpus 15

#-- Turn off syslog messages
-s none
Sp_iqstatus

Name,Value
'SAP IQ (TM)','Copyright (c) 1992-2016 by SAP AG or an SAP affiliate company. All rights reserved.'
' Version:','16.0.110.2909/11875/P/sp11.21/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-10-15 02:41:48'
' Time Now:','2018-12-26 11:37:23.222'
' Build Time:','2018-10-15 02:41:48'
' File Format:','23 on 03/18/1999 '
' Server mode:','IQ Server'
' Catalog Format:','2'
' Stored Procedure Revision:','1'
' Page Size:','524288/32768blksz/16bpp'
' Number of Main DB Files:','4'
' Main Store Out Of Space:','N'
' Number of Cache Dbspace Files:','0'
' Number of Shared Temp DB Files:','0'
' Shared Temp Store Out Of Space:','N'
' Number of Local Temp DB Files:','6'
' Local Temp Store Out Of Space:','N'
' DB Blocks: 1-1176735','IQ_SYSTEM_MAIN'
' DB Blocks: 4184832-12439453','IQ_MAIN'
' DB Blocks: 12554496-20809117','IQ_MAIN_01'
' DB Blocks: 20924160-29180381','IQ_MAIN_02'
' Local Temp Blocks: 1-2356759','IQ_SYSTEM_TEMP'
' Local Temp Blocks: 4184832-6541590','IQ_SYSTEM_TEMP_01'
' Local Temp Blocks: 8369664-10726422','IQ_SYSTEM_TEMP_02'
' Local Temp Blocks: 12554496-14911254','IQ_SYSTEM_TEMP_03'
' Local Temp Blocks: 16739328-19096086','IQ_SYSTEM_TEMP_04'
' Local Temp Blocks: 20924160-23280918','IQ_SYSTEM_TEMP_05'
' Create Time:','2012-04-01 13:08:02.111'
' Update Time:','2018-12-26 06:47:38.523'
' Main IQ Buffers:','59940, 30000Mb'
' Temporary IQ Buffers:','159841, 80000Mb'
' Main IQ Blocks Used:','12880626 of 25930434, 49%=393Gb, Max Block#: 25379851'
' Cache Dbspace IQ Blocks Used:','0 of 0, 0%=0Mb, Max Block#: 0'
' Shared Temporary IQ Blocks Used:','0 of 0, 0%=0Mb, Max Block#: 0'
' Local Temporary IQ Blocks Used:','326 of 14116987, 0%=10Mb, Max Block#: 20924208'
' Main Reserved Blocks Available:','11767 of 11767, 100%=367Mb'
' Shared Temporary Reserved Blocks Available:','0 of 0, 0%=0Mb'
' Local Temporary Reserved Blocks Available:','23567 of 23567, 100%=736Mb'
' IQ Dynamic Memory:','Current: 111413mb, Max: 141173mb'
' Main IQ Buffers:','Used: 59765, Locked: 0'
' Temporary IQ Buffers:','Used: 20, Locked: 0'
' Main IQ I/O:','I: L2664655/P61105 O: C289534/D293424/P236579 D:250790 C:39.0'
' Temporary IQ I/O:','I: L13455963/P104932 O: C689650/D802261/P112619 D:689630 C:53.9'
' Other Versions:','0 = 0Mb'
' Active Txn Versions:','0 = C:0Mb/D:0Mb'
' Last Full Backup ID:','93114427'
' Last Full Backup Time:','2018-10-04 07:00:38'
' Last Backup ID:','93114427'
' Last Backup Type:','FULL'
' Last Backup Time:','2018-10-04 07:00:38'
' DB Updated:','1'
' Blocks in next ISF Backup:','11572677 Blocks: =353Gb'
' Blocks in next ISI Backup:','11572677 Blocks: =353Gb'
' IQ large memory space:','30000Mb'
' IQ large memory flexible percentage:','50'
' IQ large memory flexible used:','0Mb'
' IQ large memory inflexible percentage:','90'
' IQ large memory inflexible used:','26544Mb'
' IQ large memory anti-starvation percentage:','50'
' DB File Encryption Status:','OFF'
' RLV Status:','RW'
' RLV memory limit (mb):','2048'
' RLV memory used (bytes):','0'
' RLV Log Buffers Allocated:','0'
' RLV Log Buffers Globally Free:','0'
' RLV Log Buffers Privately Free:','0'
' RLV Log Buffers In Use:','0'



Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Mumy, Mark <mark.mumy at sap.com<mailto:mark.mumy at sap.com>>
Sent: Friday, December 21, 2018 1:03 PM
To: Baker, Chris; Antonian, Vahe (MLEA 42); 'iqug at iqug.org<mailto:iqug at iqug.org>'
Subject: Re: [CAUTION] Re: [IQUG] IQ 16 loads to very wide tables

Keep in mind that when you do an IQ client side load, the data gets pulled from the client by IQ.  That’s all internal, not much you can do about that.  The data has to be stored somewhere on the way it.  With IQ 16, I had thought that we used the LMA area, but perhaps not.  Temp does make more sense since the cache is backed by disk while LMA is not.

When you get to the point of failure, out of buffers, are you also getting messages about temp store being full?  Can you run an sp_iqwho, sp_iqconnect, etc to see if that connection is also consuming temp store and how much?

Keep in mind, too, that each column will have a separate storage structure while loading data.  Just because the file is 5GB in size, doesn’t mean that’s all we need in memory.  If you have 7000 pages and as 128k page size, then we need 875MB just to start the load.  Since this is inbound data, it won’t be compressed but rather will be the width of the datatype.  If you’ve got a varchar (128), for instance, then you can have no more than 1000 rows per pages (128k page / 128 byte defined width).

Can you share the following:
•  Output from “start_iq -v2”
•  IQ cfg file
•  IQ page and block size (from sp_iqstatus)
•  IQ temp size

Mark

Mark Mumy
Strategic Technology Incubation Group
SAP Global Center of Excellence |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>" <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> on behalf of Chris Baker <c.baker at sap.com<mailto:c.baker at sap.com>>
Date: Thursday, December 20, 2018 at 3:06 PM
To: "Antonian, Vahe" <vahe.antonian.2 at credit-suisse.com<mailto:vahe.antonian.2 at credit-suisse.com>>, "iqug at iqug.org<mailto:iqug at iqug.org>" <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: [CAUTION] Re: [IQUG] IQ 16 loads to very wide tables

The packet size is the default =- 7300 bytes.  You can take it up to 64KB by starting the server with the -p parameter (e.g. -p 65300).

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

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

From: Antonian, Vahe <vahe.antonian.2 at credit-suisse.com<mailto:vahe.antonian.2 at credit-suisse.com>>
Sent: Thursday, December 20, 2018 3:54 PM
To: Baker, Chris <c.baker at sap.com<mailto:c.baker at sap.com>>; 'iqug at iqug.org<mailto:iqug at iqug.org>' <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: RE: IQ 16 loads to very wide tables


  *   Around 570K records in the data file (5GB is the file size)
  *   No pipes are used to my knowledge anywhere
  *   SQL files called with dbisql command and connection terminates after 570K rows loaded to table
  *   100K rows were successfully loaded from client side with NO Temp Buffer errors.
  *   P1 Case 555268/2018  registered with SAP
  *   Network packet size

     *   $ dbisql -c "uid=DBA;pwd=xxxxxxxx;dbn=UNYDS31900_IQ;eng=UNYDS31900_IQ" -nogui
     *
     *   (DBA)> select connection_property ('PacketSize')
     *   connection_property('PacketSize')
     *   ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     *   7300


Vahe Antonian
Database IB Ops Service
+1 212 538 1642 (*106 1642)
From: Baker, Chris <c.baker at sap.com<mailto:c.baker at sap.com>>
Sent: Thursday, December 20, 2018 12:06 PM
To: Antonian, Vahe (MLEA 42); 'iqug at iqug.org<mailto:iqug at iqug.org>'
Subject: RE: IQ 16 loads to very wide tables

How many rows are in the source data?  How large is the source file?

The data needs to be sent from client to server, so probably has to be materialized somewhere on the server first.  I am not sure about the implementation (i.e. is a pipe used? /tmp?) but somewhere you will probably run out of space (yes, temp cache might be used here) if it is not cleaned up.

Are you also issuing an explicit ‘commit’ after a client-side load?  Perhaps the data needs to be retained in case of a rollback, so that is why you might see temp cache usage growing until a ‘commit’ is issued on the connection.

What happens if you cut down the number of rows in a client-side load.  Does the failure still occur?

You probably need to open a support incident on this.

Although it is probably not relevant, what is the server packet size?  A larger packet size might simple help with getting the data to the server faster.

Chris

Chris Baker | Database Engineering Evangelist | CERT | PI HANA Platform Data Management | SAP
T +1 416-226-7033<tel:+1%20416-226-7033> | M +1 647-224-2033<tel:+1%20647-224-2033> | TF +1 866-716-8860<tel:+1%20866-716-8860>
SAP Canada Inc. 445 Wes Graham Way, Waterloo, N2L 6R2<x-apple-data-detectors://17/1>
c.baker at sap.com<mailto:c.baker at sap.com> | www.sap.com<http://www.sap.com/>

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

From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> On Behalf Of Antonian, Vahe
Sent: Thursday, December 20, 2018 11:48 AM
To: 'iqug at iqug.org<mailto:iqug at iqug.org>' <iqug at iqug.org<mailto:iqug at iqug.org>>
Subject: [IQUG] IQ 16 loads to very wide tables


Struggling with IQ 16 client side loads to a very wide table with 7000+ columns. After enough executions, it eventually starts to fails with temp cache errors. Interestingly, the problem never happens with server side loads and it never happens after a bounce of the instance.

temp Bufman: All buffer cache pages are in use

We think it’s a problem flushing out pages from the cache b/c it never happens on the first few loads after a bounce but eventually starts happening after enough loads and activity has run. Temp cache is hugely oversized and SP11.21 was recently applied as per advice from the SAP engineer to address a few known bugs.

Just curious if anyone else has any tips or tricks for IQ16 loads to very wide tables that might help us isolate what’s happening?  The process worked fine on IQ 15

Thanks,

Vahe Antonian
CREDIT SUISSE SERVICES (USA) LLC
Credit Suisse Services(USA)LLC | Database IB Ops Service, MLEA 42
Eleven Madison Avenue | New York NY 10010-3629 | Americas
Phone +1 212 538 1642
vahe.antonian.2 at credit-suisse.com<mailto:vahe.antonian.2 at credit-suisse.com> | www.credit-suisse.com<http://www.credit-suisse.com/>

==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================



==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug


==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================


=============================================================================== 
Please access the attached hyperlink for an important electronic communications disclaimer: 
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html 
=============================================================================== 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20190103/0d52d711/attachment-0001.html>


More information about the IQUG mailing list