Don't forget though that you can reduce the impact of an index having just a few values by appending the item ID to the index and adjusting the query accordingly. Also, see Pat's comments on indexes in later versions of jBASE.
Jim > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf > Of Bruce Willmore > Sent: Monday, January 04, 2010 11:21 AM > To: jBASE > Subject: [SPAM] Re: Performance issue on files with INDEX > > Hi everyone: > > Thought I'd chime in on this subject, since it is a pet peeve of mine. > > Experience has taught me that any file that is updated by multiple > concurrent processes is a poor candidate for an index, since the update > to the index can only occur sequentially. That means that if you have > 10 processes all performing updates to FBNK.ACCOUNT at the same time, > then they have to queue waiting to update FBNK.ACCOUNT]I. > > Also, the number of nodes that are being indexed can be a factor. I > see, for instance, that ACCOUNT.OFFICER is indexed in this file. I > assume that number of values that ACCOUNT.OFFICER can take is > relatively small. If that assumption is correct, then the time it takes > to update that index grows as the number of records indexed under that > node grows. > > And lastly, the number of indexes on a file also impacts the amount of > time required to perform updates. I do not know what an "optimal" > number is, but I suspect that 5 is too high, although I could be wrong. > > So, if you have a file with a large number of records that is being > updated by multiple concurrent processes, then I would suggest removing > those indexes entirely. If the file is not exceesively large, then > extracting your dataset via a jBC program would be much more efficient > than trying to use jQL to do the legwork for you. If the file is a > large one, then there are other alternatives, which I won't go into > here, as that discussion could get lengthy, but I would be happy to > share ideas on the subject. > > On Dec 29 2009, 10:14 am, "[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
