Very Computer

We have had a problem on our production system which is becoming
critical after requiring Informix intervention to free our logical
logs twice last week.  

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.

I am interested in suggestions on reconfiguring the logs or engine
which may help the problem until we upgrade.  System activity is such
that we fill 120 to 200 four megabyte logical logs between Friday
evening and Monday morning while various weekly reports are running.
The system currently has 24 * 4 Meg logs.  We run continuous backup of
logical logs to 8mm tape on weekends and 150 Meg tape daily.  The IBM
8mm tape seems to be very particular about media, and although we have
had no problems with Archives, I have had a failure of one sort or
another every weekend this year.

Questions:

1.      Will fewer, larger logs make any difference? (e.g. 4 * 24 Meg logs).
2.  Have other people had similar problems with ACE reports, and what
did you do to work around this problem?

System configuration:

RS/6000 Model 550 w/64 Meg RAM
AIX 3.15 <gak!>
Informix On-Line 4.00.UH3
Informix SQL 4.00.UH1
Informix 4GL 4.00.UH1
dbspace = 2 X 384 Meg partitions, 64000 pages dbspace free

--

United Engineers & Constructors        Voice:     011-353-21-378811
Ringaskiddy, Ireland                   FAX:       011-353-21-378285  

......

Quote:>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.

........

Quote:>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