Yes, this is all true. Also remember that the OP was suggesting that the memory allocated to processing the indexes was not being released after the query ends and that is the thing that needs to be verified or shown to be an error in measuring the memory usage.
If you have indexes with two values say 'Y' and 'N' and the number of 'Y's is relatively small and these are the only ones you will select, then only index the records with a 'Y' as you won't use the 'N' anyway. Look for other such optimizations. It was the case in 4.0 and before that updating an index with a many-to-one relationship was awkward to do. However all you need to do is include the item ID in the index such that you have a unique node for each data point. As Pat points out, things have moved on with indexes since 4.0, so take his advice here on creating the indexes etc. I cannot emphasize too much though that before looking for optimizations at this level, look to your application. A big oversight is processing records in an order that you think is a good idea but actually means nothing at all to the results of processing. For instance, if selecting on currency, do you actually need to process all the records for USD, then all the GBP and so on. If you are processing all the records anyway, then just keep accumulators for data points. Perhaps you need to perform one operation in sorted order but not anything else. Sometimes doing multiple operations at once will be a good idea, sometimes you might repeat the traversal in natural database order and find that it is faster that way. When you have your algorithms together, THEN look to tricks and tuning to improve performance. (That last is of course not overly relevant to the original thread about whether the memory is being leaked or not, but generally relevant to getting things to work well). Jim > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf > Of pat > Sent: Tuesday, January 05, 2010 8:30 AM > To: jBASE > Subject: [SPAM] Re: Performance issue on files with INDEX > > Au Contraire > > Using an Index with a limited number of differing values will reduce > the SELECT time when Selecting a specific value Say for example in the > extreme case of ( only ) four different 'CURRENCIES' within the items > in your file, eg : > > 1 million items with a 'CURRENCY' of 'GBP' > 1 million items with a 'CURRENCY' of 'USD' > 1 million items with a 'CURRENCY' of 'YEN' > and > 1 thousand items with a 'CURRENCY' of 'FF' > > SELECTing the million item ids for items with a 'CURRENCY' of 'USD' > will be instantaneous via the index, compared with processing the > '3,001,000' items looking for a 'CURRENCY' of 'USD' > > The downside is Creating / Updating an Index where the majority of > values for the indexed fielsd are identical > > However in jBASE 4.1 and above, the Creation / Update of an index on > such non unique values is nowhere near as painful / time consuming as > previously in jBASE 4.0 and prior releases > > And is also improved by creating the index on such fields using the '- > w' option > > Pat. > > On 4 Jan, 10:57, Mike Preece <[email protected]> wrote: > > Taking a more simplistic view... it is often counter-productive to > > create and use indexes. This is true in cases where a very high > > proportion of the data on which the index is based is similar. For > > example, suppose the CURRENCY is almost always the same. The index > for > > CURRENCY will be enormous with consequential performance degradation. > > In this case it would be better to do a straight SELECT without > > selection criteria and have a simple piece of code after the READNEXT > > to ignore items with a CURRENCY other than that in which you're > > interested. > > > > Can you tell us the total number of records in the file and the > number > > of variations for each of the indexed attributes? > > > > On Dec 29 2009, 3:14 pm, "[Xze]" <[email protected]> wrote: > > > > > 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 > > > 433M18.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 > > > 519M22.05 1 I /opt/jbase5/bin/jsh -s jsh - (Comm > > > 14 uatusr 274452 7 (6) 201 1 3391 101 1462 0 > > > 315M13.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 > > > 506M22.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 > > > 553M24.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 > > > 314M14.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? > > > > > mw42.zip > > > 100KViewDownload
-- 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
