Dear Jim,
> However, you will get more mileage from splitting up your query into > multiple selects I think. 1. The selects are simple, please see the above post > Also, is your application really going to perform 50 simultaneous selects > on this file? 2. During my test the SELECTs were NOT run simultaneously, just one-by-one as total of 50. Also we have an average of 750 active users during online, i assume 50 concurrent queries on ACCOUNT is very likely to happen What i'm concerned about is the resources: if every next SELECT allocates additional RAM (which is de-allocated only when sign off/logoff) then online is extremly slow and it'll take 1-2 hours to make it unoperable [ Xze ] On Wed, Dec 30, 2009 at 09:14, [Xze] <[email protected]> wrote: > > My appologize, in my first post forgot to mention that: > > jsh uatusr ~ -->echo $MALLOCTYPE > *buckets* > jsh uatusr ~ --> > > > Now, i changed it as follows: > > jsh uatusr ~ -->echo $MALLOCTYPE > *disclaim* > jsh uatusr ~ --> > > but again the performance-wise is unchanged > > BASE 5.0 Monitor (2.5) - AIX > [133C08:58:04 30 DEC 2009 > Port User Pid Files Perf Del Read Write Open MemF > MemU Cpu Prog > 1 uatusr 491750 37 (31) 20 4 363 6 122 0 5.25M > 0.22 1 I EX (S.COMMUNICATION,254) > 2 uatusr 418194 6 (5) 2 1 15 4 15 0 2.00M > 0.10 1 E /opt/jbase5/bin/jsh -s jsh - (jsh.b,111) > 4 cobusr 692460 6 (5) 1 1 507 1 16 0 2.06M > 0.12 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > * 5 uatusr 139486 6 (5) 0 1 1271 0 6 0 2.00M > 0.47 1 mw42 -m (mw42.b,764) > 12 uatusr 401724 7 (6) 197 1 3282 99 1433 0 > 466M31.15 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 14 uatusr 377144 33 (19) 200 1 3368 101 1460 0 428M > 0.00 3 SELECT FBNK.ACCOUNT WITH SYN.CODE EQ 2259 1425 1411 (select.b,8) > 19 uatusr 94614 7 (6) 201 1 3375 101 1462 0 > 361M24.98 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 20 uatusr 372772 7 (6) 201 1 3393 101 1462 0 > 339M22.84 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 21 uatusr 356532 7 (6) 197 1 3291 99 1433 0 > 350M24.28 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 22 uatusr 229726 7 (6) 197 1 3313 99 1433 0 > 418M28.16 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 27 uatusr 114854 7 (6) 197 1 3336 99 1433 0 > 249M17.89 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 30 uatusr 512366 7 (6) 193 1 3240 97 1404 0 > 327M22.56 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 35 uatusr 102632 7 (6) 193 1 3207 97 1404 0 > 448M31.09 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > 36 uatusr 635040 7 (6) 193 1 3227 97 1404 0 > 465M30.88 1 I /opt/jbase5/bin/jsh -s jsh - (CommandNext.b,103) > (he full mw42 -m output is attached) > > The SELECTs are simple > > SELECT FBNK.ACCOUNT WITH SYN.CODE EQ 2259 1425 1411 > SELECT FBNK.ACCOUNT WITH ACCOUNT.OFFICER EQ 1452 1526 1825 > SELECT FBNK.ACCOUNT WITH CATEGORY EQ 1360 1550 1559 > SELECT FBNK.ACCOUNT WITH CUSTOMER EQ 332984 335629 225632 45620 > SELECT FBNK.ACCOUNT WITH CURRENCY EQ GBP MDL > > As for the ACCOUNT file, it's the bank decision to keep it all there and > moreover the kernel does not 'devour' that much memory when FBNK.ACCOUNT > does not have INDEX > > Now, why is it happening ONLY when INDEXes are created? > > [ Xze ] > > > > On Wed, Dec 30, 2009 at 07:12, Jim Idle <[email protected]> wrote: > >> OK - though I am not sure that you need to use CONV_IB to do that. Yes I >> think I looked too far back in the past for the buckets thing. Isn't there a >> new algorithm that can be set though that allocates from the top address >> space and not the bottom? I know there is because I have recommended it in >> the past and it stops the memory fragmentation. Perhaps that is documented >> in the link I sent. However, the indexes should not be so inefficient memory >> wise I think - that probably needs looking in to. However storing everything >> in the one file is really something that should be looked at in the >> application anyway. >> >> Jim >> >> >> >> >> > -----Original Message----- >> > From: [email protected] [mailto:[email protected]] On Behalf >> > Of pat >> > Sent: Tuesday, December 29, 2009 8:22 PM >> > To: jBASE >> > Subject: Re: Performance issue on files with INDEX >> > >> > The 'CONV_IB(PartNo)' was previously recommended, to ensure the 'Part >> > Number' returned is an integer value, and to overcome potential >> > problems otherwise if the CALLing program and the Distributed >> > Algorithm specify different 'PRECISION's >> > >> > The 'MALLOCTYPE' environment variable is no longer relevant on Aix >> > systems running jBASE 5.018 and above ( as in this case ) >> > >> > Pat. >> > >> > On 29 Dec, 19:34, "Jim Idle" <[email protected]> wrote: >> > > Well, you are not doing yourself any favors here to be honest. First, >> > why are you calling CONV_IB on the part number? It is only going to get >> > converted to string anyway and as you just did ++ on it, it will >> > already be an integer value anyway. Don't do that, it is just extra >> > work that should not be necessary. >> > > >> > > Secondly, is there no way to reduce the number of accounts in this >> > file? Are they all live and cannot be archived in some way? This could >> > be possible of course. >> > > >> > > However, you will get more mileage from splitting up your query into >> > multiple selects I think. Also, is your application really going to >> > perform 50 simultaneous selects on this file? Are you sure that you >> > need to process this list in sorted order? It is much better to process >> > in the natural select order unless the algorithm relies on sorted >> > order. >> > > >> > > Next, though I feel there is a lot of work you can do on your code, >> > you need to change the memory allocation algorithms for AIX. Basically, >> > you don't have enough RAM to do all the indexed sorts (though as an >> > aside I am not sure why using the indexes should require so much memory >> > over not using them - you may need to ask TEMENOS about that), all at >> > once, so you are causing paging, running out of paging space and AIX >> > has a strategy to kill processes so that it does not crash. You can >> > search past posts: >> > > >> > > >> > http://jbase.markmail.org/search/?q=AIX%20malloc#query:AIX%20malloc%2.. >> > . >> > > >> > > For explanations, but basically in your login script add: >> > > >> > > export MALLOCTYPE=buckets >> > > >> > > You should also read this: >> > > >> > > >> > http://publib.boulder.ibm.com/infocenter/tivihelp/v2r1/index.jsp?topi.. >> > . >> > > >> > > Jim >> > > >> > > From: [email protected] [mailto:[email protected]] On >> > Behalf Of [Xze] >> > > Sent: Tuesday, December 29, 2009 7:15 AM >> > > To: [email protected] >> > > Subject: Performance issue on files with INDEX >> > > >> > > Dear all, >> > > >> > > We have noticed a huge performace impact on the system when we are >> > running SELECT on INDEX fields for FBNK.ACCOUNT file. >> > > >> > > After every SELECT the kernel was allocating additional space in RAM. >> > > When both RAM and paging space were 99% used, the kernel killed >> > almost all jbase sessions. >> > > >> > > LIST-INDEX FBNK.ACCOUNT >> > > >> > > INDEX definitions for file FBNK.ACCOUNT at 16:54:38 29 DEC 2009 >> > PAGE 1 >> > > INDEX NAME LOCALE NAME SORT KEYS. LOOKUP.... INDEX >> > DEFINITION................... >> > > ACCOUNT.OF en_US AR BY-AR 11 >> > > FICER >> > > CATEGORY en_US AR BY-AR 2 >> > > CURRENCY en_US AL BY-AL 8 >> > > CUSTOMER en_US AR BY-AR 1 >> > > SYN.CODE en_US AR BY-AR >> > ITYPE(\LOCAL.REF<1,7>\) >> > > 5 Records Listed >> > > >> > > VERIFY-DISTRIB FBNK.ACCOUNT >> > > Partitioning Algorithm is USER Subroutine 'AC.11' >> > > User subroutine OK. >> > > Part file 'FBNK.ACCOUNT.01', part number 1 - OK >> > > Part file 'FBNK.ACCOUNT.02', part number 2 - OK >> > > Part file 'FBNK.ACCOUNT.03', part number 3 - OK >> > > Part file 'FBNK.ACCOUNT.04', part number 4 - OK >> > > Part file 'FBNK.ACCOUNT.05', part number 5 - OK >> > > Part file 'FBNK.ACCOUNT.06', part number 6 - OK >> > > Part file 'FBNK.ACCOUNT.07', part number 7 - OK >> > > Part file 'FBNK.ACCOUNT.08', part number 8 - OK >> > > Part file 'FBNK.ACCOUNT.09', part number 9 - OK >> > > Part file 'FBNK.ACCOUNT.10', part number 10 - OK >> > > >> > > SUBROUTINE AC.11(DUMMY,INID,RESULT) >> > > DEFC JLibBCONV_IB(VAR) >> > > IF NUM(INID[1]) THEN >> > > PARTNO = INID[1] >> > > PARTNO++ >> > > END ELSE >> > > PARTNO = 11 >> > > END >> > > RESULT = JLibBCONV_IB(PARTNO) >> > > RETURN >> > > END >> > > >> > > In order to to replicate the issue we have opened 10 jbase sessions >> > and each performed 50 SELECTs on FBNK.ACCOUNT INDEX fileds. >> > > >> > > At the end, the mw42 -m output is as follows: >> > > >> > > Port User Pid Files Perf Del Read Write Open MemF >> > MemU Cpu Prog >> > > 1 uatusr 279002 6 (5) 1 1 11 2 11 0 >> > 9.39M 0.00 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. >> > > 2 uatusr 389366 7 (6) 1 1 2567 1 10 0 >> > 2.06M 0.93 2 I mw42 -m (mw42.b,232) >> > > 4 uatusr 225790 7 (6) 197 1 3300 99 1433 0 >> > 433M 18.83 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 5 uatusr 229884 7 (6) 197 1 3291 99 1433 0 >> > 466M 19.98 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 12 uatusr 340238 7 (6) 193 1 3214 97 1404 0 >> > 519M 22.05 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 14 uatusr 274452 7 (6) 201 1 3391 101 1462 0 >> > 315M 13.91 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 19 uatusr 413718 7 (6) 197 1 3321 99 1433 0 >> > 422M 16.51 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 20 uatusr 110934 7 (6) 193 1 3218 97 1404 0 >> > 506M 22.17 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 21 uatusr 352482 7 (6) 197 1 3302 99 1433 0 >> > 365M 15.73 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 22 uatusr 364998 33 (19) 192 1 3232 97 1402 0 >> > 411M 0.00 3 SELECT FBNK.ACCOUNT WITH CURRENCY EQ >> > > 27 uatusr 938076 7 (6) 197 1 3279 99 1433 0 >> > 553M 24.51 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 30 uatusr 139574 6 (5) 3 1 886 7 22 0 >> > 2.06M 0.32 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. >> > > 35 uatusr 635036 7 (6) 193 1 3252 97 1404 0 >> > 314M 14.75 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > * 36 uatusr 287038 6 (5) 0 1 902 0 6 0 >> > 9.19M 0.29 1 mw42 -m (mw42.b,764) >> > > 38 uatusr 373142 58 (50) 22 4 626 16 154 0 >> > 12.7M 0.18 1 I EX (S.COMMUNICATION,254) >> > > >> > > The same test on identical area, but WITHOUT INDEX: >> > > >> > > 14 uatpf 139592 6 (5) 1 1 14 3 13 0 >> > 9.39M 0.00 1 E /opt/jbase5/bin/jsh -s jsh - (jsh. >> > > 19 uatpf 315638 7 (6) 181 1 76.4M 91 1317 0 >> > 37.8M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 20 uatpf 209012 7 (6) 189 1 79.8M 95 1375 0 >> > 29.7M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 21 uatpf 176290 7 (6) 181 1 76.4M 91 1317 0 >> > 33.7M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 22 uatpf 262642 7 (6) 189 1 79.8M 95 1375 0 >> > 36.2M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 27 uatpf 405766 7 (6) 181 1 76.4M 91 1317 0 >> > 31.7M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 30 uatpf 139372 7 (6) 177 1 74.7M 89 1288 0 >> > 31.7M 13m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 35 uatpf 225538 7 (6) 185 1 78.1M 93 1346 0 >> > 38.2M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 36 uatpf 188786 7 (6) 181 1 76.4M 91 1317 0 >> > 37.6M 14m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 39 uatpf 450742 7 (6) 181 1 76.4M 91 1317 0 >> > 37.6M 13m 1 I /opt/jbase5/bin/jsh -s jsh - (Comm >> > > 40 uatpf 94562 33 (19) 180 1 75.3M 91 1315 0 >> > 37.6M 13m 3 SELECT FBNK.ACCOUNT WITH SYN.CODE EQ >> > > * 41 uatpf 151944 6 (5) 0 1 51209 0 6 0 >> > 9.39M 13.43 1 mw42 -m (mw42.b,764) >> > > >> > > ( The full mw42 -m output is attached ) >> > > >> > > In ther first case every next SELECT was taking additional memory >> > and was not releasing it untill the session is closed. >> > > >> > > We are using: >> > > OS - AIX 5.3.9.0 >> > > jB - Major 5.0 , Minor 20 , Patch 0364 (Change 85159) >> > > >> > > jdiag - jBASE diagnostic '$Revision: 1.15 $' >> > > System Information >> > > ================== >> > > System : AIX jbsec 3.5 00CED1BC4C00 >> > > OS Release : 5.3.9.0 >> > > UNIX User : uatusr (uid 213, euid 213) >> > > Tty name : /dev/pts/1 >> > > Time : Tue Dec 29 16:38:20 2009 >> > > Environment >> > > =========== >> > > JBCPORTNO : Not Set >> > > JBCRELEASEDIR : '/opt/jbase5' >> > > JBCGLOBALDIR : '/opt/jbase5' >> > > WARNING: JBCDATADIR is not set, Default '/opt/jbase5/jbase_data' >> > > WARNING: JBCDATADIR is subdirectory of JBCGLOBALDIR >> > > HOME : '/eoy/eoy/bnk.run' >> > > JEDIFILEPATH : '/eoy/eoy/bnk.run' >> > > JEDIFILENAME_MD : 'VOC' >> > > JEDIFILENAME_SYSTEM : '/opt/jbase5/src/SYSTEM' >> > > SYSTEM File is (DICT) : '/opt/jbase5/src/SYSTEM]D' >> > > RELEASE Information : Major 5.0 , Minor 20 , Patch 0364 >> > (Change 85159) >> > > Spooler dir (JBCSPOOLERDIR) : '/var/spool/jbase' >> > > JBCEMULATE : 'prime' >> > > WARNING: Cannot access Executable path >> > '/eoy/eoy/bnk.run/globuspatchbin', error 2 >> > > Object path (JBCOBJECTLIST) : >> > '/eoy/eoy/bnk.run/globuspatchlib:/eoy/eoy/bnk.run/lib:/eoy/eoy/bnk.run/ >> > globuslib:/eoy/eoy/bnk.run/fixlib' >> > > WARNING: Cannot access Object path '/eoy/eoy/bnk.run/globuspatchlib', >> > error 2 >> > > WARNING: Cannot access Object path '/eoy/eoy/bnk.run/fixlib', error 2 >> > > jBASE Compiler Run-time : '/opt/jbase5/config/system.properties' >> > > Program dir (JBCDEV_BIN) : '/eoy/eoy/bnk.run/bin' >> > > Subroutine dir (JBCDEV_LIB) : '/eoy/eoy/bnk.run/lib' >> > > Max open files : 65534 >> > > >> > > Can anybody please explain what is triggering this and how to fix the >> > issue? >> > > >> > > -- >> > > Please read the posting guidelines >> > at:http://groups.google.com/group/jBASE/web/Posting%20Guidelines >> > > >> > > IMPORTANT: Type T24: at the start of the subject line for questions >> > specific to Globus/T24 >> > > >> > > To post, send email to [email protected] >> > > To unsubscribe, send email to [email protected] >> > > For more options, visit this group >> > athttp://groups.google.com/group/jBASE?hl=en >> > >> > -- >> > Please read the posting guidelines at: >> > http://groups.google.com/group/jBASE/web/Posting%20Guidelines >> > >> > IMPORTANT: Type T24: at the start of the subject line for questions >> > specific to Globus/T24 >> > >> > To post, send email to [email protected] >> > To unsubscribe, send email to [email protected] >> > For more options, visit this group at >> > http://groups.google.com/group/jBASE?hl=en >> >> >> >> -- >> Please read the posting guidelines at: >> http://groups.google.com/group/jBASE/web/Posting%20Guidelines >> >> IMPORTANT: Type T24: at the start of the subject line for questions >> specific to Globus/T24 >> >> To post, send email to [email protected] >> To unsubscribe, send email to [email protected] >> For more options, visit this group at >> http://groups.google.com/group/jBASE?hl=en >> > > -- Please read the posting guidelines at: http://groups.google.com/group/jBASE/web/Posting%20Guidelines IMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24 To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en
