Just to be clear though, I'm not setting the buffer to 4gb though.....it isn't even close. FET_BUF_SIZE is bytes (according to every document I've seen), not KB....so my value of 4,000,000 is 4mb, not 4gb.
The crazy thing is most of the 4gls work fine with FET_BUF_SIZE=200000000 (200mb), still an order of magnitude less than max. The ones the croak all have big 4gl reports with big order bys.
Original Message:
Sent: Wed July 05, 2023 10:48 AM
From: Art Kagel
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
Jared:
What Tereso is trying to say is that FET_BUF_SIZE is limited to values less than 2GB, so when you set it to 4GB it was seen as negative and probably caused the slow processing issues that you saw. There is another underdocumented environment variable, BIG_FET_BUF_SIZE, that can be set to values less than 4TB. However, I'm not sure whether the 4GL libraries that you have will recognize it. That depends on what version of ESQL/C they were compiled with.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Wed July 05, 2023 10:27 AM
From: Jared Heath
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
I don't understand your usages of the word tuple here, so I'll have to go do some reading. Just to be clear though, the document you linked heavily references Java, and this is Informix 4gl, so there is no "tuple buffer" to modify that maybe Java has?
The size isn't close to the max size.
There are 6 temp dbspaces each 2gb, so 12gb of temp space. From what I can see, it is barely using any of it:
Description Offset(p) Size(p) Partnum Ext Num
------------------------------------------------------------- -------- -------- ---------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
tmpdbs1:'informix'.TBLSpace 3 50 0x01700001 1
tba:'sncuser'.t_susbdet 53 384 0x01700002 1
So 53 pages in a single temp space.
If you are talking about OS swap memory....that is intentionally turned off to keep the OS from doing it at all. This system has 300gb of memory on it....it never swaps.
------------------------------
Jared Heath
Original Message:
Sent: Fri June 30, 2023 06:19 PM
From: Tereso Garcia
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
How many tuples are expected to insert and result returned? The max size for the FET_BUF_SIZE is 2Gb or 2 147 483 648. How many temp areas do you have? Thos temp areas are used on a round robin to allocate as much sorting spaced required for those temp tables. You can monitor how the temp areas are utilize during the query execution and how much it is allocating. Depending on the temp areas size, either you allowing to use more tuples throughput but not have enough temp space for temp tables and sorting ?
You may also check your swap memory is limited.
Manage the fetch buffer size - IBM Documentation
------------------------------
Tereso Garcia
Original Message:
Sent: Wed June 28, 2023 09:34 PM
From: Jared Heath
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
Yeah, something is definitely up:
[20:26 06/28/23 star] : export FET_BUF_SIZE=10000000
[20:26 06/28/23 star] : pasp9911.4ge
double free or corruption (!prev)
Aborted (core dumped)
------------------------------
Jared Heath
Original Message:
Sent: Wed June 28, 2023 08:57 PM
From: Jared Heath
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
Increasing it to FET_BUF_SIZE=20480000 gets past the weird lockup, but the program crashes at the last Finish Report line as if the database went away (it didn't)
Tested this 5 times, every time same result.
[19:01 06/28/23 star] : pasp9911.4ge
Program stopped at "pasp9911.4gl", line number 536.
4GL run-time error number -25582.
Network connection is broken.
Chopping a zero off this value works....
[19:45 06/28/23 star] : export FET_BUF_SIZE=2000000
[19:45 06/28/23 star] : pasp9911.4ge
[19:52 06/28/23 star] :
Changing to 3 works
[19:52 06/28/23 star] : export FET_BUF_SIZE=3000000
[19:58 06/28/23 star] : pasp9911.4ge
Changing to 4 is hung
[20:05 06/28/23 star] : export FET_BUF_SIZE=4000000
[20:05 06/28/23 star] : pasp9911.4ge
Is this an undocumented 4gl limitation, or a bug?
------------------------------
Jared Heath
Original Message:
Sent: Wed June 28, 2023 07:10 PM
From: Jared Heath
Subject: Large Fetch Bufer causes 4gl program to hang on an insert into a REPORT temp table
Anyone ever seen this before I ask IBM?
The program in question works fine with FET_BUF_SIZE=2048000 but when I increase it to 4096000 it just hangs...it has been sitting at the same statement for 35 minutes. Typical runtime is 9 minutes. It ran for 3 hours earlier today before I started isolating it. Documentation suggests I should be able to go up to 2147483648 (1000x bigger than what I have it set to....)
I'm about to restart it with even a much bigger number to see if there is something weird in this range or if it is a straight wall 4gl is hitting.
IBM Informix Dynamic Server Version 14.10.FC10 -- On-Line -- Up 00:46:15 -- 63447704 Kbytes
2023-06-28 17:55:45
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
269 sncuser - - 22303 uatappx21.statenational.com 1 176128 168504 off
Program :
/snc/hos/bin/pasp9911.4ge
tid name rstcb flags curstk status
372 sqlexec 82c61800 Y--P--- 4272 cond wait netnorm -
Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
269 V a4c68040 172032 6880 252 7
269*O0 V a4c47040 4096 744 1 1
name free used name free used
overhead 0 6704 scb 0 144
opentable 0 11480 filetable 0 2336
ru 0 632 misc 0 312
log 0 16536 temprec 0 22688
keys 0 656 ralloc 0 37712
gentcb 0 1592 ostcb 0 2992
sqscb 0 29176 sql 0 12416
hashfiletab 0 552 osenv 0 3024
sqtcb 0 10552 fragman 0 7448
sapi 0 664 rsam_seqscan 0 888
sqscb info
scb sqscb optofc pdqpriority optcompind directives
82d9e1c0 ad262028 1 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
269 INSERT tba CR Not Wait 0 0 9.41 Off
Current statement name : s_rep2
Current Role : sncfull
Current SQL statement (1937965) :
insert into t_rep2 values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Host variables :
address type flags value
-----------------------------------------
0x0000000098d16438 CHAR 0x002
0x0000000098d164c8 CHAR 0x002
0x0000000098d16558 CHAR 0x002
0x0000000098d165e8 CHAR 0x002
0x0000000098d16678 CHAR 0x002
0x0000000098d16708 CHAR 0x002
0x0000000098d16798 CHAR 0x002
0x0000000098d16828 CHAR 0x002
0x0000000098d168b8 CHAR 0x002
0x0000000098d16948 CHAR 0x002
0x0000000098d169d8 CHAR 0x002
0x0000000098d16a68 CHAR 0x002
0x0000000098d16af8 CHAR 0x002
0x0000000098d16b88 CHAR 0x002
0x0000000098d16c18 CHAR 0x002
Last parsed SQL statement :
select * from pasupr
User-created Temp tables :
partnum tabname rowsize
1800002 t_rep2 92
1700002 t_rep1 92
145448 byte(s) of memory is allocated from the sscpool
------------------------------
Jared Heath
------------------------------