Re: Long Transaction

Well, if i reduce the LTXHWM, isn't it the Longtrx will occur more frequent?? Or i should said i need to increase the number of LTXHWM and LTXEHWM?? below is the output of onstat -l and onstat -p Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 6 days 16:26:22 -- 1686080 Kbytes Physical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 262 320 3622412 18611 194.64 phybegin physize phypos phyused %used 10003f 128000 27285 902 0.70 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-1 23 320 115017224 5668685 58266 20.3 97.3 Subsystem numrecs Log Space used OLDRSAM 115017224 2799032068 address number flags uniqid begin size used %used 16e79138 1 U-B---- 46507 2e5815 10000 10000 100.00 16e79154 2 U-B---- 46508 2e7f25 10000 10000 100.00 16e79170 3 U-B---- 46509 2ea635 10000 10000 100.00 16e7918c 4 U-B---- 46510 2ecd45 10000 10000 100.00 16e791a8 5 U-B---- 46511 2ef455 10000 10000 100.00 16e791c4 6 U-B---- 46512 2f1b65 10000 10000 100.00 16e791e0 7 U-B---- 46513 200035 10000 10000 100.00 16e791fc 8 U-B---- 46514 202745 10000 10000 100.00 16e79218 9 U-B---- 46515 204e55 10000 10000 100.00 16e79234 10 U-B---- 46516 207565 10000 10000 100.00 16e79250 11 U-B---- 46517 209c75 10000 10000 100.00 16e7926c 12 U-B---- 46518 20c385 10000 10000 100.00 16e79288 13 U-B---- 46519 20ea95 10000 10000 100.00 16e792a4 14 U-B---- 46520 2111a5 10000 10000 100.00 16e792c0 15 U-B---- 46521 2138b5 10000 10000 100.00 16e792dc 16 U-B---- 46522 215fc5 10000 10000 100.00 16e792f8 17 U-B---- 46523 2186d5 10000 10000 100.00 16e79314 18 U-B---- 46524 21ade5 10000 10000 100.00 16e79330 19 U-B---- 46525 21d4f5 10000 10000 100.00 16e7934c 20 U-B---- 46526 21fc05 10000 10000 100.00 16e79368 21 U-B---- 46527 222315 10000 10000 100.00 16e79384 22 U-B---- 46528 224a25 10000 10000 100.00 16e793a0 23 U-B---- 46529 227135 10000 10000 100.00 16e793bc 24 U-B---- 46530 229845 10000 10000 100.00 16e793d8 25 U-B---- 46531 22bf55 10000 10000 100.00 16e793f4 26 U-B---- 46532 22e665 10000 10000 100.00 16e79410 27 U-B---- 46533 230d75 10000 10000 100.00 16e7942c 28 U-B---- 46534 233485 10000 10000 100.00 16e79448 29 U-B---- 46535 235b95 10000 10000 100.00 16e79464 30 U-B---- 46536 2382a5 10000 10000 100.00 16e79480 31 U-B---- 46537 23a9b5 10000 10000 100.00 16e7949c 32 U-B---- 46538 23d0c5 10000 10000 100.00 16e794b8 33 U-B---- 46539 23f7d5 10000 10000 100.00 16e794d4 34 U-B---- 46540 241ee5 10000 10000 100.00 16e794f0 35 U-B---- 46541 2445f5 10000 10000 100.00 16e7950c 36 U-B---- 46542 246d05 10000 10000 100.00 16e79528 37 U-B---- 46543 249415 10000 10000 100.00 16e79544 38 U-B---- 46544 24bb25 10000 10000 100.00 16e79560 39 U-B---- 46545 24e235 10000 10000 100.00 16e7957c 40 U-B---- 46546 250945 10000 10000 100.00 16e79598 41 U-B---- 46547 253055 10000 10000 100.00 16e795b4 42 U-B---- 46548 255765 10000 10000 100.00 16e795d0 43 U-B---- 46549 257e75 10000 10000 100.00 16e795ec 44 U-B---- 46550 25a585 10000 10000 100.00 16e79608 45 U-B---- 46551 25cc95 10000 10000 100.00 16e79624 46 U-B---- 46552 25f3a5 10000 10000 100.00 16e79640 47 U-B---- 46553 261ab5 10000 10000 100.00 16e7965c 48 U-B---- 46554 2641c5 10000 10000 100.00 16e79678 49 U-B---- 46555 2668d5 10000 10000 100.00 16e79694 50 U-B---- 46556 268fe5 10000 10000 100.00 16e796b0 51 U-B---- 46557 26b6f5 10000 10000 100.00 16e796cc 52 U-B---- 46558 26de05 10000 10000 100.00 16e796e8 53 U-B---- 46559 270515 10000 10000 100.00 16e79704 54 U-B---- 46560 272c25 10000 10000 100.00 16e79720 55 U-B---- 46561 275335 10000 10000 100.00 16e7973c 56 U-B---- 46562 277a45 10000 10000 100.00 16e79758 57 U-B---- 46563 27a155 10000 10000 100.00 16e79774 58 U-B---- 46564 27c865 10000 10000 100.00 16e79790 59 U-B---- 46565 27ef75 10000 10000 100.00 16e797ac 60 U-B---- 46566 281685 10000 10000 100.00 16e797c8 61 U-B---- 46567 283d95 10000 10000 100.00 16e797e4 62 U-B---- 46568 2864a5 10000 10000 100.00 16e79800 63 U-B---- 46569 288bb5 10000 10000 100.00 16e7981c 64 U-B---- 46570 28b2c5 10000 10000 100.00 16e79838 65 U-B---- 46571 28d9d5 10000 10000 100.00 16e79854 66 U-B---- 46572 2900e5 10000 10000 100.00 16e79870 67 U-B---- 46573 2927f5 10000 10000 100.00 16e7988c 68 U-B---- 46574 294f05 10000 10000 100.00 16e798a8 69 U-B---- 46575 297615 10000 10000 100.00 16e798c4 70 U-B---- 46576 299d25 10000 10000 100.00 16e798e0 71 U-B---- 46577 29c435 10000 10000 100.00 16e798fc 72 U-B---- 46578 29eb45 10000 10000 100.00 16e79918 73 U-B---- 46579 2a1255 10000 10000 100.00 16e79934 74 U-B---- 46580 2a3965 10000 10000 100.00 16e79950 75 U-B---- 46581 2a6075 10000 10000 100.00 16e7996c 76 U-B---- 46582 2a8785 10000 10000 100.00 16e79988 77 U-B---- 46583 2aae95 10000 10000 100.00 16e799a4 78 U-B---- 46584 2ad5a5 10000 10000 100.00 16e799c0 79 U-B---- 46585 2afcb5 10000 10000 100.00 16e799dc 80 U-B---- 46586 2b23c5 10000 10000 100.00 16e799f8 81 U-B---- 46587 2b4ad5 10000 10000 100.00 16e79a14 82 U-B---- 46588 2b71e5 10000 10000 100.00 16e79a30 83 U-B---- 46589 2b98f5 10000 10000 100.00 16e79a4c 84 U-B---- 46590 2bc005 10000 10000 100.00 16e79a68 85 U-B---- 46591 2be715 10000 10000 100.00 16e79a84 86 U-B---- 46592 2c0e25 10000 10000 100.00 16e79aa0 87 U-B---- 46593 2c3535 10000 10000 100.00 16e79abc 88 U-B---- 46594 2c5c45 10000 10000 100.00 16e79ad8 89 U-B---- 46595 2c8355 10000 10000 100.00 16e79af4 90 U-B---- 46596 2caa65 10000 10000 100.00 16e79b10 91 U-B---- 46597 2cd175

