Re: Transactions jamming logical logs - INFORMIX 4.0

In article <1nqhprINNndl@emory.mathcs.emory.edu> peb@sandoz (Paul Begley) writes: ...... >Transactions spanning logical logs and 'lock' our On-Line engine. I >recognize that there are features of 4.1 which support high-water marks >for the logs, and you can turn logging off for temp tables, but we >can't migrate to 4.1 until our software provider supports it. This >will be at least 8-12 weeks. Our users run 'ACE reports from hell'. >The worst reports use DISTINCT selects with outer joins on several >tables with 15,000 to 90,000 records. The biggest problem seems to >come from the use of multiple temporary tables. Evaluating the reports >from ISQL with SET EXPLAIN ON showed some costs as high as 29,000. Our >typical report is more on the order of 3,000. When evaluating sqexplain.out, look at not only the estimated cost of queries but also the optimal path chosen for the query. If sequential scans are being used for queries or subqueries, then investigate using different indexing strategies or optimizing the query. ........ >1. Will fewer, larger logs make any difference? (e.g. 4 * 24 Meg logs). Very definitely. In Version 4.0, where you can't specify the LTHWM, when a long transaction starts using the last available logical log it begins its rollback. With 24 * 4MB logs, you have only 4MB to roll back 20MB of work. With 4 * 24MB logs, you have 24MB to roll back 72MB of work. Remember, the rollback record written to the log is small compared to the original, so it takes only a fraction of the space required by the original insert/update/delete to record the rollback. Also keep in mind that in 4.0 other processes continue to write to the logs even while the long transaction is trying to roll back. It becomes a race. If the rollback can't complete before the last log is filled, OnLine locks up. With fewer, larger logs, there's a much better chance the rollback will complete. ___ ___ Consultant, Client Srvcs Engineering / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__> (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 dberg@informix.com The opinions expressed herein are mine alone.