Indexes are of enormous benefit in queries but it seems there are still some issues to either fix or to define operating parameters for. I wouldn't say don't use them but I think that you have to do work on tuning the environment and the queries to operate well with them and that some guidelines for this should be coming from TEMENOS. Whether is a memory leak in this particular case is difficult to determine from the information available though. Please be careful about making broad statements such as "don't use them" as such statements are virtually (perhaps literally) always too broad reaching :-)
Jim > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf > Of VK > Sent: Wednesday, December 30, 2009 1:50 AM > To: jBASE > Subject: Re: Performance issue on files with INDEX > > Hi, > > revise your SELECTs to find out the way to perform the task without > index if possible. > > For example, you don't need to index on CUSTOMER field since there is > a "concat" file in T24: > > LIST FBNK.CUSTOMER.ACCOUNT > > @ID....... @ID....... CUSTOMER.CODE ACCOUNT.NUMBER.. > > 100197 100197 100197 35025 > 100362 100362 100362 15156 > 555555 555555 555555 28298 > 100378 100378 100378 15172 > > etc so you can find all accounts belonging to particular customer > quite easily. > > For other fields there might be concats as well (contact Temenos > helpdesk for this). Otherwise you can create your owns using > EB.ALTERNATE.KEY application. > > I wouldn't recommend jbase indexes... Pat wouldn't like this but... > don't use them. > This is my IMHO and please don't ask me to go deeper into that > subject :( > > About archiving - in T24 accounts are moved to so-called "history" > file after they are closed. All opened accounts are in one table. > > Happy New Year to everyone! > > VK > > On Dec 30, 8:12 am, "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 > > > > ... > > > > read more » > > -- > 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