In article <8ihgmf$5k$1@news.xmission.com>, Agnes Ng <agnesng@pc.jaring.my> writes > >Well, if i reduce the LTXHWM, isn't it the Longtrx will occur more >frequent?? Or i should said i need to increase the number of LTXHWM and >LTXEHWM?? > Reduce! Increase and you could get a state where the logical logs reach 100% full and the only fix for that is to restore from a backup! A long transaction occurs because:- a) User A does a BEGIN WORK to start a transaction. b) This gets logged to say logical log 10. c) Things get logged to the logical log. Either from the same user or other users. Online cannot reuse logical log 10 until User A either a) COMMITs the transaction b) ROLLBACKs the transaction c) crash/disconnects from online and hence online cleans up after their session by ROLLBACK'ing any uncommitted i.e. open transactions. You could also try small logical logs. >below is the output of onstat -l and onstat -p > > >Informix Dynamic Server Version 7.30.UC5 -- On-Line -- Up 6 days >16:26:22 -- 1686080 Kbytes > >Physical Logging >Buffer bufused bufsize numpages numwrits pages/io > P-2 262 320 3622412 18611 194.64 > phybegin physize phypos phyused %used > 10003f 128000 27285 902 0.70 > >Logical Logging >Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > L-1 23 320 115017224 5668685 58266 20.3 97.3 > Subsystem numrecs Log Space used > OLDRSAM 115017224 2799032068 > >address number flags uniqid begin size used %used >16e79138 1 U-B---- 46507 2e5815 10000 10000 100.00 >16e79154 2 U-B---- 46508 2e7f25 10000 10000 100.00 >16e79170 3 U-B---- 46509 2ea635 10000 10000 100.00 >16e7918c 4 U-B---- 46510 2ecd45 10000 10000 100.00 >16e791a8 5 U-B---- 46511 2ef455 10000 10000 100.00 >16e791c4 6 U-B---- 46512 2f1b65 10000 10000 100.00 >16e791e0 7 U-B---- 46513 200035 10000 10000 100.00 >16e791fc 8 U-B---- 46514 202745 10000 10000 100.00 >16e79218 9 U-B---- 46515 204e55 10000 10000 100.00 >16e79234 10 U-B---- 46516 207565 10000 10000 100.00 >16e79250 11 U-B---- 46517 209c75 10000 10000 100.00 >16e7926c 12 U-B---- 46518 20c385 10000 10000 100.00 >16e79288 13 U-B---- 46519 20ea95 10000 10000 100.00 >16e792a4 14 U-B---- 46520 2111a5 10000 10000 100.00 >16e792c0 15 U-B---- 46521 2138b5 10000 10000 100.00 >16e792dc 16 U-B---- 46522 215fc5 10000 10000 100.00 >16e792f8 17 U-B---- 46523 2186d5 10000 10000 100.00 >16e79314 18 U-B---- 46524 21ade5 10000 10000 100.00 >16e79330 19 U-B---- 46525 21d4f5 10000 10000 100.00 >16e7934c 20 U-B---- 46526 21fc05 10000 10000 100.00 >16e79368 21 U-B---- 46527 222315 10000 10000 100.00 >16e79384 22 U-B---- 46528 224a25 10000 10000 100.00 >16e793a0 23 U-B---- 46529 227135 10000 10000 100.00 >16e793bc 24 U-B---- 46530 229845 10000 10000 100.00 >16e793d8 25 U-B---- 46531 22bf55 10000 10000 100.00 >16e793f4 26 U-B---- 46532 22e665 10000 10000 100.00 >16e79410 27 U-B---- 46533 230d75 10000 10000 100.00 >16e7942c 28 U-B---- 46534 233485 10000 10000 100.00 >16e79448 29 U-B---- 46535 235b95 10000 10000 100.00 >16e79464 30 U-B---- 46536 2382a5 10000 10000 100.00 >16e79480 31 U-B---- 46537 23a9b5 10000 10000 100.00 >16e7949c 32 U-B---- 46538 23d0c5 10000 10000 100.00 >16e794b8 33 U-B---- 46539 23f7d5 10000 10000 100.00 >16e794d4 34 U-B---- 46540 241ee5 10000 10000 100.00 >16e794f0 35 U-B---- 46541 2445f5 10000 10000 100.00 >16e7950c 36 U-B---- 46542 246d05 10000 10000 100.00 >16e79528 37 U-B---- 46543 249415 10000 10000 100.00 >16e79544 38 U-B---- 46544 24bb25 10000 10000 100.00 >16e79560 39 U-B---- 46545 24e235 10000 10000 100.00 >16e7957c 40 U-B---- 46546 250945 10000 10000 100.00 >16e79598 41 U-B---- 46547 253055 10000 10000 100.00 >16e795b4 42 U-B---- 46548 255765 10000 10000 100.00 >16e795d0 43 U-B---- 46549 257e75 10000 10000 100.00 >16e795ec 44 U-B---- 46550 25a585 10000 10000 100.00 >16e79608 45 U-B---- 46551 25cc95 10000 10000 100.00 >16e79624 46 U-B---- 46552 25f3a5 10000 10000 100.00 >16e79640 47 U-B---- 46553 261ab5 10000 10000 100.00 >16e7965c 48 U-B---- 46554 2641c5 10000 10000 100.00 >16e79678 49 U-B---- 46555 2668d5 10000 10000 100.00 >16e79694 50 U-B---- 46556 268fe5 10000 10000 100.00 >16e796b0 51 U-B---- 46557 26b6f5 10000 10000 100.00 >16e796cc 52 U-B---- 46558 26de05 10000 10000 100.00 >16e796e8 53 U-B---- 46559 270515 10000 10000 100.00 >16e79704 54 U-B---- 46560 272c25 10000 10000 100.00 >16e79720 55 U-B---- 46561 275335 10000 10000 100.00 >16e7973c 56 U-B---- 46562 277a45 10000 10000 100.00 >16e79758 57 U-B---- 46563 27a155 10000 10000 100.00 >16e79774 58 U-B---- 46564 27c865 10000 10000 100.00 >16e79790 59 U-B---- 46565 27ef75 10000 10000 100.00 >16e797ac 60 U-B---- 46566 281685 10000 10000 100.00 >16e797c8 61 U-B---- 46567 283d95 10000 10000 100.00 >16e797e4 62 U-B---- 46568 2864a5 10000 10000 100.00 >16e79800 63 U-B---- 46569 288bb5 10000 10000 100.00 >16e7981c 64 U-B---- 46570 28b2c5 10000 10000 100.00 >16e79838 65 U-B---- 46571 28d9d5 10000 10000 100.00 >16e79854 66 U-B---- 46572 2900e5 10000 10000 100.00 >16e79870 67 U-B---- 46573 2927f5 10000 10000 100.00 >16e7988c 68 U-B---- 46574 294f05 10000 10000 100.00 >16e798a8 69 U-B---- 46575 297615 10000 10000 100.00 >16e798c4 70 U-B---- 46576 299d25 10000 10000 100.00 >16e798e0 71 U-B---- 46577 29c435 10000 10000 100.00 >16e798fc 72 U-B---- 46578 29eb45 10000 10000 100.00 >16e79918 73 U-B---- 46579 2a1255 10000 10000 100.00 >16e79934 74 U-B---- 46580 2a3965 10000 10000 100.00 >16e79950 75 U-B---- 46581 2a6075 10000 10000 100.00 >16e7996c 76 U-B---- 46582 2a8785 10000 10000 100.00 >16e79988 77 U-B---- 46583 2aae95 10000 10000 100.00 >16e799a4 78 U-B---- 465

Agnes Ng wrote: > Well, if i reduce the LTXHWM, isn't it the Longtrx will occur more > frequent?? That is correct. > Or i should said i need to increase the number of LTXHWM and > LTXEHWM?? Not advisable. Rollback (of a long transaction) also requires log space. You could run into problems if the Long transaction aborts so late that there isn't enough log space to roll it back. You could try the following to resolve this problem. 1. Examine the Application that's creating the Long transaction. I can not imagine any business application that needs over 1Gb of log space for a transaction. Reprogram the code to COMMIT more often. 2. Increase log space. 2Gb is quite a bit of space. All the same, if you have the typical 9 Gb disks, there's no harm in upping your log space to use the entire disk - after all, one doesn't usually want to place any other object (physical log, data ) on the same disk. 3. If you can not change the code, see if you can fiddle with the data, though this could be tricky. I've encountered business situations where a daily process was forgotten. When, one sunny day, somebody decides to run it, it has to process too much data for the instance to handle. You could change some flags or unload some rows to reduce the amount of data to be processed by the "Long" job. Caution : Only do this if "you know what you are doing". All the best. Rudy