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
